Row-level security per Organization

Breaking: PostgreSQL 16 or newer

The foundation migration stops on PostgreSQL 15 or older. Its connection role must be able to create roles (CREATEROLE or a superuser); otherwise the migration prints the two statements an administrator runs once to create app_tenant. Apply the migrations with pnpm --filter @template-cloudflare/db db:apply-pending (deploy-prod.yml does this before the deploy).

Every table in public has row-level security. Procedures, assistant and MCP tools and the files gateway run as the app_tenant role for the session’s User and Active Organization, in one short transaction per statement. app.org_id() re-checks membership on every statement, so a removed Member sees nothing on the next query. Better Auth, cron, billing webhooks and admin keep the connection role through asSystem(db, reason, fn), whose call sites are inventoried and checked by pnpm lint. The application’s organizationId filters stay as defence in depth.

The design uses plain PostgreSQL, so it works the same on Neon, Supabase, RDS, a self-hosted server or a local Docker database. The connection role may be a superuser, an owner with BYPASSRLS or a plain owner; tables never use FORCE ROW LEVEL SECURITY.

What changes for a project built on the template:

db:apply-pending now ends with db:verify-rls, which checks the role, the identity functions, every policy and the exact grants. After a pg_dump/pg_restore into another host, grants and policies are lost: run db:apply-pending against the target, or db:verify-rls [ENV_VAR] --repair.
Tenant identity is honoured only for roles listed in app.session_roles, seeded with the migration role. If the Worker connects with a different role, add it there, or tenant requests see no rows.
Hyperdrive query caching must stay disabled. pnpm setup --hyperdrive-id <id> disables it, pnpm doctor --connect reports it, and the production deploy refuses to publish while it is on.
A new table needs pgTable.withRLS, its tenantPolicy(...), its grant in packages/db/scripts/rls-baseline.mjs and a custom migration. A new cross-tenant path needs a SystemReason and node scripts/check-system-call-sites.mjs --write.

Each tenant statement is pipelined: the identity settings and the statement travel in one round trip with a single Sync. Setting DB_TENANT_PIPELINE=0 as a Worker var or secret restores explicit BEGIN/COMMIT without a code change. pnpm --filter @template-cloudflare/db test:rls-matrix (Docker) runs both modes on PostgreSQL 16 and 18 for every connection-role profile, including a real restore, and CI runs it as its own job.

Pull request previews now take the production connection path. When production uses Hyperdrive, each PR gets its own Hyperdrive with caching disabled, pointing at the PR’s database branch. Closing the PR deletes it, and the CI token needs Hyperdrive edit permission. Previews also deploy with their secrets in the same version (wrangler deploy --secrets-file), so a new preview Worker no longer fails env validation on its first upload. Only previews set TENANCY_PROBE=1, which mounts the tenancy.probe oracle; scripts/check-production-config.mjs refuses it in the production config. Before a production deploy that touches tenancy, run node scripts/rls-preview-proof.mjs <previewUrl>, which exits non-zero on any cross-tenant or pooled-connection leak.

Sources: foundation, tenant requests, verify on apply, Hyperdrive caching, pipelining, preview-only probe, preview Hyperdrive and secrets.