Postgres row-level security

What it isCopy Anchor Link

PostgreSQL row-level security lets a table carry policies that filter or reject rows per statement, evaluated by the database regardless of which application issued the query. The relevant syntax is CREATE POLICY:

CREATE POLICY name ON table
  [ AS PERMISSIVE | RESTRICTIVE ]
  [ FOR ALL | SELECT | INSERT | UPDATE | DELETE ]
  [ TO role, ... ]
  [ USING (expr) ]
  [ WITH CHECK (expr) ]

Semantics that matter for code generation:

• Combination. Permissive policies for the same command are OR'd; restrictive policies are AND'd; the result is (AND restrictives) AND (OR permissives). Zero permissive policies means deny. FOR ALL policies fold into whichever command is being evaluated. Defaults are PERMISSIVE, ALL and PUBLIC.
• Clause legality. SELECT takes only USING; INSERT takes only WITH CHECK; DELETE takes only USING; UPDATE and ALL take both, and if WITH CHECK is omitted USING is reused for the new row.
• Cross-command coupling. UPDATE and DELETE statements that read columns (WHERE, RETURNING, SET) also need a passing SELECT policy, and RETURNING rows must satisfy it or the statement errors.
• Denial modes. USING silently filters (zero rows); WITH CHECK raises 42501; a missing GRANT raises 42501 before any policy runs.
• Expressions cannot contain aggregates or window functions and run with the caller's privileges, so referenced tables and functions need grants; LEAKPROOF functions may run before policy quals.

Introspection is through the pg_policies view (schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check), backed by pg_policy (polroles of 0 means PUBLIC). The qual and with_check columns are deparsed with pg_get_expr, which returns normalised SQL (explicit casts, parenthesisation, ( SELECT auth.uid() AS uid)), not the original text. Enablement lives in pg_class.relrowsecurity and relforcerowsecurity.

SupabaseCopy Anchor Link

Every request runs as anon or authenticated: PostgREST issues SET LOCAL ROLE from the JWT role claim, and service_role has bypassrls. Grants are separate from policies, and new tables in public may already grant all four privileges to anon and authenticated, so generated SQL revokes and re-grants next to the policies.

• auth.uid() is NULL when unauthenticated, so null = user_id is never true. auth.jwt() is current_setting('request.jwt.claims', true)::jsonb; read app_metadata, never user-writable user_metadata, and expect the claims to be stale until the token refreshes. auth.role() and auth.email() are deprecated in favour of the TO clause.
• PostgREST exposes the claims as the GUC request.jwt.claims. The per-claim request.jwt.claim.<name> settings are legacy: PostgREST 12.0.0 removed db-use-legacy-gucs, the option that wrote them (changelog). Even so, Supabase's auth.uid() still reads request.jwt.claim.sub first. PermDock's withSubject preambles and rls verify set only request.jwt.claims, and permdock_user_id() reads only that.
• The performance guide and the Splinter lints say: wrap helpers as (select auth.uid()) so they run once per statement (auth_rls_initplan); index every filtered column; always write TO authenticated; prefer IN (subselect) over correlated joins, or a security definer function (with set search_path = '', in a non-exposed schema) to avoid 42P17 recursion; avoid many permissive policies per role and command (multiple_permissive_policies).
• The RBAC pattern (app_role and app_permission enums, user_roles and role_permissions tables, a custom_access_token_hook that injects user_role, and a security definer stable authorize(permission) function used as using ((select authorize('channels.delete')))) is a named-permission catalog in SQL.
• Policies appear nowhere else: supabase gen types typescript emits tables, views, functions and enums only, and the Supabase MCP server has no policy tool, so introspection means execute_sql against pg_policies. Its get_advisors tool runs the Splinter RLS lints.

Generic Postgres, Neon, Nile and PostgRESTCopy Anchor Link

