# Row-level security per Organization

PostgreSQL now enforces tenant isolation on every table. The template requires PostgreSQL 16 or newer, on any host, and verifies RLS on every migration.

<Callout title="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).
</Callout>

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.

- [ADR-0009: row-level security](/docs/adr/0009-row-level-security)
- [Tenant isolation](/docs/features/database#tenant-isolation) and [restoring or switching providers](/docs/features/database#restore-or-switch-providers)
- [Local PostgreSQL](/docs/development/setup#local-postgresql)
- [Deployment and previews](/docs/deploy)

Sources: [foundation](https://github.com/CarlosZiegler/template-cloudflare/commit/c50a66a), [tenant requests](https://github.com/CarlosZiegler/template-cloudflare/commit/a0f3bd0), [verify on apply](https://github.com/CarlosZiegler/template-cloudflare/commit/bd44355), [Hyperdrive caching](https://github.com/CarlosZiegler/template-cloudflare/commit/40aa79c), [pipelining](https://github.com/CarlosZiegler/template-cloudflare/commit/39d14a3), [preview-only probe](https://github.com/CarlosZiegler/template-cloudflare/commit/4e96220), [preview Hyperdrive and secrets](https://github.com/CarlosZiegler/template-cloudflare/commit/9fdc5fe).