rls

Status: planned Phase: 3

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.

generate

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

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.

Targets:

Dialects decide how the subject reaches SQL:

Semantics mirrored from the research: 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; service_role is never emitted.

Only portable conditions compile (0010): equality against the subject, in / notIn membership (emitted as IN (select ...)), literal columns, and / or / not. 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.

With --rbac-scaffold and --dialect supabase, the output also contains Supabase's recommended user_roles, role_permissions and authorize('permission') function, and policies call authorize('post.delete') instead of inlining role checks, so the named-permission catalog exists in the database too.

import

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, parses each USING and WITH CHECK expression with pgsql-parser (libpg_query compiled to WASM) and pattern-matches the AST against the portable subset, recognising the EXISTS form of membership and the Supabase auth.uid() / auth.jwt() idioms. Recognised expressions become portable conditions; everything else becomes opaque({ sql, fingerprint }), where fingerprint is a hash of the deparsed AST so formatting changes do not register as drift.

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

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

permdock rls verify --db $DATABASE_URL
permdock rls verify --db $DATABASE_URL --subjects fixtures/subjects.json --emit pgtap > tests/rls.sql

verify runs a parity test for every (subject, permission, row) triple in the fixtures: can() in-process against the PermDock policy, then 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). Outcomes per row are allowed, filtered (the row disappears from a SELECT) or rejected (error 42501). Disagreements are listed with the grant and the policy name; the exit code is 1.

Tests can be emitted as pgTAP (--emit pgtap) for teams that run database tests in CI, or executed directly from Node using the connection string. @permdock/testing exposes the same runner for Vitest.

CI

- run: pnpm exec permdock rls generate --target sql --dialect supabase --check   # generated migration up to date
- 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.

Open questions

• Whether generate should emit REVOKE / GRANT statements for table privileges alongside policies. The research recommends it because a missing grant raises the same 42501 as a WITH CHECK failure and masquerades as a policy denial; leaning yes, behind --grants.
• How approval: 'human' grants should compile: skipped with a warning (RLS has no approval concept) is the current plan.

Related

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