Without Supabase helpers, the pattern is a GUC set per transaction (set_config('app.user_id', ..., true)) and policies reading current_setting('app.user_id', true)::uuid. Neon's Data API validates the JWT and pg_session_jwt provides auth.user_id() (text sub) and auth.uid() (uuid) with roles authenticated and anonymous. Nile isolates tenant-aware tables through set nile.tenant_id and a reserved tenant_id column instead of user-written policies, so it is a tenant-equality target only. PostgREST uses an authenticator role, SET LOCAL ROLE from the role claim, claims in request.jwt.claims and a db-pre-request hook.

Authoring surfacesCopy Anchor Link

• Drizzle: pgPolicy(name, { as, to, for, using, withCheck }) in the table's third argument (any policy auto-enables RLS; pgTable.withRLS enables it with none), pgPolicy(...).link(table) for tables Drizzle does not own, pgRole(...).existing(). drizzle-orm/supabase exports anonRole, authenticatedRole, serviceRole, authUid and createDrizzle(token, ...), which sets the claim GUCs and set local role inside a transaction; drizzle-orm/neon exports crudPolicy({ role, read, modify }). drizzle-kit generate diffs policies.
• Prisma 8 (changelog, prisma-next#945): @@rls on a model enables RLS fail-closed; top-level policy_select|insert|update|delete|all blocks carry target, roles, using and withCheck; role declarations; migration plan emits CREATE POLICY and ALTER POLICY ... RENAME; db verify fails on policy drift; @prisma/orm-extension-supabase supplies the Supabase roles. Prisma up to 7 has no native RLS; the sanctioned pattern is a client extension that runs set_config and the query inside $transaction, and nested many-to-many writes can lose the GUC.
• Kysely has nothing built in (use onReserveConnection or a transaction plus set_config). Kysera's @kysera/rls drives app-side filters and native CREATE POLICY from one schema, but only in one direction and only for policies written as raw SQL.
• Schema-as-code tools: Atlas has policy blocks, row_security, lint rules and atlas schema test; Bytebase reviews policies as SQL; Sqitch is plain SQL. CREATE POLICY has no IF NOT EXISTS, so idempotent scripts use DROP POLICY IF EXISTS or a DO block over pg_policies.
• Parsers: pgsql-parser (libpg_query in WASM, symmetric parse and deparse) and @pgsql/parser (PG 15 to 18 grammars); the pure TypeScript pgsql-ast-parser has incomplete coverage. Since qual is a bare expression, parse SELECT 1 WHERE <qual> and take the where clause.

TestingCopy Anchor Link

pgTAP via supabase test db gives structural asserts (policies_are, policy_roles_are, policy_cmd_is, has_table_privilege) and behavioural ones after set local role authenticated and set local request.jwt.claims. Basejump's supabase-test-helpers add tests.authenticate_as(), tests.rls_enabled() and tests.freeze_time(). Match the assertion to the denial mode: a missing grant and a WITH CHECK failure are throws_ok ... '42501'; a USING filter is is_empty over a statement with RETURNING, followed by a read proving the row is intact. Never prove an allowed write with lives_ok, which passes on zero rows.

Splinter, supautils and pg_jsonschemaCopy Anchor Link

Three Supabase projects constrain what generated SQL may contain.

• Splinter is a set of SQL lint views over the catalog, released in CalVer (2026.09.1); each release ships the combined splinter.json. supabase db advisors --local or --db-url runs it, --type security --fail-on warn makes it a gate, and the Supabase MCP server's get_advisors returns the same rows. The lints that generated RLS can trip are 0003 auth_rls_initplan (an unwrapped auth.uid(), auth.jwt() or current_setting() in policy text), 0006 multiple_permissive_policies (two permissive policies for one role and command), 0010 security_definer_view, 0011 function_search_path_mutable (any function, triggers included, without set search_path), 0013 rls_disabled_in_public, 0014 extension_in_public, 0024 rls_policy_always_true, 0025 public_bucket_allows_listing, and 0028 and 0029 (a security definer function in an exposed schema executable by anon or authenticated). A schema is exposed when it is public or listed in [api] schemas of supabase/config.toml; splinter reads that list from pgrst.db_schemas.
• supautils (3.4.4) runs inside every Supabase database. For postgres, the role that runs migrations, it rejects alter role (attributes, rename, password) and drop role on a reserved role: anon, authenticated, service_role, authenticator, dashboard_user, pgbouncer and the supabase_* roles. It also rejects a grant of a reserved membership such as authenticator or a supabase_* admin role. alter role … set stays allowed on the four API roles, and create event trigger runs. A migration that passes against plain Postgres can fail on Supabase for these reasons alone; PD063 reports them.
• pg_jsonschema adds extensions.jsonb_matches_schema(schema json, instance jsonb) and its json twin; Supabase ships 0.3.3. It validates Draft 2020-12 without asserting format (use pattern), resolves no remote $ref (inline $defs), and compiles the schema on every call; compiled-schema functions exist on the main branch only. A check constraint over it is the database's boundary validation for a jsonb column, added not valid and then validated so existing rows do not block the migration.

Why it matters for PermDockCopy Anchor Link

RLS is the only enforcement layer that survives a bypassed application, and Supabase users already write it. A policy written twice, once in SQL and once in TypeScript, drifts. PermDock's portable condition AST is designed so one condition evaluates in the UI, filters arrays, compiles to where clauses and generates RLS, and so existing RLS imports back into a typed catalog. No other tool does the import direction: supazod-style generators read gen types output, which has no policy data. ZenStack is the contrast case: it compiles @@allow / @@deny into application-side query filters and skips RLS; PermDock offers both app-side where filters and RLS, with parity tests as the glue. See the rls adapter and CLI rls.

How PermDock uses itCopy Anchor Link

permdock rls generate --target drizzle|sql|prisma --dialect supabase|neon|guc
permdock rls import --db $DATABASE_URL --out src/permissions.generated.ts
permdock rls verify --db $DATABASE_URL

• Generate. Roles and grants become policies (mapping below). A SELECT policy is generated or verified whenever update or delete grants exist, and a table left with zero permissive policies for a role and command is warned about. Companion ENABLE ROW LEVEL SECURITY, optional FORCE, grant and revoke statements, (select ...) wrappers and index suggestions are emitted alongside. Role checks go through generated security definer helpers (permdock_has, and one permitted_<scope>_ids and one membership-only member_<scope>_ids per named scope the policy declares) over a seeded role_permissions table, called uncorrelated so they run once per statement (see below), and each table gets one permissive policy per command. With --rbac supabase, the user_roles / authorize() scaffold is generated as well; permdock supabase hook generate writes the hook. SQL output is idempotent (drop policy if exists then create policy); the Drizzle and Prisma targets leave diffing to drizzle-kit, Prisma 8 or Atlas.
• Import. pg_policies plus pg_class.relrowsecurity are read, qual and with_check parsed with pgsql-parser and pattern-matched to portable nodes; anything else becomes opaque({ sql, fingerprint }), kept verbatim for regeneration and flagged in the catalog as untestable app-side. Fingerprints come from the deparsed AST, so pg_get_expr normalisation does not register as drift. FOR ALL is split into four entries (export merges them only if identical); roles = {public} maps to all subjects and non-Supabase role names to opaque constraints. The output is a deterministic definePermissions() file that merges with hand-written ones by key.
• Verify. For each fixture (subject, row, next row, action), can() runs in-process and the same operation runs inside BEGIN ... ROLLBACK with set local role and set_config('request.jwt.claims', ...), using RETURNING; outcomes are classified allowed, filtered (zero rows) or rejected (42501) and any mismatch fails. Only the outcome is compared, so a three-valued-logic or cast difference that changes the outcome is a mismatch and one that does not is invisible; an opaque grant is reported as a note, never evaluated app-side. Emitted as pgTAP or run from Node.

The portable subsetCopy Anchor Link

Import recognises both the EXISTS (select 1 ...) and the IN (subselect) membership forms, and the helper calls in both policy layouts; generate emits the helpers for role checks and keeps exists joins only for resource-scoped roles. now() and interval arithmetic, CASE, multi-join subqueries, current_user, custom functions and claim checks beyond simple equality become opaque. The operators are written with the names on the conditions page.

One helper per scopeCopy Anchor Link

A policy that declares organization and customer (inside it) gets permitted_organization_ids and permitted_customer_ids. Each returns the instances of its own scope, read from memberships of exactly that scope, so an organization owner's customer set is empty and a portal contact's organization set is empty (no cascade). A resource that carries both keys is narrowed per grant: an organization role compares organization_id, a customer role compares customer_id, and the two branches OR together in one permissive policy. Memberships under the first scope are narrowed to the active-tenant claim when it is set. In database mode each helper reads the table mapped for its scope; in jwt mode, the memberships claim entries whose scope is its own, with the first scope's id in within.

Why InitPlansCopy Anchor Link

Postgres evaluates a policy's USING expression as a filter on every candidate row. A subquery that references no column of the outer row is uncorrelated: the planner turns a scalar one, (select f()), into an InitPlan and an x in (select f()) into a hashed SubPlan, runs either once when the statement starts, and the per-row filter only compares against the cached result. A subquery or function call that mentions a row column (authorize('post.read', "orgId"::text), exists (... where m.org_id = "orgId") that the planner cannot hash) is correlated and runs for every row. Splinter's auth_rls_initplan lint is the same observation applied to auth.uid(), and the guc dialect wraps current_setting(...) the same way.

The generated helpers are built so every call is uncorrelated: they take only a grant key, read the subject from auth.uid() and the claims inside the function, and return either a boolean (global roles) or the set of one scope's ids (scoped roles). The row-dependent part of the policy is then a plain comparison of the row's scope column against that set. On 10,000 rows across 20 tenants the per-row shape runs its role check 10,000 times; the helper shape runs it once per helper and is over 1,000 times faster in tests/integration/bench (the test asserts loops = 1 and a 5x floor).

security definer is what lets the helper read role_permissions, user_roles and the membership table without granting authenticated access to them, and it prevents the recursion error (42P17) a policy over the membership table would otherwise hit. set search_path = '' with fully qualified names closes the search_path hijack a definer function is otherwise open to, and execute is granted to authenticated only.

Mapping tableCopy Anchor Link

RisksCopy Anchor Link

• Semantic drift. SQL three-valued logic (auth.uid() NULL, nullable columns) versus JavaScript booleans; uuid versus text sub; stale JWT claims versus live application lookups.
• USING versus WITH CHECK confusion, the classic "user reassigns user_id" hole; ALL policies hiding intent; the SELECT prerequisite for UPDATE and DELETE.
• Grants. A 42501 from a missing grant looks like a policy denial, so code generation owns grants too.
• Bypasses. service_role, bypassrls and table owners without FORCE skip policies; views default to security definer (use security_invoker = true).
• Performance. Unwrapped helpers, unindexed filter columns, correlated joins, many permissive policies, recursion; run Splinter or get_advisors next to verify, which does not read them.
• Round-trip stability. pg_get_expr rewrites text, so import-then-generate compares ASTs, never policy text.

SourcesCopy Anchor Link

• PostgreSQL CREATE POLICY, pg_policies, pg_policy.
• Supabase RLS guide, RLS performance, Custom claims and RBAC, Testing, Splinter, supautils, pg_jsonschema, supabase-mcp.
• Drizzle RLS, Neon RLS with Drizzle, Neon Data API, Nile tenant isolation, PostgREST auth.
• Prisma 8 changelog, prisma-next#945, Prisma RLS client extension.
• Kysera multi-tenancy, ZenStack access control, Atlas RLS guide, supabase-test-helpers.
• pgsql-parser, @pgsql/parser.