Runbook: provision the dedicated cuppy Postgres cluster

Status: PENDING OPERATOR. This is item 5 of EPIC-PLAN.md §1. CUP-DEV-STD-001 §5 puts cluster provisioning, role creation and secrets in the operator's hand; nothing below has been applied. Prepared by FEAT-009 (sprint S1).

Why a second cluster and not a second database. F3: "run a second Postgres cluster for cuppy alone … with its own data directory, superuser, pg_hba, and backup schedule, bound to localhost. Nothing from datashield_gtm or the other apps shares it." Sizing (shared_buffers, work_mem) is then for cuppy's workload instead of competing with the Library's cluster, and backups go to a separate target so one restore never touches the other.

Preconditions, all currently true on this host

	checked
PostgreSQL 16 server binaries at /usr/lib/postgresql/16/bin	yes, 16.13
pg_createcluster on PATH	yes, /usr/bin/pg_createcluster
pgvector installed, so CREATE EXTENSION vector needs no package work	yes, 0.6.0
ltree, pgcrypto installed	yes, 1.2 and 1.3
port 5433 free	confirm with `ss -ltnp \	grep 5433` before starting

pgvector was the open question this runbook was written to answer. It is present, so there is no operator package item — only the cluster, the roles, the extensions and pgBackRest.

1. Create the cluster

sudo pg_createcluster 16 cuppy --port 5433 --start-conf manual

Then edit /etc/postgresql/16/cuppy/postgresql.conf. Only these lines differ from the package default; every number is the operator's to tune, and none of them is read by the application:

listen_addresses = '127.0.0.1'      # F3: bound to localhost
port = 5433
shared_buffers = 1GB                # sized for cuppy alone, not shared with datashield_gtm
work_mem = 16MB
maintenance_work_mem = 256MB
effective_cache_size = 3GB
wal_level = replica                 # pgBackRest needs replica or logical
archive_mode = on
archive_command = 'pgbackrest --stanza=cuppy archive-push %p'
max_wal_size = 4GB
log_min_duration_statement = 1000

/etc/postgresql/16/cuppy/pg_hba.conf — loopback only, scram on TCP, peer on the socket so the bootstrap superuser needs no stored password:

local   all   postgres                 peer
host    all   all        127.0.0.1/32  scram-sha-256
host    all   all        ::1/128       scram-sha-256

sudo pg_ctlcluster 16 cuppy start
sudo systemctl enable postgresql@16-cuppy      # if the cluster should come back on boot

2. Roles, extensions and the database

One script, the same one the developer cluster runs, so dev and production cannot drift: scripts/db/bootstrap-roles.sql. It creates cuppy_rls (nologin), the three login roles, the database owned by cuppy_migrate, the extensions schema with vector, ltree and pgcrypto, the per-role search_path, and it asserts at the end that no non-bootstrap role holds BYPASSRLS.

Generate three passwords and keep them out of the shell history and out of git:

APP_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')
JOBS_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')
MIG_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')

sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d postgres \
  -v ON_ERROR_STOP=1 -f scripts/db/bootstrap-roles.sql \
  -v cuppy_db=cuppy \
  -v role_app=cuppy_app -v role_jobs=cuppy_jobs \
  -v role_migrate=cuppy_migrate -v role_rls=cuppy_rls \
  -v app_password="$APP_PW" -v jobs_password="$JOBS_PW" -v migrate_password="$MIG_PW"

The script prints bootstrap complete with the database name and the extension list.

The role matrix it creates (V3):

role	connects	owns	may
cuppy_app	yes	nothing	DML on application tables, under RLS, with the V3 column grants. No DDL, no SET ROLE
cuppy_jobs	yes	nothing	what the *_jobs_all policies permit, plus the topology reads
cuppy_migrate	yes	every table cuppy_rls does not, FORCE on, so the owner is filtered too	DDL only, under one advisory lock
cuppy_rls	no	hubs, hub_members, hub_grants, org_members, tenant_members, tenants (RLS on, FORCE off) plus breakglass_tickets and every app_* function	nothing on its own. It is the identity the SECURITY DEFINER bodies run as

cuppy_migrate is granted membership in cuppy_rls, which it needs to hand that role ownership of the six tables and the functions. It is NOINHERIT, so a migration takes those privileges only with an explicit SET LOCAL ROLE.

No role in the cluster has BYPASSRLS, cuppy_migrate included. The bootstrap superuser exists for CREATE EXTENSION and role creation and appears in no connection string. Z3 refuses to boot unless SELECT count(*) FROM pg_roles WHERE rolbypassrls AND rolname <> 'postgres' returns zero.

3. Connection settings for the application

