ADR 0009 — Row-Level Security

Date: 2026-06-13 Status: Accepted — DB layer live & verified; runtime cutover staged

Context

Tenant isolation was enforced only at the application layer (where[tenant] filters + Payload access control). At the scale we are targeting (thousands of sub-accounts), a single forgotten filter or a future query bug could expose one tenant's data to another. We want a database-level backstop that makes cross-tenant reads/writes structurally impossible, independent of app code.

The feedback DB user is a superuser with BYPASSRLS, so RLS never applies to it. A non-superuser runtime role is required for RLS to take effect.

Decision

Adopt PostgreSQL Row-Level Security on every tenant-scoped table, keyed on a per-request session variable, with a fail-closed design.

1. Runtime role app_user (NOSUPERUSER, NOBYPASSRLS) — the application connects as this role. feedback remains owner/superuser for migrations. Grants: apps/cms/rls/0002_grants.sql.

2. Policies (apps/cms/rls/0001_enable_rls.sql) on the 17 tenant-scoped tables (contacts, companies, deals, pipelines, services, tasks, appointments, conversations, messages, channels, message_templates, automations, automation_runs, activities, identities, roles, users_tenants). RLS is ENABLEd and FORCEd. The single tenant_isolation policy:

   USING / WITH CHECK (
     current_setting('app.is_platform', true) = 'on'
     OR tenant_id = nullif(current_setting('app.current_tenant', true), '')::int
   )
   

   • No context set ⇒ 0 rows (fail-closed; a wiring bug can never leak, only show empty data).
   • app.current_tenant = <id> ⇒ only that tenant's rows; writes to any other tenant are rejected by WITH CHECK.
   • app.is_platform = 'on' ⇒ platform super-admin sees/writes all tenants.

3. Per-request context must be set transaction-locally (set_config(..., true) / SET LOCAL) so it is discarded at transaction end and can never leak across pooled connections.

Verification (as app_user, via SET ROLE)

Cutover (final, deliberate step)

The DB layer is live. To switch enforcement on for the running app:

1. Wire per-request context: at the start of each authenticated request set app.current_tenant to the caller's active tenant (and app.is_platform=on for platform admins), transaction-locally, on the operation's own connection.
2. Change DATABASE_URI from feedback:... to app_user:... and redeploy.
3. Smoke-test reads/writes per tenant; instant rollback = revert DATABASE_URI.

Because the policy is fail-closed, the worst case of a wiring mistake is empty data (visible, reversible), never a cross-tenant leak.

Consequences

• Cross-tenant data exposure becomes structurally impossible once cut over.
• All app DB access must run with a tenant context (or platform flag); ad-hoc scripts must connect as feedback or set the context explicitly.
• Migrations run as feedback (owner), unaffected by RLS.