rls

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 page; the semantics and the portable subset come from the Postgres RLS research.

generateCopy Anchor Link

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 <table>_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: <resources>; otherwise its policies still target public.<resource> 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:

<out>.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:

Dialects decide how the subject reaches SQL. --dialect overrides rls.dialect from the config, and supabase is the default when neither is set:

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, 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). 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 verbsCopy Anchor Link

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:

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

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_<scope>_ids per named scope the policy declares (permitted_tenant_ids and permitted_team_ids for a policy that declares none):

member_<scope>_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:

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_<scope>_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 that anon reads calls them. Policies call them uncorrelated, so Postgres evaluates each call once per statement instead of once per row:

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:

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:

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):

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_<scope>_ids, member_<scope>_ids and member_<scope>_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 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_<scope>_ids, member_<scope>_ids, member_<scope>_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).

With the guc dialect the claims are settings: <prefix>.user_role holds a comma-separated role list and <prefix>.memberships holds the JSON array.

Active tenantCopy Anchor Link

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" = <tenant claim>, 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 <schema>.member_<first scope>_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.

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 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). rls.jsonSchema: true or 'auto' adds a pg_jsonschema check that each body matches approval-request-v1.json (body validation).

A scope's membership table names the column of its own id and of each ancestor it carries:

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

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

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.

SuspensionCopy Anchor Link

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.

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_<scope>_ids return nothing for a suspended user. With scopes.<scope>, 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 <table> s where s.<id> = <membership id> and <active>), 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_<resource>_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).

Permissions a suspended scope keepsCopy Anchor Link

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:

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_<scope>_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.
• 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), so can() agrees with RLS.

member_<scope>_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).

Suspended membershipsCopy Anchor Link

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):

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_<scope>_ids and its _for form, member_<scope>_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_<scope>_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). permdock doctor lists the keys suspended memberships keep (PD061), and permdock supabase inspect writes the column to the manifest's memberships entries and the keys to rls.suspension.memberships.keep.

Policy layoutCopy Anchor Link

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 writes the one custom_access_token_hook, reading the same user_roles table (Supabase provider). 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.

--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 <out>.migration.sql. It is off by default because a migration or seed script that connects as the owner stops seeing rows (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 actorsCopy Anchor Link

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.

rls: {
  readOnlyActors: true, // ['support', 'impersonation']
},

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 covers the in-process side.

Realtime channels and Storage bucketsCopy Anchor Link

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_<scope>_ids, so API-key ceilings, suspension and custom roles apply as they do on your tables. supabaseRls() passes both through.

import { permissions } from "./src/permissions";

rls: supabaseRls({
  realtime: {
    topics: {
      // join with supabase.channel('org:<organization id>:chat', { config: { private: true } })
      "org:{organization}:chat": { read: permissions.chat.read, write: permissions.chat.send },
    },
  },
  storage: {
    buckets: {
      // object names start with '<organization id>/'
      "org-files": {
        scope: "organization",
        read: permissions.file.read,
        write: permissions.file.upload,
        delete: permissions.file.delete,
      },
    },
  },
}),

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));

• A topic pattern is :-separated segments with exactly one {<scope>} 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_<scope>_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). 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_<op>_read_only_actors. --helpers-only writes none of these policies and says so.

API keysCopy Anchor Link

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). rls.apiKeys reads that claim, by default api_key:

{ "sub": "<user id>", "api_key": { "id": "key_1", "scopes": ["task.read"] } }
{ "sub": "", "api_key": { "id": "key_2", "tenant": "<tenant id>", "roles": ["developer"], "scopes": ["task.read", "task.update"] } }

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_<scope>_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 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_<first scope>_ids returns that tenant for the grant keys its roles hold at the first scope, and member_<first scope>_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_<scope>_ids and member_<scope>_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) 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 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(), 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 rulesCopy Anchor Link

When a role declares min, max, transferOnly, assigns or for, the output adds, next to the helpers:

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)):

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

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_<table>() 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.

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.

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_<scope>_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 rolesCopy Anchor Link

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

select permdock.permdock_replace_global_roles(
  '6f1c...'::uuid,
  array['platform-support', 'auditor']
);

Custom rolesCopy Anchor Link

--custom-roles (or rls.customRoles: true) adds, next to the helpers:

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_<scope>_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 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 roleCopy Anchor Link

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 (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), so the database repeats that check:

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:

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.

select permdock.permdock_replace_custom_role_grants(
  'o_acme', 'organization', null, 'dispatcher',
  array['job.read', 'job.update@own'], array['job.delete'], array['viewer']
);

Trusted callersCopy Anchor Link

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:

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

An application that already stores custom roles in its own tables names them in rls.customRoles.from, and generate --backfill-out <file>|<dir>/|- 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:

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 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. 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 auditCopy Anchor Link

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:

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" } },
},

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()) can name each row.

Link capabilitiesCopy Anchor Link

--capabilities (or rls.capabilities: true) adds one helper and one anon branch per resource-scoped grant:

The claim is the Capability object (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 viewsCopy Anchor Link

--fields views (or rls.fields: 'views') adds, after the policies, one view per table whose read grants (read, get, list) carry fields:

<permitted> 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_<scope>_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.

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 (<unrestricted columns and key>) for each role and adds revoke select (<restricted columns>) ... 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 <out>.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).

Declarative schemasCopy Anchor Link

Supabase's declarative schemas keep the desired schema under supabase/schemas, and the CLI writes migrations from it. --split writes one file per part:

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_<table>_<columns>_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 <file> 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-deltaCopy Anchor Link

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:

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

permdock rls generate --target sql --split helpers,seeds,indexes,policies,hook \
  --seeds-out supabase/migrations/<timestamp>_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 <version>_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.

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 <file>` (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 <schema>.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=<helper 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/<timestamp>_permdock_grants.sql \
  --seeds-out supabase/migrations/<timestamp>_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 onlyCopy Anchor Link

--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_<resource>_rows('<permission>'), 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). It is for a project whose policies stay hand-written and call permdock_has, permitted_<scope>_ids and member_<scope>_ids directly, usually after 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, in the output or the helpers part, so a portal role whose grants list fields reads its columns through <table>_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.

migrateCopy Anchor Link

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:

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

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:

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.

ShimsCopy Anchor Link

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 reports the remaining callers of each legacy name as PD056; drop the wrapper once it is quiet.

Retire a materialised tableCopy Anchor Link

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 <file>|<dir>/|- writes the migration that removes them:

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

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

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=<tables> 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 reports each table the migrations still create as PD066.

importCopy Anchor Link

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.<name> and, with --db, a slice of pg_proc.prosrc. Calls to the generated helpers are read back in both layouts: "<col>" in (select permitted_tenant_ids('<key>')) 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('<key>')) 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).

Field views are read back too. A security_invoker view named <table>_visible in the generated shape, inline or joined to its <table>_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.

verifyCopy Anchor Link

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 <n> 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). 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 <n> tree check(s) against the database (<g> granted) and exits 1 on any disagreement (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 <n> policies on <t> table(s) and <h> 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 <table>_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? }.

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('<statement>'), '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.

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

When SQL is the authority, skip generate. Pin the TypeScript policy (with sqlFunction twins) and fail the job on mismatch:

- run: pnpm exec permdock rls verify --db $DATABASE_URL --fixtures rls.fixtures.json

CICopy Anchor Link

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

RelatedCopy Anchor Link

• RLS adapter
• Postgres RLS standard
• Postgres RLS research
• Supabase provider
• Drizzle adapter
• Prisma adapter