Application map / Runbooks / RLS activation

RLS Activation Runbook

Switching the app's request path from the owner database connection onto the restricted gallopify_app role, so row-level security actually filters.

How the switch works

A table owner bypasses RLS unconditionally, so the policies merged long ago have always been inert on the owner connection. The code is fully prepared:

• The restricted role gallopify_app (login, no createdb/createrole, and by Postgres default no superuser/bypassrls) is created idempotently by migration, with least-privilege grants audited against every request-path call site.
• The app has two engines: raw sessions always use the owner DATABASE_URL (migrations too — the migration runner never reads the app URL, so a migration can never run as the restricted role), while RLS-scoped request sessions use a separate restricted-role engine only when APP_DATABASE_URL is set and differs, injecting the verified JWT claims into the session before yielding.
• FORCE was investigated and confirmed unnecessary: the restricted role is neither owner nor bypassrls, so plain ENABLE fully applies to it.
• The Doppler secret APP_DATABASE_URL is therefore the only real activation gate.

What the restricted role may touch

GRANT USAGE ON SCHEMA public TO gallopify_app;
GRANT SELECT, INSERT, UPDATE         ON properties         TO gallopify_app;
GRANT SELECT                         ON investments        TO gallopify_app;
GRANT SELECT                         ON investments_catalog TO gallopify_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON upcoming_cashflows TO gallopify_app;
GRANT SELECT                         ON transactions       TO gallopify_app;
GRANT SELECT                         ON management_requests TO gallopify_app;

Deliberately not granted: the migration-version table, sequences (all PKs are generated UUIDs), default privileges (a new table gets zero restricted-role access until its own migration makes a deliberate grant decision — safe by default), and anything DDL-adjacent or role-escalating. notifications and offboarding_requests intentionally have no grants and no policies: they are reached only through backend-owned owner sessions whose services enforce recipient/initiator/approver checks — the safe-by-default posture working as designed.

Activation steps (per environment: dev → staging → production)

1. Apply migrations normally (owner connection). This creates the role and grants; nothing changes yet because nothing connects as it. On Neon the migration tolerates the non-superuser owner.
2. Set the role's password out-of-band against that environment's database, as the owner (e.g. Neon console SQL editor — never in a migration, never committed): ALTER ROLE gallopify_app PASSWORD '<freshly generated>'. (Neon assigns every role a control-plane password; this replaces it with one you control.)
3. Build the connection string — same host/database as the owner URL, swapping only the credentials — and store it as a new Doppler secret in that environment's backend config, never reusing the owner secret's name.
4. Set APP_DATABASE_URL in that Doppler config and redeploy/restart (settings are cached per process; a running process won't see the change). Raw owner traffic keeps using DATABASE_URL; only RLS-scoped request sessions move to the restricted role.
5. Verify it is live: hit the health endpoint, then exercise one real cross-org request with a real cross-org account and confirm it 404s or empty-lists rather than leaking rows — the end-to-end proof that enforcement is doing something. A quick psql double-check: SET ROLE gallopify_app; SELECT count(*) FROM properties; should return 0 with no claims set (fail closed).
6. Rollback is unsetting APP_DATABASE_URL and redeploying — RLS sessions immediately fall back to the owner engine. Never roll back by downgrading the migration in a live environment; that would drop the role out from under live connection pools.

Notes that keep this honest under Neon

• The Neon owner is not a superuser: it cannot set (or even explicitly unset) superuser/bypassrls/replication on any role, so the migration relies on Postgres defaults and best-effort strips attributes on pre-existing roles.
• Tests already prove the semantics on real Postgres: same-org caller sees its property, cross-org caller sees nothing, platform admin sees everything, a claims-less session sees nothing, grants match the audited set exactly, and — the discriminating control — the owner connection still bypasses RLS unconditionally.
• The application/SQL predicate pair is kept in lockstep by a parity test running shared fixtures against both forms in CI.