# Row-level security isolates Organizations

The repository's architectural decision, rationale and consequences.

# 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](0003-organizations-are-the-tenant.md)) 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 [#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 [#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:

| profile                   | example                                           | system path sees all rows because                |
| ------------------------- | ------------------------------------------------- | ------------------------------------------------ |
| superuser                 | stock local `postgres`                            | superusers bypass RLS                            |
| owner with BYPASSRLS      | Neon `neondb_owner`                               | BYPASSRLS                                        |
| plain owner, no BYPASSRLS | RDS master user, Supabase `postgres`, self-hosted | table owners are exempt from RLS without `FORCE` |

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:

```sql
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 [#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 [#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.