curiositech/postgres-row-level-security

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.

---

Jump to your fire

| Symptom | Section | |---|---| | "Need 'user can only see their own rows'" | Canonical patterns | | "RLS query went from 1ms to 1700ms" | Performance | | "Two policies — what's the merge?" | PERMISSIVE vs RESTRICTIVE | | "Service role / admin needs to bypass" | Bypass paths | | "Views aren't enforcing RLS" | Views gotcha | | "When is USING vs WITH CHECK used?" | USING vs WITH CHECK |

---

Decision diagram

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 (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 (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 &lt;role&gt;]
  N -->|No| P[Done]
  O --> P

---

1. CREATE POLICY syntax

From postgresql.org/docs/current/sql-createpolicy.html:

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 ) ]
    [ WITH CHECK ( 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 access
CREATE POLICY "users see own rows" ON profiles
  FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Restrictive: but admins must come from the office network
CREATE POLICY "admin local only" ON profiles
  AS RESTRICTIVE FOR SELECT TO 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.

---

3. Canonical Supabase patterns

Supabase wraps PostgREST + RLS with helper functions that read JWT claims. From supabase.com/docs/guides/database/postgres/row-level-security:

| Function | Returns | |---|---| | auth.uid() | 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() IS NOT NULL AND auth.uid() = user_id)

Pattern: User can only see their own rows

CREATE POLICY "Own rows visible" ON todos
  FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

Pattern: User can only INSERT rows for themselves

CREATE POLICY "Users can create own profile" ON profiles
  FOR INSERT TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

Pattern: User can UPDATE their own rows AND can't reassign them

CREATE POLICY "Update own profile" ON profiles
  FOR UPDATE TO authenticated
  USING      ((SELECT auth.uid()) = user_id)   -- which rows can I touch
  WITH CHECK ((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 FOR UPDATE TO 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;

GRANT SELECT, INSERT, UPDATE, DELETE ON public.todos TO authenticated;
GRANT SELECT ON public.todos TO anon;
ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;

JWT claims are accessible via:

current_setting('request.jwt.claims', true)::json->>'email'

Supabase's auth.uid() / auth.jwt() are wrappers around exactly this current_setting('request.jwt.claims', ...) mechanism.

---

5. Performance: the subselect trick

This is the single highest-leverage RLS performance pattern. From Gary Austin's RLS-Performance benchmark repo:

| Technique | Before → After | Improvement | |---|---|---| | Add btree index on user_id | 171ms → <0.1ms | 99.94% | | Wrap auth.uid() in (SELECT …) | 179ms → 9ms | 94.97% | | 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 anon
CREATE POLICY ... ON todos USING (...);

-- Good: skip evaluation for non-matching roles
CREATE 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

CREATE FUNCTION get_team_total(team_id uuid) RETURNS int
LANGUAGE sql SECURITY DEFINER AS $$
  SELECT count(*) 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 | | UPDATE policy | USING only | USING + WITH CHECK to prevent reassignment | | Performance debugging | "RLS is slow, disable it" | EXPLAIN ANALYZE; subselect wrap; index audit | | Service role | Used in client code | Server-only; client never sees it | | Views | Surprised they bypass | Knows pre-PG15 quirk; uses security_invoker = true | | Multiple policies | Confused by interaction | Knows OR-permissive AND-restrictive merge |

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.

---

NOT for this skill

• General authorization design (use authorization-design)
• OAuth / OIDC token issuance (use oauth2-and-oidc-from-scratch)
• Postgres performance beyond RLS (use postgres-explain-analyzer)
• Application-level authorization (Casbin, Oso, etc.) — different layer
• Schema design and normalization (use database-design-patterns)
• Postgres connection pooling (use postgres-connection-pooling)
• Specific Supabase features (Realtime, Storage RLS) — different concerns

---

Sources

• PostgreSQL: Row Security Policies — ENABLE / FORCE / BYPASSRLS, default-deny semantics
• PostgreSQL: CREATE POLICY reference — full syntax, USING/WITH CHECK applicability
• Supabase: Row Level Security guide — auth.uid(), auth.jwt(), canonical patterns, performance section
• Gary Austin: RLS-Performance benchmarks — the empirical numbers behind the subselect pattern
• PostgREST: Authentication / role impersonation
• Supabase: RLS Performance & Best Practices — community-curated patterns