# ADR-0002: Postgres role passwords are synced by a one-shot systemd unit

Date: 2026-07-08
Status: Accepted

## Context

The Supabase services on app-host and the ingester on mqtt-ingest connect to
db-host over TCP and need password auth, but every login role
(`supabase_admin`, `supabase_auth_admin`, `authenticator`, `gebos_ingest`)
was created passwordless.

NixOS deliberately offers **no declarative option** for Postgres passwords:
anything the configuration references lands world-readable in `/nix/store`,
and `initialScript` — the only built-in hook that could set one — runs solely
at first initdb. Passwords are data, not configuration.

Two supporting gaps surfaced at the same time:

* NixOS's default `pg_hba.conf` covers only local sockets and loopback, so
  connections from 10.0.0.0/8 were rejected before password auth even
  started, firewall rule notwithstanding.
* The upstream `supabase/postgres` image ships role *attributes* our vanilla
  Postgres lacked: `supabase_admin` is a SUPERUSER there (Studio/postgres-meta
  connect as it), and `supabase_auth_admin` owns the `auth` schema (GoTrue
  runs its own migrations in it at startup).

## Decision

A oneshot unit, `gebos-postgres-passwords` (`nix/modules/gebos-postgres.nix`),
runs on db-host after `postgresql.service` on every boot/deploy:

* Runs as the `postgres` OS user (local peer auth); reads the sops-rendered
  env file via systemd `LoadCredential`, so the root-owned 0400 file needs no
  permission widening.
* Idempotently syncs: `postgres` ← `postgres_admin_password`;
  `supabase_admin` / `supabase_auth_admin` / `authenticator` ← the shared
  `supabase_postgres_password` (mirroring upstream's single
  `POSTGRES_PASSWORD` convention); `gebos_ingest` ←
  `ingester_postgres_password`.
* Enforces the two upstream attributes: `supabase_admin SUPERUSER` and
  `ALTER SCHEMA auth OWNER TO supabase_auth_admin`.
* Guards every role for existence (tolerates a cluster the schema-v1
  migration hasn't reached) and discards query output
  (`--output=/dev/null`) so passwords never reach the journal.
* db-host enables the `supabase` and `ingester` secret toggles solely to get
  those keys into its env file; `.sops.yaml`'s single key group already
  permits this.
* `pg_hba` gains `host all all 10.0.0.0/8 scram-sha-256`.

## Consequences

* No password ever exists in git, the nix store, or SQL files; rotation is
  `sops nix/secrets/secrets.yaml` + deploy + `systemctl restart
  gebos-postgres-passwords` (the unit does not auto-rerun on secret-content
  changes alone).
* The three Supabase roles share one password by design — per-role passwords
  would diverge from the vendored compose, which interpolates a single
  `${POSTGRES_PASSWORD}` everywhere.
* db-host can decrypt the Supabase JWT/API-key secrets it doesn't strictly
  need; acceptable until `.sops.yaml` moves to per-prefix rules (noted there).