Managed Postgres deployment (infra/data)

Last Updated: 2026-06-11

Provision DigitalOcean Managed PostgreSQL for Garage v1 (ADR 09). One cluster serves gateway, ops-api, and PDS authz proxy — same schema, same RLS role (substratum_gateway).

When to run

Before (or in parallel with) infra/app, infra/admin, and infra/pds — application droplets need firewall access via the substratum tag.

1. Provision cluster

Choose a strong password for login role substratum_gateway (you will know and rotate this yourself — not the DO doadmin password). Store it in Pulumi stack config before the first pulumi up:

cd infra/data
npm ci
pulumi stack select data   # or: pulumi stack init data
pulumi config set digitalocean:token --secret
pulumi config set doProjectId cd1eca0e-c563-4501-9896-0c21db8b3b55
pulumi config set gatewayRolePassword '<your-chosen-password>' --secret
pulumi up

Optional sizing (Garage 0–500 users defaults are fine):

pulumi config set size db-s-1vcpu-1gb
pulumi config set nodeCount 1
pulumi config set pgVersion 16

Optional: allow office/VPN CIDR in addition to tagged droplets:

pulumi config set extraTrustedCidrs '["203.0.113.10/32"]' --json
pulumi up

2. Connection details (from stack outputs)

You set gatewayRolePassword once in Pulumi config. Pulumi composes full connection strings in stack state — read them with pulumi stack output (do not hand-assemble URIs on hosts).

DATABASE_ADMIN_URL (DO doadmin) is fully provisioned by the cluster. DATABASE_URL embeds your chosen gateway password plus host/port from the cluster.

Stack output	Env var	Role
databaseAdminUri (alias databaseAdminUrl)	DATABASE_ADMIN_URL	DO doadmin — migrations + bootstrap only
databaseUri (alias databaseUrl)	DATABASE_URL	substratum_gateway — gateway, ops-api, pds-authz-proxy runtime
tranquilPdsUri (alias tranquilPdsDatabaseUrl)	DATABASE_URL on PDS droplet	tranquil_pds — Tranquil PDS repo store (separate DB)
postgresPassword	POSTGRES_PASSWORD	Echo of your gatewayRolePassword config — pass to bootstrap_database

pulumi stack output databaseHost
pulumi stack output databasePort
pulumi stack output databaseName
pulumi stack output databaseAdminUri --show-secrets
pulumi stack output databaseUri --show-secrets
pulumi stack output tranquilPdsUri --show-secrets
pulumi stack output postgresPassword --show-secrets

Export for bootstrap and service wiring:

export DATABASE_ADMIN_URL="$(pulumi stack output databaseAdminUri --show-secrets)"
export DATABASE_URL="$(pulumi stack output databaseUri --show-secrets)"
export POSTGRES_PASSWORD="$(pulumi stack output postgresPassword --show-secrets)"

After data.yml runs, read the same outputs from the data stack in Pulumi Cloud (or re-run the commands above locally with PULUMI_ACCESS_TOKEN).

3. Bootstrap schema + substratum_gateway role

Run migrations once from a trusted host (app droplet, CI, or laptop allowed by firewall). Use stack outputs for all three env vars:

cd infra/data
export DATABASE_ADMIN_URL="$(pulumi stack output databaseAdminUri --show-secrets)"
export POSTGRES_PASSWORD="$(pulumi stack output postgresPassword --show-secrets)"

# One-off bootstrap (gateway or ops-api binary)
cargo run -p substratum-gateway
# exits after bootstrap if you only need migrations — or use ops-api which also calls bootstrap_database on start

On startup, bootstrap_database applies SeaORM migrations and creates login role substratum_gateway with RLS-safe grants when POSTGRES_PASSWORD is set and the admin connection is privileged.

Tranquil PDS: pulumi up creates database tranquil_pds and role tranquil_pds on the same cluster. Spindle data.yml step 3 runs bootstrap-worker.sh (ephemeral substratum-tagged droplet → grants → destroy). verify-data.sh only validates stack outputs.

4. Wire application services

Use the same DATABASE_URL everywhere (gateway, ops-api, pds-authz-proxy):

export DATABASE_URL="$(pulumi stack output databaseUri --show-secrets)"

Service	Also needs
Gateway	PUBLIC_BASE_URL, swarm secrets, blockstore — app deployment
ops-api	PUBLIC_BASE_URL = admin.* — admin deployment
pds-authz-proxy	Entitlement lookups only — PDS deployment
Tranquil PDS	DATABASE_URL=<tranquilPdsUri> — PDS deployment

Never point runtime traffic at DATABASE_ADMIN_URL — superuser/doadmin bypasses RLS (crates/resolution/AGENTS.md).

5. Verify

# From an allowed host (tagged droplet or extraTrustedCidr)
psql "$DATABASE_ADMIN_URL" -c '\dt'
psql "$DATABASE_URL" -c 'SELECT current_user'
# expect: substratum_gateway

Gateway smoke: GET /api/v1/health after app edge is wired.

Backups (automatic)

DigitalOcean Managed PostgreSQL includes daily automatic backups with 7-day retention at no extra charge — this covers both substratum and tranquil_pds databases on the cluster.

• Verify: DO control panel → Databases → substratum-garage → Backups
• Restore: DO console → restore to a new cluster or fork — update DATABASE_URL / tranquilPdsUri stack outputs and redeploy services (PDS deployment)
• Do not rely on PDS droplet automated backups or block volume snapshots — customer repo state is here and in Spaces only

Long-term retention beyond 7 days requires operator-run logical dumps (pg_dump) to external storage — out of scope for Garage v1.

Rotate gateway role password

1. Choose a new password.
2. Update Pulumi config (and Spindle POSTGRES_PASSWORD if using data.yml):

   pulumi config set gatewayRolePassword '<new-password>' --secret
   pulumi up   # refreshes databaseUri / postgresPassword stack outputs

3. Re-run bootstrap from an allowed host with stack outputs (DATABASE_ADMIN_URL + new POSTGRES_PASSWORD) so Postgres role password matches.
4. Restart gateway, ops-api, and pds-authz-proxy with the new DATABASE_URL from stack output.

CI (Spindle)

.tangled/workflows/data.yml runs pulumi up on infra/data changes, then verifies stack outputs including databaseAdminUri and databaseUri. It does not run migrations — bootstrap remains an explicit operator step after first provision.

Secret	Required	Notes
PULUMI_ACCESS_TOKEN	yes	Pulumi Cloud
DIGITALOCEAN_TOKEN	yes	DO API
POSTGRES_PASSWORD	yes	Your substratum_gateway password — synced to gatewayRolePassword stack config before pulumi up
DIGITALOCEAN_PROJECT_ID	no	Defaults to production DO project UUID

Not Spindle secrets: DATABASE_ADMIN_URL and DATABASE_URL — read from data stack outputs after pulumi up (same pattern as deploy SSH keys from edge stacks).

Troubleshooting

Symptom	Fix
Connection timeout from droplet	Confirm droplet has tag substratum; re-run pulumi up on app/admin/pds stacks
password authentication failed for gateway role	Re-run bootstrap with stack DATABASE_ADMIN_URL + POSTGRES_PASSWORD outputs
RLS not enforced	DATABASE_URL must use substratum_gateway, not doadmin
SSL required	Always append ?sslmode=require on managed DO connections

Related

• infra/data/AGENTS.md
• Garage v1 rollout
• Production deployment