platform/scripts/setup/officer-setup/lib/postgres.sh

#!/bin/bash
# =============================================================================
# officer-setup — Postgres
# =============================================================================
#
# Definitions only.
#
# ── Only Postgres ──
#
# The original offered five containers. Of those, Redis and SearXNG are not
# referenced anywhere in the platform — no import, no environment variable, no
# mention — and Nginx Proxy Manager is a deployment choice rather than something
# a setup script should pick. Mailhog is a development convenience and is offered
# separately.
#
# Postgres is the only one Officer cannot run without: it is the single database,
# holding the account, passkeys, settings, dashboards, email accounts and the
# queue.
#
# ── Where it goes ──
#
# $OFFICER_ROOT/dockers/postgres/, which is the same convention the app store
# uses for anything it provisions: one directory per service, the compose file
# inside it, and RELATIVE bind mounts so the data sits beside the compose file
# where both a human and the platform can find it.

[[ -n "${OFFICER_SETUP_POSTGRES_LOADED:-}" ]] && return 0
OFFICER_SETUP_POSTGRES_LOADED=1

# One network for everything Officer provisions, so containers can reach each
# other by name. Postgres needs nothing from it today — the platform is a host
# process and reaches it over loopback — but a reverse proxy in front of the web
# UI, or any app-store service that talks to another, does. Creating it now means
# the later ones do not have to migrate onto it.
OFFICER_NETWORK="${OFFICER_NETWORK:-officerdev}"

PG_IMAGE="${PG_IMAGE:-postgres:18-alpine}"
PG_DATABASE="${PG_DATABASE:-officer}"
PG_CONTAINER="${PG_CONTAINER:-officer-postgres}"
PG_PORT="${PG_PORT:-5432}"

# ── The CLIENT, on the host, matching the server in the container ──
#
# `psql` was on no install. The server runs in Docker, so nothing ever put a client on the
# host, and `docker exec officer-postgres psql` is not a substitute for a member: they have
# their own Postgres role (`provisionPostgresRole` for Developers) and no access to the
# owner's Docker socket.
#
# The version is derived from PG_IMAGE rather than typed again, because the pairing is not
# cosmetic: **pg_dump refuses a server newer than itself** ("server version 18.6, pg_dump
# version 16.x — aborting"). Ubuntu 24.04 ships client 16 against this 18 server, so the
# archive package is not merely old, it is unusable for dumps. That is also why this lives
# beside the server definition rather than in machine-setup's package list — one constant,
# one place to bump.
pg_client_major() { sed -E 's/^postgres:([0-9]+).*/\1/' <<<"$PG_IMAGE"; }

pg_client_installed() {
  command -v psql >/dev/null 2>&1 && [[ "$(psql --version | grep -oE '[0-9]+' | head -1)" == "$(pg_client_major)" ]]
}

# PGDG, added the same way docker.sh adds Docker's: key to its own file, one sources.list.d
# entry, no add-apt-repository. Non-fatal — an install without psql is a working platform,
# just a more annoying one to operate.
install_pg_client() {
  local major codename
  major="$(pg_client_major)"
  [[ -n "$major" ]] || {
    warn "could not read a major version out of PG_IMAGE=${PG_IMAGE} — skipping the client"
    return 1
  }

  if pg_client_installed; then
    ok "psql ${major} already installed"
    return 0
  fi

  codename="$(. /etc/os-release && echo "${VERSION_CODENAME:-}")"
  [[ -n "$codename" ]] || {
    warn "could not work out this release's codename — cannot add the PostgreSQL repository"
    return 1
  }

  install -d -m 0755 /usr/share/postgresql-common/pgdg
  curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
    -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc || {
    warn "could not fetch the PostgreSQL signing key"
    return 1
  }
  chmod a+r /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
  echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt ${codename}-pgdg main" \
    >/etc/apt/sources.list.d/pgdg.list

  DEBIAN_FRONTEND=noninteractive NEEDRESTART_MODE=a apt-get update -qq || true
  DEBIAN_FRONTEND=noninteractive NEEDRESTART_MODE=a apt-get install -y -qq "postgresql-client-${major}" || {
    warn "postgresql-client-${major} did not install"
    return 1
  }

  # The exit status is not the gate — same lesson as rootless Docker and the claude CLI: what
  # matters is whether the binary is there AND is the version we asked for, because apt can
  # succeed while holding an older client back.
  pg_client_installed || {
    warn "psql is not version ${major} after installing — check: apt-cache policy postgresql-client-${major}"
    return 1
  }
  ok "psql $(psql --version | grep -oE '[0-9]+\.[0-9]+' | head -1) installed for every account on this machine"
}

