# Postgres RLS Source: https://permdock.com/docs/adapters/rls permdock rls generate, import and verify round-trip PermDock policies and Postgres row-level security across Drizzle, raw SQL and Prisma 8 targets for Supabase, Neon and generic Postgres. `permdock rls` is the CLI surface (with a `permdock/rls` runtime for the parity runner) that compiles roles and grants to Postgres row-level security policies, imports existing policies back into generated definitions, and verifies that in-process decisions and database outcomes agree. ## Database-first (SQL is the authority) [#database-first-sql-is-the-authority] TypeScript-first is the default: `generate` emits policies from `definePolicy`. Teams whose SQL catalog is authority (declarative `supabase/schemas`, helper functions, CI that must not `db push`) keep that catalog. They never have to run `generate`. They pin a policy with `sqlFunction` twins, map names in `rls.functions`, and run `permdock rls verify --db` so `can()` on the twin agrees with the live function. `--inline-functions` is for generate targets that cannot call a SQL function; it is not required for database-first CI. ```bash pnpm exec permdock rls verify --db $DATABASE_URL --fixtures rls.fixtures.json ``` `verify` exits 1 on any mismatch. Opaque grants are reported as untestable app-side. `sqlFunction` grants are reported as verified through twin. Doctor `PD016` warns when an `rls` config still has opaque grants or `sqlFunction` grants with no fixtures. ## Purpose [#purpose] A permission such as `allow(permissions.post.update, { to: relation(permissions.post, 'author') })` (or `{ where: { authorId: principal.id } }`) should be true in the UI, in the API and in the database. Today teams hand-write the RLS twin of every app rule and the two drift. No existing tool goes from RLS to application permissions, and the only dual-mode prior art (Kysera `@kysera/rls`) generates one way from raw SQL ([Postgres RLS](/docs/standards/postgres-rls)). `permdock rls` makes the [portable condition](/docs/concepts/conditions) the single source: `generate` emits policies, `import` reads them back (portable where possible, `opaque` otherwise), and `verify` proves parity with fixtures. ## API [#api] ```bash permdock rls generate --target drizzle|sql|prisma --dialect supabase|neon|guc [--rbac supabase] [--authorize database|jwt] [--memberships [:tenant_col,user_col,role_col]] [--tenant-type uuid] [--policy-per-role] [--policy-name '{table}_{op}'] [--fields views [--revoke-columns]] [--out ] permdock rls import --db $DATABASE_URL | --sql schema.sql --out src/permissions.generated.ts [--schema zod|valibot|arktype] [--from drizzle] permdock rls verify --db $DATABASE_URL [--fixtures rls.fixtures.ts] [--format pgtap|node] [--tree] ``` Semantics the generator applies: | PermDock | Postgres | | --- | --- | | `read` (instance action) | `FOR SELECT USING (cond)` | | `create` (collection action with `check`) | `FOR INSERT WITH CHECK (cond on new row)` | | `update` | `FOR UPDATE USING (where on current row) WITH CHECK (check on new row; defaults to where)` | | `delete` | `FOR DELETE USING (cond)` | | `allow` | `AS PERMISSIVE`, one policy per table and command (`{table}_{op}`) | | `deny` | `AS RESTRICTIVE` with `NOT (cond)`, one policy per table and command (`deny_{table}_{op}`) | | global role | `(select .permdock_has(''))` | | role on a named scope | `"" in (select .permitted__ids(''))` | | relation through the parent chain | `"" in (select descendant from .permdock_closure where resource = '' and ancestor = any (array(select .permitted__ids(''))))` ([closure table](#closure-table)) | | role, no public grant | `TO authenticated` (default) | | `anyone()` grant | ORed into the `TO authenticated` policy, and a `{table}_{op}_anon` policy `TO anon` (`anonymous` on `neon`) | | Supabase anonymous sign-in, with `rls.anonymousSignIns: 'deny'` | `and ((select auth.jwt()) ->> 'is_anonymous') is distinct from 'true'` on every branch but `anyone()`'s | | `service_role` | never emitted in a policy or helper, since it bypasses RLS; only `rls.trustedReaders` may name it, for the execute grant on `permdock_trusted_role_permissions` | | `rls.realtime` topic, `rls.storage` bucket (Supabase) | Policies on `realtime.messages` and `storage.objects` keyed by a topic segment or folder ([Realtime and Storage](/docs/cli/rls#realtime-channels-and-storage-buckets)) | Additional rules: a `SELECT` policy is generated (or its coverage asserted) whenever `update` or `delete` grants exist, because Postgres requires SELECT access to filter and for `RETURNING`; `ENABLE ROW LEVEL SECURITY` is always emitted, before the grants, and every table is schema-qualified (`public` when the mapping names none); `REVOKE ALL ... FROM anon, authenticated` followed by the exact `GRANT`s is emitted next to the policies, because a missing grant raises `42501` and masquerades as a policy denial; helper calls are wrapped as `(select permdock.permdock_user_id())` so they run once per statement; an index suggestion is printed for every filtered column, or written as the `indexes` part of [`rls generate --split`](/docs/cli/rls#declarative-schemas). Supabase gives an [anonymous sign-in](https://supabase.com/docs/guides/auth/auth-anonymous) the `authenticated` role, so the grants to roles and to `authenticated()` reach it; `rls.anonymousSignIns: 'deny'` keeps a token with `is_anonymous: true` to the `anyone()` grants, and `subjectFromSupabase(claims, { anonymousSignIns: 'deny' })` refuses it in process too; `permdock doctor` PD057 names a subject call without it. Grants come from `definePolicy({ roles })` and top-level `definePolicy({ grants })` alike, and a grant Postgres cannot enforce (a `plan()` or `assurance()` grantee, or an `actor()` grantee other than the one below) fails generation with the permission named. `deny(permission, { to: actor('oauth-client') })` on its own compiles, because the token shows a delegated client: it becomes a RESTRICTIVE policy that refuses rows while `client_id` or `act` is in the claims, so a third-party app's token cannot reach what the user's own session can. `contains` on a column whose resource schema says it is an array compiles to `value = any(column)`, with the value cast to the item type; on any other column it is `LIKE` with `\`, `%` and `_` in the value escaped, so the pattern matches literally, as the in-process `where` does. ## Per-statement helpers (InitPlan) [#per-statement-helpers-initplan] The role check of every grant lives in a generated function, never in the per-row part of a policy: * `.permdock_has(p_grant text) returns boolean`: the subject holds a global role with the grant key. * `.permitted__ids(p_grant text) returns setof `, one per [named scope](/docs/concepts/scopes) the policy declares (`permitted_tenant_ids` and `permitted_team_ids` when it declares none): the instances of that scope in which the subject holds it, through memberships of exactly that scope. * `.member__ids() returns setof `, one per declared scope: the instances of that scope the subject holds any live membership of. It takes no permission key, for policies such as "a member may read their organization's row". * `.member__ids_for(p_user uuid) returns setof `, Supabase only, for each scope with a membership source (`rls.membershipSources` or `supabase.hook.memberships`) or a mapped `rls.memberships` table: the same answer as `member__ids()` for `p_user`, read from the membership tables in both modes. It is for code that runs before a token exists, such as a [`supabase.hook.claims`](/docs/adapters/supabase-hook#claims-other-packages-own) function: `execute` is revoked from `public`, `anon` and `authenticated`, and only the hook migration grants it, to `supabase_auth_admin`. * `.permdock_has_for(p_user, p_grant text) returns boolean` and `.permitted__ids_for(p_user, p_grant text) returns setof `, `database` mode only: what `permdock_has` and `permitted__ids` answer for `p_user` (a `uuid` on Supabase, `text` on the other dialects), read from the same tables with the same expiry and suspension filters and no active-tenant narrowing, since a named user has no token. They are for trusted SQL that acts for a stored user: a job, a trigger, an approval decided after the request. `execute` is revoked from `public`, `anon` and `authenticated`; a `security definer` function runs them as its owner, and a backend role needs a grant of its own (`grant execute on function permdock.permitted_organization_ids_for(uuid, text) to service_role`). They join the subject's roles (the `user_roles` table in `database` mode, the role claim in `jwt` mode) or memberships (the memberships table, or the `memberships` claim) with a generated `.role_permissions (role, permission, grant_key, scope, effect)` table that `generate` seeds from the policy. The grant key is the permission key; roles that grant one permission under different portable conditions get one key per condition group (`post.update#1`, `post.update#2`), and each group is one OR branch of the policy with its condition ANDed on. `relation`, `principal.id` and `where` conditions stay in the policy because they depend on the row. Postgres plans an uncorrelated scalar subquery as an **InitPlan** and an uncorrelated `IN (select ...)` as a **hashed SubPlan**: both run once per statement and the per-row filter only reads the result. Any expression that mentions a column of the row, such as `authorize('post.read', "orgId"::text)`, is correlated and runs once per row, however it is wrapped. On 10,000 rows across 20 tenants, as a member of one tenant, the benchmark in `tests/integration/bench` measures (Postgres 16): ```text -- before: per-row authorize() plus a membership join per role Seq Scan on post (actual rows=500 loops=1) Filter: ($0 OR $1 OR ((SubPlan 3) AND (hashed SubPlan 9)) OR ...) SubPlan 3 -> Result (actual rows=1 loops=10000) -- authorize(..., "orgId"::text), per row Execution Time: ~1600 ms -- after: helper calls, uncorrelated Seq Scan on post (actual rows=500 loops=1) Filter: ($0 OR (hashed SubPlan 2) OR $2 OR (hashed SubPlan 4)) InitPlan 1 (returns $0) -> Result (actual rows=1 loops=1) -- permdock_has('post.read') SubPlan 2 -> ProjectSet (actual rows=1 loops=1) -- permitted_tenant_ids('post.read') Execution Time: ~1 ms ``` The benchmark asserts on `EXPLAIN (ANALYZE, FORMAT JSON)` that every node calling a helper has `loops = 1` (whether Postgres labels it an InitPlan or a hashed SubPlan), that both shapes return the same rows, and that the helper shape is at least 5x faster with an absolute ceiling. Security notes on the helpers: * They are `security definer` so they can read `role_permissions`, `user_roles` and the memberships table, which `authenticated` cannot select; the policy never needs a grant on those tables, and their own RLS does not recurse into the helper. * `set search_path = ''` and fully qualified names stop a caller from shadowing a table or function through `search_path`. * They are `stable`, take only the grant key, and read the subject inside the function (on Supabase through `permdock_user_id()`, which answers as `auth.uid()` does and returns null for an empty `sub`; see [the dialects](/docs/cli/rls#generate)) and the claims: a caller cannot pass another user's id. `member__ids_for(p_user)`, `permdock_has_for` and `permitted__ids_for` take a user, so no client role may execute them. * `execute` is revoked from `public` and `anon` and granted to `authenticated`, so `anon` policies (`anyone()` grants) never reference them. The exceptions are an `anon` policy that reads the subject, which calls `permdock_user_id()`, and a [field view](#field-security) `anon` reads: Postgres checks `execute` on every function a view calls before it runs, so the helpers are granted to `anon` too. They find no subject for `anon` (no `sub`, no role claim, no membership row) and return nothing. A hand-written policy that calls a helper says `to authenticated`: a policy for `public` or `anon` makes Postgres check `execute` for `anon` too, the statement fails, and an InitPlan over a function the role may not execute has crashed the backend (signal 11) on some Postgres builds. When such policies must stay, `rls.anonExecute: true` grants `anon` usage on the helper schema and `execute` on every helper, the same grant field views get. * `role_permissions` has RLS enabled and every privilege revoked from `anon`, `authenticated` and `public`; it changes only through a migration that re-runs `generate`. ### SQL helper contract [#sql-helper-contract] Other packages call the helpers from policies PermDock does not generate: better-supabase's Storage and Realtime policies do through the provider from [`permdock/better-supabase`](/docs/adapters/better-supabase), and an app's own RPCs may. These signatures, the claims they read and the rules below are the contract; they change only with a new major of the hook marker (`-- permdock:hook v1`) and the [hook manifest](/docs/concepts/wire-formats#supabase-hook-manifest). | Function | Returns | Answers | | --- | --- | --- | | `.permdock_has(p_grant text)` | `boolean` | The caller holds a global role whose grants include `p_grant` | | `.permitted__ids(p_grant text)` | `setof ` | The instances of `` in which the caller holds a role that grants `p_grant`, through memberships of exactly that scope; with the active-tenant claim set, only inside that tenant unless `rls.tenants` is `'all'` | | `.member__ids()` | `setof ` | The instances of `` the caller holds any live membership of, with any role; not narrowed to the active tenant | | `.member__ids_for(p_user uuid)` | `setof ` | What `member__ids()` answers for `p_user`, read from the membership sources: expired memberships, a suspended user and suspended instances drop out. Executable by `supabase_auth_admin` only, for `supabase.hook.claims` functions | | `.permdock_has_permission(p_permission text)` | `boolean` | The caller holds an unconditional global allow of the permission and no global deny of it | | `.permitted__ids_by_permission(p_permission text)` | `setof ` | The instances of `` an unconditional allow of the permission reaches, minus those a deny of it reaches | | `.permitted__ids_by_permission(p_permission text, p_conditioned boolean)` | `setof ` | With `true`, also the instances a conditioned allow of the permission reaches, minus only the instances an unconditional deny reaches; the caller applies the row condition | | `.permitted__permission_keys(p_id )` | `setof text` | Every permission key the caller holds on one instance of ``: the keys for which `permdock_has_permission` or `permitted__ids_by_permission` admits `p_id`, in one call; `authenticated` may execute it | | `.permitted__permission_keys(p_id , p_keys text[])` | `setof text` | The same, limited to the keys in `p_keys` (a former key counts as its current one); `authenticated` may execute it | | `.permitted__permission_keys_for(p_user, p_id )` | `setof text` | The same keys for `p_user`; `database` mode, executable by no client role | | `.grant_keys(p_permission text, p_scope text, p_effect text)` | `setof text` | The grant keys of the permission's unconditional allows on `p_scope`, every deny key with `p_effect` `'deny'`, and the keys of allows or denies with a row condition with `'conditioned-allow'` or `'conditioned-deny'` | | `.permdock_role_permissions(p_role, p_scope, p_tenant, p_scope_id)` | `table` | Rows `(permission, effect)`: the keys `p_role` holds on `p_scope`, `allow` or `deny`; a declared role or, in `database` mode, a custom role of the tenant; `authenticated` may execute it | | `.permdock_trusted_role_permissions(p_role, p_scope, p_tenant, p_scope_id)` | `table` | The same rows without the caller check, for server code; no client role may execute it, and `rls.trustedReaders` grants it to the roles it lists | | `.permdock_permission_keys()` | `setof text` | Every declared permission key, sorted; `authenticated` may execute it | | `.permdock_permission_keys(p_scope text)` | `setof text` | The declared keys some declared role is allowed on `p_scope` (`global` or a scope name), sorted; `authenticated` may execute it | | `.permdock_has_for(p_user, p_grant text)` | `boolean` | What `permdock_has` answers for `p_user`; `database` mode, executable by no client role | | `.permitted__ids_for(p_user, p_grant text)` | `setof ` | What `permitted__ids` answers for `p_user`, never narrowed to an active tenant; `database` mode, executable by no client role | | `.permitted__rows(p_permission text)` | `setof text` | With `rls.rowHelpers`: the ids of ``'s rows the caller may act on with `p_permission`, decided by the same allows and denies as the generated policies ([row helpers](#row-helpers)); `authenticated` may execute it | | `.permitted__row(p_row
, p_permission text)` | `boolean` | With `rls.rowHelpers`: whether the caller may act on the row value `p_row` with `p_permission`, by the same allows and denies, read from its columns instead of looked up by id ([row helpers](#row-helpers)); `authenticated` may execute it | | `.permitted__rows_for(p_user, p_permission text, p_claims jsonb)` | `setof text` | The same rows for `p_user`, with `p_claims` as the rest of the token; not in the `neon` dialect, executable by no client role | | `.permitted__ids(p_relation text)` | `setof text` | The ids of `` the caller holds `p_relation` on in the [relationship graph](#closure-table), every graph relation when `p_relation` is `null` | * **Grant keys.** `p_grant` is a permission key (`invoice.read`). PermDock's own policies pass `invoice.read#1`, `invoice.read#2` for the condition groups of one permission; the part after `#` is positional by default, so it can change when a role's grants change. SQL that does not apply a condition itself calls the permission-key forms below instead of spelling a grant key. SQL that repeats a condition names the group in the policy, `allow(permissions.invoice.read, { where: { status: 'sent' }, group: 'sent' })`, and calls `permitted__ids('invoice.read#sent')`: a named key stays the same when other grants are added, removed or reordered, and the unnamed groups keep their positional keys. `generate` refuses one name on two conditions of a permission, and two names on one condition. * **Permission keys.** `permdock_has_permission(p_permission)` and `permitted__ids_by_permission(p_permission)` take a permission key (a former key from `definePermissions` `renamed` maps to the current one) and answer with the grant keys of its unconditional allows on that scope, minus any instance a deny of the permission reaches, conditional or not. A permission whose allows on a scope all carry a row condition, a validity window or a break-glass override answers nothing there, so these forms never grant more than the policy's unconditional rows. `grant_keys(p_permission, p_scope, p_effect default 'allow')` lists those keys (`'deny'` lists the deny keys, `'conditioned-allow'` and `'conditioned-deny'` the keys that carry a row condition) for SQL that passes them to another helper. A field list (`fields`) is not a row condition: an allow limited to some fields still lists its instances, here and in `permittedIds`. `permitted__ids_by_permission(p_permission, true)` also lists the instances a conditioned allow reaches and subtracts only the instances an unconditional deny reaches, for a nested scope whose grants all carry a condition (portal contacts who may read only sent quotes): the result says where some rows may be allowed, and the query still applies the condition. In `database` mode `permdock_has_permission_for(p_user, p_permission)` and `permitted__ids_by_permission_for(p_user, p_permission)` answer for a named user, executable by no client role. A screen or RPC that checks many permissions on one organization calls `permitted__permission_keys(p_id)` once instead of one helper call per key: it returns each key `permdock_has_permission` or `permitted__ids_by_permission` would admit for that instance, global grants included, with the same rule that a conditioned allow counts for nothing. A grant at another scope is not read for this one, as with `permitted__ids_by_permission`; `permitted__permission_keys_for(p_user, p_id)` answers for a named user in `database` mode. Both take an optional second list, `p_keys`, to answer for a few keys only. They read the roles, memberships, custom roles and grants once for all keys in one query, so asking for every key of a 190-key catalog costs a few milliseconds, where a call per key costs a helper evaluation each (`tests/integration/src/rls-permission-keys-bench.test.ts` holds it under a third of the per-key time). * **Role readers.** `permdock_role_permissions(p_role, p_scope, p_tenant default null, p_scope_id default null)` answers which permission keys a role holds, for a role editor or an admin screen that would otherwise hand-write the join. `p_tenant` has the type of the tenant column (`rls.tenantType`, `uuid` by default). A declared role answers from `role_permissions` whatever the other arguments, which is the catalog; the effect is `deny` for a deny grant of the role, and a permission with several grants is listed once per effect, whatever their row conditions. With `rls.customRoles` in `database` mode, any other role name is a custom role: `p_tenant` names its tenant, `p_scope` its scope and `p_scope_id` its instance (null at the first scope), and the answer is what `resolveCustomRole` returns, so a stored entry outside the ceiling never appears and a deny of the role itself only removes an allow. Only a member of the tenant (or of the instance below the first scope) may read a tenant's custom role, and a platform role (`p_scope` `global`) only a holder of a `meta.manageRoles` permission; anyone else gets SQLSTATE `42501`, and a scope or name that cannot hold a custom role `22023`. In `jwt` mode custom roles live in claims, so an undeclared name answers nothing. `permdock_permission_keys()` lists the catalog's keys without a role, and `permdock_permission_keys(p_scope)` only the keys some declared role is allowed on that scope (`global`, or a scope name), so a role editor at the organization offers only what an organization role can hold. Neither function is executable by `anon`. Server code that reads any tenant's roles (an admin backend, a support console on a read-only role, a job) calls `permdock_trusted_role_permissions` with the same arguments: the same answer and argument checks, without the member check. It is revoked from `public`, `anon` and `authenticated`, and `rls.trustedReaders: ['service_role', 'support_reader']` grants it to those Postgres roles. * **Role and scope only.** The helpers check the role and the scope and nothing else: no `where`, `check`, relation, plan, actor or assurance. A call is correct only for a permission whose grants carry no other condition. The catalog marks each permission with `rowConditions`, `true` when a code grant has a `where`, `check`, closure, field list, purpose, break-glass override, `validFrom` / `validUntil` window, or a relation, plan, actor or assurance grantee. `permdock doctor` PD037 and `permdock rls verify --db` refuse a Storage or Realtime policy that calls a helper for a `rowConditions: true` key. * **Exact-text ids.** Scope ids compare as exact text: the helpers never apply `lower()` or trim, and neither does `decide`. A producer writing ids into a claim or a membership table writes the id's canonical text, `uuid::text` for a uuid column (Postgres prints it in lower case). An upper-case uuid in a claim is a different id to `decide`, while a `uuid` cast in Postgres would still match it, so the application and the database would disagree. * **Claims.** In `jwt` mode the helpers read `user_role`, `memberships` and the active-tenant claim (`tenant_id` by default, `rls.tenantClaim`) from `auth.jwt()`, top-level or under `app_metadata`. `schemas/supabase-claims-v1.json` in the `permdock` package is their JSON Schema: a membership is `{ scope, id, within?, roles, via?, expiresAt?, grantedBy?, reason? }` with a non-empty `roles`, and the hook also writes `memberships_truncated`, `attrs` and `authz_ver`. `permdock supabase hook generate` is the one writer; `permdock supabase inspect --json` prints which claims it writes and where the helpers live. ### Row helpers [#row-helpers] `rls.rowHelpers: true` (or a list of resource names) writes `permitted__rows(p_permission)` for each resource a grant reaches, with the resource name in snake\_case. It returns the ids of the rows the caller may read, update or delete with that permission: one `case` arm per permission of the resource, the allows the generated policies would OR together for it, minus its denies. Closure walks, link hops, the restricted stop, `includes`, groups, [`requires`](/docs/concepts/policies#requires) and [`inherit()`](/docs/concepts/relationships#inheriting-a-permission-through-a-link) apply as they do in the policies, and an action with no SQL command (`archive`, `review`) gets an arm too, so a hand-written policy or a trusted function asks `id in (select permitted_doc_rows('doc.archive'))` instead of restating the graph. It works with `--helpers-only`, where it is the generated part a hand-written policy calls. Every resource an `inherit()` grant targets gets its helper whether or not `rls.rowHelpers` lists it, written first as an empty stub so helpers that call each other can be created in any order. An unknown key, a key of another resource or a `create` permission answers no rows. `permitted__row(p_row, p_permission)` answers the same question for one row value, reading its columns instead of looking up its id. A table's `select` policy that asks `id in (select permitted_doc_rows('doc.read'))` cannot see a row the same statement inserts, so `insert ... returning` fails with a row-level security error; `using (permdock.permitted_doc_row(doc, 'doc.read'))` passes the new row itself, and an `insert` policy's `with check` can call it the same way. ```sql create policy doc_select on public.doc for select to authenticated using (permdock.permitted_doc_row(doc, 'doc.read')); ``` `permitted__rows_for(p_user, p_permission, p_claims default '{}')` answers for a stored user. It sets the subject for the one call, transaction-local, and restores the previous values: `request.jwt.claims` with `sub` and `role` merged over `p_claims`, or `.user_id` and one setting per claim in the `guc` dialect. In `jwt` mode pass the claims the user's token would carry (memberships, tenant) in `p_claims`. No client role may execute it, and the `neon` dialect, whose subject comes from `auth.session()`, gets none. ### Ownership rules [#ownership-rules] A role's `for` reaches the helpers, and the membership `exists` check of a resource role, as a kind check on each membership row (the table's `via` column or `via: { value }` constant, or the claim entry's `via`), so a staff role held through a contact membership selects no rows in the database either; a table with no `via` column holds a role with `for` for nothing. `min`, `max` and `transferOnly` become triggers on each scope's membership table, or on the tables of the `fromTable` / `fromJunction` sources that hold the scope: a deferred constraint trigger counts holders at commit, so a transfer in one transaction passes and removing the last owner fails with SQLSTATE `23514`, and statement triggers over transition tables refuse a statement that changes a transfer-only role's holder count. `permdock_can_assign(p_role, p_scope_id)` answers the `assigns` graph for the application's own policies on those tables. `decideRoleChange` checks the same rules in the application; the triggers are the backstop for writes that skip it ([ownership](/docs/concepts/ownership), [CLI](/docs/cli/rls)). `tests/integration/src/named-scopes.test.ts` and `rls-ownership.test.ts` run them against Postgres. ### Custom roles [#custom-roles] With `--custom-roles`, the scope helpers also resolve tenant-defined [custom roles](/docs/concepts/custom-roles). In `database` mode they read `custom_role_permissions (tenant_id, scope, scope_id, role, permission, effect)` and `custom_role_includes (tenant_id, scope, scope_id, role, include_role)`; in `jwt` mode, the `grants` map on each membership of the `memberships` claim. Either way, `permdock_custom_keys` turns the custom role into grant keys through the `permdock_ceiling` view, which holds only the keys of declared `assignable` roles and those roles' denies: allowed rows are unioned, denied permissions subtracted, included roles contribute their ceiling keys, and a row outside the ceiling reaches nothing. Platform custom roles (`scope: 'global'`) are rows with a null `tenant_id`, or entries of the top-level `role_grants` claim, and `permdock_has` unions them into every check. The resolution is the same one `resolveCustomRole` runs in the application, so `decide` and the database agree; In `database` mode the helpers read the custom role's name from an `rls.memberships` table or from `fromTable` / `fromJunction` membership sources alike. `tests/integration/src/rls-custom-roles.test.ts` checks both `database` layouts and `jwt` mode with `permdock rls verify --db` and `rlsParity`. Portable subset compiled by `generate` and recognised by `import`: | Portable node | `supabase` | `neon` | `guc` | | --- | --- | --- | --- | | `eq(row.user_id, principal.id)` | `(select permdock.permdock_user_id()) = user_id` | `(select auth.user_id()) = user_id` | `(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` | same via `auth.session()` | `(select current_setting('app.tenant_id', true))::uuid = tenant_id` | | `eq(principal.claim.user_role, 'admin')` | `((select auth.jwt()) ->> 'user_role') = 'admin'` | claim function | GUC read | | `eq(row.region, principal.claims.attrs.region)` (nested claim) | `region = ((select auth.jwt()) -> 'attrs' ->> 'region')` | same via `auth.session()` | `region = (nullif((select current_setting('app.attrs', true)), '')::jsonb ->> 'region')` | | `lte(row.clearance, principal.claims.attrs.clearance)` on a numeric column | `clearance <= (case when jsonb_typeof(((select auth.jwt()) -> 'attrs' -> 'clearance')) = 'number' then ((select auth.jwt()) -> 'attrs' ->> 'clearance')::numeric end)` | same via `auth.session()` | a flat setting is cast as is; a nested one like `supabase` | | `in(row.region, principal.claims.attrs.regions)` (array claim) | `region = any (array(select (e #>> '{}') from jsonb_array_elements() e where jsonb_typeof(e) = 'string'))`; `notIn` is `region is not null and not (region = any (...))` | same | the setting holds JSON text | | membership (`in(row.id, context.teamIds)`) | `id in (select team_id from team_user where user_id = (select permdock.permdock_user_id()))` | same | same | | `sqlFunction('job_permitted', { args, twin })` | `job_permitted("id")` (`--inline-functions` emits the twin) | same | same | | `{ subject: { session: { live: true } } }` | `(select permdock.permdock_session_live())`; the helper finds the token's `session_id` in `auth.sessions` ([live sessions](/docs/concepts/conditions#live-sessions)) | Refused: `rls generate` exits 2 | Refused: `rls generate` exits 2 | | A scoped role's grant | `org_id in (select permdock.permitted_tenant_ids('post.read'))`, `customer_id in (select permdock.permitted_customer_ids('quote.read'))`; the helper reads that scope's membership table (`database`) or the `memberships` claim (`jwt`) | same | same, user and claims from GUCs | | A global role's grant | `(select permdock.permdock_has('post.read'))`; the helper reads `user_roles` (`database`) or the role claim (`jwt`) | same | same | | A grant with `validFrom` / `validUntil` | `now() >= to_timestamp() and now() < to_timestamp()` ANDed into the grant's access check, on allow and deny branches alike; a one-sided window emits one comparison | same | same | | A custom role (`--custom-roles`) | No policy change: each `permitted__ids` also returns the instances where a held custom role's keys, bounded by `permdock_ceiling`, include the grant key | same | same | | `memberOf(tenant, row.org_id, roles)` in a condition, no membership table | `org_id = ((select auth.jwt()) ->> 'tenant_id')::uuid` (cast to `--tenant-type`) | same via `auth.session()` | `current_setting('app.tenant_id', true)::uuid = org_id` | | `memberOf(tenant, row.org_id, roles)` in a condition, with a membership table (`supabaseRls({ memberships })` or `--memberships
`) | `exists (select 1 from organization_members m where m.organization_id = org_id and m.user_id = (select permdock.permdock_user_id()) and m.role = any('{admin,viewer}'))` | same | same, user from the GUC | | `memberOf(, row.team_id, roles)` for a scope under the first | `exists (select 1 from team_members m where m.team_id = team_id and m.user_id = (select permdock.permdock_user_id()) and m.role = any(...))`, over that scope's table, plus the active-tenant equality on its first-scope column | same | same | | `memberOf(resource, row.id, roles)` with declared parents | A resource role: one `exists (...)` per mapped resource on the chain, each over that resource's membership table and keyed by the row field holding its id (`id` for the resource itself, `folder_id`, `project_id` for ancestors); a resource with no mapped table contributes nothing | same | same | | `related(folder.viewer, row.folderId, depth 16)` (a `through: 'parent'` grant) | `"folderId" in (select descendant::uuid from permdock.permdock_closure where resource = 'folder' and ancestor = any (array(select permdock.permitted_folder_ids('viewer'))))`; `and depth <= n` when the grant's depth is below the deepest walk of that resource; `and "restricted" is not true` when the row reaches the folder through its parent and declares `restricted` | same, subject from `auth.user_id()` | same, subject from the GUC | | `related(folder.editor, row.id, depth 0)` (an edge relation on the row's own resource) | `"id" in (select p.id::uuid from permdock.permitted_folder_ids('editor') as p(id))` | same | same | | An edge relation with `match`, `includes` or `groups` | No policy change: `permitted_folder_ids('viewer')` adds `and "role" = 'viewer'` for `match`, unions the relations `viewer` includes, and for a group row reads the members of the group through a recursive CTE bounded at 16 levels | same | same | | `related(team.lead, row.folderId)` with `through: ['folder', 'team']` | The link path from the grantee back to the row: `"folderId"::text in (select permdock.permdock_link_folder_team(array(select permdock.permitted_team_ids('lead'))))`, one `permdock_link__` helper per hop | same | same | | `(select authorize('channels.delete'))` in a hand-written policy (with `--rbac supabase`) | imported as `opaque` | opaque | opaque | | literals, `isNull`, boolean columns, `and` / `or` / `not` | literal SQL; `not` renders as `(…) is not true`, so a comparison against a NULL column counts as false before it is negated, as in the evaluator | same | same | `memberOf` is the node a tenant-, team- or resource-scoped role produces ([tenancy](/docs/concepts/tenancy)); it is portable only when the generator knows where memberships live. The dialect option `memberships` names the table and columns per scope kind (`{ tenant: { table, tenant, user, role }, team: {...}, resource: { document: {...} } }`); with an active-tenant claim configured and no table, tenant scopes compile to the claim equality and team or resource scopes are reported as non-portable. `import` recognises both the claim equality and the `exists` join and maps them back to `memberOf` when the table matches a configured mapping, otherwise to the older `in(...)` membership form. Expired memberships (`expiresAt`) compile to `and (m.expires_at is null or m.expires_at > now())` when the mapping names an `expiresAt` column. The same `memberOf` tree is what `permdock/drizzle`, `permdock/prisma` and `permdock/kysely` compile at query time: active-tenant equality, `in` over held tenant or team ids, declared-parent `or`, or an `exists` join when `options.memberships` names the table. Prisma has no `EXISTS` in `WhereInput`, so it always uses the frozen membership ids. `permdock rls generate` emits the SQL twin of that subset. ### Subject attributes (ABAC) [#subject-attributes-abac] Attribute conditions compare a row column with a claim: `where: { region: principal.claims.attrs.region, clearance: { lte: principal.claims.attrs.clearance } }`. `generate` compiles them as follows: * **Nested paths.** `principal.claims.attrs.region` (or `principal.claim.…`) walks each segment with `->` and reads the last with `->>`. Every segment must match `^[A-Za-z_][A-Za-z0-9_]*$` and is checked against the prototype-key blocklist (`__proto__`, `constructor`, `prototype`); a path has at most eight segments. In the `guc` dialect the first segment is the setting (`app.attrs`, holding JSON text) and the rest is a JSON path. * **Typed comparisons.** The claim is cast to the column's type, read from the resource's Standard JSON Schema: numbers (`integer`, `number`) compare as `numeric`, booleans as `boolean`, and `date-time`, `date` and `uuid` strings as `timestamptz`, `date` and `uuid`. A number or boolean claim must be that JSON kind; a string `"3"` against a numeric column is `null`, so the row is filtered, as `can` denies it. Other columns, and resources whose validator has no JSON Schema, compare as text. * **Array claims.** `in` and `notIn` against a claim build a Postgres array from the claim's elements of the column's kind with `array(select ...)`, which is uncorrelated, so Postgres evaluates it once per statement (an InitPlan). A missing or non-array claim is the empty array. `notIn` keeps a `null` column out, as the in-memory evaluator does. * **Request context.** A ref to `context.*` is not in the token, so no policy can read it. `generate` refuses the grant with a message naming `permdock doctor` PD027, or skips it under `--skip-closures`; the check stays in the application. Only server-set claims belong in these conditions. On Supabase that is a claim the custom access token hook adds or `app_metadata`, never `user_metadata`, which the user can edit. `tests/integration/src/rls-abac.test.ts` runs a region and clearance matrix through `rlsParity` on the `supabase`, `neon` and `guc` dialects, against `can` and the snapshot client. Anything else (`now()` arithmetic, `CASE`, multi-join subqueries, unmapped custom functions) becomes `opaque({ sql, fingerprint })`: kept verbatim for regeneration, flagged in the catalog, unusable for app-side `can`. Named helpers listed in `rls.functions` become `sqlFunction` instead. ## Closure table [#closure-table] Graph grants ([relationships](/docs/concepts/relationships)) compile against a closure table instead of a recursive query per row. When a policy has `through: 'parent'` grants, `generate` emits: * `.permitted__ids(p_relation text)` for every resource a graph grant reads, with `` in snake\_case (`chatThread` becomes `permitted_chat_thread_ids`): a `security definer` helper that returns, as text, the ids of that resource the subject holds `p_relation` on (all graph relations when it is `null`). An edge relation reads its table (`object`, `subject` and, when named, `expiresAt > now()`); a field or principal relation reads the resource's own table, with the principal relation's `period` against `now()`. It is keyed by relation, not by permission key, so a grant on `viewer` never matches an `editor` edge. * `.permdock_closure(resource, ancestor, descendant, depth)`, one row per ancestor a row reaches within the deepest `depth` any grant walks for that resource, including the row itself at depth `0`. The walk from a row stops after a restricted row whose `restricted` closes `'parent'`, so an ancestor above such a row never appears for anything at or below it. A self-parented resource whose `restricted` closes a link a grant crosses keeps 32 levels here. RLS is enabled on it, and `authenticated` reads only rows whose ancestor its own `permitted__ids(null)` returns, so the table does not expose other tenants' trees. * `.permdock_restricted_()` for a self-parented resource whose `restricted` closes a link a grant crosses: a `security definer` helper that returns, as text, the ids of the rows that are restricted, sit below a restricted row within 31 levels, or have a chain longer than that. A grant across the closed link adds `"parentId" is null or "parentId"::text not in (select .permdock_restricted_())` next to the row's own `restricted is not true`, so a restricted row hides its subtree from the link as `can` does, and a row the same statement inserts is checked through its parent. * Per self-parented table, four statement triggers (`after insert`, `after update`, `after delete` with transition tables, `after truncate`) that call `.permdock_closure_(ids)`. It refuses a write that makes a row its own ancestor (`check_violation`), recomputes the closure for the changed rows and their descendants within the depth, and runs once over the whole table when the migration is applied. A graph grant then compiles to the subquery in the table above. The helper sits in `array(select ...)` so Postgres runs it once per statement as an InitPlan; the outer `in (select descendant ...)` is a hashed subplan, also once per statement. Row ids are compared in the column's type from the resource schema (`descendant::uuid`); a column with no schema type is compared as text. `--force` on a table whose field or principal relations the helper reads from that same table is reported, since the helper would recurse through the table's own policy. `permdock rls verify --tree --db ` checks parity on a generated graph instead of fixtures: for each walked resource it seeds two chains one level past the depth, side branches that alternate restricted, child rows of every resource that parents to it and edges or relation columns for a few generated principals, then compares `can()` with what each principal selects under RLS, and rolls everything back. Required columns the generator does not set, such as a tenant foreign key, take an existing referenced value, a value the column's enum type or check constraint admits, a value from `rls.treeValues`, or a placeholder of their type, distinct per row in a column that a unique index covers, directly or through an index expression. Connect as a role that owns the tables (or bypasses RLS) and is a member of `authenticated`. The output counts checks and granted checks, so a tree nobody reaches cannot pass by agreeing. `tests/integration/src/rls-verify-tree.test.ts` runs it over the shared SaaS folder tree; `tests/integration/src/rls-graph.test.ts` runs it, and a fixed tree through moves, restriction changes and a refused cycle, on Postgres. ## Field security [#field-security] `fields` and `pick` redact columns in the application; the policies above decide rows. `--fields views` (`rls.fields: 'views'`) carries the field decision into the database as one view per table whose read grants limit fields: ```sql create or replace view "invoice_visible" with (security_invoker = true) as select "id", case when ("orgId" in (select "permdock".permitted_tenant_ids('invoice.read#1'))) or ("orgId" in (select "permdock".permitted_tenant_ids('invoice.read#2'))) or (("orgId" in (select "permdock".permitted_tenant_ids('invoice.read#4'))) and ("authorId" = (select "permdock".permdock_user_id()))) then "amount" end as "amount", case when (...) and not (select "permdock".permdock_has('invoice.read#5')) then "note" end as "note", "title" from "invoice"; ``` * **Rows** come from the table's own policies, because the view is `security_invoker`: a row the caller cannot select is not in the view either. With `--helpers-only` those policies are hand-written and the views are still generated, one mask per column over the same grants, so a customer-scope role with `fields` and a staff role without them read the same view with different columns. * **Columns** are restricted when a read allow's `fields` leaves them out or a read deny's `fields` lists them. A restricted column is `case when then col end`, where `` is `can(permission, row, { field })`: covering allows ORed, covering denies negated. The row key and every other column pass through unchanged. * **Grant keys** split by field set as well as by condition, so each helper call names the roles that reach a column: above, `invoice.read#2` is finance with `['id', 'orgId', 'title', 'amount']` and `invoice.read#5` is the auditor's deny on `note`. * **Field-only grants** (a read deny with `fields`, an allow with an empty list) leave the row policy, because in the application they never decide the row; they live in the masks. * **Cost.** Each mask term is the row policy's own uncorrelated helper call, so the helpers still run once per statement, never once per row. A view alone protects nothing while the table returns the columns to anyone who selects it, so the command warns and doctor PD030 flags it. `--revoke-columns` (`rls.revokeColumns: true`) closes the table: `anon` and `authenticated` keep `select` on only the unrestricted columns and the key. A `security_invoker` view cannot read a column its caller has lost, so the restricted columns come from a companion, `
_visible_fields`, which runs with its owner's rights. The companion lives in the helper schema (`rls.schema`, `permdock` by default), which the Data API does not serve, so Supabase's `security_definer_view` lint (0010) does not flag it; the view's roles get `usage` on that schema: ```sql create or replace view "permdock"."invoice_visible_fields" with (security_barrier = true) as select "id" as "permdock_key", case when (...) then "amount" end as "amount", ... from "public"."invoice" where (...amount mask...) or (...note mask...); create or replace view "public"."invoice_visible" with (security_invoker = true) as select t."id", f."amount", f."note", t."title" from "public"."invoice" t left join "permdock"."invoice_visible_fields" f on f."permdock_key" = t."id"; ``` The companion masks every column with the full field decision, denies included, and keeps only rows where some restricted column is readable. Reading it directly therefore shows nothing `pick` would hide: a readable column implies a readable row. `security_barrier` stops a caller's predicate from running before the masks. The companion's owner must see the rows: the table owner without `FORCE ROW LEVEL SECURITY`, or a `BYPASSRLS` role; otherwise every restricted column reads as `null`, which fails closed. Writes are unchanged: `insert`, `update` and `delete` keep their table-level grants and policies, but `returning *` and `select *` on the table now fail with `42501`. Parity is checked end to end. `rls verify` (with `rls.fields: 'views'`) and `rlsParity({ fieldViews: true })` read each fixture's row back from `
_visible` and compare the columns holding a value with those `pick` keeps, plus the key. `rls import` reads the views back as a `fieldViews` export of per-column grants. `tests/integration/src/rls-fields.test.ts` runs the matrix on Postgres for the `supabase`, `neon` and `guc` dialects, with and without `--revoke-columns`, including `anon` reads through the view. ## Request lifecycle [#request-lifecycle] `generate`: 1. Load `definePolicy` output; collect role and top-level grants and group them by resource table and action. 2. Assign grant keys (one per permission and condition group) and seed `role_permissions`; emit the helpers. 3. Flatten allows and denies per (table, command, audience) into one permissive and one restrictive policy (`--policy-per-role` keeps one per role and permission). 4. Compile each condition with the dialect and write the target: * `sql`: one idempotent migration (helpers, `drop policy if exists` + `create policy`, grants, `enable row level security`). * `drizzle`: `pgPolicy(...).link(schema.
)` exports, with `authenticatedRole` and `authUid` from `drizzle-orm/supabase` under the `supabase` dialect and `pgRole(...).existing()` otherwise ([Drizzle](/docs/adapters/drizzle)). * `prisma`: Prisma 8 `policy_*` blocks and a header naming the models that need `@@rls`. A deny grant stops generation, because Prisma 8 policies are permissive only ([Prisma](/docs/adapters/prisma)). For `drizzle` and `prisma`, everything a policy object cannot express (helpers, the closure table, grants, column privileges, `enable` and `force row level security`, field views) goes to `.migration.sql` beside the output. `--check` compares both files. 5. With `--rbac supabase`, also emit the `app_role` / `app_permission` enums, `user_roles`, and an `authorize()` function over the same `role_permissions` for hand-written policies ([Supabase provider](/docs/adapters/supabase)). The token hook comes from `permdock supabase hook generate`. `import`: 1. Read `pg_policies` (`schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check`) and `pg_class.relrowsecurity`, or parse a SQL dump. 2. Parse `qual` and `with_check` with `pgsql-parser` (libpg\_query in WASM) by wrapping each as `select 1 where ` and taking the `whereClause`. 3. Pattern-match to portable nodes (recognising both `IN (select ...)` and `EXISTS (select 1 ...)` membership forms, and `FuncCall` names listed in `rls.functions` as `sqlFunction`); split `FOR ALL` into four entries; map `roles = {public}` to all subjects. 4. Fingerprint by the deparsed AST, not the source text, because `pg_get_expr` normalises casts and parentheses. Unmapped function calls stay `opaque` and print `add rls.functions.`. 5. Write `src/permissions.generated.ts`: a deterministic `definePermissions()` with a `// @generated` header, resource schemas for the chosen validator or referencing Drizzle schemas via `drizzle-zod`, and a catalog of `table, cmd, permissive, roles, condition | opaque, fingerprint, sourceSql`. Import writes definitions only; grants stay with the author in `definePolicy`. `verify`: 1. For each fixture `{ subject, row, newRow?, action }` compute the in-process outcome with `can`. A fixture subject may carry `roles`, `memberships` and `tenant`; the runner puts them in the claims (or GUCs), which is what `jwt`-mode helpers read. In `database` mode the helpers read `user_roles` and the membership table, so the database under test holds the same memberships as the fixtures. 2. In a `BEGIN ... ROLLBACK` transaction run `set local role`, `set_config('request.jwt.claims', ..., true)` with `sub` and the tenant claim (or the GUC equivalents), execute the statement with `RETURNING`, and classify: `allowed` (rows returned), `filtered` (zero rows), `rejected` (SQLSTATE `42501`). 3. Compare: `granted` must be `allowed`; `denied` must be `filtered` (for `USING`) or `rejected` (for `WITH CHECK` and missing grants). `sqlFunction` grants compare `can()` on the twin with the database executing the real function. Emit as pgTAP files for `supabase test db` or run directly from Node with the optional `pg` peer (`--db`). The preamble in step 2 is the one PostgREST runs and the one `withPostgresClient` from `@supabase/server/middleware/postgres` runs per transaction on a direct connection: `set_config('request.jwt.claims', , true)` then `set local role` to the token's `role`, which it accepts only as `authenticated` or `anon` and refuses for anything else, `service_role` included. A policy that passes `permdock rls verify` therefore behaves the same under `ctx.postgres`, and a `toWhere` filter can ride the same client (`ctx.postgres.query` with the compiled predicate) when the app wants the filter in the query as well as in the policy. `withPostgresAdminClient` is the bypass; it belongs behind an explicit `permdock.assert` ([Supabase provider](/docs/adapters/supabase)). ## What it validates [#what-it-validates] * `generate`: every `update` / `delete` grant has SELECT coverage; no table ends with zero permissive policies for a role and command it grants; one permissive policy per table and command, so Splinter's `multiple_permissive_policies` has nothing to report; role checks run once per statement, so `auth_rls_initplan` has nothing to report either; no `service_role` policies; non-portable grants are reported with their role and grant so the author can choose `opaque` or a rewrite. * `import`: fingerprints of previously imported policies; a changed fingerprint is reported as drift rather than silently overwritten; Splinter-style findings (`auth_rls_initplan`, `multiple_permissive_policies`, `rls_enabled_no_policy`) are surfaced when the database exposes them. * `verify`: mismatches fail the run; opaque policies are reported as untestable app-side; `sqlFunction` grants are reported as verified through twin; SQL three-valued logic risks (`auth.uid()` NULL, nullable columns) are called out when a fixture row has nulls in filtered columns. ## How denials surface [#how-denials-surface] In the database, a denial is one of three observable outcomes and the runner names them the same way: | Outcome | Cause | Client sees | | --- | --- | --- | | `filtered` | `USING` policy false | zero rows, no error | | `rejected` | `WITH CHECK` false, or missing `GRANT` | SQLSTATE `42501` | | `allowed` | policy true and grant present | rows returned | The in-process side answers with a [Decision](/docs/concepts/decisions), so a `denied` decision paired with `filtered` or `rejected` is parity, and `granted` paired with anything else is a failure. HTTP adapters convert `42501` from the database into the same RFC 9457 `403` body as an in-process denial when the app opts in. ## Databases without row-level security [#databases-without-row-level-security] `permdock rls` is Postgres-only because `CREATE POLICY` is. SQLite and its hosted forms (Cloudflare D1, Turso and libSQL, Expo SQLite and op-sqlite on device), MySQL and PlanetScale, and document stores have no RLS to generate or verify. The rest of the data story still applies to them: `permdock.where` compiles the same portable condition to Drizzle, Kysely or Prisma `where` clauses for those dialects, and `filter` runs in memory. The parity suite runs on Postgres and PGlite only; `ormParity` in `permdock/testing` takes a `run` callback that queries any database, so an app on SQLite can run it against its own schema. What these targets lose is the second, database-enforced line of defence; the [threat model](/docs/security/threat-model) lists RLS as defence in depth, not as the primary control, so a D1 or Turso deployment is complete without it. On-device SQLite in React Native additionally uses the client snapshot for `filter`; the `where` compiler is the same one the server uses ([React Native](/docs/adapters/react-native)). MongoDB and local-first sync engines with their own permission languages are not `where` targets ([ecosystem index](/docs/research/ecosystem-index)). ## Example app [#example-app] `apps/examples/supabase-rls`: a Hono server on `127.0.0.1:3469` that boots without a database. `GET /rls/authorize` checks that generated SQL never contains `service_role`; `PATCH /posts/:id` uses `subjectFromSupabase` with fixed claims. ## Why [#why] * **Generated SQL keeps the evaluator's semantics, wildcards included.** A `contains` value that reaches `LIKE` unescaped turns `%` and `_` into wildcards, so the database admits rows `can` refuses. Escaping in SQL rather than in the value covers claim references too, where the value is only known at query time. * **`FORCE ROW LEVEL SECURITY` is a flag.** Without it the table owner bypasses every policy, which is the usual path for migrations, seed scripts and the Supabase dashboard. Forcing it by default would break those on the first run. `--force` (or `rls.force`) emits it for teams whose application connects as the owner. The Drizzle and Prisma targets write it to their `.migration.sql` file. * **Drizzle and Prisma get a policy file and a migration file.** drizzle-kit and Prisma 8 diff policies, so the policies belong in their schema where the migration tools see them. Neither models grants, `security definer` functions or triggers, and leaving those out would leave RLS enabled with no grant (every query `42501`) or policies calling helpers that do not exist. One generated SQL file beside the policies keeps both halves from the same run, and `--check` fails when either drifts. * **Drizzle policies use `.link`, not the table callback.** Writing into the `pgTable` call would mean editing the user's schema file. `pgPolicy(...).link(table)` attaches the policy from a separate module, so the generated file can be overwritten on every run. * **Hand-written views are a `doctor` warning, not generated SQL.** Views over application data are application SQL, and some are meant to read past RLS (a public aggregate), so doctor PD022 names each view without `security_invoker` and leaves the choice explicit ([doctor](/docs/cli/doctor)). The only views `generate` writes are the opt-in field views, and it writes them `security_invoker`. * **Field security is a per-row view, not Postgres column privileges.** RLS stayed row-level until `--fields views`, and it still is by default. Column privileges belong to a database role, not to a row or a tenant, so `grant select (amount)` cannot say that finance reads `amount` only in its own organization, and a Postgres role per app role and tenant is the design the claims-not-roles rule below rejects. A view with a `case` per restricted column evaluates the same field decision as `pick` for each row, from the same helper calls as the row policy, so it costs one helper call per statement and stays in step with the application. A permission per field (`invoice.read.amount`) would multiply keys and policies for what is one grant option. * **`--revoke-columns` is opt-in and uses an owner-rights companion.** Closing the columns breaks `select *` and `returning *` on the table, which existing code relies on, so it is a second flag. Column privileges are the only way to close a column, and a `security_invoker` view cannot read a column its caller lost, so the restricted columns go through `
_visible_fields`. The companion is safe to read directly because it masks each column with the whole field decision and keeps only rows with a readable column. The rows of `
_visible` still come from the caller's own RLS. PD022 exempts the companion by its `permdock:field-companion` comment. * **The row key always passes through.** The view is filtered and joined on it, and a client needs it to address the row it can read. A read grant whose `fields` omit the key still sees it in the database; the command warns, and parity checks count the key as kept. * **`REVOKE` / `GRANT` is always emitted, with no `--grants` flag.** The `sql` target revokes table privileges from `anon` and `authenticated` and grants back only the commands the policy uses. A policy without the matching grant fails with `42501`, and a grant without a policy is the open-by-default mistake RLS exists to prevent. Making the pair optional would allow either half to go missing. * **The `neon` dialect is pinned to `auth.user_id()`.** That is the helper `pg_session_jwt` installs, and the generated SQL calls it through `(select ...)` so Postgres evaluates it once per statement. Claims go through `auth.session()`. A different helper name is a `guc` dialect with `--guc-prefix`. * **App roles are claims, not Postgres roles.** Every generated policy targets `authenticated`. Roles, tenants and memberships reach the policy as JWT claims or GUCs, or through the membership table for `memberOf`. PermDock never generates a Postgres role per tenant or per app role: the number of Postgres roles would grow with the customer count, `GRANT` changes would need DDL at request time, and connection pools hold one role per connection. A deployment that already uses per-tenant Postgres roles makes each one a member of `authenticated` (`grant authenticated to tenant_a`), so the generated policies apply to it, and the policies still read the tenant from the claim. * **Role checks are helper functions, not inline joins.** An inline `exists` over the membership table per role, or `authorize(perm, "org_id")`, repeats the role lookup for every row; one `security definer` helper per scope answers it once per statement and keeps the grant-to-role mapping in one seeded table that `import` can read back. Collapsing to one policy per table and command keeps the plan to one filter, and `--policy-per-role` remains for teams that review policies per role. * **Attribute claims are typed by the resource schema, not by a flag.** A claim is text after `->>`, and comparing text with a numeric or date column is a type error at query time, or worse, a text comparison where `'10' < '9'`. The resource schema already says what each column is, so the generator reads it instead of asking for a per-column flag. Numbers and booleans also check the claim's JSON kind: a cast would turn the string `"3"` into `3` and grant a row the in-memory evaluator denies, and parity between `can` and the database is the point of generating the policy. Array claims use `array(select ...)` rather than a row-by-row `jsonb_array_elements` so the claim is expanded once per statement, like the role helpers. * **Casts go on the parameter, never on the column.** `((select auth.jwt()) ->> 'tenant_id')` is `text`; comparing it to a `uuid` column either fails or casts the column, which cannot use its index. `--tenant-type` (default `uuid`) casts the claim instead. The helpers, the token hook and the ownership triggers compare a membership's user and scope columns uncast too: a PL/pgSQL variable declared `
.%type` takes the value, and a statement parameter leaves Postgres to infer it from the column; `tests/integration/bench/param-casts.test.ts` checks the plan uses the index. The closure and link helpers over graph resources still take `text[]`, because a graph resource declares no id type and an array of `%type` needs Postgres 17. * **Graph grants read a trigger-maintained closure, not a recursive query.** A recursive CTE in a policy runs per row; the closure turns reachability into an indexed lookup, and statement triggers keep it current in the same transaction as the write, so a moved folder is visible or hidden as soon as it commits. The triggers stop at the same restricted rows and depth as `can`, and refuse cycles, so the database and the evaluator agree by construction and `rls verify --tree` checks it. * **`ancestor = any (array(select ...))`, not `ancestor in (select ...)`.** Postgres pulls a plain `in` subquery into a semi join, and with a set-returning helper on the inner side it can rescan the helper for every closure row. The array form is always an InitPlan, run once per statement. * **`rls migrate` rewrites only what it can prove equivalent.** A project moving off its own helpers usually has hundreds of calls in policies, and rewriting them by hand is where a key gets mistyped. The safe subset is narrow: a plain column, a literal key that maps to a declared permission, seeded on the helper's scope and free of row conditions. Anything else is reported with its line and left as written, because a guessed rewrite of a computed id or a conditioned key would silently widen or narrow access. Calls inside function bodies are never touched: those functions run with their own privileges and are often the legacy helpers themselves. Splicing by parser location rather than deparsing keeps the reviewer's diff to the calls that changed. * **`--helpers-only` exists for hand-written policies.** Some tables keep policies that a grant cannot express, and a team adopting PermDock table by table should not have to choose between generated policies everywhere and none. With only the helpers generated, `verify --introspect` cannot compare policies, so it checks what still can go wrong: the seeds exactly, and every key a hand-written policy passes. * **`rls import` proposes, never assumes, a membership table.** An `EXISTS` join over a table that `rls.memberships` does not name stays `opaque`, and the command prints a commented stanza to paste. Guessing that a table holds memberships would turn an unknown join into a grant. ## Related standards [#related-standards] * [Postgres RLS](/docs/standards/postgres-rls): `CREATE POLICY` semantics, permissive versus restrictive, `pg_policies`, the portable subset, parsers and testing. * [CLI: rls](/docs/cli/rls): command reference. * [Drizzle](/docs/adapters/drizzle), [Prisma](/docs/adapters/prisma), [Kysely](/docs/adapters/kysely), [Supabase](/docs/adapters/supabase). * [Tenants, teams and scoped roles](/docs/concepts/tenancy): the `memberOf` node and its portable compilation.