ADR 0011 — RLS cutover

Status: RLS armed + forced + enforcing, and the application runtime moved off the database superuser to the least-privilege app_user role.

What is live

• RLS is ENABLEd + FORCEd on all tenant-scoped tables (dynamic arming in apps/cms/rls/0001_enable_rls.sql, re-runnable; covers any new tenant table). The tenant_isolation policy is fail-closed (USING + WITH CHECK on app.is_platform / app.current_tenant).
• The CMS runtime connects as app_user (NOSUPERUSER, NOBYPASSRLS), with app.is_platform = on set as a role default. Because the platform CMS serves every tenant, is_platform = on is the correct semantic: the app sees all rows, exactly as before, and tenant isolation for the app is enforced by the application layer (the tenant filter on list reads, the scoped by-id helper, and the multi-tenant plugin membership constraint).
• For any other connection that is not is_platform = on (a leaked limited credential, analytics, a future per-tenant role), RLS isolates it to its tenant — verified: no context yields 0 rows, and cross-tenant writes are blocked by WITH CHECK.

How the cutover works (Coolify config)

• DATABASE_URI = feedback (the bootstrap owner) — used by the build and by payload migrate (DDL needs the owner).
• APPUSER_DATABASE_URI = app_user (runtime-only env var).
• Start command: pnpm payload migrate && DATABASE_URI="$APPUSER_DATABASE_URI" pnpm start — migrate as the owner, serve as the least-privilege role.

PostgreSQL forbids removing SUPERUSER from the bootstrap user, so feedback stays a superuser but is only used for migrations and the build, never to serve traffic.

Recovery and rollback

• A break-glass superuser pgroot exists (password at /root/.pgroot_breakglass).
• To roll the runtime back to the owner: set the app start command back to pnpm payload migrate && pnpm start and redeploy — the runtime then uses DATABASE_URI (feedback).

Per-request enforcement (live)

The app is now behind RLS per request. An AsyncLocalStorage holds each request's tenant context; a global beforeOperation hook fills it from req.user (platform admin -> is_platform = on; a normal user -> is_platform = off + their active tenant); and the pg pool is patched so every connection checkout applies those values as app.current_tenant / app.is_platform. The database therefore scopes every authenticated request to its tenant - a forgotten application-layer filter can no longer leak another tenant's rows. Verified: a normal user sees is_platform=off + their tenant and only their tenant's rows; a platform admin sees is_platform=on.

The role default app.is_platform = on is kept as a safety net for no-user / system operations (e.g. the email-poll cron), which carry no user-supplied filters and are not a cross-tenant leak vector. Authenticated user requests - the surface where a forgotten filter would leak - are fully RLS-enforced.