docker_network_exists() { docker network inspect "$OFFICER_NETWORK" &>/dev/null; }
ensure_docker_network() {
  docker_network_exists && return 1
  docker network create "$OFFICER_NETWORK" >/dev/null 2>&1
}

pg_service_dir() { echo "${OFFICER_ROOT}/dockers/postgres"; }
pg_compose_file() { echo "$(pg_service_dir)/docker-compose.yaml"; }
pg_env_file() { echo "$(pg_service_dir)/.env"; }

pg_compose_exists() { [[ -f "$(pg_compose_file)" ]]; }
pg_container_running() { docker ps --filter "name=^${PG_CONTAINER}$" --format '{{.Names}}' 2>/dev/null | grep -q .; }

# Is something already answering on the port? A Postgres the user runs their own
# way is a perfectly good answer, and finding out by failing to bind is not.
pg_port_in_use() { ss -ltn 2>/dev/null | grep -qE "127\.0\.0\.1:${PG_PORT}\b|\*:${PG_PORT}\b|0\.0\.0\.0:${PG_PORT}\b"; }

# Bound to loopback, deliberately, and the reason is worth keeping next to the
# line it explains.
#
# Publishing a port makes Docker write its own DNAT and ACCEPT rules into
# iptables, and those are evaluated BEFORE ufw sees the packet. So `ports:
# "5432:5432"` is reachable from the internet while `ufw status` reports
# everything denied. Binding to 127.0.0.1 sidesteps it entirely: the DNAT rule
# only matches traffic arriving on loopback.
#
# Loopback is not the whole story, though, and the password is not decoration.
# Every account ON this machine can open 127.0.0.1:5432 — including the per-user
# Linux accounts Officer gives its members. What stops them is that they cannot
# authenticate. The password is the boundary between the platform and anyone
# with a login here, which is why it is random and why both files holding it are
# 0600.
write_pg_compose() {
  local password="$1" dir
  dir="$(pg_service_dir)"
  install -d -m 0755 -o "$USERNAME" -g "$(user_group)" "$dir"

  cat >"$(pg_compose_file)" <<COMPOSE
# Written by officer-setup. Officer's database.
#
# The port is bound to 127.0.0.1 on purpose. Docker publishes ports by writing
# iptables rules beneath ufw, so "5432:5432" would be reachable from the internet
# whatever the firewall reports. The platform runs on this machine, so loopback
# is all it needs.
services:
  postgres:
    image: ${PG_IMAGE}
    container_name: ${PG_CONTAINER}
    restart: unless-stopped
    ports:
      - "127.0.0.1:${PG_PORT}:5432"
    environment:
      POSTGRES_PASSWORD: \${POSTGRES_PASSWORD}
      POSTGRES_DB: ${PG_DATABASE}
      PGDATA: /var/lib/postgresql/data
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./dumps:/dumps
    networks:
      - ${OFFICER_NETWORK}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 10

networks:
  ${OFFICER_NETWORK}:
    external: true
COMPOSE

  # The password lives beside the compose file rather than inside it, so the
  # compose file can be read, copied or committed without carrying a credential.
  umask 077
  cat >"$(pg_env_file)" <<ENVF
# Written by officer-setup. Read by docker compose from this directory.
POSTGRES_PASSWORD=${password}
ENVF
  chown "${USERNAME}:$(user_group)" "$(pg_compose_file)" "$(pg_env_file)"
  chmod 600 "$(pg_env_file)"
  return 0
}

pg_password_from_env_file() {
  [[ -r "$(pg_env_file)" ]] || return 1
  awk -F= '/^POSTGRES_PASSWORD=/ { print substr($0, index($0, "=") + 1); exit }' "$(pg_env_file)"
}

pg_compose_up() { as_owner "docker compose --project-directory '$(pg_service_dir)' up -d" /; }

# Wait for it to answer, rather than assuming `up -d` means ready. Postgres
# initialises its data directory on first start, which takes several seconds, and
# everything after this — db:push especially — fails confusingly against a
# database that is still starting.
pg_wait_ready() {
  local tries="${1:-30}"
  while ((tries-- > 0)); do
    docker exec "$PG_CONTAINER" pg_isready -U postgres >/dev/null 2>&1 && return 0
    sleep 1
  done
  return 1
}

pg_url() { echo "postgresql://postgres:${1}@127.0.0.1:${PG_PORT}/${PG_DATABASE}"; }

# Does this URL actually answer? Asked of any URL, provisioned or given, because
# a database nobody can reach is the failure that makes every later section look
# broken for its own reasons.
pg_url_works() {
  local url="$1"
  as_owner "docker run --rm --network host ${PG_IMAGE} psql '${url}' -c 'select 1' >/dev/null 2>&1" /
}