Postgres RLS

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)Copy Anchor Link

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.

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.

PurposeCopy Anchor Link

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). permdock rls makes the portable condition the single source: generate emits policies, import reads them back (portable where possible, opaque otherwise), and verify proves parity with fixtures.

APICopy Anchor Link

permdock rls generate --target drizzle|sql|prisma --dialect supabase|neon|guc [--rbac supabase] [--authorize database|jwt] [--memberships <table>[:tenant_col,user_col,role_col]] [--tenant-type uuid] [--policy-per-role] [--policy-name '{table}_{op}'] [--fields views [--revoke-columns]] [--out <path>]
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:

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 GRANTs 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. Supabase gives an anonymous sign-in 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)Copy Anchor Link

The role check of every grant lives in a generated function, never in the per-row part of a policy:

• <schema>.permdock_has(p_grant text) returns boolean: the subject holds a global role with the grant key.
• <schema>.permitted_<scope>_ids(p_grant text) returns setof <scope type>, one per named scope 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.
• <schema>.member_<scope>_ids() returns setof <scope type>, 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".
• <schema>.member_<scope>_ids_for(p_user uuid) returns setof <scope type>, 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_<scope>_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 function: execute is revoked from public, anon and authenticated, and only the hook migration grants it, to supabase_auth_admin.
• <schema>.permdock_has_for(p_user, p_grant text) returns boolean and <schema>.permitted_<scope>_ids_for(p_user, p_grant text) returns setof <scope type>, database mode only: what permdock_has and permitted_<scope>_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 <schema>.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):

-- 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) and the claims: a caller cannot pass another user's id. member_<scope>_ids_for(p_user), permdock_has_for and permitted_<scope>_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 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 contractCopy Anchor Link

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, 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.

• 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_<scope>_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_<scope>_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_<scope>_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_<scope>_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_<scope>_permission_keys(p_id) once instead of one helper call per key: it returns each key permdock_has_permission or permitted_<scope>_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_<scope>_ids_by_permission; permitted_<scope>_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 helpersCopy Anchor Link

rls.rowHelpers: true (or a list of resource names) writes permitted_<resource>_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 and inherit() 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_<resource>_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.

create policy doc_select on public.doc for select to authenticated
  using (permdock.permitted_doc_row(doc, 'doc.read'));

permitted_<resource>_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 <gucPrefix>.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 rulesCopy Anchor Link

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, CLI). tests/integration/src/named-scopes.test.ts and rls-ownership.test.ts run them against Postgres.

Custom rolesCopy Anchor Link

With --custom-roles, the scope helpers also resolve tenant-defined 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:

memberOf is the node a tenant-, team- or resource-scoped role produces (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)Copy Anchor Link

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 tableCopy Anchor Link

Graph grants (relationships) compile against a closure table instead of a recursive query per row. When a policy has through: 'parent' grants, generate emits:

• <schema>.permitted_<resource>_ids(p_relation text) for every resource a graph grant reads, with <resource> 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.
• <schema>.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_<resource>_ids(null) returns, so the table does not expose other tenants' trees.
• <schema>.permdock_restricted_<resource>() 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 <schema>.permdock_restricted_<resource>()) 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 <schema>.permdock_closure_<resource>(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 <url> 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 securityCopy Anchor Link

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:

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 <permitted> then col end, where <permitted> 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, <table>_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:

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 <table>_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 lifecycleCopy Anchor Link

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.<table>) exports, with authenticatedRole and authUid from drizzle-orm/supabase under the supabase dialect and pgRole(...).existing() otherwise (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).

   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 <out>.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). 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 <expr> 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.<name>.
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', <ctx.jwtClaims>, 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).

What it validatesCopy Anchor Link

• 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 surfaceCopy Anchor Link

In the database, a denial is one of three observable outcomes and the runner names them the same way:

The in-process side answers with a Decision, 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 securityCopy Anchor Link

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 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). MongoDB and local-first sync engines with their own permission languages are not where targets (ecosystem index).

Example appCopy Anchor Link

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.

WhyCopy Anchor Link

• 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). 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 <table>_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 <table>_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 <table>.<column>%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 standardsCopy Anchor Link

• Postgres RLS: CREATE POLICY semantics, permissive versus restrictive, pg_policies, the portable subset, parsers and testing.
• CLI: rls: command reference.
• Drizzle, Prisma, Kysely, Supabase.
• Tenants, teams and scoped roles: the memberOf node and its portable compilation.