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](#3-canonical-supabase-patterns) | | "RLS query went from 1ms to 1700ms" | [Performance](#5-performance-the-subselect-trick) | | "Two policies — what's the merge?" | [PERMISSIVE vs RESTRICTIVE](#2-permissive-vs-restrictive-merge-semantics) | | "Service role / admin needs to bypass" | [Bypass paths](#6-bypass-paths-bypassrls-and-security-definer) | | "Views aren't enforcing RLS" | [Views gotcha](#anti-patterns) | | "When is USING vs WITH CHECK used?" | [USING vs WITH CHECK](#1-create-policy-syntax) | --- ## Decision diagram ```mermaid 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](https://www.postgresql.org/docs/current/sql-createpolicy.html): ```sql 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 ```sql 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. ```sql 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: ```sql -- 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](https://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: ```sql USING (auth.uid() IS NOT NULL AND auth.uid() = user_id) ``` ### Pattern: User can only see their own rows ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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** ```sql 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: ```sql 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](https://github.com/GaryAustin1/RLS-Performance): | 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 ```sql 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>` ```sql -- 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: ```js // 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 ```sql 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 ```sql 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 |