Row-level security isolates Organizations

Status: accepted · Date: 2026-10-16

Enforcement: packages/db/src/rls-catalog.test.ts, db:verify-rls at the end of db:apply-pending, the rls-matrix CI job, scripts/check-system-call-sites.mjs in pnpm lint, and the Hyperdrive and production-config checks in the deploy workflow.

Until now, tenant isolation (ADR-0003) lived only in application filters: every query carried where organizationId = …. One forgotten filter would leak another Organization's rows. PostgreSQL row-level security now enforces the same boundary in the database, and the filters stay as defence in depth.

Postgres is generic; Neon is only a host

The design uses standard PostgreSQL and nothing host-specific: no neondb_owner, no drizzle-orm/neon (authUid, crudPolicy), no Neon roles or extensions. It works the same on local Docker or Homebrew Postgres, Neon, Supabase, RDS or self-hosted servers, through Hyperdrive or a direct pg connection from DATABASE_URL. Migrations never name the connection role; they use CURRENT_USER.

Minimum version: PostgreSQL 16. The connection role is granted app_tenant WITH INHERIT FALSE, SET TRUE, which is PostgreSQL 16 syntax. The foundation migration checks server_version_num and stops with a clear message on 15 or older. A fallback for 14/15 (a plain GRANT, which makes the role inherit app_tenant) was rejected: inheritance would let app_tenant's policies apply to the connection role and would leave a second, untested code path. Every current managed host offers 16 or newer.

Decision

Tenant traffic runs as app_tenant. It is NOLOGIN NOINHERIT NOBYPASSRLS, owns nothing, and holds exactly the privileges in packages/db/scripts/rls-baseline.mjs (UPDATE only on the columns tenant code writes, so authorship cannot be reassigned). A tenant statement runs in a transaction that sets role to app_tenant and app.user_id / app.org_id with set_config(…, true), so nothing survives COMMIT on a pooled connection (Hyperdrive runs in transaction mode).

Policies live in the Drizzle schema. Every table is pgTable.withRLS; policies use pgPolicy(…, { to: appTenant }) via the helpers in packages/db/src/schema/tenancy.ts, and drizzle-kit generates the SQL. app.org_id() returns the Active Organization only while the context User is a Member right now (SECURITY DEFINER, fixed search_path), so a removed Member sees nothing on the next statement even if the application forgets to check. Policies call them as (select …) so each is an InitPlan evaluated once per statement; a row-level membership test compares against (select app.member_orgs())::text[], the context User's current Organizations, never a per-row function call. They are plpgsql because a non-inlinable SQL function is re-planned on every statement before PostgreSQL 18; plpgsql caches the plan per session. Tables tenant code may only lock get an update policy with WITH CHECK (false). Better Auth's tables, OAuth storage, queues and job cursors have RLS with no tenant policy and no grant: default deny.

The connection role is the system path, whatever it is. It stays the owner of every table. Three profiles are supported and tested:

Tables therefore do not use FORCE ROW LEVEL SECURITY: with FORCE, a plain owner would be subject to the tenant policies (none of which admit it) and Better Auth, cron and webhooks would see zero rows. Isolation comes from switching to a role that is neither owner nor bypassing, not from FORCE. The runtime role (Hyperdrive's or DATABASE_URL's) must be the role that ran the migrations, or at least own the tables; confirm it with select pg_has_role(current_user, 'app_tenant', 'SET') on the runtime connection.

