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.
Installs into .claude/skills of the current project.
Are you the author of Postgres Row Level Security?
Add the live security badge to your README. It updates with every re-scan.
[](https://www.skillsdirectory.com/skills/curiositech-postgres-row-level-security)
---
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.
category: Backend & Databases
tags: [postgres, rls, row-level-security, supabase, postgrest, multi-tenant, authorization]
allowed-tools: Read, Grep, Glob, Edit, Write, Bash(psql:*, grep:*, rg:*)
---
# 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 <role>]
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 |
|---|---|---|
| **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](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) — ENABLE / FORCE / BYPASSRLS, default-deny semantics
- PostgreSQL: [`CREATE POLICY` reference](https://www.postgresql.org/docs/current/sql-createpolicy.html) — full syntax, USING/WITH CHECK applicability
- Supabase: [Row Level Security guide](https://supabase.com/docs/guides/database/postgres/row-level-security) — `auth.uid()`, `auth.jwt()`, canonical patterns, performance section
- Gary Austin: [RLS-Performance benchmarks](https://github.com/GaryAustin1/RLS-Performance) — the empirical numbers behind the subselect pattern
- PostgREST: [Authentication / role impersonation](https://docs.postgrest.org/en/v12/references/auth.html)
- Supabase: [RLS Performance & Best Practices](https://github.com/orgs/supabase/discussions/14576) — community-curated patterns
