DB TLS + SCRAM switch-over window

This is the runbook for the owner-scheduled restart window (GSEC Q10 = B): the two internal DB hops — API → PgBouncer and PgBouncer → Postgres — move onto the internal per-service TLS certificates and SCRAM-SHA-256 password auth, in one drained window together with the API image switch.

Everything below is prepared and inert until this window runs:

• CORE/startup/db-tls/ — the compose override (docker-compose.db-tls.yml), the cert/config fragments, the entrypoint wrappers, and rehash-scram.sh.
• CORE/startup/pgbouncer/provision-auth.sh — SCRAM-aware since the prep: its userlist entry is now the plaintext pgbouncer_auth password (the form PgBouncer needs for its own server-side SCRAM handshake; still works against the old md5 Postgres).
• The API already reads POSTGRES_SSLMODE / POSTGRES_SSLROOTCERT (inert R-057 wiring in CORE/API/database.py); this window just sets them.

In plain words

The database side of the house currently talks over the internal Docker network with no encryption and md5 password checks. The certificates for Postgres (postgres-app, my_postgres_db) and PgBouncer (pgbouncer, mod_pgbouncer) were issued at install time and have been auto-renewed since. This window does three things: (1) re-store the long-lived database passwords in the stronger SCRAM format, (2) tell Postgres and PgBouncer to speak TLS on the way to each other, and (3) tell the API to demand TLS and verify the server's identity certificate. If anything misbehaves, removing one compose file (the override) and recreating the containers puts the stack back exactly where it was.

Before the window (prep, no downtime)

cd CORE/startup

# 1. Certs current (idempotent — the watchdog already does this; just confirm):
bash lib/internal-certs.sh "$PWD" renew
openssl verify -CAfile internal-tls/ca-bundle.pem internal-tls/postgres/cert.pem
openssl verify -CAfile internal-tls/ca-bundle.pem internal-tls/pgbouncer/cert.pem
openssl x509 -in internal-tls/postgres/cert.pem -noout -ext subjectAltName
#   expect: DNS:postgres-app, DNS:my_postgres_db, DNS:localhost
openssl x509 -in internal-tls/pgbouncer/cert.pem -noout -ext subjectAltName
#   expect: DNS:pgbouncer, DNS:mod_pgbouncer, DNS:localhost

# 2. Dry-run the SCRAM re-hash (prints role names + booleans only, never
#    passwords/verifiers):
bash db-tls/rehash-scram.sh dry

Pick the mode:

MOD_DB_TLS	pgbouncer → postgres	API connect args
require	server_tls_sslmode=verify-full	POSTGRES_SSLMODE=verify-full
prefer	server_tls_sslmode=prefer	POSTGRES_SSLMODE=prefer

require is the target state. (PgBouncer has no "force TLS on clients" knob; the API's sslmode=verify-full is what makes the client side mandatory, and pg_hba's hostssl-first ordering keeps the DB hop TLS-preferred regardless.)

The window (ordered steps)

All commands from CORE/startup. Set C="docker compose -f docker-compose.yml -f db-tls/docker-compose.db-tls.yml".

0. Pause the agent lanes. The API will be down for ~2–4 minutes; an API down for more than 120 s makes every worker fence its external GPU stacks (compose-stops ha_class=external stacks). Either pause the agent lanes for the window or accept the fences and re-dispatch. Then drain the platform the usual way (dispatch paused; wait for in-flight runs/tasks to finish — do not check after, check before you restart).

1. Re-hash the long-lived roles to SCRAM (while the stack still runs on the old configs — rehash needs no TLS at all):

bash db-tls/rehash-scram.sh dry     # confirm the plan
bash db-tls/rehash-scram.sh apply   # re-hash superuser + pgbouncer_auth,
                                    # set the plaintext userlist entry,
                                    # wire the TLS fragment into postgresql.conf