Creating the role. The migration creates app_tenant when the connection role has CREATEROLE (managed hosts' admin users do) or is a superuser. Without it, the migration stops and prints the SQL an administrator must run once:

CREATE ROLE app_tenant NOLOGIN NOINHERIT NOBYPASSRLS;
GRANT app_tenant TO <connection role> WITH INHERIT FALSE, SET TRUE;

It also refuses an existing app_tenant that is SUPERUSER, BYPASSRLS or LOGIN.

Identity is bound to this database's roles. app_tenant is cluster-wide, so any other role that may SET it (the owner of a sibling database after an administrator's GRANT) could connect here and claim any User through the GUCs. The identity functions (app.user_id(), app.org_id(), app.is_member(), app.member_orgs(), app.user_is_active(); all plpgsql, STABLE SECURITY DEFINER PARALLEL RESTRICTED with a fixed search_path) return nothing unless session_user is listed in app.session_roles (app.session_trusted()), which migrations seed with CURRENT_USER. The tables every User reads (feature_flag, app_update_config) require app.session_trusted() too, instead of USING (true). A runtime role different from the migration role must be added there (db:verify-rls says so). A per-database role name was rejected: policies name their role statically, so it would mean generating every policy per database for no extra protection over the allowlist; revoking CONNECT from PUBLIC was rejected as host-hostile (managed platforms connect their own roles).

Verified on every apply, repairable after a restore. The migration journal records what ran, not what survived. pnpm --filter @template-cloudflare/db db:verify-rls [ENV_VAR] checks the role, the allowlist, the functions, RLS on every table (and no FORCE), every policy of the latest drizzle-kit snapshot and the exact privilege set; --repair re-applies the idempotent baseline and recreates missing policies in one transaction. db:apply-pending runs both after migrating and fails with actionable messages otherwise. Moving providers: create app_tenant and grant it to the target connection role first if that role cannot create roles, restore the dump (pg_restore --no-owner --no-acl loses grants, and loses policies when the role is missing), then run db:apply-pending against the target — it repairs grants, functions, allowlist and policies. The RLS matrix proves this with a real pg_dump/pg_restore from a superuser cluster into a plain-owner cluster.

Entry points. @template-cloudflare/db/tenancy (packages/db/src/tenancy.ts) exposes:

• tenantDatabase(systemDb, { userId, organizationId }) — a Drizzle handle whose every statement, and every explicit transaction(), is its own short app_tenant transaction. It refuses SET, RESET, DISCARD and set_config(), and every transaction-control statement except those drizzle's transaction() sends; a connection that is not idle when released (pg's ReadyForQuery status) is rolled back and destroyed instead of pooled. All oRPC procedures, the assistant, MCP tools and the files gateway get this as context.db. orgProcedure narrows it to the Active Organization after the membership check, which is a single system statement (asSystem(…, "auth.membership"), user and Organization from the session, never from input); the database re-checks membership on every tenant statement anyway.
• withTenant(db, scope, fn) — one app_tenant transaction, for code that needs it.

One round trip per tenant statement (pipelining). A standalone statement is sent as Parse/Bind/Execute of the settings (set_config('role', 'app_tenant', true), app.user_id, app.org_id), then Parse/Bind/Execute of the statement, then a single Sync. Without BEGIN, everything up to the Sync is one implicit transaction: the Sync commits it, any failure rolls the whole of it back and the server skips the remaining messages, so the statement never runs without its identity and nothing survives on the pooled connection. Explicit transaction()s pipeline BEGIN with the settings, and SAVEPOINT/RELEASE travel in front of the next statement. Kill switch: DB_TENANT_PIPELINE=0 (a Worker var or secret, read per handle, no code change) restores BEGIN, settings, statement, COMMIT. packages/db/src/tenancy-pipeline.test.ts proves isolation and the absence of role/GUC leaks across statements and pooled connections in both modes, and test:rls-matrix runs every suite with the switch on and off. Before production, node scripts/rls-preview-proof.mjs <baseUrl> attacks a real deploy behind Hyperdrive (each PR preview gets its own Hyperdrive, so it takes the production connection path) (cross-tenant reads, writes and IDOR, plus a concurrent burst alternating tenants) and exits non-zero on any leak. Because the application filters would hide a broken policy or a missing SET ROLE from those attacks, it also calls tenancy.probe. The probe exists only on PR previews: it is mounted when the Worker has TENANCY_PROBE=1, which scripts/preview-config.mjs sets for every preview and which scripts/check-production-config.mjs refuses in the production config (a deploy step); in production the procedure is absent from the router (404). It answers only booleans about the caller: whether its tenant statement and a withTenant transaction ran as app_tenant, whether app.user_id()/app.org_id() are the caller's, whether rows of other Organizations are visible without application filters (one row per Organization table, found as the connection role through the organization_id index and checked by TID, so the cost is constant), and whether a system statement right after finds app_tenant or tenant settings left on the connection. A batch carries at most one probe call. The proof was checked against injected faults (a permissive policy, session-level set_config(…, false), a tenant entry without the role switch); each makes it exit non-zero with the cause.

