Designing Postgres Row-Level Security (RLS) policies for multi-tenant authorization, especially in Supabase / PostgREST stacks — `CREATE POLICY` syntax, USING vs WITH CHECK, PERMISSIVE/RESTRICTIVE merge semantics, the `(SELECT auth.uid())` performance pattern that turns 171ms scans into <1ms, indexes still required, role-based bypass via BYPASSRLS, security-definer escape hatches. Grounded in postgresql.org, Supabase docs, and Gary Austin's RLS-Performance benchmarks. NOT for: general authorization design, OAuth/OIDC token issuance, application-layer authorization frameworks (Casbin/Oso), or non-RLS Postgres performance tuning.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
license
Apache-2.0
name
postgres-row-level-security
description
Designing Postgres Row-Level Security (RLS) policies for multi-tenant authorization, especially in Supabase / PostgREST stacks — `CREATE POLICY` syntax, USING vs WITH CHECK, PERMISSIVE/RESTRICTIVE merge semantics, the `(SELECT auth.uid())` performance pattern that turns 171ms scans into <1ms, indexes still required, role-based bypass via BYPASSRLS, security-definer escape hatches. Grounded in postgresql.org, Supabase docs, and Gary Austin's RLS-Performance benchmarks. NOT for: general authorization design, OAuth/OIDC token issuance, application-layer authorization frameworks (Casbin/Oso), or non-RLS Postgres performance tuning.
{"category":"Backend & Databases","tags":["postgres","rls","row-level-security","supabase","postgrest","multi-tenant","authorization"],"provenance":{"kind":"first-party","owners":["port-daddy"]},"pairs-with":[{"skill":"postgres-explain-analyzer","reason":"EXPLAIN ANALYZE is how the (SELECT auth.uid()) initPlan and the tenant-column index are actually verified; RLS predicates are just WHERE clauses to the planner."},{"skill":"local-first-tenancy-boundary","reason":"Audits the product-level tenancy/scope model that these database-level RLS policies enforce."}],"io-contract":{"kind":"deliverable","consumes":["[Truncated]","[Truncated]"],"produces":["[Truncated]","[Truncated]"]}}
Postgres Row-Level Security
TL;DR: ENABLE ROW LEVEL SECURITY is default-deny. CREATE POLICY adds USING (read filter) and/or WITH CHECK (write filter). Multiple PERMISSIVE policies OR together; multiple RESTRICTIVE policies AND. The single biggest performance fix: wrap auth.uid() in (SELECT auth.uid()) to cache the result once per statement (94-99% latency reduction). RLS adds a WHERE clause but does not add indexes — your user_id column still needs one.
flowchart TD
A[Multi-tenant table needs authorization] --> B[ALTER TABLE t ENABLE ROW LEVEL SECURITY]
B --> C{Owner should also be subject to RLS?<br/>e.g. service_role wired through PostgREST}
C -->|Yes| D[ALTER TABLE t FORCE ROW LEVEL SECURITY]
C -->|No| E[Skip FORCE - owner bypasses]
D --> F[Decide policy types per command]
E --> F
F --> G{SELECT visibility?}
G -->|"User sees own rows"| H["CREATE POLICY ... FOR SELECT TO authenticated<br/>USING ((select auth.uid()) = user_id)"]
F --> I{INSERT validation?}
I -->|"User can only create own row"| J["CREATE POLICY ... FOR INSERT TO authenticated<br/>WITH CHECK ((select auth.uid()) = user_id)"]
F --> K{UPDATE - both filter AND validate?}
K -->|Yes| L[USING + WITH CHECK both<br/>typically same predicate]
H --> M[Performance audit]
J --> M
L --> M
M --> N{Hot read path?}
N -->|Yes| O[Wrap auth.uid in SELECT subquery<br/>+ index user_id<br/>+ specify TO <role>]
N -->|No| P[Done]
O --> P
CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL|SELECT|INSERT|UPDATE|DELETE } ]
[ TO { role_name | PUBLIC |CURRENT_ROLE|CURRENT_USER|SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITHCHECK ( check_expression ) ]
USING vs WITH CHECK
Command
USING (filter visible/affected rows)
WITH CHECK (validate new row values)
SELECT
required
not allowed
INSERT
not allowed
required
UPDATE
yes (which existing rows updatable)
yes (resulting row valid)
DELETE
required
not allowed
ALL
yes
yes (defaults to USING if omitted)
The mental model: USING is "can the user see / affect this row?" WITH CHECK is "is the user allowed to write this exact value?"
For UPDATE, both apply: USING gates which rows the user can attempt to update; WITH CHECK gates whether the resulting row is allowed (so a user can't UPDATE their row to assign it to someone else).
If you omit WITH CHECK on an UPDATE/ALL policy, it defaults to the USING expression — usually what you want.
Default-deny
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
Once enabled, no rows are visible to any non-bypass role until policies are added. This is the correct safe default — fail closed, not open.
ALTER TABLE t FORCE ROW LEVEL SECURITY; -- owner is also subject to policies
Without FORCE, the table owner bypasses RLS. With FORCE, even the owner must satisfy policies. Use FORCE when the connection role (e.g., the Postgres user PostgREST connects as) might own tables — otherwise you have a bypass-by-accident.
2. PERMISSIVE vs RESTRICTIVE merge semantics
For a given command, the effective predicate is:
( PERMISSIVE_1 OR PERMISSIVE_2 OR ... )
AND
( RESTRICTIVE_1 AND RESTRICTIVE_2 AND ... )
Multiple PERMISSIVE policies → combined with OR (any can grant access).
Multiple RESTRICTIVE policies → combined with AND (all must pass).
Default policy type is PERMISSIVE.
A table with RLS on but only RESTRICTIVE policies still denies everything (no permissive predicate to be true).
Worked example: layer a network restriction on top of normal access:
-- Permissive: standard accessCREATE POLICY "users see own rows" ON profiles
FORSELECTTO authenticated
USING ((SELECT auth.uid()) = user_id);
-- Restrictive: but admins must come from the office networkCREATE POLICY "admin local only" ON profiles
AS RESTRICTIVE FORSELECTTO admin
USING (pg_catalog.inet_client_addr() <<= inet '10.0.0.0/8');
A regular user sees their own row (permissive matches). An admin sees rows only when also on 10.0.0.0/8 (permissive AND restrictive). A user not on the office network as admin sees nothing.
Do not rely on RESTRICTIVE alone — without at least one matching permissive, all rows are filtered.
UUID of authenticated user; NULL if unauthenticated
auth.jwt()
Full JWT as JSON; access claims via ->/->>
auth.role()
Role string (anon, authenticated, etc.)
The NULL gotcha: USING (auth.uid() = user_id) silently fails for anon callers (NULL = anything → NULL → row not visible — actually correct, but the failure mode is silent). Recommended:
USING (auth.uid() ISNOT NULLAND auth.uid() = user_id)
Pattern: User can only see their own rows
CREATE POLICY "Own rows visible" ON todos
FORSELECTTO authenticated
USING ((SELECT auth.uid()) = user_id);
Pattern: User can only INSERT rows for themselves
CREATE POLICY "Users can create own profile" ON profiles
FORINSERTTO authenticated
WITHCHECK ((SELECT auth.uid()) = user_id);
Pattern: User can UPDATE their own rows AND can't reassign them
CREATE POLICY "Update own profile" ON profiles
FORUPDATETO authenticated
USING ((SELECT auth.uid()) = user_id) -- which rows can I touchWITHCHECK ((SELECT auth.uid()) = user_id); -- new value must still be mine
Pattern: Team membership via JWT app_metadata
CREATE POLICY "User is in team" ON team_documents
TO authenticated
USING (team_id IN (SELECT auth.jwt() ->'app_metadata'->'teams'));
Supabase explicitly warns: prefer raw_app_meta_data over raw_user_meta_data in policies — users can self-edit user_metadata.
Pattern: Require MFA (AAL2) for sensitive write
CREATE POLICY "Require AAL2" ON profiles
AS RESTRICTIVE FORUPDATETO authenticated
USING ((SELECT auth.jwt()->>'aal') ='aal2');
4. PostgREST role wiring
Behind Supabase / any PostgREST stack:
authenticator (LOGIN, NOINHERIT) — connects to Postgres, then SET LOCAL ROLE per request based on the JWT
anon (NOLOGIN) — used when no JWT or no role claim
authenticated — used when JWT validates
service_role — has BYPASSRLS; must never reach the browser
GRANT authenticated TO authenticator;
GRANT anon TO authenticator;
GRANTSELECT, INSERT, UPDATE, DELETEON public.todos TO authenticated;
GRANTSELECTON public.todos TO anon;
ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
Wrap security-definer function (SELECT is_admin())
11_000ms → 7ms
99.94%
Add explicit .eq('user_id', …) filter on client
171ms → 9ms
94.74%
Rewrite join → IN (SELECT …)
9_000ms → 20ms
99.78%
Add TO authenticated (skip anon eval)
170ms → <0.1ms
99.78%
Why the subselect works
USING (auth.uid() = user_id) calls auth.uid()per row. On a 1M-row scan, that's 1M function calls.
USING ((SELECT auth.uid()) = user_id) causes Postgres to build an initPlan — a one-time computation that runs once per statement, then is reused across all row evaluations. The result: 1 call instead of 1M.
This is only safe when the function result is row-independent. auth.uid(), auth.jwt(), current_setting() qualify. If your predicate uses a function whose result depends on the row, you can't wrap it.
Indexes still matter
CREATE INDEX idx_todos_user_id ON todos (user_id);
RLS adds an implicit WHERE user_id = auth.uid() clause but does not create indexes. The policy column (typically user_id) must be indexed or every read becomes a sequential scan with a per-row filter.
Always specify TO <role>
-- Bad: policy evaluated for ALL roles including anonCREATE POLICY ... ON todos USING (...);
-- Good: skip evaluation for non-matching rolesCREATE POLICY ... ON todos TO authenticated USING (...);
When TO is omitted, the policy is evaluated against PUBLIC — every role, including ones that should never reach the table. Specifying TO authenticated lets Postgres skip the policy entirely for anon.
Push the predicate from the client too
Even though RLS enforces it, the optimizer builds better plans when the predicate is also in the query:
// Worse: relies on RLS alone
supabase.from('todos').select('*')
// Better: explicit predicate gives the optimizer hints
supabase.from('todos').select('*').eq('user_id', user.id)
The explicit .eq() lets Postgres use the index directly; without it, the planner may decide the RLS predicate alone isn't index-worthy.
6. Bypass paths: BYPASSRLS and SECURITY DEFINER
Two ways to legitimately skip RLS:
BYPASSRLS role attribute
ALTER ROLE service_role WITH BYPASSRLS;
The role permanently ignores all RLS policies on all tables. Use for:
Backup tools
Migration scripts
Service workers that need cross-tenant access
Never expose a BYPASSRLS connection to user-facing code. Supabase's service_role key (in .env) gives this access — leaking it is a full data breach.
SECURITY DEFINER functions
CREATEFUNCTION get_team_total(team_id uuid) RETURNSintLANGUAGEsql SECURITY DEFINER AS $$
SELECTcount(*) FROM team_members WHERE team_id = $1
$$;
The function runs with the privileges of the function owner, not the caller. If the owner is postgres (RLS bypass), the function bypasses RLS for the duration of its execution.
This is the canonical escape hatch for narrow whitelisted operations — but it's also the most common place RLS gets accidentally bypassed. Rules:
Only create SECURITY DEFINER functions you've reviewed line-by-line
Always SET search_path = '' inside to prevent search-path injection
Don't expose them in API schemas (Supabase: keep them out of the public schema)
Anti-patterns
Anti-pattern
Why it bites
Fix
RLS policy uses auth.uid() (no subselect)
Per-row function call → 100-1000× slowdown
(SELECT auth.uid()) for caching
user_id column not indexed
Sequential scan + per-row filter
CREATE INDEX ON tbl (user_id)
Policy without TO <role> clause
Evaluates for every role including anon
Always specify TO authenticated (or whichever)
Views silently bypass RLS
Pre-PG15: views run with creator's privileges
PG15+: WITH (security_invoker = true). Older: revoke direct grants and rely on the view's RLS
service_role key leaks to browser
Full database compromise
Service role only on server-side; browser uses anon + RLS
SECURITY DEFINER exposed in PostgREST API schema
API call bypasses RLS
Keep these in a private schema; not in public
Restrictive-only policies (no permissive)
All rows denied
Add at least one permissive policy or rely on grant-based access
USING (true) policy
RLS enabled but no actual filtering
Either remove the policy or add real predicate
UPDATE without WITH CHECK
User can update their row to belong to someone else
Always pair USING + WITH CHECK on UPDATE
Trusting raw_user_meta_data in policies
Users can self-edit it
Use raw_app_meta_data instead
FK / unique constraint as covert channel
These bypass RLS by design
Audit FK reveals (the existence of a referenced row leaks); design schema accordingly
Novice / Expert / Timeline
Novice
Expert
First policy
USING (auth.uid() = user_id) no TO, no subselect
TO authenticated USING ((SELECT auth.uid()) = user_id) + index
Timeline: pre-PG15 — view RLS bypass was the constant gotcha. PG15+ (2022) — security_invoker = true makes views respect RLS. PG16+ — lateral joins inside policies got better-optimized. Supabase's (SELECT auth.uid()) pattern (~2023) is the single biggest performance discovery — pre-2023 docs may not show it.
Quality gates
An RLS configuration ships when:
Test:ALTER TABLE … ENABLE ROW LEVEL SECURITY is set; verified by \d+ <table> showing "Row security enabled".
Test: All policies specify TO <role> (no PUBLIC defaults). CI grep for CREATE POLICY without TO.
Test: All policies that use auth.uid() / auth.jwt() wrap them in (SELECT …). CI grep / lint.
Test:user_id (or equivalent tenant column) has a btree index. pg_indexes query in CI.
Test: RLS performance benchmark — query that should be <10ms via index actually is. Regression test with EXPLAIN ANALYZE.
Test: UPDATE policies that should prevent reassignment have both USING and WITH CHECK. Code review checklist.
Test: No SECURITY DEFINER function in the API-exposed schema (Supabase: not in public). CI grep.
Test: Multi-tenant isolation tested end-to-end — user A cannot see user B's rows even via crafted queries.
Test:service_role key is not present in any client-side bundle. Bundle scan in CI.
Manual: Views in the data layer use WITH (security_invoker = true) (PG15+) or are explicitly designed for RLS bypass.
Deterministic Audit
Before shipping an RLS configuration (or reviewing another agent's), write it as a JSON
rls-policy-plan matching schemas/postgres-row-level-security-plan.schema.json and run it
through the deterministic auditor:
auditPostgresRowLevelSecurity(plan) (in scripts/postgres_row_level_security_audit.mjs)
turns this skill's Quality Gates and Anti-patterns into machine-checkable rules over
structured fields — no keyword matching: RLS not enabled, restrictive-only policy sets
that deny everything, policies without TO <role>, auth functions not wrapped in the
(SELECT …) subselect, UPDATE policies missing WITH CHECK (the reassignment hole), an
unindexed tenant column, a table owner who connects without FORCE, SECURITY DEFINER
functions exposed in the API schema, and a service_role key reachable from the client.
It returns { pass, score, findings, recommendations }; examples/sample-input.json is
a correctly hardened own-rows configuration that audits pass: true. Version history:
CHANGELOG.md.
NOT for this skill
General authorization design (use authorization-design)
