Lars Nolden
2ce68cb098
- Move db/schema.sql to supabase/migrations/ as the first supabase CLI migration (manual `db push` only, no automated runner); re-add the gebos_ingest role + grants there since init.sql never re-runs - Add gebos-postgres-passwords oneshot on db-host: syncs role passwords from sops (LoadCredential, journal-safe), makes supabase_admin SUPERUSER and hands the auth schema to supabase_auth_admin to match the upstream supabase/postgres image; add pg_hba rule for 10.0.0.0/8 - Vendor the official docker-compose (studio/kong/auth/rest/meta only, external Postgres, loopback Studio with no Kong dashboard route) plus kong.yml (trimmed) and kong-entrypoint.sh (verbatim); tested: compose config, Kong config parse, migration applied on TimescaleDB pg17 - Document decisions as ADRs 0001-0004 (migrations, passwords, vendored stack, JWT API keys incl. verify/mint procedure) Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2.8 KiB
2.8 KiB
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.confcovers 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/postgresimage ships role attributes our vanilla Postgres lacked:supabase_adminis a SUPERUSER there (Studio/postgres-meta connect as it), andsupabase_auth_adminowns theauthschema (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
postgresOS user (local peer auth); reads the sops-rendered env file via systemdLoadCredential, so the root-owned 0400 file needs no permission widening. - Idempotently syncs:
postgres←postgres_admin_password;supabase_admin/supabase_auth_admin/authenticator← the sharedsupabase_postgres_password(mirroring upstream's singlePOSTGRES_PASSWORDconvention);gebos_ingest←ingester_postgres_password. - Enforces the two upstream attributes:
supabase_admin SUPERUSERandALTER 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
supabaseandingestersecret toggles solely to get those keys into its env file;.sops.yaml's single key group already permits this. pg_hbagainshost 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.yamlmoves to per-prefix rules (noted there).