Into the vault, and into the service's environment as these names. Never as DATABASE_URL: the ecosystem file pins that variable to datashield_gtm, and the connection module refuses a DSN naming that database, port 5432, a non-loopback host, or any role but the three (CLAUDE.md rule 3).

CUPPY_DATABASE_URL=postgresql://cuppy_app:<APP_PW>@127.0.0.1:5433/cuppy
CUPPY_JOBS_DATABASE_URL=postgresql://cuppy_jobs:<JOBS_PW>@127.0.0.1:5433/cuppy
CUPPY_MIGRATE_DATABASE_URL=postgresql://cuppy_migrate:<MIG_PW>@127.0.0.1:5433/cuppy

Secrets rotation is operator-hand (CUP-DEV-STD-001 §5). Re-running bootstrap-roles.sql with new passwords is the rotation path; it is idempotent.

4. Apply the migrations

cd apps/api
CUPPY_MIGRATE_DATABASE_URL='postgresql://cuppy_migrate:<MIG_PW>@127.0.0.1:5433/cuppy' \
  npx tsx src/scripts/migrate.ts

Each file runs in one transaction holding pg_advisory_xact_lock(1685026937) — V3's key — and the run holds pg_advisory_lock on the same session, so the backfill dance V3 describes (NO FORCE … backfill … FORCE) is under the lock as specified. The runner records each filename with a sha256 and refuses to proceed if a landed migration's file has changed. --test adds the gate-test fixtures and must not be used on production: it creates rls_probe* tables.

Expected output on a fresh cluster: applied 14, already present 0.

5. pgBackRest (Z9)

Z9: "a daily base backup, continuous WAL archiving to object storage, fourteen-day point-in-time recovery, thirty-five-day retention, a monthly restore drill into a scratch cluster recorded in the ledger."

/etc/pgbackrest/pgbackrest.conf — a separate repository path from the existing cluster's, which is the F3 requirement that one restore never touches the other:

[cuppy]
pg1-path=/var/lib/postgresql/16/cuppy
pg1-port=5433

[global]
repo1-path=/var/lib/pgbackrest/cuppy
repo1-retention-full=5
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<generate, into the vault>
start-fast=y
compress-type=zst
archive-async=y
spool-path=/var/spool/pgbackrest
log-level-console=info
log-level-file=detail

sudo -u postgres pgbackrest --stanza=cuppy stanza-create
sudo -u postgres pgbackrest --stanza=cuppy check          # verifies archiving works
sudo -u postgres pgbackrest --stanza=cuppy --type=full backup

Schedule, as postgres:

15 2 * * 0   pgbackrest --stanza=cuppy --type=full backup
15 2 * * 1-6 pgbackrest --stanza=cuppy --type=diff backup

Retention: repo1-retention-full=5 weekly fulls plus continuous WAL gives more than the thirty-five days Z9 asks for and more than fourteen days of PITR. Adjust repo1-retention-* if the object-storage target changes; the numbers above are the operator's, not the application's.

The monthly restore drill, recorded in tracking/metrics/ledger.jsonl:

sudo -u postgres pgbackrest --stanza=cuppy --delta \
  --pg1-path=/var/lib/postgresql/16/drill restore
# start the scratch cluster on a free port, then:
#   select count(*) from hubs;  select max(applied_at) from schema_migrations;

Until the cutover, scripts/backup-sqlite.sh keeps the nightly .backup of storage/app.db with seven copies (Z9's interim half, FEAT-005).

6. Verify, and what to paste back

sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c "select version()"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select extname, extversion, extnamespace::regnamespace from pg_extension order by 1"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select rolname, rolcanlogin, rolbypassrls from pg_roles where rolname like 'cuppy%' order by 1"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select c.relname, c.relrowsecurity, c.relforcerowsecurity, c.relowner::regrole
     from pg_class c join pg_namespace n on n.oid = c.relnamespace
    where n.nspname = 'public' and c.relkind in ('r','p') order by 1"
sudo -u postgres pgbackrest --stanza=cuppy info

Four things must be true before S1 closes, and the outputs above are the evidence (CUP-DEV-STD-001 R5 — "verified" as free text is banned):

1. vector, ltree and pgcrypto are installed in the extensions schema.
2. rolbypassrls is false for every cuppy* role.
3. relforcerowsecurity is false on exactly the six tables whose owner is cuppy_rls,

and true on every other RLS table.

1. pgbackrest info shows one full backup and WAL archiving ok.

Rollback

sudo pg_ctlcluster 16 cuppy stop
sudo pg_dropcluster 16 cuppy --stop
sudo rm -rf /var/lib/pgbackrest/cuppy

Nothing on the shared cluster is touched by any step in this runbook, and no application reads the cuppy DSNs until they are set, so a rollback before step 3 is invisible to production.