Confirm from the apply output: scram=true for modTech and pgbouncer_auth, and "userlist.txt set to the plaintext pgbouncer_auth entry". (The plaintext entry is what PgBouncer needs for its own SCRAM server-side handshake — a stored SCRAM verifier can't be used for that.)

2. Flip the install flag (persisted to the startup .env, the same way MOD_FRONT_TLS_MODE persists; the installer flag --db-tls=require does this at fresh installs):

bash -c 'source ./lib/mod-env.sh; update_env_var MOD_DB_TLS require; update_env_var MOD_DB_TLS_SSLMODE verify-full'

3. Restart Postgres with the override (this activates TLS on the server; the wrapper copies the certs in with postgres-owned 0600):

$C --profile all up -d --force-recreate postgres-app
docker ps --filter name=my_postgres_db          # wait: healthy
docker exec my_postgres_db psql -U modTech -d modTech -tAc 'show ssl'            # expect: on
docker exec my_postgres_db psql -U modTech -d modTech -tAc 'show password_encryption'  # expect: scram-sha-256

4. Restart PgBouncer (it verifies Postgres with verify-full):

$C --profile all up -d --force-recreate pgbouncer
docker ps --filter name=mod_pgbouncer           # wait: healthy (its healthcheck
                                               # probes postgres-app directly —
                                               # that is also the DNS-stranding canary)
docker run --rm --network modtex-network postgres:13 \
  psql "postgresql://pgbouncer_auth@pgbouncer:6432/pgbouncer" -c 'SHOW SERVERS'
#   server tls column should show a TLS-verified server (login OK = SCRAM works)

DNS-stranding check: if the API (or the SHOW SERVERS login above) can't reach the DB right after these restarts, PgBouncer may have cached an NXDOMAIN for the postgres-app alias (known failure mode, hardened by dns_nxdomain_ttl=0 but the backstop is still manual): docker restart mod_pgbouncer after my_postgres_db is healthy. Its healthcheck turning healthy is the signal it recovered.

5. Recreate the API (image switch + override; api-background and mod-migrate come along — they share the override):

$C --profile all up -d --force-recreate api api-background   # + the release-override pin if mid-update
curl -fsS http://127.0.0.1:8000/health

6. Verify the hops are actually TLS (all commands read-only, print booleans only):

# Postgres sees TLS sessions for the API:
docker exec my_postgres_db psql -U modTech -d modTech \
  -c "SELECT ssl, version FROM pg_stat_ssl" 
#   every row for the API's pooler+direct connections: ssl=t
# End-to-end through PgBouncer, full verification, SCRAM:
docker run --rm --network modtex-network -v "$PWD/internal-tls/ca-bundle.pem:/ca.pem:ro" \
  postgres:13 sh -c 'psql "host=pgbouncer port=6432 dbname=modTech sslmode=verify-full sslrootcert=/ca.pem" -c "SELECT 1" ' \
  # (supplied with the rotating/known user+password, never printed)
# PgBouncer server-side TLS status:
docker run --rm --network modtex-network postgres:13 \
  psql "postgresql://pgbouncer_auth@pgbouncer:6432/pgbouncer" -c 'SHOW POOLS' -c 'SHOW SERVERS'

7. Restore dispatch and un-pause the agent lanes.

Downtime estimate

Step	Time
drain (wait for in-flight)	0–15 min, usually under 2
Postgres recreate + healthy	~20–40 s
PgBouncer recreate + healthy	~15–30 s
API recreate (incl. image switch)	~40–120 s
API unavailable total	~2–4 min

The stack remains DRAINED for the whole window, so users see queueing, not errors; the hard number that matters is the API-down duration versus the 120 s worker-fence threshold — hence step 0.

ROLLBACK

Reverse order, no data loss (migrations are additive; SCRAM-stored hashes still authenticate under the old md5 pg_hba lines, so rollback never needs to re-hash anything):

1. bash -c 'source ./lib/mod-env.sh; update_env_var MOD_DB_TLS off' (and drop MOD_DB_TLS_SSLMODE).
2. Remove the override from every compose command — i.e. recreate with the plain file:

   docker compose -f docker-compose.yml --profile all up -d --force-recreate \
     api api-background pgbouncer postgres-app

   (PgBouncer goes back to auth_type=md5 from the stock pgbouncer.ini; Postgres back to ssl=off + stock pg_hba.)
3. If the API recreate in the window used a new image, roll the API back to the previous release pin the way update.sh does (write_override_old path / previous digest override).
4. If Postgres itself was recreated with the TLS fragment and would not come back without the override (it will not — the fragment only adds TLS, and the base config file is untouched), a plain recreate restores the stock behaviour; the appended include/hba_file lines in the volume's postgresql.conf can then be deleted by hand if desired:

   docker exec my_postgres_db sh -c \
     'sed -i "/internal-tls/d" "$PGDATA/postgresql.conf"'

5. Verify: show ssl → off; SHOW POOLS on pgbouncer logs in with md5; API /health OK. Resume dispatch.

What stays true afterwards

• Vault dynamic roles are ephemeral and now store SCRAM verifiers automatically (server default) — no per-role action ever again.
• provision-auth.sh re-writes the plaintext userlist.txt entry on every update/install, so future updates can't silently revert the switch (the role's stored hash re-stores as SCRAM automatically from the server default).
• Cert renewal is unchanged: the watchdog renews the leaves; the keys are reused, so identities are stable. After a renewal, nothing needs a restart — the containers re-read the copied files on their next start.