• tenantStep(tx, scope, fn) — an app_tenant savepoint inside a system transaction (the import worker inserts todos this way while holding its lease).
• asSystem(db, reason, fn) — the only sanctioned way to use the connection role, with an enumerated SystemReason. scripts/check-system-call-sites.mjs (run by pnpm lint) fails when a call site is missing from packages/db/src/system-call-sites.txt, and lint refuses createDb/createJobDb outside the listed entry points.

Same-Organization constraints bind the system path too. Composite foreign keys keep files, document text, chunks, imports and tool approvals inside one Organization, and CHECK constraints keep storage keys under the row's own orgs/<id>/ prefix.

Why not the alternatives

• One transaction per request (the original plan): it holds a connection across storage and provider calls, and it changes statement semantics — a caught SQL error would abort every later statement of the request. Per-statement tenant transactions keep plain-pool semantics; pipelined, they cost no extra round trip (two extra with DB_TENANT_PIPELINE=0).
• FORCE without switching role: BYPASSRLS ignores it, and on a plain owner it breaks every system path.
• A separate LOGIN role and a second Hyperdrive: two pools, ownership changes, and Better Auth on another connection, for no extra isolation.
• Session-level SET ROLE: leaks across a transaction-mode pool.
• drizzle-orm/neon helpers or drizzle-kit entities.roles: host-specific (Neon Authorize/JWT) or non-idempotent CREATE ROLE.

Consequences

• A new table needs pgTable.withRLS, its policies and grants, or packages/db/src/rls-catalog.test.ts fails. The catalog test also refuses FORCE, policies for any role but app_tenant, and any privilege without a matching policy.
• A new cross-tenant path needs a SystemReason and an inventory line in the same change. The inventory also lists every systemDb reference and createRequestScope() call, and asSystem(db, reason, (s) => s) is refused: the work happens inside the callback.
• A new tenant grant goes into packages/db/scripts/rls-baseline.mjs and a migration together; db:verify-rls (and the catalog test) fail on any difference.
• Better Auth, cron, billing webhooks and admin keep running as the owner; their correctness rests on code, the constraints above and the gaps closed alongside this change.
• RLS does not survive SQL injection (a RESET ROLE returns to the owner). Drizzle parameterises every value; keep it that way.
• Under RLS, operators that are not LEAKPROOF in the user's own predicates (ILIKE, LIKE, full-text @@, cross-type timestamp comparisons) can never be index conditions ahead of the policy: GIN/trigram indexes do nothing for tenant traffic. Narrow by an indexed tenant column and keep the filter cheap (e.g. the stored document_chunk.search vector). Marking operators LEAKPROOF needs a superuser and weakens the guarantee; rejected.
• Hyperdrive query caching must stay disabled: reads are cached by query text and parameters, and Cloudflare documents neither the role nor transaction-local settings as part of the key (nor whether reads inside a transaction are exempt). pnpm setup --hyperdrive-id disables it, pnpm doctor --connect reports it, and the production deploy refuses to publish when packages/project-setup-cli/src/hyperdrive.mjs check finds it on.
• Webhook endpoints are per User, not per Organization: a User's endpoint receives the signals of every Organization they belong to (never row contents; removed Members stop receiving). Scoping endpoints to an Organization is a product change left open.
• pnpm --filter @template-cloudflare/db test:rls-matrix runs migrations (with db:verify-rls), the catalog, cross-role, restore and isolation suites and the tenant-path regressions on PostgreSQL 16 and 18 for each profile (plus a no-CREATEROLE owner, a real pg_dump/pg_restore into a plain-owner cluster and a PostgreSQL 15 refusal); CI runs it as its own job.