or_app Role — Deployed Environment Cutover

How to move a deployed environment's app deployment onto the or_app database role after the provisioning migration has shipped there. Deployed environment config (Kubernetes, via Duplo) lives outside this repo — this page is the procedure for whoever has access to make that change.

Background

The app's runtime database connection has historically used the same superuser/owner credentials as migrations. A migration provisions or_app: a plain, non-superuser login role with the DML grants the app needs. Provisioning alone changed nothing at the time it shipped — no table's row-level-security policy named or_app yet, so a connection as or_app behaved identically to the owner connection it replaces.

That is no longer true for every table: a follow-up migration puts or_resources under a restrictive, FORCE'd policy naming or_app, so once both that migration and the app's tenant-scope plumbing (app/api/tenancy.py, wired into before_request) are deployed, the cutover below is no longer pure parity for or_resources — cross-tenant reads/writes to that table are enforced at the database. If this environment is cutting over after that migration shipped, deploy the app code (which sets the app.vendor_id GUC) at or before the migration; an or_app connection with no GUC-setting app code fails closed (zero rows) rather than behaving like the owner connection. The cutover below only changes which role the app deployment authenticates as — for any table with no or_app policy yet, that still enables no enforcement.

Prerequisites

• The provisioning migration has been deployed to this environment (its migration job/step ran under the existing owner/admin credentials — that part is unaffected by anything below).
• The environment is running a Flask image whose entrypoint supports the ONRAMP_MIGRATION_DATABASE_USER / ONRAMP_MIGRATION_DATABASE_PASSWORD override (see docker/flask-entrypoint.sh). The entrypoint runs flask db upgrade at every pod start, so without this override a pod flipped to or_app would try to run migrations as a role that cannot CREATE TABLE / CREATE ROLE.
• You have access to the environment's SSM parameters and its Duplo deployment config.

Procedure

1. Deploy the migration. Runs under the environment's existing owner/admin database credentials, same as any other migration. This creates or_app (or re-asserts NOSUPERUSER/NOBYPASSRLS if the role already exists — an existing role's password is never touched, so the rotation in step 2 survives any later re-apply) and grants it schema usage, table DML, and default privileges on future tables/sequences. Default privileges are per-grantor: they cover objects created by the same owner/admin identity that ran this migration, so keep running migrations under that identity (see the caveats below if it ever changes). On first creation the migration seeds or_app's password from the same secret the owner/admin connection uses — treat that as a bootstrap value only, not the long-term password.

2. Set a distinct password for or_app. Do not leave it sharing the owner/admin password beyond the bootstrap step in (1). Generate a new secret, store it in SSM alongside the environment's other database credentials, then apply it to the role directly:

   ALTER ROLE or_app PASSWORD '<new-secret>';

3. Preflight — prove the rotation actually happened. The flip in step 4 is blocked on this check: do not proceed past it while it fails. From a shell with database access, attempt to authenticate as or_app using the owner/admin password:

   PGPASSWORD='<owner-admin-password>' psql -h <db-host> -U or_app -d <db-name> -c 'SELECT 1'

   This MUST be rejected with a password authentication error. If it succeeds, or_app still shares the privileged owner secret — stop and complete step 2 first. (A login attempt is the reliable check; password hashes in pg_authid can't be compared, because SCRAM salts differ even for identical passwords.)

4. Flip the app deployment's identity in Duplo. The Flask image's entrypoint runs flask db upgrade at every pod start before serving, so the flip is two coordinated settings on the serving deployment:

   • ONRAMP_DATABASE_USER → or_app, and the database password env/secret reference → the SSM secret from step 2 (the serving identity);
   • ONRAMP_MIGRATION_DATABASE_USER / ONRAMP_MIGRATION_DATABASE_PASSWORD → the owner/admin credentials the deployment used before the flip. The entrypoint uses these for the boot-time migration step only, and requires them to be set together — a pod with only one of them set fails at boot instead of guessing an identity.

   If the environment also has a separate migration job, leave its config untouched — it keeps the owner/admin credentials. Future migrations (including ones that CREATE TABLE, which or_app cannot do — it has schema USAGE, not CREATE) then always run under a role-capable identity.

5. Verify.

   • New pods pick up the flipped env vars and the app boots without connection errors. The entrypoint logs Running migrations as <owner/admin user>... — confirming the boot-time migration step is on the role-capable identity, not or_app.
   • Confirm from a shell with database access that the active app connections show usename = or_app (e.g. SELECT usename, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY usename;).
   • Exercise a few read and write flows end to end (login, load a project, edit a task). For any table with no or_app policy yet, nothing should differ from before the cutover — this step is confirming parity, not new restrictions. For or_resources specifically (enforced as of the RLS-rollout-2 migration), also confirm: same-vendor resource read/write still works, a cross-vendor resource is not visible, and an ONRAMP_ADMIN session still lists resources across vendors (its scope resolves wider, not to a single tenant).
   • Watch application logs / error tracking for a few minutes for anything permission-shaped (permission denied for schema, permission denied for table) — a sign or_app is missing a grant the app actually needs.

6. Rollback. Flip ONRAMP_DATABASE_USER (and the password reference) on the serving deployment back to the owner/admin identity it used before. The ONRAMP_MIGRATION_DATABASE_* overrides may stay or go — they point at the same owner/admin identity either way. No database-side change is needed — or_app and its grants are harmless to leave in place.

Caveats

• Managed Postgres: CREATE ROLE ... LOGIN requires elevated privileges the migration runner may not have by default on managed Postgres (e.g. rds_superuser on RDS). Confirm the environment's migration-runner credentials have the required privilege before deploying the migration.
• Changing the migration identity: ALTER DEFAULT PRIVILEGES is per-grantor. If an environment ever moves its migrations to a different owner/admin role, tables created by the new identity will NOT inherit or_app's grants until the two ALTER DEFAULT PRIVILEGES ... TO or_app statements from the provisioning migration are re-run as that new identity (plus a one-time GRANT ... ON ALL TABLES/SEQUENCES to cover anything it already created).