# rls Source: https://permdock.com/docs/cli/rls Generate Postgres row-level security policies from a PermDock policy, import existing policies into a generated definition, and verify that both agree. `permdock rls` is the round-trip between PermDock's policy and Postgres row-level security. `generate` compiles roles and grants to policies, `import` reads `pg_policies` back into a `definePermissions()` module, and `verify` proves that `can()` in-process and the database agree. The runtime side (`permdock.where()` and the `toWhere` compilers) is documented on the [RLS adapter](/docs/adapters/rls) page; the semantics and the portable subset come from the [Postgres RLS research](/docs/standards/postgres-rls). ## generate [#generate] ```bash permdock rls generate --target drizzle --dialect supabase --out src/db/policies.ts permdock rls generate --target sql --dialect neon --out migrations/0007_rls.sql permdock rls generate --target prisma --dialect guc --out prisma/policies.prisma permdock rls generate --target sql --dialect supabase --rbac-scaffold # also emit Supabase's authorize() RBAC tables permdock rls generate --target sql --dialect supabase --memberships organization_members:organization_id,user_id,role --tenant-type uuid permdock rls generate --target sql --policy-per-role --out migrations/rls.sql # one policy per role and permission permdock rls generate --target sql --inline-functions --out migrations/rls.sql permdock rls generate --target sql --force --out migrations/rls.sql # also FORCE ROW LEVEL SECURITY permdock rls generate --target sql --fields views --out migrations/rls.sql # plus _visible field views permdock rls generate --target sql --fields views --revoke-columns --out migrations/rls.sql # and close restricted columns on the table permdock rls generate --target sql --split helpers,seeds,indexes,policies,hook \ --seeds-out supabase/migrations/20260101000001_permdock_seeds.sql # declarative schemas on pg-delta permdock rls generate --target sql --rbac supabase --helpers-only --out supabase/migrations/0056_permdock.sql # policies stay hand-written permdock rls generate --target sql --rbac supabase --out supabase/schemas/permdock.sql \ --backfill-out supabase/migrations/ # copy rls.customRoles.from into the custom-role tables ``` Inputs: the policy module from `permdock.config.ts` (roles as data) and a mapping from resources to tables. The mapping is read from `--from drizzle` (table names and columns from the Drizzle schema, resource schemas via `drizzle-zod`) or declared in the config as `rls.tables`. Without `rls.tables`, every resource is a table of the same name in `public`. With it, a resource it does not name is not assumed to be a table: with `--helpers-only` it gets no index and `generate` prints `no index is suggested for resources without an rls.tables entry: `; otherwise its policies still target `public.` and `generate` prints that it does. Map every resource that is a table; a resource that only names permissions (an analytics area, a settings page) needs no entry. A table name without a schema is in `public`; `schema.table` names a table in another schema, such as one a package installs in its own schema (`{ webhook: 'integrations.webhook_endpoints' }`). `generate`, `verify` (with `--db` and in the pgTAP output) and `verify --tree` use the qualified name as written, and the default Drizzle export and Prisma model name is the table name without its schema. The same holds for the tables in `rls.memberships` and `rls.assignments`. Targets: | `--target` | Output | | --- | --- | | `drizzle` | `pgPolicy(...).link(schema.
)` exports, using `drizzle-orm/supabase` helpers (`authenticatedRole`, `authUid`) when `--dialect supabase` and `pgRole(...).existing()` otherwise, plus `.migration.sql` | | `sql` | `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` plus `CREATE POLICY` statements, the `REVOKE` / `GRANT` statements for the target role, one file, idempotent | | `prisma` | Prisma 8 native `policy_*` blocks, with a header naming the models that need `@@rls`, plus `.migration.sql`; a deny grant fails generation | `.migration.sql` holds what a policy object cannot: helpers, the closure table, grants, column privileges, `enable` / `force row level security` and field views. Config for the two targets: | Key | Meaning | | --- | --- | | `rls.drizzle.schema` | Import path of the module exporting the tables, default `./schema` | | `rls.drizzle.exports` | Table name to export name, when it is not the table name in camelCase | | `rls.prisma.models` | Table name to model name, when it is not the table name in PascalCase | Dialects decide how the subject reaches SQL. `--dialect` overrides `rls.dialect` from the config, and `supabase` is the default when neither is set: | `--dialect` | `principal.id` | claims / tenant | | --- | --- | --- | | `supabase` | `(select permdock.permdock_user_id())` | `auth.jwt() ->> 'claim'` | | `neon` | `(select auth.user_id())` | JWT claims via `pg_session_jwt`; the anonymous role is Neon's `anonymous` | | `guc` | `(select current_setting('app.user_id', true))` | `(select current_setting('app.', true))`; the prefix is configurable with `--guc-prefix` | On Supabase, `rls generate` writes `permdock_user_id()` into the helper schema, and every generated helper, policy, field view and trigger reads the subject through it, whatever `rls.authorize`, `rls.tenants` and `rls.apiKeys` say. It returns the `sub` of `request.jwt.claims` as a uuid, and null when the setting is absent or the `sub` is empty, such as for a [tenant service key](#api-keys), where `auth.uid()` fails the uuid cast. It ignores the legacy `request.jwt.claim.sub` setting, which `auth.uid()` still reads first: a test or a job that impersonates a user sets `request.jwt.claims`, and `permdock doctor` reports PD062 for SQL that sets `request.jwt.claim.sub`. A hand-written policy that such a token can reach calls `(select permdock.permdock_user_id())` instead of `auth.uid()`. `execute` on it follows the helpers' grants; when an `anon` policy reads the subject (an `anyone()` grant with a `principal.id` condition), the output grants `anon` usage on the helper schema and `execute` on the helpers, as `rls.anonExecute: true` does. Semantics: `read` becomes `FOR SELECT USING`, `create` becomes `FOR INSERT WITH CHECK`, `update` becomes `FOR UPDATE USING (where) WITH CHECK (check)`, `delete` becomes `FOR DELETE USING`; `allow` grants are `PERMISSIVE`, `deny` grants are `RESTRICTIVE` with `NOT (condition)`; a `SELECT` policy is emitted whenever `UPDATE` or `DELETE` is, because Postgres needs it; the default target role is `authenticated` (`anyone()` grants also target `anon`, or `anonymous` on `neon`); `service_role` is never emitted, nor a role statement supautils rejects on Supabase ([PD063](/docs/cli/doctor#pd063-statements-supautils-rejects)). Grants come from `definePolicy({ roles })` and from top-level `definePolicy({ grants })`; a grant limited to a grantee Postgres cannot see (`plan()`, `actor()`, `assurance()`) or to several roles at once fails the command with the permission named. ### Action verbs [#action-verbs] Each action verb compiles to one SQL command. The defaults are `read`, `list` and `get` to `select`, `create` to `insert`, `update` to `update` and `delete` to `delete`. `rls.actions` merges over them, so an app whose verbs are `view` or `archive` says what each one means in SQL: ```ts rls: { actions: { view: "select", edit: "update", archive: "update", approve: "none", // no policy; permdock_has('expense.approve') still answers }, } ``` `'none'` compiles no policy for the verb. Every granted action is seeded into `role_permissions` either way, so a hand-written policy or function can still ask `permdock_has` about it. A verb with no entry and no default, such as `publish`, compiles no policy. ### Role checks: per-statement helpers [#role-checks-per-statement-helpers] Every generated file starts with the helper functions and the table they read, all in one schema (`--rbac-schema`, `rls.schema`, default `permdock`): `permdock_has` for global roles and one `permitted__ids` per [named scope](/docs/concepts/scopes) the policy declares (`permitted_tenant_ids` and `permitted_team_ids` for a policy that declares none): | Object | What it answers | | --- | --- | | `role_permissions (role, permission, grant_key, scope, effect)` | Which declared role holds which grant key at which scope (`global` or a scope name). Seeded by `generate` with an upsert; rows it no longer emits are deleted. | | `permdock_has(p_grant text) returns boolean` | Does the subject hold a global role with this grant key | | `permitted__ids(p_grant text) returns setof ` | Which instances of the scope the subject holds this grant key in, through memberships of exactly that scope (no cascade) | | `member__ids() returns setof ` | Which instances of the scope the subject holds any live membership of, whatever the role; no permission key, no cascade and no active-tenant narrowing | | `member__ids_for(p_user uuid) returns setof ` | `supabase` only, for a scope with a membership source or mapped table: the same for `p_user`, read from the tables, for a [`supabase.hook.claims`](/docs/adapters/supabase-hook#claims-other-packages-own) function; only `supabase_auth_admin` may execute it | | `permdock_has_permission(p_permission text)`, `permitted__ids_by_permission(p_permission text)`, `grant_keys(p_permission text, p_scope text, p_effect text)` | The same answers by permission key: the unconditional allows of the permission minus any deny, so hand-written SQL needs no positional `#n` grant key; `_for(p_user, p_permission)` forms in `database` mode. `permitted__ids_by_permission(p_permission, p_conditioned boolean)` with `true` also lists the instances a conditioned allow reaches, minus only unconditional denies, for SQL that applies the row condition itself | | `permdock_role_permissions(p_role text, p_scope text, p_tenant default null, p_scope_id text default null)`, `permdock_permission_keys()`, `permdock_permission_keys(p_scope text)` | The permission keys a role holds on a scope with their effect (a declared role, or with `customRoles` in `database` mode a custom role of the caller's tenant), and every declared key or, with `p_scope`, the keys some declared role is allowed on that scope, for role editors and admin screens; executable by `authenticated` | | `permdock_trusted_role_permissions(p_role text, p_scope text, p_tenant default null, p_scope_id text default null)` | The same rows without the caller check, for server code; executable by no client role, and by the Postgres roles `rls.trustedReaders` lists, `service_role` included | | `permdock_has_for(p_user, p_grant text)`, `permitted__ids_for(p_user, p_grant text)` | `database` mode: the answers of `permdock_has` and `permitted__ids` for `p_user`, with no active-tenant narrowing, for trusted SQL acting for a stored user; no client role may execute them | `member__ids()` answers "which organizations am I in" for a policy that has no permission to name: an organization switcher, the `organizations` row a member may read, or a co-member check on `profiles`: ```sql create policy "organizations_select" on "organizations" as permissive for select to authenticated using ("id" in (select "permdock".member_organization_ids())); ``` It drops expired memberships, suspended users and suspended instances by the same rules as `permitted__ids`. A portal contact on `customer` is not in `member_organization_ids()`, because a membership of a child scope is not one of its parent. A `memberOf` condition with no roles compiles to the same call when the scope's memberships come from `rls.membershipSources`. The helpers are `language sql stable security definer set search_path = ''` with fully qualified names; `execute` is revoked from `public` and `anon` and granted to `authenticated`, except that `anon` also gets it when a [field view](#field-views) that `anon` reads calls them. Policies call them uncorrelated, so Postgres evaluates each call once per statement instead of once per row: ```sql create policy "post_select" on "post" as permissive for select to authenticated using ((select "permdock".permdock_has('post.read')) or ("orgId" in (select "permdock".permitted_tenant_ids('post.read')))); ``` A grant key is the permission key. When roles grant one permission with different portable conditions (or effects), each condition group gets its own key, `post.update#1`, `post.update#2`, and becomes one OR branch with its condition ANDed on. Row conditions (`where`, `relation`, `principal.id`) stay in the policy; resource-scoped roles keep their `exists` join over the mapped membership table. `--authorize` chooses where the helpers read roles and memberships: | Mode | Global roles | Scope memberships | | --- | --- | --- | | `database` | `rls.roles`, default `supabase.hook.roles`, else `.user_roles (user_id, role)`: the RBAC scaffold's table, or one `generate` creates | Each scope's table from `rls.memberships.scopes.` (`--memberships` and `rls.memberships.tenant` / `.team` for the first and second scope); otherwise the `fromTable` / `fromJunction` sources in `rls.membershipSources`, default `supabase.hook.memberships`; with neither, that helper returns no rows and the command warns | | `jwt` | The `rls.roleClaim` claim (default `user_role`, a string or an array; Supabase falls back to `app_metadata`) | The `memberships` claim, `[{ scope, id, within?, roles, expiresAt? }]`; each helper reads the entries whose `scope` is its own | When the app keeps its own global-roles table, name it in `rls.roles` (the same shape as `supabase.hook.roles`, which is the default). A role column that holds a foreign key is read through the roles table by key: ```ts rls: { roles: { table: 'user_roles', // user_id, role_id role: { through: 'roles', on: { role_id: 'id' }, column: 'key' }, // roles.key holds the role name }, }, ``` `permdock_has` and `permdock_can_assign` then join `user_roles` to `roles` on `on` and compare `roles.key`, and `generate` no longer creates `user_roles`. An unqualified `through` is in the table's schema. When `rls.customRoleWrites.roles` names the same roles table with a `tenant` column, its rows with a tenant are tenant custom roles: the global-role readers (`permdock_has`, the token hook) and `permdock_replace_global_roles` consider only its rows where that column is null. `--rbac supabase` brings its own `user_roles (user_id, role app_role)`, so it refuses `rls.roles`. A scope's membership role takes the same `through`, on an `rls.memberships` table (`role`) and on a membership source (`fromTable` `columns.role`, `fromJunction` `roles`): ```ts rls: { authorize: 'database', memberships: { scopes: { organization: { table: 'organization_users', // user_id, organization_id, role_id user: 'user_id', role: { through: 'roles', on: { role_id: 'id' }, column: 'key' }, columns: { organization: 'organization_id' }, }, }, }, }, ``` Every object that reads the membership role joins the roles table and compares its key: `permitted__ids`, `member__ids` and `member__ids_for`, the custom-role match, the ownership triggers, `permdock_can_assign`, `authorize()` and the `exists` of a role-bound `memberOf` (a scalar subquery there, so the roles table cannot capture a column the policy names unqualified). A membership whose reference matches no roles row holds no role. In `jwt` mode the helpers read keys from the `memberships` claim, which the token hook mints through the same join. A tenant's custom role is a roles row whose key is unique within its tenant; the membership holds that key and resolves as a [custom role](/docs/concepts/custom-roles) of the tenant. Keep tenant keys apart from the declared role names (a check constraint on the roles table): a tenant row keyed `owner` would hold the declared `owner` role. An `rls.memberships` table whose rows hold roles in several columns lists them: `role: ['tier', { through: 'roles', on: { role_id: 'id' }, column: 'key' }]`. A row holds every non-null key. The helpers, the `memberOf` `exists`, `permdock_can_assign` and the ownership triggers expand each row into one row per key, so `min`, `max` and `transferOnly` count a user who holds `approver` in both columns once, and setting either column moves the holder count. The token hook maps the table with the same sources. A membership source may also read its user through another table (`fromTable` `columns.user`, `fromJunction` `user`), for portal contacts whose login lives on a profile: `user: { through: 'contact_profiles', on: { contact_profile_id: 'id' }, column: 'user_id' }`. In `database` mode, `permitted__ids`, `member__ids`, `member__ids_for` and `permdock_can_assign` join the profile table and compare its user column with the caller, and `rls.suspension` applies to that user and to the source's instances as for any other source: a suspended organization voids its customers' contact memberships. A row whose profile is missing or has no user holds no membership. `--indexes` adds the profile's user column and the reference. In `jwt` mode the helpers read the `memberships` claim, which the hook mints through the same join ([token hook](/docs/adapters/supabase-hook#sources)). With the `guc` dialect the claims are settings: `.user_role` holds a comma-separated role list and `.memberships` holds the JSON array. #### Active tenant [#active-tenant] By default (`rls.tenants: 'active'`) the helpers narrow to the tenant claim (`rls.tenantClaim`, default `tenant_id`) when the token carries a non-empty one: the helper of every scope under the first only returns memberships inside that tenant (the membership's `id` for the first scope, its `within` entry for the others), in both modes. Without the claim they return every tenant the subject is a member of. A `memberOf` on the first scope with no memberships table compiles to `"orgId" = `, and a nested scope's membership `exists` compares its tenant column with the claim. The Supabase token hook writes the claim from `supabase.hook.activeFrom` when the user holds a membership there, so a session whose user picked an organisation once is narrowed to it from then on. An application whose tenant comes from the URL or the query, where one session works in several tenants, sets `rls.tenants: 'all'`. The helpers then ignore the claim and admit every tenant the subject holds the permission in, the root `memberOf` compiles to `"orgId" in (select .member__ids())`, and the nested `exists` drops the tenant comparison. Each query still filters to the tenant the request names; the database guarantees membership, not the request's tenant. ```ts title="permdock.config.ts" export default defineConfig({ rls: { authorize: "database", tenants: "all" }, }); ``` `rls.anonExecute: true` grants `anon` usage on the helper schema and `execute` on the helpers, for hand-written policies that apply to `public` or `anon` and call them. Prefer `to authenticated` on those policies: a statement `anon` runs that reaches a helper it may not execute fails, and as an InitPlan it has crashed the backend on some Postgres builds. The helpers find no subject for `anon` and return nothing. `rls.approvals: true` adds the [approval store](/docs/adapters/approvals#a-generated-store-for-postgres-and-supabase) to the helpers: the `approval_requests` table and the `permdock_approval_*` functions `supabaseApprovalStore` calls, all closed to client roles. `rls.approvals: { table, token?, body?, mirror?, open?, schema? }` writes the same functions over an approvals table the app already has, attaching each request to a row the app inserted with `open: 'attach'` and into another schema with `schema` ([adopting a table](/docs/adapters/approvals#adopting-an-existing-approvals-table)). `rls.jsonSchema: true` or `'auto'` adds a pg\_jsonschema check that each `body` matches `approval-request-v1.json` ([body validation](/docs/adapters/approvals#a-generated-store-for-postgres-and-supabase)). A scope's membership table names the column of its own id and of each ancestor it carries: ```ts rls: { authorize: 'database', scopeTypes: { organization: 'uuid', customer: 'uuid' }, memberships: { scopes: { organization: { table: 'organization_users', user: 'user_id', role: 'role', columns: { organization: 'organization_id' } }, customer: { table: 'customer_contacts', user: 'user_id', role: 'role', columns: { customer: 'customer_id', organization: 'organization_id' }, }, }, }, } ``` A membership table that holds every scope (`memberships (user_id, scope, scope_id, role)`) or a contact table with fixed roles has no per-scope mapping. Pass the [membership sources](/docs/adapters/supabase-hook#sources) instead: the helpers then run the same `select` the token hook and the application's `MembershipSource` run, so the three agree by construction. A row with a null user column is never a membership. ```ts import { fromJunction, fromTable } from "permdock/supabase"; const memberships = [ fromTable({ table: "memberships", columns: { via: "via", expiresAt: "expires_at" }, }), fromJunction({ table: "contacts", scope: "customer", within: { organization: "organization_id" }, roles: ["contact"], via: "contact", }), ]; export default { rls: { authorize: "database", membershipSources: memberships }, supabase: { hook: { memberships } }, }; ``` With membership sources in `database` mode (`rls.membershipSources`, default `supabase.hook.memberships`), `permdock_can_assign` reads the sources for a scope that has no mapped table, and the holder-count and transfer-only triggers sit on each source's table ([ownership rules](#ownership-rules)). `tenant` and `team` on a table are shorthands for the first and second scope's column, so `{ table, tenant: 'org_id', user, role }` keeps working. `via` names the column holding the membership kind (`Membership.via`): when a role declares `for`, the helpers and the `exists` checks on resource and nested-scope membership tables count it only on rows whose kind is listed, and a table without a `via` column holds such a role for nothing. When every row of a table has the same kind, as in a staff table next to a separate contact table, give it as a constant instead of a column: `via: { value: 'staff' }`, the same as `fromJunction`'s `via`. The constant is checked when the SQL is generated, not per row: a role whose `for` lists the kind gets no filter, and a role whose `for` leaves it out is excluded from that table (`not (role = any(...))`, or `false` where the role is known). A table with no `via` at all is treated as a constant with no kind, so every role with `for` is excluded from it. In `jwt` mode the kind is the membership entry's `via`, and a role with `for` in the global role claim grants nothing. ### Suspension [#suspension] `rls.suspension` names the tables that say whether a user or a scope instance is active. Each entry is `{ table, id, disabledAt?, status?, active? }`: `id` is the column holding the user or instance id, `disabledAt` a nullable timestamp that suspends the row when set, and `status` a column whose value must be one of `active`. An entry needs `disabledAt`, `status` or both. ```ts rls: { suspension: { users: { table: 'profiles', id: 'id', disabledAt: 'disabled_at' }, scopes: { organization: { table: 'organizations', id: 'id', disabledAt: 'disabled_at' }, customer: { table: 'customers', id: 'id', status: 'status', active: ['active', 'prospect'] }, }, }, } ``` With `users`, `permdock_has` and every `permitted__ids` return nothing for a suspended user. With `scopes.`, a helper drops each membership whose own instance or any ancestor instance is suspended: a disabled organization voids its staff memberships and every customer contact inside it. The check is `exists (select 1 from
s where s. = and )`, so a missing row counts as suspended. It runs inside the helpers in both modes, and in every check a policy carries inline: the `exists` over a membership table for a role-bound `memberOf`, the root-scope comparison with the tenant claim, and each `permitted__ids` graph helper (user suspension only, since a relation names no scope instance). In `jwt` mode that is one indexed lookup per helper call, and a suspension applies on the next statement even though the token still carries the membership. In `database` mode the membership table of a nested scope must carry the id column of every suspendable ancestor (`columns.organization` on the customer table); otherwise `generate` fails. Scope keys accept the `tenant` / `team` aliases. The in-process side reads suspension through the `MembershipSource` ([scopes](/docs/concepts/scopes)). #### Permissions a suspended scope keeps [#permissions-a-suspended-scope-keeps] Some actions must work on a suspended tenant: cancelling a scheduled deletion, restoring it, exporting its data before a purge. `keep` on a scope lists them, as permission references or keys: ```ts rls: { suspension: { scopes: { organization: { table: 'organizations', id: 'id', disabledAt: 'disabled_at', keep: [permissions.organization.restore, permissions.invoice.export], }, }, }, } ``` Members of a suspended instance, and of every instance nested in it, then hold the kept permissions through their roles and nothing else. A membership under several suspended instances holds the keys every one of them keeps. The rule applies wherever the scope's suspension applies: * `permitted__ids(p_grant)` and its `_for` form compare the grant key's permission with the kept keys, in `database` and `jwt` mode, and so do the service-key rows of [API keys](#api-keys). * A check a policy carries inline (a role-bound `memberOf`, the root-scope comparison with the tenant claim) knows its permission when it is generated, so a kept permission's policy drops the suspension check and every other one keeps it. * `authorize(requested_permission, requested_tenant)` answers a kept permission for a suspended tenant from the member's roles, and `false` for every other. * The membership sources (`fromTable`, `fromJunction`) and so the token hook, `subject_for` and `members_of` keep the rows of a suspended instance with a `keep` list of the keys they still grant ([wire format](/docs/concepts/wire-formats#membership-and-custom-role)), so `can()` agrees with RLS. `member__ids()`, a `memberOf` condition without roles that reads it, `permdock_can_assign` and the role listings never count a suspended instance: they name no permission. `permdock doctor` lists each scope's kept keys and warns on a key the definitions do not declare ([PD061](/docs/cli/doctor)). #### Suspended memberships [#suspended-memberships] A user can be suspended in one instance while their other memberships and their sign-in stay intact. `disabledAt` on a membership table names a nullable timestamp column: a row with a value is a suspended membership. The row and its role stay, so lifting the suspension restores the same role, and only its permissions drop. Set it on an `rls.memberships` table and on the membership sources (`fromTable` `columns.disabledAt`, `fromJunction` `disabledAt`): ```ts const suspension = { memberships: { keep: [permissions.organization.leave] } }; rls: { suspension, memberships: { scopes: { organization: { table: 'organization_users', user: 'user_id', role: 'role', columns: { organization: 'organization_id' }, disabledAt: 'disabled_at', }, }, }, }, supabase: { hook: { memberships: [ fromJunction({ table: 'organization_users', scope: 'organization', id: 'organization_id', roles: 'role', disabledAt: 'disabled_at', suspension, }), ], }, }, ``` `rls.suspension.memberships.keep` lists what a suspended membership still holds through its roles, as for a scope's `keep`; without it a suspended membership holds nothing. The column applies wherever `expiresAt` does: * In `database` mode, `permitted__ids` and its `_for` form, `member__ids`, the `exists` a role-bound `memberOf` carries inline, `permdock_can_assign` and `authorize()` read the column on the table, so a suspension applies on the next statement. * The membership sources leave a suspended row out, or keep it with a `keep` list of the keys it still grants. The token hook, `subject_for`, `members_of` and the in-process `MembershipSource` read the same rows, so `can()`, `where()` and RLS agree. A helper reading the sources honours that `keep` column. * In `jwt` mode the helpers and `authorize()` read the `memberships` claim, which the hook writes without the suspended membership or with its `keep`. A suspension changes a source row, so the hook's version triggers bump the user's authorization version: in process, `claimsFirst` then reads the token as stale and every `fresh` permission denies. The helpers follow on the next token refresh, as for a removed membership. * The holder-count triggers (`min`, `max`, `transferOnly`) count no suspended membership, so suspending the last owner of an instance fails with `last-holder` as removing them would. * `member__ids()`, `permdock_can_assign` and the role listings never count a suspended membership, `keep` or not: they name no permission. `disabledAt` is one of the columns that decide a membership, so a client that can write it can lift its own suspension ([PD028](/docs/cli/doctor)). `permdock doctor` lists the keys suspended memberships keep ([PD061](/docs/cli/doctor)), and `permdock supabase inspect` writes the column to the manifest's `memberships` entries and the keys to `rls.suspension.memberships.keep`. ### Policy layout [#policy-layout] The default is one `PERMISSIVE` policy per table, command and database role, named `{table}_{op}` (`post_select`, `post_update`), so Splinter's `multiple_permissive_policies` lint stays quiet: no role sees two permissive policies for one command. `anyone()` grants are ORed into the `authenticated` policy and also get a `{table}_{op}_anon` policy `TO anon` that holds only them, and denies are one `RESTRICTIVE` `deny_{table}_{op}` policy per role. The tenant claim is cast to the tenant column's type wherever it meets a column (`"orgId" = ((select auth.jwt()) ->> 'tenant_id')::uuid`), and the tenant helper returns that type, so the comparison can use the column's index. Only portable conditions compile: equality against the subject, `in` / `notIn` membership (emitted as `IN (select ...)`), literal columns, `and` / `or` / `not`, `memberOf`, and `sqlFunction`. A `sqlFunction` grant emits `name(args)` so the database keeps its helper; `--inline-functions` (or `rls.inlineFunctions`) compiles the twin instead. Generate reports those grants as portable via twin. A closure grant fails generation with the grant's location and the message that the grant must be rewritten as a portable condition or excluded with `--skip-closures`. Grants with `approval: 'human'` are skipped with a warning, because RLS has no approval step; the table stays closed for that permission. With `--rbac-scaffold` and `--dialect supabase`, the output also contains Supabase's recommended `app_role` / `app_permission` enums, `user_roles` and the `authorize('permission', tenant)` function over the shared `role_permissions`, so the named-permission catalog exists in the database too. It emits no token hook: [`permdock supabase hook generate`](/docs/cli/supabase) writes the one `custom_access_token_hook`, reading the same `user_roles` table ([Supabase provider](/docs/adapters/supabase)). Generated policies still call the helpers, never `authorize()` per row; `authorize()` is there for hand-written policies and RPCs, and it answers "does a role hold this permission" without row conditions or denies. | Flag | Values | Default | Effect | | --- | --- | --- | --- | | `--rbac` | `supabase` | off | Emit the RBAC scaffold (`--rbac-scaffold` is an equivalent spelling). Any other value exits 2; so does a dialect other than `supabase`. | | `--rbac-schema` | a Postgres identifier | `rls.schema`, `rls.rbac.schema`, or `permdock` | Schema for `role_permissions`, the helpers, and the RBAC scaffold. | | `--authorize` | `database`, `jwt` | `rls.authorize`, `rls.rbac.authorize`, else `database` with `--rbac` or a memberships table and `jwt` otherwise | Where the helpers (and `authorize()`) read roles and memberships. `jwt` needs no query but stays stale until the token refreshes (doctor PD019). | | `--memberships` | `
:tenant,user,role[,expires_at]` | `rls.memberships.tenant` | The first scope's memberships table `database` mode reads. Other scopes come from `rls.memberships.scopes`. | | `--tenant-type` | a Postgres type | `rls.tenantType`, or `uuid` | Type of the first scope's columns: the tenant claim is cast to it and its helper returns it. `rls.teamType` does the same for the second scope and defaults to the tenant type; `rls.scopeTypes` sets any scope by name. | | `--policy-per-role` | flag | `rls.policyPerRole`, off | One policy per role and permission (`{role}_{permission}`, the layout before the helpers), each still calling the helpers. | | `--policy-name` | template | `{table}_{op}`, or `{role}_{permission}` with `--policy-per-role` | Policy names from `{table}`, `{op}`, `{role}` and `{permission}`. A collapsed layout needs `{table}` and `{op}` and rejects `{role}` and `{permission}`. | | `--custom-roles` | flag | `rls.customRoles`, off | The scope helpers also resolve tenant-defined [custom roles](/docs/concepts/custom-roles), always intersected with the generated `permdock_ceiling` view. `database` mode adds the `custom_role_permissions` and `custom_role_includes` tables; `jwt` mode reads the `memberships[].grants` claim. With `--rbac`, `authorize()` answers tenant requests from custom roles too. | | `--fields` | `views` | `rls.fields`, off | Emit one `security_invoker` view `
_visible` per table whose read grants limit `fields` ([field views](#field-views)). Any other value exits 2. | | `--revoke-columns` | flag | `rls.revokeColumns`, off | With `--fields views`, grant `anon` and `authenticated` `select` on only the unrestricted columns of each such table, so restricted columns are read through `
_visible`. Breaks `select *` on the table. Without `--fields views` it exits 2. | | `--capabilities` | flag | `rls.capabilities`, off | [Link capabilities](/docs/concepts/capabilities) reach resource-scoped grants: adds `permdock_capability_ids(p_resource, p_role, p_permission)`, which reads the `capability` claim `exchangeCapability` mints, and one `anon` policy per resource-scoped grant. A resource role with no memberships table gets only the `anon` policy, with a warning, instead of failing. | | `--db` | a Postgres URL | off | Read the database's columns and indexes and leave out every index suggestion an existing index already leads with, or whose table or column is missing ([declarative schemas](#declarative-schemas)). Needs the `pg` peer. | `--force` (or `rls.force: true`) makes the table owner subject to the policies too. The `sql` target adds `ALTER TABLE ... FORCE ROW LEVEL SECURITY` after each `ENABLE`; `drizzle` and `prisma` write it to `.migration.sql`. It is off by default because a migration or seed script that connects as the owner stops seeing rows ([RLS](/docs/adapters/rls)). `--emit` is an older spelling of `--format`. With `--rbac`, the command prints a reminder to run `permdock supabase hook generate` for the token hook. ### Read-only actors [#read-only-actors] A token that acts for a user names the actor in its `act` claim: a support session (`act.kind: 'support'`) or an impersonation (`act.kind: 'impersonation'`). The helpers read memberships, not `act`, so to Postgres such a token is its user and writes what the user may write. `rls.readOnlyActors` adds one `RESTRICTIVE` policy per table and write command that the generated policies grant to `authenticated`, named `{table}_{op}_read_only_actors`. It refuses the write when `act.kind` is one of the listed kinds, unless `act.read_only` is `false`. Reads are never touched. ```ts title="permdock.config.ts" rls: { readOnlyActors: true, // ['support', 'impersonation'] }, ``` ```sql create policy "quotes_update_read_only_actors" on "public"."quotes" as restrictive for update to authenticated using (not coalesce(((select auth.jwt()) -> 'act' ->> 'kind') = any(array['support', 'impersonation']::text[]) or (((select auth.jwt()) -> 'act' ->> 'kind') is null and ((select auth.jwt()) -> 'act') ? 'session_id'), false) or coalesce(((select auth.jwt()) -> 'act' ->> 'read_only') = 'false', false)) with check (/* the same condition */); ``` `true` lists `support` and `impersonation`; a list such as `['support']` names the kinds. With `support` listed, an `act` with a `session_id` and no `kind` (better-supabase 0.5.0 support tokens) counts as a support session. Another actor kind, such as an agent acting with delegated scopes, keeps the user's writes. An `insert` policy has only `with check`, a `delete` policy only `using`. `rls verify` checks the policies with the rest, and `--helpers-only` writes none and says so. [Support access](/docs/guides/support-access) covers the in-process side. ### Realtime channels and Storage buckets [#realtime-channels-and-storage-buckets] On the `supabase` dialect, `rls.realtime` and `rls.storage` write policies on `realtime.messages` (private channels) and `storage.objects`. Each policy admits a row only where the subject holds the permission in the scope instance that a topic segment or a folder names. It checks that through `permitted__ids`, so API-key ceilings, suspension and custom roles apply as they do on your tables. `supabaseRls()` passes both through. ```ts title="permdock.config.ts" import { permissions } from "./src/permissions"; rls: supabaseRls({ realtime: { topics: { // join with supabase.channel('org::chat', { config: { private: true } }) "org:{organization}:chat": { read: permissions.chat.read, write: permissions.chat.send }, }, }, storage: { buckets: { // object names start with '/' "org-files": { scope: "organization", read: permissions.file.read, write: permissions.file.upload, delete: permissions.file.delete, }, }, }, }), ``` ```sql create policy "permdock_realtime_org_organization_chat_select" on "realtime"."messages" as permissive for select to authenticated using (extension in ('broadcast', 'presence') and (select realtime.topic()) ~ '^org:[^:]+:chat$' and split_part((select realtime.topic()), ':', 2) in (select x::text from "permdock".permitted_organization_ids_by_permission('chat.read') x)); ``` | Entry | Commands | Policy names | | --- | --- | --- | | Topic `read` | `select`: receive broadcast and presence | `permdock_realtime__select` | | Topic `write` | `insert`: send broadcast, track presence | `permdock_realtime__insert` | | Bucket `read` | `select`: download, list | `permdock_storage__select` | | Bucket `write` | `insert` and `update`: upload, overwrite | `permdock_storage__insert`, `_update` | | Bucket `delete` | `delete` | `permdock_storage__delete` | * A topic pattern is `:`-separated segments with exactly one `{}` segment naming a declared scope. Other segments match literally. Realtime authorises the channel being joined, `realtime.topic()`, and the policies cover only the `broadcast` and `presence` extensions. * A bucket's `folder` is the 1-based `storage.foldername(name)` entry that holds the id, default 1. An `update` checks the new name too, so an object cannot move into another instance's folder. * A topic needs `read`; a bucket needs at least one of `read`, `write` and `delete`. * Each permission is a reference from your definitions. The policies call `permitted__ids_by_permission`, which counts only the allows without a row condition, minus any deny: a topic or a folder has no resource columns to check a condition against. A permission that also has a relationship grant or a `where` (a chat thread shared with one person) is accepted with a warning, and its topic admits only the holders whose role reaches the instance; the share keeps applying in the table policies. * An undeclared scope, a dialect other than `supabase`, a duplicate policy name or one over 63 bytes exits 2. * The output does not enable RLS, revoke or grant on either table. Supabase owns both, and supautils `policy_grants` lets `postgres` create the policies. * With `--split`, the policies go in the `policies` part, after your tables' policies. Under pg-delta they go in the `--seeds-out` migration instead, each in a `do` block that creates it only when `to_regclass` finds its table: the Realtime and Storage services create `realtime.messages` and `storage.objects`, so pg-delta's shadow database and a local stack with `[realtime]` or `[storage]` off have neither, and a declarative file that names them cannot apply ([pg-delta](#pg-delta)). Regenerate after enabling a service locally and reset the database to get its policies. The bucket is a row in `storage.buckets`: create it in a seed or a versioned migration. * With `rls.readOnlyActors`, the write commands get the restrictive read-only policies too, named `realtime_messages_insert_read_only_actors` and `storage_objects__read_only_actors`. `--helpers-only` writes none of these policies and says so. ### API keys [#api-keys] A backend that verifies an API key and then queries Postgres as the key's user (or, for a key that belongs to a tenant, as no user) can pass the key in a claim, so the database narrows the request the same way `subjectFromApiKey` does in process ([API keys](/docs/concepts/credentials)). `rls.apiKeys` reads that claim, by default `api_key`: ```json { "sub": "", "api_key": { "id": "key_1", "scopes": ["task.read"] } } { "sub": "", "api_key": { "id": "key_2", "tenant": "", "roles": ["developer"], "scopes": ["task.read", "task.update"] } } ``` ```ts title="permdock.config.ts" rls: { apiKeys: true, // { claim: 'api_key', scopes: 'scopes', tenant: 'tenant', roles: 'roles', serviceRoles: [] } }, ``` * **The scopes are a ceiling.** `rls generate` writes `permdock_api_key_allows(p_grant)`, and `permdock_has` and every `permitted__ids` return nothing for a grant key whose permission the claim's `scopes` list does not name. Allows that call no helper (`authenticated()`, resource memberships, relations, OAuth actors) get the same check with the permission key in their policy; an allow with [`requires`](/docs/concepts/policies#requires) passes only when the claim names every one of its required keys as well, and for a read-only permission such as `drive.read` granted through a share or another grantee that is not a role, the required keys alone are enough. Keys stored with a feature scope such as `file.read` therefore keep the read shares that require it, while a write such as an editor share's `node.update` with `requires: file.read`, or a grant with `requires: [file.read, file.update]`, needs every key it names. The required permission's own helpers apply the same ceiling, so a claim that names `drive.read` without `file.read` does not reach them either; `can()` decides the same way. The entries are permission keys (a former key from `renamed` counts as its current key); there is no wildcard, and a claim whose `scopes` is not a list allows nothing. Denies always apply, and a request without the claim is not limited. Hand-written policies that call the helpers get the ceiling with them. * **A key with a tenant and no subject is a service principal.** When `sub` is empty or missing and the claim names a `tenant`, `permitted__ids` returns that tenant for the grant keys its `roles` hold at the first scope, and `member__ids` lists it, the way a `service` credential holds one `{ tenant, roles, via: 'credential' }` membership. `serviceRoles` gives the roles of a key whose claim lists none; without either, the key holds nothing. Such a key holds no global role, no membership of another scope and no custom role, and a role with `for` must allow the `credential` kind. A suspended tenant (`rls.suspension.scopes`) suspends its keys. * **A key that names a tenant stays in it.** When the claim has a `tenant`, a user key's `permitted__ids` and `member__ids` keep only that tenant and the instances inside it, whatever `rls.tenants` says, and a scope membership check from `memberOf` compares its tenant column with it. A user in two tenants whose key names one reads only that one. A scope outside the first scope's chain, or a membership table without a column for the first scope, admits nothing for such a key, and `permdock_has` returns false: a global role reaches every tenant, so a key held to one does not carry it. The `_for` helpers name their user and ignore the claim. A verifier that sets the claim from a `Credential` copies its `tenant`, so a user key held to one tenant in process ([API keys](/docs/concepts/credentials#using-a-key)) is held to it in the database too. * **A user key without a tenant** keeps its owner's live roles and memberships under the ceiling, narrowed to the [tenant claim](#role-checks-per-statement-helpers) unless `rls.tenants` is `'all'`. `claim`, `scopes`, `tenant` and `roles` rename the claim and its fields for a key format that uses other names, such as `{ tenant: 'organization_id' }`. On Supabase the helpers and policies read the subject through [`permdock_user_id()`](#generate), which reads an empty `sub` as no user where `auth.uid()` fails. The claim is trusted as the token is: only a backend that verified the key may set it, never a client. The Supabase manifest lists the settings as `rls.apiKeys` and the helper in `rls.helpers`. ### Ownership rules [#ownership-rules] When a role declares [`min`, `max`, `transferOnly`, `assigns` or `for`](/docs/concepts/ownership), the output adds, next to the helpers: | Object | What it enforces | | --- | --- | | `permdock_holders_` (deferred constraint trigger and function) | `min` and `max` per instance of the scope, counted over live rows of its membership table (expiry and kind included), at commit. An instance with no membership rows left is being deleted and skips `min`. Errors are SQLSTATE `23514` with hint `last-holder` or `max-holders` | | `permdock_transfer_only__insert` / `_update` / `_delete` (statement triggers over transition tables) | A `transferOnly` role's holder count in an instance that had holders before a statement and has some after it must not change: move it in one `UPDATE`, or demote the only holder before promoting the next one in the same transaction ([moving a transfer-only role](/docs/concepts/ownership#moving-a-transfer-only-role)). Hint `transfer-only` | | `permdock_can_assign(p_role text, p_scope_id text) returns boolean` | Whether the signed-in user holds, on a live membership of instance `p_scope_id` (the role's own scope or an ancestor's), a role whose `assigns` lists `p_role`; global assigners count at every instance. A null `p_scope_id` assigns a global role: only a global role whose `assigns` lists it answers `true`, and a scoped role never does. `stable security definer`, `search_path = ''`, `execute` for `authenticated` only | | `permdock_can_assign_for(p_user, p_role text, p_scope_id text) returns boolean` | `database` mode: what `permdock_can_assign` answers for `p_user` (a `uuid` on Supabase, `text` otherwise), with no active-tenant narrowing, for trusted SQL that acts later for a stored user, such as accepting an invitation the inviter sent earlier. No client role may execute it | | `permdock_can_assign_any(p_role text, p_tenant, p_scope text, p_scope_id text) returns boolean` | One check for any role: `permdock_can_assign(p_role, p_scope_id)` for a declared role and, with `rls.customRoles` in `database` mode, `permdock_can_assign_custom_role(p_tenant, p_scope, p_scope_id, p_role)` for a custom one. `p_scope` `'global'` (null tenant and instance) assigns a platform role. `p_tenant` has the tenant type. `execute` for `authenticated` only | | `permdock_can_assign_any_for(p_user, p_role text, p_tenant, p_scope text, p_scope_id text) returns boolean` | `database` mode: the same over the `_for` forms, for trusted SQL acting for a stored user. Written when every function it calls has a `_for` form. No client role may execute it | The triggers sit on the scope's table in `rls.memberships`. A scope with no mapped table gets them from its membership sources (`rls.membershipSources`, default `supabase.hook.memberships`, in either mode): one function per scope counts holders over every `fromTable` and `fromJunction` source that can hold the scope, and the same trigger names go on each source table. A `fromTable` row counts for the scope its `scope` column names, a `fromJunction` row for its own scope with its fixed or column roles, expired rows count for no one, and suspension does not change the count. The source tables must be base tables, because Postgres puts no constraint or transition-table triggers on a view. A source whose `sql` carries no `holders` (a `SqlMembershipSource` you build yourself rather than with `fromTable` or `fromJunction`) has no table PermDock knows how to read row by row, so its scope gets no triggers. Without a table or a source, the output says so and only `decideRoleChange` checks the counts. `rls.ownershipTriggers: false` leaves the holder-count and transfer-only triggers out everywhere, and `rls.ownershipTriggers: { organization: false }` for the scopes it names, for an application that enforces the counts its own way; `decideRoleChange` still checks them and `permdock_can_assign` is still written. PermDock generates no policies on your membership tables. Use the helper in your own (for a role column `through` a roles table, pass the key: `(select key from public.roles where id = role_id)`): ```sql create policy organization_users_insert on public.organization_users for insert to authenticated with check ((select permdock.permdock_can_assign(role, organization_id::text))); create policy customer_contacts_insert on public.customer_contacts for insert to authenticated with check ((select permdock.permdock_can_assign(role, organization_id::text))); ``` #### Assignment triggers [#assignment-triggers] `rls.assignments` writes those checks for you as triggers. Each scope's table in `rls.memberships`, the global-roles table `rls.roles` (else `supabase.hook.roles`), and each table listed in `tables` (an invitations table that names a role), gets `permdock_assignment_
()` and a `before insert or update or delete` trigger. On a write by a client role (`authenticated`, `anon`), every role the old row and the new row hold must be one the caller may assign at that instance: a declared role through `permdock_can_assign`, a custom role through `permdock_can_assign_custom_role(p_tenant, p_scope, p_scope_id, p_role)`, which runs the custom-role write checks (membership, `rls.customRoleWrites.requires`, and authority over every permission the role allows) on its stored definition. A role that is neither is refused. `permdock_can_assign_custom_role` (and its `_for` form) is written whenever a role declares `assigns` and custom roles live in the tables, with or without `rls.assignments`. A refusal raises SQLSTATE `42501` with hint `not-assignable-by`, so removing or demoting a holder of a role the caller may not assign fails too. The trigger functions run as the invoker, so a write that does not run as a client role is trusted: the table owner, a migration, a `security definer` function such as an onboarding or invitation-accept function, and a backend role. That is the bootstrap path for the first owner of a new tenant; it needs no setting the client could also set. The triggers need a role that declares `assigns`; without one, `generate` writes none and warns. ```ts title="permdock.config.ts" rls: { authorize: "database", customRoles: true, assignments: { tables: [{ table: "invitations", scope: "organization", id: "organization_id", role: "role" }], }, }, ``` An entry in `tables` assigns at `scope`, with the instance id in `id` and the tenant column in `tenant` for a scope below the first; `role` takes the same forms as a membership table's (a column, a `through` reference, or a list), and `user` names the column `ownRole` compares with the caller. A membership source that is not a mapped table gets no assignment trigger. A row of the global-roles table assigns a global role, checked like a tenant assignment at no instance: `permdock_can_assign(role, null)` for a declared role, which only a global role whose `assigns` lists it passes, and `permdock_can_assign_custom_role(null, 'global', null, role)` for a platform custom role. A client that holds no such role cannot give itself or anyone else a global role. The generated `user_roles` table is not guarded: no client role may write it. `ownRole: 'refuse'` also refuses a client write to a row whose user is the caller, whatever the role, so nobody adds, changes or removes their own roles through the Data API, even a platform admin whose `assigns` lists the role. It applies to every guarded table with a user column: the membership tables, the global-roles table, and a listed table that names its `user`. A map refuses it only on the tables it names, by `schema.table` as configured; a named table that no trigger guards, or that has no user column, is an error. The refusal raises SQLSTATE `42501` with hint `self-demotion` for the old row and `not-assignable-by` for the new one, as `decideRoleChange` reports a change that targets the actor. ```ts title="permdock.config.ts" rls: { roles: { table: "user_roles", user: "user_id", role: "role" }, assignments: { ownRole: "refuse" }, // or only some tables: assignments: { ownRole: { "public.user_roles": "refuse" } }, }, ``` A row whose `id` is null assigns a global role, such as an invitation to a platform role. A declared role is checked by `permdock_can_assign(role, null)`, which only a global assigner passes, and a custom role as a platform custom role: `permdock_can_assign_custom_role(null, 'global', null, role)`, which needs a `meta.manageRoles` permission held through a global role. A scoped role on such a row is refused, so one invitations table can hold both tenant and platform invitations. Trusted SQL that runs later for a stored user re-checks the assignment with the `_for` forms: `permdock_can_assign_for(p_user, p_role, p_scope_id)` for a declared role and `permdock_can_assign_custom_role_for(p_user, p_tenant, p_scope, p_scope_id, p_role)` for a custom one, or `permdock_can_assign_any_for(p_user, p_role, p_tenant, p_scope, p_scope_id)` for either, so the caller needs no branch on whether the role is declared. An invitation-accept function calls them with the inviter, so an invitation stops working once the inviter may no longer assign its role. The custom-role form needs `member__ids_for` for every scope (Supabase, with each scope mapped to a table or a membership source); without it the output leaves the custom-role form out. No client role may execute either. #### Replacing a user's global roles [#replacing-a-users-global-roles] With `rls.roles` set, `generate` also writes `permdock_replace_global_roles(p_user, p_roles text[])` in the helper schema. It makes the user's rows in the global-roles table match `p_roles` in one call: it deletes the rows whose role is not in the list and inserts the roles the user does not hold yet, so an admin console saves a role editor without a delete-then-insert race. `p_user` is `uuid` on Supabase and `text` otherwise. With a role column that references a roles table (`through`), an entry that is not a key of that table raises `22023` with hint `unknown-role` before anything changes. When that table is also `rls.customRoleWrites.roles` with a `tenant` column, only its rows without a tenant count: a tenant role named `dispatcher` is neither inserted nor kept, and a key only tenant roles have is `unknown-role`. The function runs as the invoker (it is not `security definer`), so the table's own grants and policies apply to every row it writes, and the `rls.assignments` trigger judges each deleted and inserted row for a client role, with `ownRole: 'refuse'` included. `execute` is revoked from `public` and `anon` and granted to `authenticated`; it is not granted to `service_role`, which needs no trigger check and can call it after its own grant. The role column must accept a `text` value on insert (a `text` column, or a type with an assignment cast from `text`). ```sql select permdock.permdock_replace_global_roles( '6f1c...'::uuid, array['platform-support', 'auditor'] ); ``` ### Custom roles [#custom-roles] `--custom-roles` (or `rls.customRoles: true`) adds, next to the helpers: | Object | What it holds | | --- | --- | | `custom_role_permissions (tenant_id, scope, scope_id, role, permission, effect)` | `database` mode: one row per `CustomRole.grants` entry; `scope` is the scope the role is held at (default the first), `scope_id` pins it to one instance or is null, `effect` is `allow` or `deny` | | `custom_role_includes (tenant_id, scope, scope_id, role, include_role)` | `database` mode: one row per `CustomRole.includes` entry | | `permdock_ceiling` (view) | The `role_permissions` rows of declared roles marked `assignable`, in every scope including `global`, plus those roles' denies on the same permissions | | `permdock_custom_keys(p_allow, p_deny, p_include, p_scope)` | The grant keys one custom role holds in a scope, computed from the ceiling exactly as `resolveCustomRole` does | | `permdock_replace_custom_role_grants(p_tenant, p_scope, p_scope_id, p_role, p_allow, p_deny, p_include)` | `database` mode: saves one custom role, replacing its rows in both tables after the checks below; `execute` goes to `authenticated` | | `permdock_rename_custom_role_grants(p_tenant, p_scope, p_scope_id, p_from, p_to)` | `database` mode: moves a custom role's rows to a new name that has none | | `permdock_delete_custom_role_grants(p_tenant, p_scope, p_scope_id, p_role)` | `database` mode: removes a custom role's rows | | `permdock_custom_role_guard`, `permdock_custom_role_beyond` | `database` mode: the shared checks of the three functions above; no `execute` for `anon` or `authenticated` | The membership table, membership source (`fromTable`, `fromJunction`) or claim holds the custom role's name like any role; a declared role name never resolves as custom. With membership sources in `database` mode, a custom role matches on the source row's instance of the first scope (its `id` for that scope, `within` for a nested one) as `tenant_id`, and on its `id` as `scope_id`, so a nested source needs `within` set to match. Each `permitted__ids` unions the declared branch with the custom one, so a policy needs no change. Both tables have RLS enabled and every privilege revoked from `anon`, `authenticated` and `public`; the application writes them from its own server connection, the same data its `RoleSource` reads. A row naming a permission outside the ceiling reaches no key, so writing to the tables directly can never widen access. In `jwt` mode each membership may carry `grants`, a map from custom role name to compact entries: `key` allows, `-key` denies, `@role` includes. A custom role rides the membership of the scope it is held at. `customRoleClaim(roles)` from `permdock` builds the map for a token hook; the claim is read only with `--custom-roles`, and it adds a few bytes per entry to every token. A [platform custom role](/docs/concepts/custom-roles#platform-custom-roles) is a row with `scope = 'global'` and null `tenant_id` and `scope_id`; check constraints refuse any other combination. `permdock_has` unions its keys from `user_roles` in `database` mode, and from the top-level `role_grants` claim (the same compact map, keyed by role name) in `jwt` mode, so a global custom role answers in every tenant like a declared global role. With `--rbac supabase`, the scaffold's `user_roles.role` is `text` instead of the `app_role` enum when `--custom-roles` is on, because a custom role name is data, not a declared value. A stored entry under a key renamed with `definePermissions(..., { renamed })` resolves like the current key: `permdock_custom_keys` reads each former key as its current key, for allows, denies and the ceiling alike, so the database and `resolveCustomRole` agree during the deprecation window. #### Saving a custom role [#saving-a-custom-role] In `database` mode, save a custom role with `permdock_replace_custom_role_grants` instead of writing the tables. It runs as `security definer` and applies the rules of `validateCustomRole` and `assignablePermissions` to the signed-in caller (`auth.uid()` or the dialect's subject): * `p_scope` is a declared scope with a non-null `p_tenant`, or `global` with a null `p_tenant` and `p_scope_id` for a [platform custom role](/docs/concepts/custom-roles#platform-custom-roles) (`unknown-scope`). `p_role` is not a declared role name (`declared-role`). * The caller holds a live membership of `p_tenant`, or of instance `p_scope_id` of `p_scope`, or holds a permission with `meta.manageRoles` through a global role (`not-member`). A platform custom role takes the global `meta.manageRoles` permission; a membership is not enough. * With `rls.customRoleWrites.requires`, the caller holds one of the named permissions in `p_tenant` (or at instance `p_scope_id`, or through a global role), and only through a global role for a platform custom role (`manage-roles`). `requires: 'manageRoles'` names every permission with `meta.manageRoles`. The check runs before any hand-out check, so a member who may not manage roles cannot save, rename or delete a role, even one inside what they hold. * Each `p_allow` entry (`key`, or `key@level` when resources declare levels) is a declared key (`unknown-permission`), inside the ceiling of `p_scope` (`outside-ceiling`) and at a declared level (`unknown-level`). Each `p_deny` entry is a declared key; each `p_include` entry is a declared role whose allows are inside the ceiling (`unknown-role`, `outside-ceiling`). A former key is stored as its current key. * The caller may hand out every permission the role would allow, and every level of a leveled one: it holds the permission in `p_tenant` (or at instance `p_scope_id`), through declared roles, custom roles or global grants, and its own grants reach the level. A platform custom role counts only global grants. Holding a permission with `meta.manageRoles` lifts this check, as in-process (`not-assignable-by`). * The same hand-out check runs on the role's stored definition, so a caller cannot rewrite or remove a role that allows more than it may hand out. Without `rls.customRoleWrites.requires`, any member may write a role inside what they may hand out, and may remove a role whose permissions they all hold. Set it to the permission the application's role editor asserts (`member.assignRole` in the [recipe](/docs/concepts/custom-roles#recipe-a-permission-matrix-editor)), so the database repeats that check: ```ts title="permdock.config.ts" export default defineConfig({ rls: { authorize: "database", customRoles: true, customRoleWrites: { requires: ["member.assignRole"] }, }, }); ``` `generate` exits `2` when `requires` names an undeclared key, no key, or `'manageRoles'` while no permission declares `meta.manageRoles`. A failed check raises `22023` (invalid definition) or `42501` (caller), with the reason above as the error `hint`. `permdock_rename_custom_role_grants` and `permdock_delete_custom_role_grants` run the same membership and stored-definition checks; a rename refuses a declared name and a name that already has rows (`role-exists`). Call them in the transaction that renames or deletes the role in the application's own roles table, and in the one that updates memberships naming it. A role held only through `meta.manageRoles` on the role, not on a permission, does not lift the check in SQL. `jwt` mode has no tables, so these functions are not emitted. When the application keeps one row per custom role in a table of its own, `rls.customRoleWrites.roles` names it and `generate` adds `permdock_cascade_custom_role()` and an `after update or delete` trigger on it, so the grants follow the row instead of every write path calling the rename and delete functions: ```ts title="permdock.config.ts" customRoleWrites: { roles: { table: "roles", // one row per role key: "key", // the role name tenant: "organization_id", // null: a platform custom role (scope global) scope: "scope", // optional text column naming the scope; default the first scope id: "scope_id", // optional: the instance the role is pinned to skip: "builtin", // optional boolean column: rows that are declared or system roles }, }, ``` A rename, or a move to another tenant, scope or instance, carries the role's grants and includes along, refusing a target that already has rows (`role-exists`); a delete removes them; rows the `skip` column marks are ignored. A signed-in caller at trigger depth 1 passes the write functions' checks: membership and authority over the stored definition where the role was, and after a move, membership and authority where it lands and entries inside that scope's ceiling. A migration, a job or a nested trigger moves the rows as the function owner. The roles table's own RLS still decides who may update or delete its rows. ```sql select permdock.permdock_replace_custom_role_grants( 'o_acme', 'organization', null, 'dispatcher', array['job.read', 'job.update@own'], array['job.delete'], array['viewer'] ); ``` #### Trusted callers [#trusted-callers] `permdock_trusted_replace_custom_role_grants`, `permdock_trusted_rename_custom_role_grants` and `permdock_trusted_delete_custom_role_grants` take the same arguments and run the definition checks (`unknown-scope`, `declared-role`, `unknown-permission`, `outside-ceiling`, `unknown-level`, `unknown-role`, `role-exists`) without the caller checks: no subject, membership or hand-out check. Use them where no tenant member is the caller: a seed or migration, a background job, a platform console that checks the operator in the application, or a backend that provisions default roles for a new tenant. A platform custom role is written with `p_tenant` and `p_scope_id` null and `p_scope` `'global'`. `execute` is revoked from `public`, `anon` and `authenticated`, so only the function owner calls them until a migration of yours grants another role. `generate` never grants them to a role, `service_role` included: ```sql grant execute on function permdock.permdock_trusted_replace_custom_role_grants(uuid, text, text, text, text[], text[], text[]) to provisioning_backend; ``` The argument types follow `rls.tenantType`. Grant only a role whose sessions the application controls: the trusted functions believe their caller. #### Backfill from existing tables [#backfill-from-existing-tables] An application that already stores custom roles in its own tables names them in `rls.customRoles.from`, and `generate --backfill-out |/|-` writes a data migration that copies them into `custom_role_permissions` and `custom_role_includes`. An object for `rls.customRoles` turns custom roles on like `true`. Each role row comes from `rls.customRoleWrites.roles`, which supplies the name, tenant, scope, instance and `skip` flag, so that key is required: ```ts title="permdock.config.ts" rls: { authorize: "database", customRoles: { from: { permissions: { table: "role_permissions", // one row per permission a role holds role: { through: "roles", on: { role_id: "id" }, column: "key" }, permission: "permission", // or a reference to a permissions table effect: "effect", // optional: 'allow' or 'deny'; default allow level: "level", // optional: when resources declare levels }, includes: { table: "role_includes", role: { through: "roles", on: { role_id: "id" }, column: "key" }, include: { through: "roles", on: { included_id: "id" }, column: "key" }, }, }, }, customRoleWrites: { roles: { table: "roles", key: "key", tenant: "organization_id", skip: "system" } }, migrate: { helpers: {}, prefixes: { "organization.": "" } }, }, ``` The migration starts with `-- permdock:backfill v1` and holds one `do` block. For each roles-table row that is not skipped and is not a declared role, it maps every stored key the way [`migrate`](#migrate) does (`rls.migrate.keys`, then `renamed`, then the longest `rls.migrate.prefixes` match) and saves the role through `permdock_trusted_replace_custom_role_grants`, so the same definition checks apply. An entry PermDock cannot hold is left out: an effect other than `allow` or `deny`, an undeclared key or level, a key outside the scope's ceiling, an undeclared include or one that allows more than the ceiling. The block ends with a notice counting the copied roles and one warning listing up to 50 dropped entries. A role that already has rows in either table is skipped, so running the migration again copies nothing and keeps any edit made after the copy. A directory output gets a new versioned file only when the newest `permdock:backfill` file differs, and `--check` reports a stale one, as with [`--seeds-out`](#declarative-schemas). `generate` exits `2` when `--backfill-out` is set without `rls.customRoles.from`, in `jwt` mode (build the claim with `customRoleClaim` instead), without `rls.customRoleWrites.roles`, or when a `through` or `column` is not that table and its key. ### Triggers and audit [#triggers-and-audit] The tables `generate` creates take triggers the application declares, so they live in the generated files instead of a hand-written migration that has to run after them. `rls.triggers` maps a generated table to its triggers; `rls.audit` registers tables with an audit module that creates its own trigger: ```ts title="permdock.config.ts" rls: { triggers: { custom_role_permissions: [ { name: "notify_role_change", when: "after", events: ["insert", "update", "delete"], function: "app.notify_role_change", args: ["roles"] }, ], }, audit: { function: "better_supabase.audit", args: { category: "permissions" } }, }, ``` | Key | Meaning | | --- | --- | | `rls.triggers.
` | `name` (`permdock_` is reserved), `when` (`before` or `after`), `events`, `level` (`row` by default), a schema-qualified `function`, `args` | | `rls.audit.function` | A schema-qualified function called once per table as `function('.
'::regclass, )` | | `rls.audit.tables` | Default: `custom_role_permissions`, `custom_role_includes` and `user_roles`, among those this run creates | | `rls.audit.args` | Named arguments passed after the table, as string literals | Each trigger is written as `drop trigger if exists` followed by `create trigger`, after the table it is on. A `truncate` trigger needs `level: 'statement'`. The audit calls are data statements, so with `--split` they go in the `seeds` part next to the seeds when that part is split out, and after the helpers otherwise. `generate` exits `2` for a table this run does not create, an unqualified function, a reserved name or an unknown event. `custom_role_permissions` and `custom_role_includes` carry an `id bigint generated always as identity primary key`, so an audit module that reads a table's primary key (such as [better-supabase's `audit()`](/docs/adapters/better-supabase#audit-the-permdock-tables)) can name each row. ### Link capabilities [#link-capabilities] `--capabilities` (or `rls.capabilities: true`) adds one helper and one `anon` branch per resource-scoped grant: | Object | What it does | | --- | --- | | `permdock_capability_ids(p_resource text, p_role text, p_permission text)` | `stable`, `search_path = ''`, executable by `anon` and `authenticated`. Returns the resource id in the `capability` claim when its `v` is `1`, `holder` is `link`, `on.resource` is `p_resource`, `roles` includes `p_role`, `permissions` (when present) includes `p_permission` and `expiresAt` is in the future; nothing otherwise. | | `
__anon` policies | `to anon`: `""::text in (select permdock_capability_ids('', '', ''))`, ANDed with the grant's row condition. `` is the row's id for a role on the row's own resource, or the parent field for a role on an ancestor, exactly as `can()` matches a resource membership. | The claim is the `Capability` object ([wire formats](/docs/concepts/wire-formats)) inside a short-lived Supabase access token with `role: 'anon'`, which the application's server mints with `exchangeCapability` after `subjectFromCapability` verified the link; no policy trusts a capability the server did not exchange. Privileges follow the policies: `anon` gets only the commands it has a policy for. ### Field views [#field-views] `--fields views` (or `rls.fields: 'views'`) adds, after the policies, one view per table whose read grants (`read`, `get`, `list`) carry `fields`: | Object | What it holds | | --- | --- | | `
_visible` | `with (security_invoker = true)`: every column of the resource schema in order. A column some read allow leaves out of its list, or some read deny lists, is `case when then "" end`; every other column, and the row key, is the table's column unchanged. Granted `select` to `authenticated`, plus `anon` when an `anyone()` or link branch reads the table | | `
_visible_fields` | With `--revoke-columns` only: in the helper schema (`rls.schema`), `with (security_barrier = true)`, owned by whoever runs the migration. The row key as `permdock_key` and each restricted column masked the same way, for the rows where one of them is readable. `
_visible` reads the table's rows as the caller and left-joins it on the key. Commented `permdock:field-companion
_visible` | `` for a column is `can(permission, row, { field })` in SQL: the OR of the branches of read allows whose `fields` include the column (or have none), ANDed with `not` of the OR of covering read denies. Each branch is the row policy's own clause: the uncorrelated helper call (`permitted__ids` or `permdock_has`) and the grant's row condition, so the helpers still run once per statement. A branch with no helper call (an `authenticated()` grant) adds the signed-in check its `to authenticated` policy gave it. Grant keys also split by field set, so `invoice.read#2` (finance, four fields) and `invoice.read#1` (admin, every field) reach different columns. ```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'))) then "amount" end as "amount", "title" from "invoice"; ``` A read deny with `fields` and a read allow with an empty list only shape columns, as in the application (a field-only deny does not deny the row), so they leave the row policy and live in the masks. The resource needs a schema with Standard JSON Schema output to list its columns; without one the command fails with the resource named. Without `--revoke-columns` the table still returns every column to a direct read, and the command prints which. With it, the `sql` target replaces the table-level `select` grant by `grant select ()` for each role and adds `revoke select () ... from anon, authenticated`; `insert`, `update` and `delete` keep their table-level grants, so `returning *` and `select *` on the table fail with `42501` and clients read through the view. `drizzle` and `prisma` put the views and the column statements in `.migration.sql`. With `--force` the companion's owner is subject to the table's policies, which target only `anon` and `authenticated`, so it needs `BYPASSRLS` or every restricted column reads as `null`; the command warns. Doctor PD030 warns while field-limited columns stay readable on the table ([doctor](/docs/cli/doctor)). ### Declarative schemas [#declarative-schemas] Supabase's [declarative schemas](https://supabase.com/docs/guides/local-development/declarative-database-schemas) keep the desired schema under `supabase/schemas`, and the CLI writes migrations from it. `--split` writes one file per part: | Part | Holds | | --- | --- | | `helpers` | `role_permissions` (and its seeds without the `seeds` part), `permdock_has`, `permitted__ids`, `member__ids`, the RBAC scaffold with `--rbac`, the closure and break-glass tables | | `seeds` | The `role_permissions` rows, starting with `-- permdock:seeds v1` | | `indexes` | `create index if not exists` for every column the policies filter on (scope keys, and condition fields compared with the subject, a claim or a membership) and for each membership table's user column, starting with `-- permdock:indexes v1` | | `policies` | `enable row level security`, the table grants and every `create policy` | | `hook` | The token hook from `permdock supabase hook generate`; needs `supabase.hook` | Give the parts in any order and any subset; they are written as `helpers`, `seeds`, `indexes`, `policies`, `hook`. Without the `indexes` part, each index is printed as an `index suggestion` warning instead. The part names its indexes `permdock_
__idx`. An index is suggested only for a column a generated policy or helper looks rows up by: a scope key, a condition field compared with the subject, a claim or a membership (`ownerId: principal.id`, `memberOf`), and a membership table's user column followed by its scope column. A condition that compares with a constant (`status: 'sent'`), a negation, a range and an array `contains` get no index: they filter rows the scope key already found. With `--db $DATABASE_URL`, `generate` reads the database's columns and indexes and leaves out an index that an existing one already leads with (an index on `(organization_id, created_at)` serves `(organization_id)`), so an index the project already has under another name is not created twice. A target on a table or column the database does not have is left out with a `no index suggested on ...` warning. Partial and expression indexes do not count as covering. Without `--db`, nothing is read and every target is written; drop a line the project already has, or leave the part out and keep the indexes in your own schema files. Because `--db` makes the `indexes` part depend on the database it reads, run `--check` with the same `--db` or without it on both sides. Without `--split`, `--out` is one file as before. The `role_permissions` rows are data, which no schema diff carries, so the `seeds` part goes to `--seeds-out ` when it is set (a versioned migration; `-` prints it). Every policy change that changes a grant key needs a new seeds migration, and doctor PD054 warns when the last seeds in the migrations differ from what the policy compiles to. `--check` compares every part, the grants and the seeds file, and names each part that drifted (`rls generate drift: policies: missing supabase/schemas/public/policies/permdock.sql`). #### pg-delta [#pg-delta] With `[experimental.pgdelta] enabled = true` in `supabase/config.toml`, and no `--out` or `rls.out`, `--split` writes pg-delta's per-schema, unnumbered layout under `declarative_schema_path` (default `supabase/schemas`). pg-delta orders statements by their dependencies, so no file needs a number: | Part | File | | --- | --- | | `helpers` | `/helpers.sql` | | `indexes` | `/indexes.sql` | | `policies` | `public/policies/permdock.sql` | | `hook` | `/functions/custom_access_token_hook.sql` | `` is the helper schema (`permdock` unless `rls.schema` says otherwise). pg-delta refuses a declarative file that inserts rows, so the `helpers` part needs the `seeds` part with `--seeds-out`. The shadow database pg-delta loads the declarative files into has no `realtime.messages` or `storage.objects`, so with `rls.realtime` or `rls.storage` the `policies` part leaves their policies out and the `seeds` migration carries them, guarded by their tables; the `policies` part then needs the `seeds` part with `--seeds-out` too. pg-delta keeps grants and policies, so the hook keeps its `supabase_auth_admin` grants and `--grants-out` is not needed. `permdock supabase hook generate` without `--out` writes the same hook path. An explicit `--out` with `{part}` overrides the layout. ```bash permdock rls generate --target sql --split helpers,seeds,indexes,policies,hook \ --seeds-out supabase/migrations/_permdock_seeds.sql supabase db schema declarative sync --no-apply -f permdock # a reviewed migration for the schema files ``` `declarative sync` replays the migrations before it diffs, so the seeds migration has to sort after the migration that creates `role_permissions`: generate the schema files, sync, then write the seeds to a migration named after the synced one. `--seeds-out` may name the migrations directory instead of a file (a path that ends in `/` or an existing directory). `generate` then compares the rows with the newest migration there that starts with `-- permdock:seeds v1` and writes a new `_permdock_seeds.sql` only when they differ, numbered one second after the newest versioned migration in the directory (the clock is read only when the directory has none), so the same directory always gives the same name and a repeated `--check` names the same file, so an unchanged policy writes nothing and an applied migration is never rewritten in place. With `--check` it fails when that newest migration is missing or out of date. Run it after `declarative sync` or `db diff`, so the new migration sorts after the one that creates `role_permissions`; doctor PD054 warns when the newest seeds migration no longer matches the policy. ````bash permdock rls generate --target sql --split helpers,seeds,indexes,policies,hook --seeds-out supabase/migrations/ ``` Doctor PD042 and PD043 are off under pg-delta. In CI, run the generate step with `--check`, and run `declarative sync` and expect `No schema changes found`. #### supabase db diff Without pg-delta, `supabase db diff` writes the migrations and applies the schema files in the order of `[db.migrations] schema_paths`. Give `--out` a `{part}` placeholder, such as `supabase/schemas/identity/056_permdock_{part}.sql`. `db diff` compares the two databases with migra, which diffs tables, functions, views, triggers, policies and table privileges, but not schema privileges, function privileges, view privileges or view options. `--grants-out ` (with the `hook` or `helpers` part) moves exactly those statements out of the parts into a file of their own, which starts with `-- permdock:grants v1`: - From the `hook` part: `usage` on each schema the hook reads, the execute grants on the hook and on each `supabase.hook.claims` function to `supabase_auth_admin`, the revoke on the hook from `authenticated`, `anon` and `public`, the revokes on the version and protection trigger functions, and the revoke on the hook schema from `public`. - From the `helpers` part: the revoke from `public` and the `usage` grant on the helper schema, the execute grants and revokes on every helper, the revoke on the `permdock_ceiling` view, and `alter view .permdock_ceiling set (security_invoker = true)` for the option `db diff` drops from the view. The `select` grants and `permdock_auth_admin_read_*` policies on the tables the hook reads, and the revokes on `role_permissions`, `user_roles`, the custom-role tables and the version table, stay in the parts: `db diff` diffs them, so a migration that left them out would drop them on the next diff. Each part says where the rest went in its second line. When both parts are split, the file holds the helpers' statements first and the hook's after them, under the hook's marker; with only the `helpers` part it starts with `-- permdock:grants v1 schema=`. `--grants-out -` prints them instead. ```bash SPLIT="--split helpers,seeds,policies,hook --out supabase/schemas/identity/056_permdock_{part}.sql" permdock rls generate $SPLIT --grants-out - --seeds-out - # the schema files, grants and seeds printed supabase db diff -f permdock # a migration for helpers, policies and hook supabase migration new permdock_grants # empty migrations that sort after it supabase migration new permdock_seeds permdock rls generate $SPLIT --grants-out supabase/migrations/_permdock_grants.sql \ --seeds-out supabase/migrations/_permdock_seeds.sql ```` The grants migration has to sort after the `db diff` migration that first creates the hook, which is why it is created second. `create or replace function` keeps the grants, so later `db diff` migrations need no new grants file; one that drops and recreates the hook or a helper, or adds a helper, does. Every statement in the file can run again, so regenerate it into a new migration after such a change. Doctor PD042 fails while no migration from the one that creates the hook on grants it to `supabase_auth_admin`. PD043 warns when `schema_paths` applies a file that calls the helpers before the `helpers` part; with no `schema_paths`, files apply in path order, so number the helpers part below the files that call it. In CI, run the same command with `--check`. PermDock's integration suite checks the split with `@pgkit/migra`, the engine `supabase db diff` runs, called with the CLI's options (`add_all_changes(true)`, privileges included): a database built from the migrations (the parts and the grants file) and one built from the schema files alone produce an empty diff in both directions. ### Helpers only [#helpers-only] `--helpers-only` (or `rls.helpersOnly: true`) writes the helpers, their `role_permissions` seeds and the scaffold, and no table policies, grants or `enable row level security`. Add `rls.rowHelpers: true` (or a list of resources) for `permitted__rows('')`, the rows one permission reaches with every graph and `requires` rule applied, so the hand-written policies call it instead of restating closure walks and link hops ([row helpers](/docs/adapters/rls#row-helpers)). It is for a project whose policies stay hand-written and call `permdock_has`, `permitted__ids` and `member__ids` directly, usually after [`migrate`](#migrate) has moved them off a home-grown set of helpers. It needs `--target sql` and combines with `--split helpers` (a `policies` part is an error). With `--fields views` it also writes the [field views](/docs/adapters/rls#field-security), in the output or the `helpers` part, so a portal role whose grants list `fields` reads its columns through `
_visible` while the row policies stay hand-written; `--revoke-columns` is refused, because the table grants are yours, so revoke the restricted columns from client roles in them. `verify --introspect` then checks the helpers and the hand-written policies' keys instead of the generated policies. ## migrate [#migrate] ```bash permdock rls migrate --rbac supabase --sql supabase # dry run: every rewrite and every skipped call permdock rls migrate --rbac supabase --sql supabase --write # apply them permdock rls migrate --rbac supabase --sql supabase --json # { rewrites, skipped, retire } permdock rls migrate --rbac supabase --sql supabase --retire-out supabase/migrations/ # drop rls.migrate.tables once unread ``` `migrate` rewrites calls to a project's own SQL helpers in `create policy` and `alter policy` statements onto the generated ones, file by file, under `--sql` (a file or a directory of `.sql` files). It parses each file with `pgsql-parser` (the same optional peer as `import`) and splices only the call's text, so comments and formatting around it are kept. It takes the generate flags, because it checks every key against what `generate` would seed. Without `--write` it only prints. `rls.migrate` names the helpers and how keys map: ```ts rls: { migrate: { helpers: { org_ids_with_permission: { form: 'ids', scope: 'organization' }, // (key) -> setof id has_org_permission: { form: 'row', scope: 'organization' }, // (id, key) -> boolean authorize_scope: { form: 'scoped' }, // (scope, id, key) -> boolean is_system_user_with: { form: 'global' }, // (key) -> boolean is_org_member: { form: 'membership', scope: 'organization' }, // (id) -> boolean }, keys: { 'organization.customers.view': 'customers.read' }, // exact, checked first; then definePermissions renamed prefixes: { 'organization.': '', 'system.': 'platform.' }, // longest prefix wins globalScopes: ['system'], // scoped literals that mean global }, }, ``` | Form | Before | After | | --- | --- | --- | | `ids` | `org_ids_with_permission('k')` | `permdock.permitted_organization_ids('k2')` | | `row` | `has_org_permission(col, 'k')` | `(col in (select permdock.permitted_organization_ids('k2')))` | | `scoped` | `authorize_scope('organization', col, 'k')` | the `row` form, on the named scope (`rls.migrate.scopes` renames a literal) | | `scoped`, global | `authorize_scope('system', null, 'k')` | `(select permdock.permdock_has('k2'))` | | `global` | `is_system_user_with('k')` | `(select permdock.permdock_has('k2'))`, without the `select` when the call already is one | | `membership` | `is_org_member(col)` | `(col in (select permdock.member_organization_ids()))` | The rewritten forms are uncorrelated, so Postgres runs each helper once per statement. A call is reported and left as written, with its file, line and reason, when the rewrite could change what it admits: | Reason | When | | --- | --- | | `not-a-column` | The id is an expression, a function call or a parameter, not a plain column | | `dynamic-key` | The key is not a string literal | | `unknown-key` | The mapped key is not in the policy; the command exits `1` while one remains | | `row-conditions` | The key's grants carry row conditions the helpers do not apply; let `generate` write that table's policy | | `not-granted-on-scope` | No role holds an allow for the key on that scope (a deny seed does not count), or its action is not one `generate` seeds (`read`, `list`, `get`, `create`, `update`, `delete`) | | `missing-helper` | `generate` writes no such helper, such as `member__ids` for a scope without a membership source | | `function-body` | The call is inside a `create function` or `do` body, which runs with its own privileges | | `not-in-policy` | The call is in a view, a trigger or any statement other than a policy | | `unparsed` | The file does not parse | A key renamed with `definePermissions(..., { renamed })` maps to its current key without a `keys` entry: `keys` wins, then `renamed`, then `prefixes`. `generate` also seeds every `role_permissions` row again under each former key, so SQL that still passes an old key keeps the access the current key has until the alias is removed. ### Shims [#shims] ```bash permdock rls generate --target sql --rbac supabase --helpers-only --shims ``` `--shims` (or `rls.shims: true`, or `rls.shims: { schema: 'legacy' }`) adds one wrapper per `rls.migrate.helpers` entry under its legacy name, in `public` unless a schema is set. Each wrapper maps the key the way `migrate` does and calls the generated helper, so function bodies, views and triggers that `migrate` leaves alone keep working while they are rewritten by hand. The generated helpers take grant keys, and a permission whose grants differ by condition has several (`quote.read#1`, `quote.read#2`) and none named after the permission. So each wrapper carries, per helper scope, the grant keys of every permission as a literal map that `generate` writes from the same seeds as `role_permissions`: the keys of its unconditional allows, and all of its deny keys. A wrapper answers the instances where the caller holds any of those allow keys, minus those where it holds a deny key; the global form answers the same with `permdock_has`. A wrapper cannot apply a row condition or a validity window, so a conditional allow answers nothing through it and a conditional deny always subtracts: a staff role with `quote.read` answers, a contact role whose `quote.read` needs a row condition does not. Rewrite those callers to policies `generate` writes. Regenerate the wrappers whenever the seeds change. The wrappers are `language sql stable security definer` with `search_path = ''`. They read no table, and call only the helpers, which read the caller's claims, so they answer the same as `security invoker` would; as `security definer` a caller needs `execute` on the wrapper and not `usage` on the helper schema, so a server role that calls the legacy helper keeps working without a grant on that schema. `execute` goes to `authenticated` only. Without `rls.migrate.helpers` the flag exits `2`. `create or replace` cannot change an existing function's argument names or return type, so drop a legacy helper with a different signature first. [`doctor`](/docs/cli/doctor) reports the remaining callers of each legacy name as PD056; drop the wrapper once it is quiet. ### Retire a materialised table [#retire-a-materialised-table] A project whose helpers read a permissions table it keeps in step with triggers (`user_permissions`, refreshed when memberships change) names that table in `rls.migrate.tables`. `migrate` then reports, per table, the triggers and functions that refresh it and everything that still reads it, and `--retire-out |/|-` writes the migration that removes them: ```ts title="permdock.config.ts" migrate: { helpers: { has_org_permission: { form: "row", scope: "organization" } }, tables: { user_permissions: {}, // refresh functions found by what writes the table "app.member_grants": { refresh: ["app.rebuild_member_grants"] }, // or named }, }, ``` ```bash permdock rls migrate --rbac supabase --sql supabase --retire-out supabase/migrations/ ``` `migrate` reads every file under `--sql` in path order and keeps each object's last definition, so a function replaced by a [shim](#shims) or a view dropped in a later file no longer counts. A refresh function is one whose body inserts into, updates, deletes from, truncates, merges into or refreshes the table, one listed in `refresh`, or one that calls such a function; a refresh trigger is a trigger on another table that runs one. A reader is a policy on another table, a view or a function that names the table and is not a refresh function. A legacy helper from `rls.migrate.helpers` that nothing calls any more (the PD056 count is zero) is not a reader: the migration drops it. | Report line | Meaning | | --- | --- | | `retire
: N refresh trigger(s), N refresh function(s), N reader(s)` | What the migration would drop, and what still blocks it | | `read by at :` | One reader: `policy`, `view` or `function` | | `retire
: no longer created by --sql` | A later file already drops it | While any listed table still has a reader, `--retire-out` writes nothing and the command exits `1`. Otherwise the migration starts with `-- permdock:retire v1 table=` and drops, in order, the refresh triggers, the refresh functions, the uncalled legacy helpers and the tables, each with `if exists` and without `cascade`, so a dependant the analysis missed fails the migration instead of disappearing. A directory output gets a versioned file only when the newest `permdock:retire` file differs, and once that file is under `--sql` the next run prints `nothing to retire`. `--json` adds a `retire` object with `tables` and `helpers`. [`doctor`](/docs/cli/doctor#pd066-a-materialised-table-still-created) reports each table the migrations still create as PD066. ## import [#import] ```bash permdock rls import --db $DATABASE_URL --out src/permissions.generated.ts permdock rls import --db $DATABASE_URL --out src/permissions.generated.ts --schema valibot permdock rls import --db $DATABASE_URL --out src/permissions.generated.ts --from drizzle ``` `import` reads `pg_policies` (with `--db`, optional `pg` peer) or a SQL dump (`--sql`), parses each `USING` and `WITH CHECK` expression with `pgsql-parser` (libpg\_query compiled to WASM; an optional peer, so run `pnpm add -D pgsql-parser` first, or `import` prints that line and exits) and pattern-matches the AST against the portable subset, recognising the `EXISTS` form of membership, the Supabase `auth.uid()` / `auth.jwt()` idioms, and `FuncCall` names listed in `rls.functions`. Recognised expressions become portable conditions; mapped functions become `sqlFunction` with the configured twin; everything else becomes `opaque({ sql, fingerprint })`, where `fingerprint` is a hash of the deparsed AST so formatting changes do not register as drift. Unmapped functions print `add rls.functions.` and, with `--db`, a slice of `pg_proc.prosrc`. Calls to the generated helpers are read back in both layouts: `"" in (select permitted_tenant_ids(''))` and the team form become `memberOf` with the roles that `role_permissions` (the seeds in a dump, or the live table with `--db`) lists for that key, and `(select permdock_has(''))` stays `opaque` in the condition, because a global role has no row form. Each catalog entry lists those branches under `grants` as `{ key, permission, scope, roles, where? }`, so the imported file still names which roles each policy admits. An `EXISTS` or `IN` subquery over a table that `rls.memberships` does not name stays opaque, and the command prints a commented `rls.memberships.tenant` stanza for that table with the default column names to edit; it never assumes the table holds memberships. The output is a deterministic `definePermissions()` module with a `// @generated` header: one resource per table with its actions derived from the policy commands, resource schemas emitted for the validator chosen with `--schema zod|valibot|arktype` from the table's columns, or referencing existing Drizzle tables through `drizzle-zod` with `--from drizzle`. Conditions are emitted as a role fragment alongside, so the generated file can be merged with hand-written definitions and policy fragments ([Larger apps](/docs/getting-started/larger-apps)). Field views are read back too. A `security_invoker` view named `
_visible` in the generated shape, inline or joined to its `
_visible_fields` companion, becomes an entry of a `fieldViews` export: `{ view, table, companion?, passthrough, restricted: [{ column, grants, denies }] }`, where `grants` and `denies` are the helper branches of the column's mask in the same `{ key, permission, scope, roles, where? }` form, and the command prints which columns are field-limited. With `--db` the views come from `pg_get_viewdef` and `pg_class.reloptions`. Other views are ignored. No other tool imports RLS into application permissions; Kysera's `@kysera/rls` is the only dual-mode prior art and does not read from the database. ## verify [#verify] ```bash permdock rls verify --fixtures rls.fixtures.json permdock rls verify --db $DATABASE_URL --fixtures rls.fixtures.json permdock rls verify --fixtures rls.fixtures.json --format pgtap > tests/rls.sql permdock rls verify --db $DATABASE_URL --tree permdock rls verify --db $DATABASE_URL --introspect permdock rls verify --advisors --db $DATABASE_URL --json ``` `verify` runs a parity test for every (subject, permission, row) triple in the fixtures: `can()` in-process against the PermDock policy, then, with `--db` (optional `pg` peer), the same operation in the database under `set local role authenticated` with the subject's claims in `request.jwt.claims` (Supabase) or the GUCs (`guc`): `sub`, the tenant claim, the role claim and `memberships`, so `jwt`-mode helpers see the fixture's subject. A `create` fixture inserts every column of `row`; an `update` fixture with `newRow` writes its columns and compares against `can()` on the `{ current, next }` pair. A fixture file may be `{ fixtures, customRoles }`: the custom roles resolve in-process through a `RoleSource`, travel in the `memberships[].grants` claim, and, with `rls.customRoles` and `rls.authorize: 'database'`, are written to the custom-role tables inside each fixture's rolled-back transaction, so connect as a role that owns those tables. A row that already exists is kept (`on conflict do nothing`), so fixtures can name custom roles the database already holds. Outcomes per row are `allowed`, `filtered` (the row disappears from a `SELECT`) or `rejected` (error `42501`). Disagreements are listed with the grant; the exit code is `1`. On success it prints `verified fixture(s) in-process`, or `in-process and against the database` with `--db`. Opaque grants are reported as untestable app-side. With `--db`, `verify` also reads `pg_policies` on `storage.objects` and `realtime.messages` and reports a `PD037` mismatch for each policy that calls a helper with a permission whose grants carry row conditions ([doctor](/docs/cli/doctor)). `sqlFunction` grants compare the twin in memory with the live function and are reported as verified through twin. `--tree` (needs `--db`) checks graph grants on a generated object graph instead of fixtures: two chains one level past each walked resource's depth, side branches that alternate restricted, child rows and edges for a few generated principals, all seeded and rolled back in one transaction, then compared with `can()` per principal. A required column the generated rows leave out (`NOT NULL` without a default, such as a tenant column or a name) gets an existing value of the table its foreign key references, the first label of its enum type, a value its single-column check constraint admits (the first literal of an `= 'x'` or `in (...)` check, or the bound of a `> n` or `>= n` check), or a placeholder of its type. A column that a unique index covers, such as `name` in a `(drive_id, parent_id, name)` index or in an expression index on `(drive_id, coalesce(parent_id, ''), lower(name))`, gets a different value in each seeded row: a numbered text, a new uuid, a counted number from the check's bound or a later date, unless its enum or an equality check allows only one value. `rls.treeValues` sets columns of every row seeded into a table, keyed by the table name as configured, for a constraint the generator cannot read: `treeValues: { 'public.file_nodes': { kind: 'file', name: 'tree' } }`. The generated id, parent, restricted and relation columns always win. Connect as a role that owns the tables and is a member of `authenticated`. It prints `verified tree check(s) against the database ( granted)` and exits `1` on any disagreement ([closure table](/docs/adapters/rls#closure-table)). `--introspect` (needs `--db`) compares the live catalogs with what `rls generate` would write from the same config and generate flags, without running fixtures. It reads `pg_policies`, `pg_class.relrowsecurity`, the `anon` and `authenticated` table grants and `pg_proc`, with catalog queries modelled on postgres-meta's (not a dependency), and prints one line per difference: a generated policy that is missing, or whose command, `permissive` / `restrictive` mode or roles differ; a policy on a generated table that generate does not write (a hand-written permissive policy widens access); a table without row level security; a missing grant (which answers `42501` instead of filtering) or a grant no policy allows; a `security definer` helper that is missing, is no longer `security definer`, or no longer sets `search_path = ''`. Policy expressions are not compared, since Postgres rewrites their text; the fixtures and `--tree` check what they do. It also prints a `warning:` for each column the generated SQL filters on that no index starts with (the same columns as the `indexes` part; an expression or partial index does not count, and a column the table does not have is skipped). A missing index slows the policy down but grants nothing, so the warnings do not fail the command. It prints `introspected policies on table(s) and helper(s): no drift`, or exits `1` with the list. With `--helpers-only` there are no generated policies to compare, so `--introspect` checks a mixed setup instead: the helpers as above, the `role_permissions` rows against the seeds exactly (`missing` and `unexpected` rows), and every policy outside the Supabase system schemas (plus `storage.objects` and `realtime.messages`) by the keys it passes to the helpers. A key the policy does not declare always denies, and a key whose grants carry row conditions grants more than the application does; both are drift. A table with row level security whose policies call no helper is printed as `info:` and does not fail the command. With `rls.fields: 'views'`, every instance read fixture also reads its row from `
_visible` (or the table itself when no view exists) and compares the columns that hold a value with those `pick` keeps, plus the key, which always passes through; a denied row must return nothing. Fixture rows are compared column by column, so give each read fixture the whole row as the database holds it. Statements read back only the key (`returning "id"`), so a table closed with `--revoke-columns` does not reject them. `--advisors` runs the Supabase CLI's security advisors, `supabase db advisors --type security`, on `--db` or, without it, on the local stack (`--local`), instead of the fixtures. It looks for `supabase` in the project's `node_modules/.bin`, then on `PATH`, and exits `2` with an install line when neither has it. Each finding prints its level, its Splinter lint name, the doctor check that reports the same problem in migrations, its detail and its remediation link. It exits `1` on any `WARN` or `ERROR` row. `--json` prints `{ findings, errors, warnings }`, where each finding is `{ name, level, detail, remediation, cacheKey, code? }`. | Lint | Doctor check | | --- | --- | | `function_search_path_mutable` (0011) | PD048 | | `auth_rls_initplan` (0003) | PD051 | | `rls_disabled_in_public` (0013) | PD050 | | `security_definer_view` (0010) | PD022 | | `anon_security_definer_function_executable` (0028) | PD049 | | `authenticated_security_definer_function_executable` (0029) | PD046 | The repository runs Splinter's lints itself on the SQL `rls generate` writes: `tests/integration` applies several fixtures on the `supabase/postgres` image, as `postgres`, and fails on any `WARN` or `ERROR` row about an object the generated SQL created. `--format pgtap` writes the same checks as a pgTAP script for `supabase test db` or `pg_prove`, so CI can run them without Node or the `pg` peer. `verify` first decides every fixture in-process; a fixture that disagrees with its `expected` or names an unknown permission prints the mismatch and exits `1` without a script. The script then plans one test per fixture and, inside one transaction that it rolls back, creates `pg_temp.permdock_decision(statement text)`, writes the fixture custom roles as above, and for each fixture opens a savepoint, runs `set local role authenticated`, binds the subject with the same settings as `--db`, and asserts `is(pg_temp.permdock_decision(''), 'granted' | 'denied', ...)` with the outcome `can()` gave. The statement is the one `--db` runs, with the fixture values inlined as untyped literals, and `permdock_decision` answers `granted` when it returns a row and `denied` when it returns none or fails, the same rule as `--db`. An opaque grant becomes `skip()`. The field-view comparison (`rls.fields: 'views'`) and `--tree` run only with `--db`. The fixture rows must already exist in the database the script runs against, as with `--db`. ```bash permdock rls verify --fixtures rls.fixtures.json --format pgtap > supabase/tests/permdock_rls_test.sql supabase test db ``` `permdock/testing` exposes the same runner for Vitest. ## Database-first CI [#database-first-ci] When SQL is the authority, skip `generate`. Pin the TypeScript policy (with `sqlFunction` twins) and fail the job on mismatch: ```yaml - run: pnpm exec permdock rls verify --db $DATABASE_URL --fixtures rls.fixtures.json ``` ## CI [#ci] ```yaml - run: pnpm exec permdock rls generate --target sql --dialect supabase --check # generated migration up to date # declarative schemas: the same --split, --out and --grants-out as the generate step, plus --check - run: pnpm exec permdock rls verify --db $DATABASE_URL # in the job with a Postgres service ``` `tests/integration` in this repository runs `verify` against Postgres in testcontainers. ## Related [#related] * [RLS adapter](/docs/adapters/rls) * [Postgres RLS standard](/docs/standards/postgres-rls) * [Postgres RLS research](/docs/standards/postgres-rls) * [Supabase provider](/docs/adapters/supabase) * [Drizzle adapter](/docs/adapters/drizzle) * [Prisma adapter](/docs/adapters/prisma)