Postgres RLS

Status: planned Phase: 3

permdock rls is the CLI surface (with a permdock/rls runtime for the parity runner) that compiles roles and grants to Postgres row-level security policies, imports existing policies back into generated definitions, and verifies that in-process decisions and database outcomes agree.

Purpose

A permission such as allow(permissions.post.update, { where: { authorId: subject.id } }) should be true in the UI, in the API and in the database. Today teams hand-write the RLS twin of every app rule and the two drift. No existing tool goes from RLS to application permissions, and the only dual-mode prior art (Kysera @kysera/rls) generates one way from raw SQL (RLS research). permdock rls makes the portable condition the single source: generate emits policies, import reads them back (portable where possible, opaque otherwise), and verify proves parity with fixtures.

API

permdock rls generate --target drizzle|sql|prisma --dialect supabase|neon|guc [--rbac supabase] [--memberships <table>[:tenant_col,user_col,role_col]] [--out <path>]
permdock rls import   --db $DATABASE_URL | --sql schema.sql --out src/permissions.generated.ts [--schema zod|valibot|arktype] [--from drizzle]
permdock rls verify   --db $DATABASE_URL [--fixtures rls.fixtures.ts] [--format pgtap|node]

Semantics the generator applies:

Additional rules: a SELECT policy is generated (or its coverage asserted) whenever update or delete grants exist, because Postgres requires SELECT access to filter and for RETURNING; ENABLE ROW LEVEL SECURITY is always emitted; REVOKE ALL ... FROM anon, authenticated followed by the exact GRANTs is emitted next to the policies, because a missing grant raises 42501 and masquerades as a policy denial; helper calls are wrapped as (select auth.uid()) so they run once per statement; an index suggestion is printed for every filtered column.

Portable subset compiled by generate and recognised by import:

memberOf is the node a tenant-, team- or resource-scoped role produces (tenancy); it is portable only when the generator knows where memberships live. The dialect option memberships names the table and columns per scope kind ({ tenant: { table, tenant, user, role }, team: {...}, resource: { document: {...} } }); with an active-tenant claim configured and no table, tenant scopes compile to the claim equality and team or resource scopes are reported as non-portable. import recognises both the claim equality and the exists join and maps them back to memberOf when the table matches a configured mapping, otherwise to the older in(...) membership form. Expired memberships (expiresAt) compile to and (m.expires_at is null or m.expires_at > now()) when the mapping names an expiresAt column.

Anything else (now() arithmetic, CASE, multi-join subqueries, custom functions) becomes opaque({ sql, fingerprint }): kept verbatim for regeneration, flagged in the catalog, unusable for app-side can.

Request lifecycle

generate:

1. Load definePolicy output; group grants by resource table and action.
2. Flatten allows and denies per (table, command, role class) into permissive and restrictive policies.
3. Compile each condition with the dialect; emit pgPolicy(...) entries (Drizzle, using authenticatedRole and authUid from drizzle-orm/supabase), an idempotent migration (sql: drop policy if exists + create policy, grants, enable row level security), or Prisma 8 policy_* blocks with @@rls.
4. With --rbac supabase, also emit the user_roles / role_permissions tables, the custom_access_token_hook, and the authorize() function; named permissions compile to authorize('key') (Supabase provider).

import:

1. Read pg_policies (schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check) and pg_class.relrowsecurity, or parse a SQL dump.
2. Parse qual and with_check with pgsql-parser (libpg_query in WASM) by wrapping each as select 1 where <expr> and taking the whereClause.
3. Pattern-match to portable nodes (recognising both IN (select ...) and EXISTS (select 1 ...) membership forms); split FOR ALL into four entries; map roles = {public} to all subjects.
4. Fingerprint by the deparsed AST, not the source text, because pg_get_expr normalises casts and parentheses.
5. Write src/permissions.generated.ts: a deterministic definePermissions() with a // @generated header, resource schemas for the chosen validator or referencing Drizzle schemas via drizzle-zod, and a catalog of table, cmd, permissive, roles, condition | opaque, fingerprint, sourceSql.

verify:

1. For each fixture { subject, row, newRow?, action } compute the in-process outcome with can. A fixture subject may carry memberships and tenant; the runner writes the memberships into the configured membership table inside the transaction so the exists join sees the same state the evaluator does.
2. In a BEGIN ... ROLLBACK transaction run set local role, set_config('request.jwt.claims', ..., true) (including the tenant claim), set_config('request.jwt.claim.sub', ..., true) (or the GUC equivalents), execute the statement with RETURNING, and classify: allowed (rows returned), filtered (zero rows), rejected (SQLSTATE 42501).
3. Compare: granted must be allowed; denied must be filtered (for USING) or rejected (for WITH CHECK and missing grants). Emit as pgTAP files for supabase test db or run directly from Node with pg.

What it validates

• generate: every update / delete grant has SELECT coverage; no table ends with zero permissive policies for a role and command it grants; no service_role policies; non-portable grants are reported with their role and grant so the author can choose opaque or a rewrite.
• import: fingerprints of previously imported policies; a changed fingerprint is reported as drift rather than silently overwritten; Splinter-style findings (auth_rls_initplan, multiple_permissive_policies, rls_enabled_no_policy) are surfaced when the database exposes them.
• verify: mismatches fail the run; opaque policies are reported as untestable app-side; SQL three-valued logic risks (auth.uid() NULL, nullable columns) are called out when a fixture row has nulls in filtered columns.

How denials surface

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

The in-process side answers with a Decision, so a denied decision paired with filtered or rejected is parity, and granted paired with anything else is a failure. HTTP adapters convert 42501 from the database into the same RFC 9457 403 body as an in-process denial when the app opts in.

Databases without row-level security

permdock rls is Postgres-only because CREATE POLICY is. SQLite and its hosted forms (Cloudflare D1, Turso and libSQL, Expo SQLite and op-sqlite on device), MySQL and PlanetScale, and document stores have no RLS to generate or verify. The rest of the data story still applies to them: permdock.where compiles the same portable condition to Drizzle, Kysely or Prisma where clauses for those dialects, filter runs in memory, and the parity suite runs against SQLite in CI so the compilers agree with the evaluator even where no database policy exists. What these targets lose is the second, database-enforced line of defence; the threat model lists RLS as defence in depth, not as the primary control, so a D1 or Turso deployment is complete without it. On-device SQLite in React Native additionally uses the client snapshot for filter; the where compiler is the same one the server uses (React Native). MongoDB and local-first sync engines with their own permission languages are evaluated as where targets at Phase 3 planning (local-first sync).

Example app

apps/examples/supabase-rls: a Supabase project with posts, organization_members and team_members, a PermDock policy with ownership grants, tenant- and team-scoped roles compiled to memberOf, generated Drizzle pgPolicy entries and an SQL migration, the authorize() RBAC scaffold, an import run against the same database that reproduces the definitions, and verify output in both pgTAP and Node form.

Related standards

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

Open questions

• Membership conditions: resolved by ADR 0024. Scoped roles produce a dedicated memberOf node compiled through the dialect's memberships mapping; the older in(row.id, subject.context.<key>) form stays supported for hand-written grants. Still open: whether import should propose a memberships mapping when it finds an exists join over a table it does not know.
• FORCE ROW LEVEL SECURITY and security_invoker views: emit by default or behind flags.
• How app roles that are claims (user_role) map when a project uses Postgres roles per tenant instead of Supabase's three roles.
• Whether import should write into definePolicy role fragments as well as definePermissions, or leave grants to the author.
• neon dialect helper names (auth.user_id(), auth.session()) need pinning against the current Neon Data API.