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}"

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" /
}