RLS policy cost, measured

Row Level Security policy text is a query plan input. The shape you pick decides whether your lookup helper runs once per statement or once per row, and whether the optimizer can even see an index. This is the measured version of the three shapes Supabase projects keep deferring: the (select auth.uid()) wrap, the SECURITY DEFINER helper, and the joined-table EXISTS - plus the grant-target and client-filter shapes people treat as synonyms and are not.

Provenance. Fixtures and runs come from the supabase-lab OpenTofu repo (disposable supabase_project, micro, ap-southeast-1; Postgres 17.6; SQL issued over the session-mode pooler at port 5432). Fixture sizes: 100k-row items, 2k-row threads, 300-row posts; two fake user UUIDs. Timings are single-sample on a micro - plan shapes are the findings, milliseconds are directional. The full SQL and output are linked below.

TL;DR

• (select auth.uid()) changes only the plan, not the access decision. It hoists the stable helper to an InitPlan evaluated once per statement. Measured on 100k rows, unindexed: 100,001 helper calls -> 1 call, and the row digests (count + sum) are identical in both directions.
• With an index on the policy column, the bare form already evaluates O(1) per query (2 calls vs 1 here). The wrap still wins, but the remaining gap is noise; deferring the rewrite on already-indexed columns is defensible.
• A table joined inside a policy’s EXISTS enforces its own RLS recursively. Postgres may decorrelate the EXISTS into a hashed subplan (loops=1), in which case the wrap inside the EXISTS hoists too. At other sizes the same statement can come back as a correlated SubPlan evaluated per outer row.
• Predicate shape does not decide grant visibility; the grant target does. TO public on a SELECT policy exposes it to anon. TO authenticated does not.
• Client-side filters combine with the policy by conjunction. A client filter that drifted from the policy can only return rows the policy already admits - drift hides rows, it cannot reveal them.

Which shape to pick

Shape	What it costs	Pick it when
owner = auth.uid() (bare)	Helper call per row scanned	Never. Rewrite or index
owner = (select auth.uid())	Helper once per statement (InitPlan)	Default for auth.uid() / auth.jwt() predicates
EXISTS over a joined table	Joined table’s own RLS, recursively; cost depends on plan form	Fine - index the joined column, wrap the helper
SECURITY DEFINER helper	Join runs off the RLS path; pin search_path	Policy needs another table’s data (see Supabase RLS guidance1)

The hoist: bare vs (select auth.uid())

auth.uid() is a STABLE function. As a plain filter term it evaluates once per row. Wrapped as (select auth.uid()) the subquery references no column of the target table, so Postgres hoists it to an InitPlan that runs once per statement¹. Measured on the 100k-row fixture as an authenticated role with an unindexed owner column:

• bare: Filter: (owner = auth.uid()) - 100,001 helper calls, 252.9ms
• wrapped: Filter: (owner = (InitPlan 1).col1) - 1 call, 22.2ms

Row digests are identical in both forms (count 90,000; sum 4,500,010,000). The wrap is access-control-neutral by construction. A caveat: if the wrapped subquery references a row column, it becomes a correlated SubPlan and the win disappears - the hoist only applies where the helper is genuinely row- independent, which auth.uid() is.

What an index does to the win

Once items(owner) has an index, the bare form’s stable helper becomes an index condition evaluated O(1) times per scan rather than per tuple: 2 calls here against 1 for the wrapped form. Timing converged (24.1ms / 24.2ms on this run). Read that two ways: the wrap is still strictly better, and deferring the rewrite on a column you just indexed buys real time. The ordering that pays: index the columns your policies filter on, then wrap the helpers.

Nested EXISTS policies

A read policy like

exists (select 1 from threads i where i.topic = posts.topic and i.user_id = auth.uid())

does not skip threads’ own RLS: the joined table’s policy evaluates inside the subquery (recursive), and the visibility matrix measured exactly that (180 / 120 / 0 / 0 across four subjects, anon included). The plan chose a hashed subplan at 300x2000 fixture scale (loops=1), and the wrap inside the EXISTS hoisted into the subplan’s InitPlan there too - 2,003 bare helper calls versus 1 wrapped. Index the joined column; the hashed build narrows to the caller’s rows (Index Cond off the join policy’s own wrapping).

The plan form is a choice, not a guarantee. A correlated SubPlan at larger scale or different statistics evaluates the subquery per outer row, and there the bare form’s helper cost is per row of the outer table - the rewrite case is strongest exactly there. If a policy looks like this shape and the table is growing, wrap the helper before you need to.

Grant target, not predicate

TO public admits the anon role; TO authenticated does not. Measured on the same predicate both ways: anon read 5,250 rows through TO public, 0 through TO authenticated. Audit by grant target, not by reading predicates.

Realtime multiplies the policy cost by subscribers

Postgres Changes authorizes every event against each subscriber: one change on a table with 100 subscribers performs 100 authorization checks, so throughput scales with subscriber count, not write rate². Every read-path cost above therefore also prices the delivery path. A policy that is cheap to read is cheap to authorize; a policy that seq-scans a joined table per event does it per subscriber. The grant-target coupling cuts the other way too: a TO authenticated read path delivers nothing to anonymous subscribers while the REST path keeps working through a function.

Client filters compose safely

Supabase composes a client query’s own WHERE with the policy predicate by conjunction. Measured with policy owner = auth.uid() OR tag = 'public' and an other-user filter: 90,250 visible unfiltered; owner-drifted filter returns the other user’s 250 public rows - never more; private-drifted filter returns 0. Bad client-side duplication only ever narrows.

Reading the numbers

What generalizes: plan shapes (InitPlan vs per-row Filter, recursive RLS, grant-target gating, conjunction) and call counts, because both follow from planner mechanics, not hardware. What does not: the milliseconds (single sample, micro instance, cold cache), the specific plan form chosen for the EXISTS (statistics-dependent), and fixture sizes (100k/2k/300 rows chosen to make the effects visible, not to model any real workload). Re-run the matrix at your own row counts before quoting a number to anyone.

Decision guide

Evidence

Claim	How it was checked
Wrapping hoists to InitPlan; call count 100,001 -> 1; digests match	Measured (EXPLAIN ANALYZE + sequence-counted helper)
Index collapses bare/filter cost; 2 vs 1 calls	Measured (same method, after CREATE INDEX)
Joined table inside EXISTS enforces own RLS; matrix 180/120/0/0	Measured (role-switch count matrix)
Wrap hoists inside EXISTS (2,003 -> 1 at hashed-subplan form)	Measured (sequence counter inside policy)
TO public admits anon; TO authenticated denies	Measured (anon subject, same predicate)
Client-filter drift only narrows	Measured (OR-tagged policy, owner-drifted filter)
Realtime authorizes per subscriber per event	Documented in Supabase Realtime scaling notes2, not tested here

Reproducing

The full SQL matrix and run output live in the supabase-lab repo (make up, then make destroy; the project is gone). The method: log in as postgres over the session-mode pooler (port 5432; transaction mode drops session GUCs), then SET ROLE authenticated and set "request.jwt.claims" = '{"sub":"<fixture-uuid>"}'. Call counts use a PL/pgSQL helper that increments a sequence alongside auth.uid(), which survives the pooler because the sequence is ordinary DDL. RLS tests run as postgres prove nothing - that role carries BYPASSRLS on Supabase.

References

References

1. Supabase, “Row Level Security,” Supabase Docs. https://supabase.com/docs/guides/database/postgres/row-level-security ↩ ↩²

2. Supabase, “Using Postgres Changes,” Supabase Docs. https://supabase.com/docs/guides/realtime/postgres-changes ↩ ↩²