# Postgres row-level security

Source: https://permdock.com/docs/standards/postgres-rls

Postgres RLS as a compile target and import source for PermDock policies, covering CREATE POLICY semantics, Supabase and Neon helpers, GUC patterns, pg_policies introspection, the Drizzle and Prisma 8 authoring surfaces, testing and the risks of round-tripping.

## What it is [#what-it-is]

PostgreSQL row-level security lets a table carry policies that filter or reject rows per statement, evaluated by the database regardless of which application issued the query. The relevant syntax is [`CREATE POLICY`](https://www.postgresql.org/docs/current/sql-createpolicy.html):

```sql
CREATE POLICY name ON table
  [ AS PERMISSIVE | RESTRICTIVE ]
  [ FOR ALL | SELECT | INSERT | UPDATE | DELETE ]
  [ TO role, ... ]
  [ USING (expr) ]
  [ WITH CHECK (expr) ]
```

Semantics that matter for code generation:

* **Combination.** Permissive policies for the same command are OR'd; restrictive policies are AND'd; the result is `(AND restrictives) AND (OR permissives)`. Zero permissive policies means deny. `FOR ALL` policies fold into whichever command is being evaluated. Defaults are `PERMISSIVE`, `ALL` and `PUBLIC`.
* **Clause legality.** `SELECT` takes only `USING`; `INSERT` takes only `WITH CHECK`; `DELETE` takes only `USING`; `UPDATE` and `ALL` take both, and if `WITH CHECK` is omitted `USING` is reused for the new row.
* **Cross-command coupling.** `UPDATE` and `DELETE` statements that read columns (`WHERE`, `RETURNING`, `SET`) also need a passing `SELECT` policy, and `RETURNING` rows must satisfy it or the statement errors.
* **Denial modes.** `USING` silently filters (zero rows); `WITH CHECK` raises `42501`; a missing `GRANT` raises `42501` before any policy runs.
* **Expressions** cannot contain aggregates or window functions and run with the caller's privileges, so referenced tables and functions need grants; `LEAKPROOF` functions may run before policy quals.

Introspection is through the [`pg_policies`](https://www.postgresql.org/docs/current/view-pg-policies.html) view (`schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check`), backed by [`pg_policy`](https://www.postgresql.org/docs/current/catalog-pg-policy.html) (`polroles` of `0` means `PUBLIC`). The `qual` and `with_check` columns are deparsed with `pg_get_expr`, which returns normalised SQL (explicit casts, parenthesisation, `( SELECT auth.uid() AS uid)`), not the original text. Enablement lives in `pg_class.relrowsecurity` and `relforcerowsecurity`.

### Supabase [#supabase]

Every request runs as `anon` or `authenticated`: PostgREST issues `SET LOCAL ROLE` from the JWT `role` claim, and `service_role` has `bypassrls`. Grants are separate from policies, and new tables in `public` may already grant all four privileges to `anon` and `authenticated`, so generated SQL revokes and re-grants next to the policies.

* `auth.uid()` is NULL when unauthenticated, so `null = user_id` is never true. `auth.jwt()` is `current_setting('request.jwt.claims', true)::jsonb`; read `app_metadata`, never user-writable `user_metadata`, and expect the claims to be stale until the token refreshes. `auth.role()` and `auth.email()` are deprecated in favour of the `TO` clause.
* PostgREST exposes the claims as the GUC `request.jwt.claims`. The per-claim `request.jwt.claim.<name>` settings are legacy: PostgREST 12.0.0 removed `db-use-legacy-gucs`, the option that wrote them ([changelog](https://github.com/PostgREST/postgrest/blob/main/CHANGELOG.md)). Even so, Supabase's `auth.uid()` still reads `request.jwt.claim.sub` first. PermDock's `withSubject` preambles and `rls verify` set only `request.jwt.claims`, and `permdock_user_id()` reads only that.
* The [performance guide](https://supabase.com/docs/guides/database/postgres/row-level-security-performance) and the Splinter lints say: wrap helpers as `(select auth.uid())` so they run once per statement ([`auth_rls_initplan`](https://supabase.github.io/splinter/0003_auth_rls_initplan/)); index every filtered column; always write `TO authenticated`; prefer `IN (subselect)` over correlated joins, or a `security definer` function (with `set search_path = ''`, in a non-exposed schema) to avoid `42P17` recursion; avoid many permissive policies per role and command ([`multiple_permissive_policies`](https://supabase.github.io/splinter/0006_multiple_permissive_policies/)).
* The [RBAC pattern](https://supabase.com/docs/guides/database/postgres/custom-claims-and-role-based-access-control-rbac) (`app_role` and `app_permission` enums, `user_roles` and `role_permissions` tables, a `custom_access_token_hook` that injects `user_role`, and a `security definer stable` `authorize(permission)` function used as `using ((select authorize('channels.delete')))`) is a named-permission catalog in SQL.
* Policies appear nowhere else: `supabase gen types typescript` emits tables, views, functions and enums only, and the Supabase MCP server has no policy tool, so introspection means `execute_sql` against `pg_policies`. Its `get_advisors` tool runs the Splinter RLS lints.

### Generic Postgres, Neon, Nile and PostgREST [#generic-postgres-neon-nile-and-postgrest]

Without Supabase helpers, the pattern is a GUC set per transaction (`set_config('app.user_id', ..., true)`) and policies reading `current_setting('app.user_id', true)::uuid`. Neon's Data API validates the JWT and `pg_session_jwt` provides `auth.user_id()` (text `sub`) and `auth.uid()` (uuid) with roles `authenticated` and `anonymous`. Nile isolates tenant-aware tables through `set nile.tenant_id` and a reserved `tenant_id` column instead of user-written policies, so it is a tenant-equality target only. PostgREST uses an `authenticator` role, `SET LOCAL ROLE` from the role claim, claims in `request.jwt.claims` and a `db-pre-request` hook.

### Authoring surfaces [#authoring-surfaces]

* [Drizzle](https://orm.drizzle.team/docs/rls): `pgPolicy(name, { as, to, for, using, withCheck })` in the table's third argument (any policy auto-enables RLS; `pgTable.withRLS` enables it with none), `pgPolicy(...).link(table)` for tables Drizzle does not own, `pgRole(...).existing()`. `drizzle-orm/supabase` exports `anonRole`, `authenticatedRole`, `serviceRole`, `authUid` and `createDrizzle(token, ...)`, which sets the claim GUCs and `set local role` inside a transaction; `drizzle-orm/neon` exports `crudPolicy({ role, read, modify })`. `drizzle-kit generate` diffs policies.
* Prisma 8 ([changelog](https://www.prisma.io/changelog/2026-07-17), [prisma-next#945](https://github.com/prisma/prisma-next/pull/945)): `@@rls` on a model enables RLS fail-closed; top-level `policy_select|insert|update|delete|all` blocks carry `target`, `roles`, `using` and `withCheck`; `role` declarations; `migration plan` emits `CREATE POLICY` and `ALTER POLICY ... RENAME`; `db verify` fails on policy drift; `@prisma/orm-extension-supabase` supplies the Supabase roles. Prisma up to 7 has no native RLS; the sanctioned pattern is a client extension that runs `set_config` and the query inside `$transaction`, and nested many-to-many writes can lose the GUC.
* Kysely has nothing built in (use `onReserveConnection` or a transaction plus `set_config`). Kysera's `@kysera/rls` drives app-side filters and native `CREATE POLICY` from one schema, but only in one direction and only for policies written as raw SQL.
* Schema-as-code tools: Atlas has `policy` blocks, `row_security`, lint rules and `atlas schema test`; Bytebase reviews policies as SQL; Sqitch is plain SQL. `CREATE POLICY` has no `IF NOT EXISTS`, so idempotent scripts use `DROP POLICY IF EXISTS` or a `DO` block over `pg_policies`.
* Parsers: [`pgsql-parser`](https://github.com/constructive-io/pgsql-parser) (libpg\_query in WASM, symmetric parse and deparse) and [`@pgsql/parser`](https://www.npmjs.com/package/@pgsql/parser) (PG 15 to 18 grammars); the pure TypeScript `pgsql-ast-parser` has incomplete coverage. Since `qual` is a bare expression, parse `SELECT 1 WHERE <qual>` and take the where clause.

### Testing [#testing]

pgTAP via [`supabase test db`](https://supabase.com/docs/guides/database/testing) gives structural asserts (`policies_are`, `policy_roles_are`, `policy_cmd_is`, `has_table_privilege`) and behavioural ones after `set local role authenticated` and `set local request.jwt.claims`. Basejump's `supabase-test-helpers` add `tests.authenticate_as()`, `tests.rls_enabled()` and `tests.freeze_time()`. Match the assertion to the denial mode: a missing grant and a `WITH CHECK` failure are `throws_ok ... '42501'`; a `USING` filter is `is_empty` over a statement with `RETURNING`, followed by a read proving the row is intact. Never prove an allowed write with `lives_ok`, which passes on zero rows.

### Splinter, supautils and pg\_jsonschema [#splinter-supautils-and-pg_jsonschema]

Three Supabase projects constrain what generated SQL may contain.

* [Splinter](https://github.com/supabase/splinter) is a set of SQL lint views over the catalog, released in CalVer (2026.09.1); each release ships the combined `splinter.json`. `supabase db advisors --local` or `--db-url` runs it, `--type security --fail-on warn` makes it a gate, and the Supabase MCP server's `get_advisors` returns the same rows. The lints that generated RLS can trip are 0003 [`auth_rls_initplan`](https://supabase.github.io/splinter/0003_auth_rls_initplan/) (an unwrapped `auth.uid()`, `auth.jwt()` or `current_setting()` in policy text), 0006 [`multiple_permissive_policies`](https://supabase.github.io/splinter/0006_multiple_permissive_policies/) (two permissive policies for one role and command), 0010 `security_definer_view`, 0011 `function_search_path_mutable` (any function, triggers included, without `set search_path`), 0013 `rls_disabled_in_public`, 0014 `extension_in_public`, 0024 `rls_policy_always_true`, 0025 `public_bucket_allows_listing`, and 0028 and 0029 (a `security definer` function in an exposed schema executable by `anon` or `authenticated`). A schema is exposed when it is `public` or listed in `[api] schemas` of `supabase/config.toml`; splinter reads that list from `pgrst.db_schemas`.
* [supautils](https://github.com/supabase/supautils) (3.4.4) runs inside every Supabase database. For `postgres`, the role that runs migrations, it rejects `alter role` (attributes, `rename`, `password`) and `drop role` on a reserved role: `anon`, `authenticated`, `service_role`, `authenticator`, `dashboard_user`, `pgbouncer` and the `supabase_*` roles. It also rejects a grant of a reserved membership such as `authenticator` or a `supabase_*` admin role. `alter role … set` stays allowed on the four API roles, and `create event trigger` runs. A migration that passes against plain Postgres can fail on Supabase for these reasons alone; [PD063](/docs/cli/doctor#pd063-statements-supautils-rejects) reports them.
* [pg\_jsonschema](https://github.com/supabase/pg_jsonschema) adds `extensions.jsonb_matches_schema(schema json, instance jsonb)` and its `json` twin; Supabase ships 0.3.3. It validates Draft 2020-12 without asserting `format` (use `pattern`), resolves no remote `$ref` (inline `$defs`), and compiles the schema on every call; compiled-schema functions exist on the main branch only. A `check` constraint over it is the database's boundary validation for a `jsonb` column, added `not valid` and then validated so existing rows do not block the migration.

## Why it matters for PermDock [#why-it-matters-for-permdock]

RLS is the only enforcement layer that survives a bypassed application, and Supabase users already write it. A policy written twice, once in SQL and once in TypeScript, drifts. PermDock's portable condition AST is designed so one condition evaluates in the UI, filters arrays, compiles to `where` clauses and generates RLS, and so existing RLS imports back into a typed catalog. No other tool does the import direction: `supazod`-style generators read `gen types` output, which has no policy data. ZenStack is the contrast case: it compiles `@@allow` / `@@deny` into application-side query filters and skips RLS; PermDock offers both app-side `where` filters and RLS, with parity tests as the glue. See the [rls adapter](/docs/adapters/rls) and [CLI rls](/docs/cli/rls).

## How PermDock uses it [#how-permdock-uses-it]

```bash
permdock rls generate --target drizzle|sql|prisma --dialect supabase|neon|guc
permdock rls import --db $DATABASE_URL --out src/permissions.generated.ts
permdock rls verify --db $DATABASE_URL
```

* **Generate.** Roles and grants become policies (mapping below). A `SELECT` policy is generated or verified whenever update or delete grants exist, and a table left with zero permissive policies for a role and command is warned about. Companion `ENABLE ROW LEVEL SECURITY`, optional `FORCE`, grant and revoke statements, `(select ...)` wrappers and index suggestions are emitted alongside. Role checks go through generated `security definer` helpers (`permdock_has`, and one `permitted_<scope>_ids` and one membership-only `member_<scope>_ids` per [named scope](/docs/concepts/scopes) the policy declares) over a seeded `role_permissions` table, called uncorrelated so they run once per statement (see below), and each table gets one permissive policy per command. With `--rbac supabase`, the `user_roles` / `authorize()` scaffold is generated as well; `permdock supabase hook generate` writes the hook. SQL output is idempotent (`drop policy if exists` then `create policy`); the Drizzle and Prisma targets leave diffing to drizzle-kit, Prisma 8 or Atlas.
* **Import.** `pg_policies` plus `pg_class.relrowsecurity` are read, `qual` and `with_check` parsed with `pgsql-parser` and pattern-matched to portable nodes; anything else becomes `opaque({ sql, fingerprint })`, kept verbatim for regeneration and flagged in the catalog as untestable app-side. Fingerprints come from the deparsed AST, so `pg_get_expr` normalisation does not register as drift. `FOR ALL` is split into four entries (export merges them only if identical); `roles = {public}` maps to all subjects and non-Supabase role names to opaque constraints. The output is a deterministic `definePermissions()` file that merges with hand-written ones by key.
* **Verify.** For each fixture (subject, row, next row, action), `can()` runs in-process and the same operation runs inside `BEGIN ... ROLLBACK` with `set local role` and `set_config('request.jwt.claims', ...)`, using `RETURNING`; outcomes are classified `allowed`, `filtered` (zero rows) or `rejected` (`42501`) and any mismatch fails. Only the outcome is compared, so a three-valued-logic or cast difference that changes the outcome is a mismatch and one that does not is invisible; an opaque grant is reported as a note, never evaluated app-side. Emitted as pgTAP or run from Node.

### The portable subset [#the-portable-subset]

| Portable node | Supabase SQL | Neon or generic GUC |
| --- | --- | --- |
| `eq(row.user_id, principal.id)` | `(select auth.uid()) = user_id` | `(select auth.user_id()) = user_id` or `(select current_setting('app.user_id', true))::uuid = user_id` |
| `eq(row.tenant_id, principal.claim.tenant_id)` | `tenant_id = ((select auth.jwt()) ->> 'tenant_id')::uuid` | `tenant_id = (select current_setting('app.tenant_id', true))::uuid` |
| `eq(principal.claim.user_role, 'admin')` | `((select auth.jwt()) ->> 'user_role') = 'admin'` | GUC variant |
| Claim array contains a row column | `team_id in (select jsonb_array_elements_text((select auth.jwt())->'app_metadata'->'teams'))::uuid` | none |
| Membership via join table | `id in (select resource_id from memberships where user_id = (select auth.uid()) [and role in (...)])` | same |
| Role check of a global role | `(select permdock.permdock_has('channels.delete'))` | same helper, claims from GUCs |
| Role check of a role on a named scope | `org_id in (select permdock.permitted_organization_ids('channels.delete'))`, `customer_id in (select permdock.permitted_customer_ids('quote.read'))` | same helpers |
| Named permission in a hand-written policy | `(select authorize('channels.delete'))` with `--rbac supabase`; imported as opaque | opaque unless a function is mapped |
| Literal comparisons, `isNull`, `and`, `or`, `not`, `true`, `false` | literal SQL | literal SQL |

Import recognises both the `EXISTS (select 1 ...)` and the `IN (subselect)` membership forms, and the helper calls in both policy layouts; generate emits the helpers for role checks and keeps `exists` joins only for resource-scoped roles. `now()` and interval arithmetic, `CASE`, multi-join subqueries, `current_user`, custom functions and claim checks beyond simple equality become `opaque`. The operators are written with the names on the [conditions](/docs/concepts/conditions) page.

### One helper per scope [#one-helper-per-scope]

A policy that declares `organization` and `customer` (inside it) gets `permitted_organization_ids` and `permitted_customer_ids`. Each returns the instances of its own scope, read from memberships of exactly that scope, so an organization owner's customer set is empty and a portal contact's organization set is empty (no cascade). A resource that carries both keys is narrowed per grant: an organization role compares `organization_id`, a customer role compares `customer_id`, and the two branches OR together in one permissive policy. Memberships under the first scope are narrowed to the active-tenant claim when it is set. In `database` mode each helper reads the table mapped for its scope; in `jwt` mode, the `memberships` claim entries whose `scope` is its own, with the first scope's id in `within`.

### Why InitPlans [#why-initplans]

Postgres evaluates a policy's `USING` expression as a filter on every candidate row. A subquery that references no column of the outer row is uncorrelated: the planner turns a scalar one, `(select f())`, into an **InitPlan** and an `x in (select f())` into a **hashed SubPlan**, runs either once when the statement starts, and the per-row filter only compares against the cached result. A subquery or function call that mentions a row column (`authorize('post.read', "orgId"::text)`, `exists (... where m.org_id = "orgId")` that the planner cannot hash) is correlated and runs for every row. Splinter's `auth_rls_initplan` lint is the same observation applied to `auth.uid()`, and the `guc` dialect wraps `current_setting(...)` the same way.

The generated helpers are built so every call is uncorrelated: they take only a grant key, read the subject from `auth.uid()` and the claims inside the function, and return either a boolean (global roles) or the set of one scope's ids (scoped roles). The row-dependent part of the policy is then a plain comparison of the row's scope column against that set. On 10,000 rows across 20 tenants the per-row shape runs its role check 10,000 times; the helper shape runs it once per helper and is over 1,000 times faster in `tests/integration/bench` (the test asserts `loops = 1` and a 5x floor).

`security definer` is what lets the helper read `role_permissions`, `user_roles` and the membership table without granting `authenticated` access to them, and it prevents the recursion error (`42P17`) a policy over the membership table would otherwise hit. `set search_path = ''` with fully qualified names closes the `search_path` hijack a definer function is otherwise open to, and `execute` is granted to `authenticated` only.

## Mapping table [#mapping-table]

| PermDock concept | Postgres / Supabase concept |
| --- | --- |
| `allow(permission, { where })` | `AS PERMISSIVE ... USING (cond)` |
| `deny(permission, { where })` | `AS RESTRICTIVE ... USING ((cond) IS NOT TRUE)`, one restrictive policy per table and command; a `cond` that is NULL on a row leaves the row visible, as the evaluator does |
| Role held globally / at a named scope | `(select permdock_has('<key>'))` / `<scope key> in (select permitted_<scope>_ids('<key>'))` |
| Any membership of a named scope | `<scope key> in (select member_<scope>_ids())` |
| `read` action | `FOR SELECT USING` |
| `create` action, `check` | `FOR INSERT WITH CHECK` (next row) |
| `update` action, `where` + `check` | `FOR UPDATE USING (current) WITH CHECK (next)`; a grant on the current row only collapses to `USING` |
| `delete` action | `FOR DELETE USING` |
| App roles | JWT claims, never Postgres roles; policies default `TO authenticated`, public-visible permissions also get one policy `TO anon` (`anonymous` on Neon), so no role has two permissive policies for one command |
| `service_role` | Never emitted (bypasses RLS) |
| Anything not portable | `opaque({ sql, fingerprint })` |
| `pg_policies` row | Catalog entry: table, cmd, permissive, roles, condition or opaque, fingerprint, source SQL |
| `--target drizzle` / `--target prisma` output | Drizzle `pgPolicy`; Prisma 8 `policy_*` block with `@@rls` |

## Risks [#risks]

* **Semantic drift.** SQL three-valued logic (`auth.uid()` NULL, nullable columns) versus JavaScript booleans; `uuid` versus `text` `sub`; stale JWT claims versus live application lookups.
* **`USING` versus `WITH CHECK` confusion**, the classic "user reassigns `user_id`" hole; `ALL` policies hiding intent; the `SELECT` prerequisite for `UPDATE` and `DELETE`.
* **Grants.** A `42501` from a missing grant looks like a policy denial, so code generation owns grants too.
* **Bypasses.** `service_role`, `bypassrls` and table owners without `FORCE` skip policies; views default to `security definer` (use `security_invoker = true`).
* **Performance.** Unwrapped helpers, unindexed filter columns, correlated joins, many permissive policies, recursion; run Splinter or `get_advisors` next to `verify`, which does not read them.
* **Round-trip stability.** `pg_get_expr` rewrites text, so import-then-generate compares ASTs, never policy text.

## Sources [#sources]

* [PostgreSQL CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html), [pg\_policies](https://www.postgresql.org/docs/current/view-pg-policies.html), [pg\_policy](https://www.postgresql.org/docs/current/catalog-pg-policy.html).
* [Supabase RLS guide](https://supabase.com/docs/guides/database/postgres/row-level-security), [RLS performance](https://supabase.com/docs/guides/database/postgres/row-level-security-performance), [Custom claims and RBAC](https://supabase.com/docs/guides/database/postgres/custom-claims-and-role-based-access-control-rbac), [Testing](https://supabase.com/docs/guides/database/testing), [Splinter](https://supabase.github.io/splinter/), [supautils](https://github.com/supabase/supautils), [pg\_jsonschema](https://github.com/supabase/pg_jsonschema), [supabase-mcp](https://github.com/supabase-community/supabase-mcp).
* [Drizzle RLS](https://orm.drizzle.team/docs/rls), [Neon RLS with Drizzle](https://neon.com/docs/guides/neon-rls-drizzle), [Neon Data API](https://neon.com/docs/data-api/get-started), [Nile tenant isolation](https://www.thenile.dev/docs/tenant-virtualization/tenant-isolation), [PostgREST auth](https://docs.postgrest.org/en/latest/references/auth.html).
* [Prisma 8 changelog](https://www.prisma.io/changelog/2026-07-17), [prisma-next#945](https://github.com/prisma/prisma-next/pull/945), [Prisma RLS client extension](https://github.com/prisma/prisma-client-extensions/tree/main/row-level-security).
* [Kysera multi-tenancy](https://kysera.dev/docs/guides/multi-tenancy), [ZenStack access control](https://zenstack.dev/docs/orm/access-control/), [Atlas RLS guide](https://atlasgo.io/guides/rls-policy), [supabase-test-helpers](https://github.com/usebasejump/supabase-test-helpers).
* [pgsql-parser](https://github.com/constructive-io/pgsql-parser), [@pgsql/parser](https://www.npmjs.com/package/@pgsql/parser).