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 see an index. Measured here: the (select auth.uid()) wrap, the SECURITY DEFINER helper, the joined-table EXISTS, grant targets, and client filters.

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 remaining gap is within noise, so the rewrite can wait on already-indexed columns.
• 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.
• The grant target decides grant visibility. 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	Only on an already-indexed policy column, where the rewrite can wait; otherwise 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 (security definer set search_path = ''), revoke execute ... from public, grant EXECUTE to the intended role only - the security advisor lints this shape as function_search_path_mutable and anon_/authenticated_security_definer_function_executable (security-lockdown S01)	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. If the wrapped subquery references a row column it becomes a correlated SubPlan and runs per row again; the hoist needs a row-independent helper, 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). The wrap still makes fewer calls, and on a column you just indexed the rewrite can wait. Index the columns your policies filter on first, then wrap the helpers. Run VACUUM ANALYZE on a fixture after loading and again after CREATE INDEX before comparing plans: the post-index plan here was a Bitmap Heap Scan with no Index Only Scan because the fresh table had never been vacuumed (RUNLOG result B); whether a vacuumed table gives an Index Only Scan was not measured.

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 planner picks the plan form and can pick differently. A correlated SubPlan at larger scale or different statistics evaluates the subquery per outer row, and the bare form’s helper then runs per row of the outer table. If a policy looks like this shape and the table is growing, wrap the helper before you need to. Read the plan for which form you got: a SubPlan whose loops equals the outer row count is per-row evaluation; an InitPlan is hoisted; a hashed subplan with loops=1 is decorrelated, and the helper’s hoist then shows as an InitPlan inside it (RUNLOG result C: InitPlan 3).

Grant targets

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 the grant targets: select tablename, policyname, roles from pg_policies where 'public' = any(roles) or 'anon' = any(roles); lists the policies whose target admits anon (a standard catalogue query, not part of the matrix). The security advisor does not lint grant targets; its rls_enabled_no_policy and rls_disabled_in_public lints cover the missing-policy case (security-lockdown S01).

A permissive UPDATE policy plus a table-level UPDATE grant let anon overwrite a balance column (204); after revoke update on t and grant update (note) the same write returned 401 with SQLSTATE 42501 while note still wrote (security-lockdown S13). Policies decide rows, grants decide columns; a WITH CHECK never constrains which columns move.

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. A TO authenticated read policy also delivers nothing to anonymous Realtime 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.

What to do about it

Each row names the RUNLOG result or module id it rests on (rls-policy-cost unless prefixed); a row that is a design choice rather than a result says so.

Practice	Evidence	Module
Declare the helper security definer set search_path = '' and grant EXECUTE narrowly.	revoke execute ... from public, then grant to the intended role only; the advisor flags this shape as function_search_path_mutable and anon_/authenticated_security_definer_function_executable.	security-lockdown S01
Audit policy grant targets for public and anon yourself.	The pg_policies query above lists them; the advisor does not lint grant targets. The same predicate admitted 5,250 rows TO public and 0 TO authenticated.	result E, security-lockdown S01
VACUUM ANALYZE fixtures after loading and after CREATE INDEX, before comparing plans.	Unvacuumed, the post-index plan was a Bitmap Heap Scan with no Index Only Scan; the vacuumed case was not measured.	result B
Grant UPDATE per column on tables with UPDATE policies.	A permissive UPDATE policy plus a table-level grant let anon overwrite balance (204); revoke update on t plus grant update (note) turned the same write into 401 with SQLSTATE 42501 while note still wrote.	security-lockdown S13
Read EXPLAIN ANALYZE for the form you got.	SubPlan with loops = outer rows is per-row; InitPlan is hoisted; a hashed subplan with loops=1 is decorrelated (InitPlan 3 inside it in result C).	result C

Reading the numbers

What generalises: 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), then SET ROLE authenticated and set "request.jwt.claims" = '{"sub":"<fixture-uuid>"}'. Transaction mode (6543) drops session GUCs between transactions and, measured in the sibling rls-wire-claims experiment, leaks a bare SET to the next invocation, so it cannot run claims fixtures. 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.

Modules

This page’s own experiment, rls-policy-cost, has no test modules - its findings are RUNLOG result letters (B, C, E) cited as such above. The table below covers the modules borrowed from other experiments.

Module	Experiment	Test	Artifact
S01	security-lockdown	s01-security-advisors.ts	none published
S13	security-lockdown	s13-column-grants.ts	none published

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 ↩ ↩²