Supabase RLS Policy Performance Tuning
The dashboard query that used to return in 40 milliseconds was suddenly taking nine seconds. Same query, same indexes, same row count — the only thing that had changed was that I had finally turned on Row Level Security across all tenant tables the week before. I spent an embarrassing afternoon blaming connection pooling, then Postgres autovacuum, then the JS client, before I finally ran EXPLAIN ANALYZE and watched auth.uid() get called once per row across a 200k-row join. The policy was correct. The policy was also the bottleneck. That is the part nobody warns you about when they tell you RLS is "basically free."
This article rebuilds that exact failure in a fresh Supabase project and then walks through five optimizations — initPlan caching with (select auth.uid()), covering indexes on filtered columns, SECURITY DEFINER helper functions, denormalized tenant_id columns, and role-based bypass for trusted workloads — benchmarking each one with pgbench and EXPLAIN ANALYZE against the naive baseline. You will end with a working repo, a decision matrix that tells you which optimization to reach for given your access pattern, and a set of SQL migrations you can lift straight into your own project. The rule that earned its keep through every one of those nine-second afternoons: RLS is not slow, but a policy that runs once per row is, and the whole job is making the planner run it once per query instead.
It is written for engineers already shipping on Supabase or Postgres who hit a wall the moment real traffic met their tenant isolation rules. By the last section you will be able to read a query plan with RLS expanded, predict which optimization will actually move the needle before you write a line of SQL, and defend the trade-offs to a reviewer who asks why you bypassed the policy for the worker queue.
Step 1: Laying a Multi-Tenant Schema Foundation Without Row Security
Before any row-level security policy can be tuned, there has to be something to apply it to. This first step builds the smallest realistic Supabase-compatible schema that the rest of the walkthrough will lean on: organizations, memberships, and documents, plus a stand-in for auth.users. We deliberately leave RLS off in this step so that the next step can record a clean, policy-free baseline against the same schema we will later harden.
The design choice that matters most here is reproducibility. Instead of pointing at a hosted Supabase project, the companion code spins up an embedded Postgres through pgserver, applies the migration files Supabase would have applied for us, and runs a deterministic seed. That way EXPLAIN ANALYZE numbers you read in later steps are reproducible on any laptop, with no shared cloud state to drift.
Setup
The codebase uses uv for environment management and pytest as the test runner. Only two runtime dependencies are needed for this step: pgserver (a tiny wrapper that boots a private Postgres process from a Python test) and psycopg (the Postgres driver). Everything else — Supabase's CLI, hosted projects, environment variables — is intentionally avoided so the walkthrough stays self-contained.
The layout we create in this step:
codebase/
├── pyproject.toml
├── src/rls_tuning/
│ ├── __init__.py
│ └── db.py
├── supabase/
│ ├── migrations/
│ │ └── 20260615000001_initial_schema.sql
│ └── seed.sql
└── tests/
├── conftest.py
└── test_schema.py
The supabase/ directory mirrors the layout the Supabase CLI uses, so the same migration files would apply unchanged against a hosted project. The src/rls_tuning/ package only exposes the small helpers needed to run those SQL files against a psycopg connection — nothing more.
Implementation
The migration file is the heart of this step. It declares a minimal auth.users table so the schema can be applied against a bare Postgres instance, then defines the three tenant-scoped tables that every later policy will guard.
create schema if not exists auth;
create table if not exists auth.users (
id uuid primary key default gen_random_uuid(),
email text not null unique,
created_at timestamptz not null default now()
);
create table if not exists public.organizations (
id uuid primary key default gen_random_uuid(),
slug text not null unique,
name text not null,
created_at timestamptz not null default now()
);
Two design choices are worth flagging. First, the migration uses gen_random_uuid() from core Postgres 13+, not uuid_generate_v4() from uuid-ossp, so the SQL applies without enabling any extension. Second, the auth.users mirror only declares the columns later policies actually read — Supabase manages the real table in production, and we deliberately do not try to clone its full surface.
create table if not exists public.memberships (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references public.organizations(id) on delete cascade,
user_id uuid not null references auth.users(id) on delete cascade,
role text not null check (role in ('owner', 'admin', 'member')),
created_at timestamptz not null default now(),
unique (org_id, user_id)
);
create table if not exists public.documents (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references public.organizations(id) on delete cascade,
owner_id uuid not null references auth.users(id) on delete restrict,
title text not null,
body text not null default '',
created_at timestamptz not null default now()
);
memberships is the join table every RLS policy in this walkthrough will hit at least once — first directly, later via a SECURITY DEFINER helper that step 6 introduces. The (org_id, user_id) unique constraint matters because later steps lean on it to prove the helper function is single-row and inlinable. The documents.org_id column is the seam step 7 will copy onto a hot child table: keeping tenant id directly on the row is what lets a denormalized policy replace a cross-table join with a single equality check.
The Python side is intentionally thin. The db.py helper enumerates migration files in lexicographic order (the timestamp prefix doubles as a sort key) and applies them inside a single transaction. Then a separate helper runs the seed file the same way.
def iter_migration_files() -> list[Path]:
"""Return migration files in lexicographic order (timestamp prefix)."""
return sorted(MIGRATIONS_DIR.glob("*.sql"))
def apply_migrations(conn: psycopg.Connection) -> list[Path]:
"""Run every migration file against ``conn`` and return the applied paths."""
files = iter_migration_files()
_run_sql(conn, (path.read_text() for path in files))
return files
Splitting iter_migration_files out from apply_migrations looks fussy for two functions, but it pays back immediately in tests: we can assert the migration ordering without ever touching a real database. The seed loader is the same shape and lives next to it, so steps 2–8 can apply migrations, seed, and reset between EXPLAIN runs without copy-pasting boilerplate.
The pytest conftest.py boots one embedded Postgres per session, then resets public and auth between tests. Reusing the server saves several seconds per test; dropping the schemas instead of the whole cluster keeps the reset cheap.
@pytest.fixture()
def db(pg_uri: str) -> Iterator[psycopg.Connection]:
"""Fresh database per test — drop + recreate schemas, then migrate + seed."""
with psycopg.connect(pg_uri, autocommit=True) as admin:
with admin.cursor() as cur:
cur.execute("drop schema if exists public cascade")
cur.execute("drop schema if exists auth cascade")
cur.execute("create schema public")
with psycopg.connect(pg_uri) as conn:
apply_migrations(conn)
apply_seed(conn)
yield conn
Finally, tests/test_schema.py codifies the invariants this step promises: the four tables exist, RLS is off on the three public tables, the seed lands the expected row counts, and both the role check and the (org_id, user_id) uniqueness constraint actually reject bad inserts.
Verification
Run the full test suite from codebase/:
uv run pytest -q
The expected output, captured from a clean run:
...... [100%]
6 passed in 4.82s
The first run is slower because pgserver downloads and initialises the Postgres data directory; subsequent runs reuse it. If the suite fails on the test_rls_is_disabled_on_public_tables assertion, an earlier experiment likely left an alter table ... enable row level security lying around — drop the data directory under RLS_TUNING_PGDATA and rerun.
What we built
We now have a reproducible Supabase-shaped schema that any reader can boot in one command, with no hosted account or CLI dependency. The migration applies cleanly against a bare Postgres 13+ instance, and the seed deposits a deliberately small but structurally complete fixture: three tenants of different shapes, six users with overlapping memberships, and six documents distributed across the tenants.
The fixture is small on purpose. Step 3 will scale it up with pgbench to load-test the naive RLS policy, but for correctness tests like this one, fewer rows means faster feedback and easier EXPLAIN output to read by eye. Every UUID is pinned in the seed so the same EXPLAIN ANALYZE runs will produce stable plans across machines.
Crucially, RLS is off on every public table. That is not an oversight — it is the baseline invariant the next step depends on. Step 2 will turn RLS on with the naive auth.uid() policy that almost every Supabase project ships first, and the only way to honestly measure its overhead is to have a policy-free reference point already committed.
With the foundation in place, the rest of the walkthrough becomes a sequence of controlled experiments: each optimization changes one thing, reruns the same benchmark, and is reverted before the next one starts.
Repository
The state of the code after this step: c2c97be
Step 2: Turning On RLS With the Naive auth.uid() Predicate Shape
Step 1 left us with a Supabase-shaped multi-tenant schema, a seed, and an explicit invariant: row-level security is off on every public table. That was a baseline, not a target. This step flips the switch — RLS goes on for organizations, memberships, and documents, and each table picks up the smallest predicate that correctly implements "see only rows in tenants you belong to" using a bare auth.uid() call.
This is the shape almost every Supabase tutorial reaches for first, and it is intentionally where most production projects start to feel slow once the seed grows. We ship it on purpose. The whole point of the walkthrough is to measure that pain in step 3 and pay it down, predicate-by-predicate, in steps 4–7. To do that honestly we need a "before" state that is committed, tested, and reproducible — not a sketch in a README.
Setup
No new third-party dependencies are added in this step. The only new files are a second migration, a tiny Python helper for impersonating Supabase roles, and the policy tests:
codebase/
├── src/rls_tuning/
│ ├── __init__.py # re-exports auth_as
│ └── auth.py # NEW — set role + JWT claim from Python
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql
│ └── 20260615000002_naive_rls.sql # NEW — enable RLS, add policies
└── tests/
├── test_schema.py # updated — drop the "RLS off" assertion
└── test_naive_rls.py # NEW — behavior + shape coverage
The naming pattern — a timestamped migration plus a sibling test file — is the same shape every later step will reuse. auth.py joins db.py in the package so step 3's benchmark scripts can switch identities the same way the tests do.
Implementation
We start by giving Postgres a local stand-in for auth.uid(). On a hosted Supabase project, Supabase installs this function for us; locally we declare it ourselves so the policies parse against bare Postgres. The function reads the request.jwt.claim.sub GUC — the exact same GUC PostgREST sets when it forwards a verified JWT — and returns null when no claim is present.
create or replace function auth.uid()
returns uuid
language sql
stable
as $$
select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid
$$;
Two things matter here. The function is marked stable, which is what the planner needs to even consider folding it into a single initPlan later. And it deliberately tolerates the empty string so an unauthenticated anon session returns null instead of raising an invalid input syntax for uuid error mid-query.
Next we materialize the two Supabase roles policies are going to name by hand. We also grant usage on both schemas and select on the three public tables, because once RLS is on, unqualified selects still need table-level privileges before the policy is even consulted.
do $$
begin
if not exists (select 1 from pg_roles where rolname = 'anon') then
create role anon nologin;
end if;
if not exists (select 1 from pg_roles where rolname = 'authenticated') then
create role authenticated nologin;
end if;
end
$$;
grant usage on schema public to anon, authenticated;
grant usage on schema auth to anon, authenticated;
grant select on public.organizations to anon, authenticated;
grant select on public.memberships to anon, authenticated;
grant select on public.documents to anon, authenticated;
The roles are nologin because we never connect as them — Python connects as the superuser and then set role authenticated mid-session, exactly the way PostgREST does after verifying a JWT. Granting select to anon too is intentional: anon will hit RLS and see zero rows, which is the correct production-like outcome and the property this step's tests assert.
With identities in place, RLS goes on and three policies land — one per table — all written in the obvious "inline auth.uid()" shape.
alter table public.organizations enable row level security;
alter table public.memberships enable row level security;
alter table public.documents enable row level security;
create policy organizations_member_select on public.organizations
for select
to authenticated
using (
id in (
select org_id
from public.memberships
where user_id = auth.uid()
)
);
create policy memberships_self_select on public.memberships
for select
to authenticated
using (user_id = auth.uid());
create policy documents_member_select on public.documents
for select
to authenticated
using (
org_id in (
select org_id
from public.memberships
where user_id = auth.uid()
)
);
Notice that two of the three predicates call auth.uid() inside an IN (select ...). Because the call is not wrapped in its own (select auth.uid()), the planner cannot prove it is independent of the outer row and re-evaluates it once per candidate row. That is the exact cost step 4 will measure and remove. The membership policy is deliberately the narrowest of the three — only the caller's own rows — because a broader "same-org" predicate would self-reference public.memberships and recurse infinitely under RLS. Step 5 fixes that with a SECURITY DEFINER helper; until then, narrow is the only safe shape.
The Python side adds one helper, auth_as, that the tests and later benchmarks use to slip into a Supabase-style session and back out cleanly.
@contextmanager
def auth_as(conn: psycopg.Connection, user_id: UserId) -> Iterator[None]:
"""Run a block as Supabase's ``authenticated`` role with a fixed JWT sub.
Pass ``user_id=None`` to simulate the unauthenticated ``anon`` role.
"""
_apply_session(conn, user_id)
try:
yield
finally:
conn.rollback()
_reset_session(conn)
The rollback() in the finally is there because tests routinely leave the transaction in an error state — for example, an assertion that "user X sees zero rows" runs a query that RLS filters down to nothing, and a follow-up statement in the same transaction would otherwise fail with current transaction is aborted. Rolling back unconditionally is the cheapest way to guarantee reset role always runs on a healthy session.
The new test file then asserts two things at once. First, the behavior is correct: each seeded identity sees exactly the rows their memberships imply, anon sees nothing, and an authenticated stranger sees nothing. Second, the shape is still naive — every policy predicate contains a literal auth.uid() call, and none of them have been pre-wrapped as (select auth.uid()). That second assertion is the tripwire step 4 will flip.
Verification
Run the full suite from codebase/:
uv run pytest -q
The expected output, captured against the seed from step 1:
............... [100%]
15 passed in 3.90s
The six original schema tests still pass (one was updated to assert RLS is now on instead of off), and nine new tests cover the policies. If test_every_policy_predicate_inlines_auth_uid ever fails, it almost certainly means someone applied the step 4 rewrite early — the test is designed to flip then, and the message will point at the offending policy.
What we built
We now have a working, behavior-correct RLS layer over the schema from step 1. Every authenticated identity sees exactly the rows their memberships entitle them to, anon sessions see nothing, and a user with no memberships at all gets a clean empty result rather than a permissions error. That is the user-visible contract any Supabase project ships on day one.
We also wrote down, in tests rather than prose, what makes this shape naive: each predicate calls auth.uid() directly, and two of the three nest that call inside an IN (select org_id ...) subquery. The planner cannot hoist the call into a single initPlan, so every row scanned re-invokes it. On the six-row seed this is invisible; at a few thousand documents per tenant it becomes the dominant cost — which is exactly what step 3 is going to measure.
A second small invariant landed alongside the policies: a Python helper that flips between anon and authenticated the same way PostgREST does in production. Every benchmark, plan capture, and regression test in the remaining steps reuses it, so we never accidentally measure a superuser query and call it a tenant query.
The commit history is the seam between "before" and "after." This step is the canonical "before" — if a later optimization makes a plan worse or a policy laxer, the diff against this commit will say so out loud.
Repository
The state of the code after this step: b049081
Step 3: Benchmarking the Naive Policy Under Realistic Volume
Step 2 turned RLS on with the smallest-possible auth.uid() predicate on every tenant table, and the behavior tests passed against a six-row seed. Six rows hides everything that matters: at that size a scan, a nested loop, and a hash join all look identical in EXPLAIN ANALYZE. This step grows the data, plumbs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) into Python, and records — in tests, not in a README — the exact plan shape the naive policy produces so steps 4–7 have something concrete to beat.
The design constraint is that the measurement harness has to run in the same pytest invocation as the behavior tests. We deliberately do not require an external pgbench binary, but we do ship two pgbench scripts next to the migrations so a reader who has the binary can reproduce the same workload on their own hardware. The Python loop and the SQL scripts measure the same predicate from two angles, which is exactly what we need before any optimization lands.
Setup
No new third-party dependencies are added — psycopg and pgserver already cover everything the harness needs, and the pgbench scripts only execute through the standalone binary that ships with Postgres. The new files are a volume seeder + EXPLAIN harness in the package, two pgbench scripts in a sibling directory, and a sizable test module that pins the baseline plan shape.
codebase/
├── src/rls_tuning/
│ ├── __init__.py # updated — re-exports bench helpers
│ └── bench.py # NEW — load_volume + explain_analyze + run_query_loop
├── supabase/
│ ├── benchmarks/
│ │ ├── select_documents.sql # NEW — pgbench script for documents
│ │ └── select_memberships.sql # NEW — pgbench script for memberships
│ └── migrations/ # unchanged from step 2
└── tests/
└── test_benchmark.py # NEW — volume + plan + loop coverage
The canonical six-row seed from step 1 is untouched. The volume loader writes everything under a fixed UUIDv5 namespace so reruns produce byte-identical IDs and never collide with the behavior fixtures the earlier tests rely on. That separation matters: it means a flaky measurement never invalidates the behavior contract.
Implementation
The volume loader is intentionally a single function that returns a VolumeStats dataclass — the test asserts the row counts against the dataclass directly, which catches any silent drift in the seed math.
def load_volume(
conn: psycopg.Connection,
*,
orgs: int = 25,
users_per_org: int = 8,
docs_per_org: int = 50,
) -> VolumeStats:
org_rows = _build_org_rows(orgs)
user_rows = _build_user_rows(orgs, users_per_org)
membership_rows = _build_membership_rows(orgs, users_per_org)
document_rows = _build_document_rows(orgs, docs_per_org)
...
_refresh_statistics(conn)
return VolumeStats(...)
Two non-obvious decisions live in this function. UUIDs are derived with uuid5(BENCH_NAMESPACE, label) — that gives every reader the same IDs without storing a fixture file, and it lets the pgbench scripts hard-code one user without coordinating with the Python seeder. The closing _refresh_statistics flips autocommit so ANALYZE can run; without that step the planner would still be working off the empty-table stats and every later EXPLAIN ANALYZE would lie about the plan it would pick in production.
The EXPLAIN wrapper is deliberately thin — we want raw plan JSON, not a pretty-printer, because the assertions later walk the tree.
def explain_analyze(
conn: psycopg.Connection,
sql: str,
params: tuple = (),
) -> dict[str, Any]:
with conn.cursor() as cur:
cur.execute(f"explain (analyze, buffers, format json) {sql}", params)
row = cur.fetchone()
payload = row[0]
if isinstance(payload, str):
payload = json.loads(payload)
return payload[0]
BUFFERS is requested even though the assertions ignore it today, because the moment step 7 introduces a denormalized column on a hot child table the buffer hit count becomes the headline number for the article. Keeping it in the baseline now means the "before" snapshot already has the data we will want to quote later — no rerun required.
Walking the plan tree is split into two helpers because the tests use both shapes. iter_plan_nodes is a depth-first generator; find_node is the targeted lookup the policy assertions actually call.
def find_node(plan: dict[str, Any], *, relation: str) -> dict[str, Any] | None:
for node in iter_plan_nodes(plan):
if node.get("Relation Name") == relation:
return node
return None
The Python-side query loop mirrors pgbench -c 1 -t N: a fixed iteration count, eager fetchall() so the client cost is included, and a wall-clock measurement returned as a BenchResult. We keep pgbench as the canonical measurement for anyone who has it installed, but the Python loop lets the same number land in CI without an extra binary.
def run_query_loop(
conn: psycopg.Connection,
sql: str,
params: tuple = (),
*,
iterations: int,
) -> BenchResult:
assert iterations > 0, "iterations must be positive"
start = time.perf_counter()
with conn.cursor() as cur:
for _ in range(iterations):
cur.execute(sql, params)
cur.fetchall()
elapsed = time.perf_counter() - start
return BenchResult(iterations=iterations, total_seconds=elapsed)
The two pgbench scripts in supabase/benchmarks/ each open a transaction, set request.jwt.claim.sub to a pinned UUID, set local role authenticated, run a single select, and commit. Pinning the user is intentional: it isolates the per-row policy cost from any plan-cache effects, so the same query under the same identity is what every later step compares against. A reader who wants to defend their numbers against plan-cache cheating can swap the pinned UUID for a randomly chosen seeded user, but the canonical measurement keeps the identity fixed.
The test module records the invariants the baseline must hold. The shape assertions are the load-bearing ones: test_naive_documents_plan_pulls_in_memberships_table proves the documents policy still fans out to memberships, and test_naive_memberships_plan_filters_per_row proves the predicate carrier on the memberships scan still names either auth.uid() or its inlined request.jwt.claim.sub body. Both tests are tripwires — step 4's rewrite is supposed to flip them, and the failure message is what tells you the optimization landed.
Verification
Run the full suite from codebase/:
uv run pytest -q
The expected output, against the canonical seed plus the bench volume:
............................. [100%]
29 passed in 4.46s
The 15 behavior tests from steps 1–2 still pass, and 14 new tests in test_benchmark.py cover the volume seeder, the EXPLAIN wrapper, the plan-tree walkers, the query loop, and the naive-shape tripwires. If the run hangs for several seconds on the first iteration that is pgserver initialising its data directory; rerun and the cached cluster boots in well under a second.
What we built
We now have an honest measurement substrate for the naive policy. The volume seeder turns the six-row fixture into a few hundred tenants-worth of rows, the EXPLAIN wrapper returns parsed JSON the assertions can walk, and the Python query loop produces a BenchResult that names average latency and throughput the same way pgbench does.
We also wrote down — in tests — the exact plan shape the naive policy produces. Documents scans fan out to a subplan over memberships. Memberships scans carry a filter that names the JWT claim either symbolically as auth.uid() or via the inlined current_setting('request.jwt.claim.sub', true) body the planner substitutes when it folds the stable function. Both tripwires will fail the moment step 4 rewrites the predicate as (select auth.uid()), and the failure messages are how we will recognise the optimization landed.
The companion pgbench scripts under supabase/benchmarks/ give any reader with a Postgres install a second, external way to reproduce the headline number. They pin one authenticated user on purpose so the comparison across steps stays apples-to-apples; a reader who wants to stress the planner against plan-cache reuse can substitute a randomized seeded user without changing the rest of the harness.
What this unlocks is the rest of the walkthrough. With a committed baseline plan and a committed baseline latency, every subsequent step can change exactly one thing, rerun the same harness, and compare. That is the difference between a tuning guide and a vibes-driven blog post.
Repository
The state of the code after this step: e00635d
Step 4: Hoisting auth.uid() Into an InitPlan With (select auth.uid())
Step 3 froze the naive baseline into tests: the documents policy fans out to memberships, and the memberships scan carries either auth.uid() or its inlined current_setting('request.jwt.claim.sub', true) body inside its Filter. Both assertions are now tripwires — they are supposed to fail the moment the first real optimization lands. This step is that optimization.
The change itself is a tiny rewrite: every auth.uid() inside a policy using clause is wrapped as (select auth.uid()). The behavior is identical — the predicate still resolves to the caller's user id — but the plan shape is not. Postgres treats the scalar subquery as a non-correlated SubLink, compiles it into an InitPlan that runs exactly once per query, and reuses the cached value as a Param reference in the per-row filter. The JWT lookup that previously fired for every candidate row now fires once per statement.
Setup
No new third-party dependencies are added. The schema and the seed are untouched. The deliverable is a single migration that re-shapes the policies from step 2, two small additions to the bench helpers so the tests can name the new plan node, and a four-line edit to the policy-shape assertion that step 2 originally wrote.
codebase/
├── src/rls_tuning/
│ ├── __init__.py # updated — re-export init_plan_nodes
│ └── bench.py # updated — init_plan_nodes + explain_analyze(verbose=)
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql # unchanged
│ ├── 20260615000002_naive_rls.sql # unchanged
│ └── 20260615000003_initplan_rewrite.sql # NEW — alter policy USING ...
└── tests/
├── test_naive_rls.py # updated — assertion now requires (select auth.uid(
└── test_benchmark.py # updated — step 3 tripwires flipped to InitPlan shape
The migration is deliberately additive — it never drops the policies from step 2, it only rewrites their using expressions. That keeps the policy names, the role grants, and the with check clauses stable across the version history, so a reader reviewing the migration history sees a focused diff that is unambiguously "the predicate shape changed and nothing else did."
Implementation
The migration uses alter policy rather than drop + create. That single choice is what the test in test_naive_rls.py keys off — the policy name is stable, and only the predicate text under it changes.
alter policy organizations_member_select on public.organizations
using (
id in (
select org_id
from public.memberships
where user_id = (select auth.uid())
)
);
alter policy memberships_self_select on public.memberships
using (user_id = (select auth.uid()));
alter policy documents_member_select on public.documents
using (
org_id in (
select org_id
from public.memberships
where user_id = (select auth.uid())
)
);
Two non-obvious points live in this DDL. First, the (select auth.uid()) form has to be a plain scalar subquery — no from clause, no correlation. The moment a correlation reference sneaks in (for example, (select auth.uid() from public.memberships m where m.user_id = ...)), Postgres demotes the SubLink back to a per-row evaluation and the optimization silently disappears. Second, we deliberately do not push the wrapper into the auth.uid() function definition itself — that would change the function's contract for every other caller in the database. The wrapping happens at the use site inside the policy qual, exactly where the planner needs to see it.
The bench helpers gain two small additions. explain_analyze learns a verbose= keyword because InitPlan nodes only render their Output expressions under EXPLAIN VERBOSE; step 3's filter-walking callers do not need it, so it defaults to off and stays opt-in.
def explain_analyze(
conn: psycopg.Connection,
sql: str,
params: tuple = (),
*,
verbose: bool = False,
) -> dict[str, Any]:
flags = ["analyze", "buffers"]
if verbose:
flags.append("verbose")
flags.append("format json")
flag_text = ", ".join(flags)
with conn.cursor() as cur:
cur.execute(f"explain ({flag_text}) {sql}", params)
row = cur.fetchone()
...
The second addition is init_plan_nodes, which returns every node in the tree whose Parent Relationship is the literal string "InitPlan". That field is the canonical signal Postgres uses to mark a sub-plan as a one-shot, so it is the right thing for the assertions to key on — far more robust than fishing for substrings inside Output lists.
def init_plan_nodes(plan: dict[str, Any]) -> list[dict[str, Any]]:
return [
node
for node in iter_plan_nodes(plan)
if node.get("Parent Relationship") == "InitPlan"
]
The behavior test from step 2 keeps its job — it still walks pg_policies to confirm every predicate names auth.uid() — but its trailing assertion flips. Previously it required the qual not to contain a (SELECT AUTH.UID() substring; now it requires that it does, after whitespace is squashed and the text is upper-cased so quoting style cannot fool it.
for tablename, policyname, qual in rows:
assert "auth.uid()" in qual, (tablename, policyname, qual)
squashed = "".join(qual.split()).upper()
assert "(SELECTAUTH.UID(" in squashed, (tablename, policyname, qual)
In test_benchmark.py the two naive-shape tripwires are renamed to advertise what they now guarantee. test_optimized_documents_plan_hoists_auth_uid_into_initplan requires at least one InitPlan node anywhere in the documents plan, and additionally proves that the per-row predicate carriers on the documents scan — Filter, Index Cond, Recheck Cond — no longer contain the strings auth.uid() or request.jwt.claim.sub. The companion test for memberships enforces the same invariant on the scan that the documents subplan actually pulls from, and asserts that the InitPlan envelope is still present.
Verification
Apply the migration and run the suite from codebase/:
uv run pytest -q
The full suite — six schema tests, nine behavior tests, and fourteen benchmark tests — turns green against the rewritten policy:
............................. [100%]
29 passed in 7.89s
The two renamed tripwires (test_optimized_documents_plan_hoists_auth_uid_into_initplan and test_optimized_memberships_plan_lifts_auth_uid_out_of_per_row_filter) are the load-bearing ones — they were the assertions step 3 wrote as the naive shape, and they now pass under the optimized shape with their bodies inverted. If a future step ever regresses the predicate back to a bare auth.uid(), these two are the first signal.
What we built
The policies on every tenant table now wrap their auth.uid() calls as (select auth.uid()). Functionally nothing has changed — a member still sees only their organizations, their memberships, and the documents inside their organizations. Mechanically, the JWT lookup has moved from "once per scanned row" to "once per query."
The benchmark harness grew the smallest possible surface area needed to assert that — a verbose= flag on explain_analyze and an init_plan_nodes helper that filters by Parent Relationship == "InitPlan". Both additions are deliberately narrow because every later step will want to assert against the same node shape, and a thin helper today is what keeps step 5 and step 6 from copying plan-walking code into each test file.
The test suite has flipped from a "still naive" contract to an "InitPlan-cached" contract. The policy-shape test in test_naive_rls.py now requires the (SELECT AUTH.UID( substring inside every qual it reads from pg_policies; the two renamed plan-shape tests in test_benchmark.py now require the InitPlan envelope and the absence of any inline JWT marker on the per-row scan filters.
What this unlocks is the comparison work the rest of the article rests on. Step 5 will introduce a security-definer helper that pre-computes the caller's accessible org ids, and step 6 will add a denormalized user_id column so the documents scan stops needing the memberships subplan at all. Both of those changes are measurable only because step 4 paid down the per-row function call first — without this fix, every later benchmark would be drowning in the same auth.uid() overhead that this step finally amortizes.
Repository
The state of the code after this step: ba4faf9
Step 5: Backing the Policy Predicate With INCLUDE-Covered Indexes for Index-Only Scans
Step 4 rewired every auth.uid() call inside a policy using clause as (select auth.uid()), which let Postgres fold the JWT lookup into a one-shot InitPlan and reference the cached value as a Param in the per-row filter. The per-row predicate on memberships is now the trivial shape user_id = $0, but the scan reaching that predicate is still a sequential scan — there is no index on memberships.user_id, so every policy evaluation walks the whole table to find the rows owned by the caller. The same shape appears on documents: the org_id IN (...) probe issued by the documents policy has nothing to seek on, so it falls back to a sequential scan too.
This step closes that gap by adding two btree indexes whose leading columns match the policy predicates and whose INCLUDE columns carry the projection the policy subqueries actually return. With both indexes in place, the memberships subscan can return org_id straight from the index leaf without a heap visit, and the documents scan can probe its own covering index for the same reason.
Setup
No new third-party dependencies are added. The schema, the policies, and the bench helpers are all untouched. The deliverable is a single additive migration plus a new test file whose sole job is to lock in the index shape and prove the planner is willing to use it.
codebase/
├── src/rls_tuning/ # unchanged
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql # unchanged
│ ├── 20260615000002_naive_rls.sql # unchanged
│ ├── 20260615000003_initplan_rewrite.sql # unchanged
│ └── 20260615000004_covering_indexes.sql # NEW — create index … include
└── tests/
└── test_covering_indexes.py # NEW — pg_indexes + plan asserts
The migration is purely additive — it does not drop, recreate, or alter any existing index, and it does not touch the policies. The pre-existing memberships_org_id_user_id_key unique constraint stays in place because the new index has a different leading column and serves a different access path.
Implementation
The migration creates two indexes and nothing else. Each one names its leading filter column first and parks the projection column inside INCLUDE, which is the btree feature that records non-key payload at the leaf without making it part of the search key.
create index if not exists memberships_user_id_idx
on public.memberships (user_id)
include (org_id);
create index if not exists documents_org_id_idx
on public.documents (org_id)
include (id);
The choice of leading column on each index is dictated by the policy text from step 4. The memberships policy filter is user_id = (select auth.uid()), so the index must seek on user_id. The documents policy issues org_id in (select org_id from public.memberships where user_id = $0), so the documents-side index must seek on org_id. Reversing the order — for example, indexing memberships(org_id, user_id) — would force a full index scan, because btree leading-column rules require the high-order key to appear in the predicate, and the policy never filters by org_id first.
The INCLUDE columns are picked to satisfy the projection of the subqueries that drive the policy probe. The memberships subquery returns org_id, so adding INCLUDE (org_id) lets that subquery resolve as an Index Only Scan — the index leaf already carries every column the subscan needs to emit, and the planner can skip the heap visit entirely. The documents-side INCLUDE (id) plays the same role for select id from public.documents so the outer query can finish without a heap fetch either.
A subtle trap worth naming: the unique constraint on memberships(org_id, user_id) is not a substitute for the new index. Btree leading-column rules mean the unique index helps where org_id = ? lookups, not where user_id = ? ones. The new index is additive, and the test suite asserts both indexes coexist so a future cleanup pass cannot quietly drop the unique constraint and lose the semantic guarantee that a user only joins a given org once.
The test file mirrors that shape exactly. It opens by reading pg_indexes to confirm both new index names landed, then parses each indexdef to assert the leading key and the INCLUDE payload appear in the expected order.
def test_memberships_index_is_covering_with_org_id_in_include(
db: psycopg.Connection,
) -> None:
indexdef = _indexdef(db, name="memberships_user_id_idx").lower()
assert "(user_id)" in indexdef, indexdef
assert "include (org_id)" in indexdef, indexdef
Two further tests verify that the planner is actually willing to pick the new index. With the very small seeded dataset, Postgres normally prefers a sequential scan even when an index is available, so each test disables seq scans inside the transaction via set local enable_seqscan = off. That setting is scoped to the transaction — the auth_as context manager rolls back at exit, so the global GUC is unchanged for subsequent tests.
def test_memberships_query_picks_covering_index_when_seqscan_disabled(
db: psycopg.Connection,
) -> None:
load_volume(db, **SMALL_VOLUME)
with auth_as(db, bench_user_id(0, 0)):
with db.cursor() as cur:
cur.execute("set local enable_seqscan = off")
plan = explain_analyze(db, "select id from public.memberships")
memberships_node = find_node(plan, relation="memberships")
assert memberships_node is not None, json.dumps(plan)[:1500]
assert "Index" in memberships_node["Node Type"], memberships_node
assert "memberships_user_id_idx" in _index_names_in_plan(plan), plan
The plan assertion is intentionally generic on the node type — it accepts any Index* flavor (Index Only Scan, Index Scan, Bitmap Index Scan) because the precise pick depends on table statistics that shift as later steps grow the dataset. The load-bearing assertion is the index name: as long as memberships_user_id_idx appears somewhere in the plan tree, the new access path is in use.
Verification
Apply the migration and run the suite from codebase/:
uv run pytest -q
The full suite — six schema tests, nine behavior tests, fourteen benchmark tests from steps 3 and 4, and seven new covering-index tests — passes against the new migration:
.................................... [100%]
36 passed in 5.21s
The seven new tests fall into three families: existence (test_memberships_user_id_index_exists, test_documents_org_id_index_exists), shape (test_*_is_covering_with_*_in_include, test_unique_constraint_on_memberships_org_id_user_id_is_preserved), and planner uptake (test_*_query_picks_covering_index_when_seqscan_disabled). If a future step accidentally drops one of the new indexes, the first family fails immediately. If a refactor changes the INCLUDE payload, the second family catches it. If a later optimization makes the planner ignore the index entirely, the third family flags the regression.
What we built
Two covering indexes are now part of the schema. memberships_user_id_idx keys on user_id and carries org_id in its INCLUDE payload; documents_org_id_idx keys on org_id and carries id. Both are additive — the pre-existing unique constraint on memberships(org_id, user_id) is preserved, and the policy text from step 4 is unchanged.
The access path for an RLS-protected read is now seek-driven from end to end. The InitPlan from step 4 produces the caller's user id once; the memberships subscan uses that as the seek key against memberships_user_id_idx, returning org_id straight from the index leaf; the documents scan uses each returned org_id to seek into documents_org_id_idx, returning id straight from its leaf. Nothing in the hot path needs to walk the full heap, and nothing fans out across a sequential scan over either tenant table.
The test suite has grown by seven assertions that triangulate the new shape. Existence tests fix the index names, shape tests fix the leading key and the INCLUDE payload, and planner-uptake tests fix the runtime behavior under a controlled enable_seqscan = off regime that survives the tiny seeded dataset. The benchmark and behavior tests from earlier steps are untouched — the policy text and the InitPlan shape are exactly what step 4 froze in.
What this unlocks is the rest of the optimization ladder. Step 6 will move the membership lookup behind a SECURITY DEFINER helper so the documents plan stops scanning memberships at all, and step 7 will denormalize org_id onto a hot child table so a fan-out events read becomes a single-column comparison. Both of those changes are only measurable because step 5 has already lifted the policy probe off the heap — without the covering indexes, the next round of numbers would still be dominated by the per-row I/O this step just amortized away.
Repository
The state of the code after this step: 50fa8f5
Step 6: Hiding the Membership Join Behind a STABLE SECURITY DEFINER Helper
After step 5 the policy probe on memberships rides a covering index and the per-row predicate on documents is reduced to org_id in (select org_id from public.memberships where user_id = $0). The inner subquery still exists, though, and it is doing more work than it looks like. Every documents read re-plans a subquery on memberships, re-enters RLS to evaluate the memberships_self_select policy on each candidate row, and surfaces memberships as a visible relation in the outer plan tree.
This step replaces that inline cross-table join with a STABLE SECURITY DEFINER helper named auth.user_org_ids(). The function reads memberships with the definer's privileges, filters on (select auth.uid()), and returns the caller's org ids as a setof uuid. The two cross-tenant policies — on documents and organizations — are rewritten to call the helper, which lets the planner fold the lookup into a one-shot uncorrelated SubPlan and removes memberships from the outer plan entirely.
Setup
No new dependencies. The change is one additive migration that defines the helper function and rewrites two existing policies via alter policy, plus a new test file that locks in both the function's security posture and the new plan shape.
codebase/
├── src/rls_tuning/ # unchanged
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql # unchanged
│ ├── 20260615000002_naive_rls.sql # unchanged
│ ├── 20260615000003_initplan_rewrite.sql # unchanged
│ ├── 20260615000004_covering_indexes.sql # unchanged
│ └── 20260615000005_security_definer_helpers.sql # NEW
└── tests/
└── test_security_definer.py # NEW — 14 assertions
The migration is strictly additive on the schema side. It creates a function in the auth schema, revokes the default public execute grant, re-grants execute to the two Supabase roles (authenticated, anon), and uses alter policy ... using (...) to swap the using clause on the two cross-tenant policies. The covering indexes from step 5 are preserved — the helper's body still keys on memberships(user_id) and returns org_id, exactly the access path that index supports.
Implementation
The function is the load-bearing artifact. It is declared language sql so the planner can inline-evaluate it as a SubPlan, marked stable so it can be hoisted out of per-row evaluation, and pinned to search_path = public, auth, pg_temp so a hostile session search_path cannot redirect public.memberships to a shadow table.
create or replace function auth.user_org_ids()
returns setof uuid
language sql
stable
security definer
set search_path = public, auth, pg_temp
as $$
select org_id
from public.memberships
where user_id = (select auth.uid())
$$;
revoke all on function auth.user_org_ids() from public;
grant execute on function auth.user_org_ids() to authenticated, anon;
The (select auth.uid()) shape from step 4 is preserved inside the function body. Without it, the JWT lookup would fire once per candidate membership row even though the function itself is stable. The revoke all on function ... from public line is the security counterweight to security definer: a function that bypasses the caller's RLS must not be reachable by an unspecified role, and re-granting only to the two Supabase roles makes the surface area explicit.
The two policy rewrites are one-liners. alter policy ... using (...) swaps the predicate without dropping the policy, so the policy's name, role binding, and for clause are all preserved — only the expression changes.
alter policy organizations_member_select on public.organizations
using (id in (select auth.user_org_ids()));
alter policy documents_member_select on public.documents
using (org_id in (select auth.user_org_ids()));
The new test file in tests/test_security_definer.py triangulates the change from four angles. First, it queries pg_proc to confirm the function exists with the right volatility, language, and proconfig pin. Second, it executes the function under several auth_as contexts (Ada in Acme, Grace spanning Acme + Globex, Yukihiro alone in Initech, anon) to prove the per-caller filter still works.
def test_user_org_ids_pins_search_path_to_block_injection(
db: psycopg.Connection,
) -> None:
attrs = _fn_attrs(db, schema="auth", name="user_org_ids")
pinned = any(item.startswith("search_path=") for item in attrs["proconfig"])
assert pinned, attrs
Third, it asserts the rewritten policies. pg_policies.qual is read directly and the test rejects any text that still mentions memberships — the helper has to be the sole vehicle for that lookup, not a redundant shortcut layered on top of the old subquery.
def test_documents_policy_now_uses_user_org_ids_helper(
db: psycopg.Connection,
) -> None:
with db.cursor() as cur:
cur.execute(
"""
select qual
from pg_policies
where schemaname = 'public'
and tablename = 'documents'
and policyname = 'documents_member_select'
"""
)
qual = cur.fetchone()[0]
assert "auth.user_org_ids()" in qual, qual
assert "memberships" not in qual.lower(), qual
Fourth — and this is the tripwire that catches future regressions — the plan-shape tests run EXPLAIN (ANALYZE, FORMAT JSON) on select id from public.documents and walk the tree looking for any node whose relation is memberships. There must be none. The verbose variant of the same plan is searched for the literal string auth.user_org_ids() in the SubPlan's Output, and the SubPlan's Actual Loops counter is asserted to equal 1, proving the function fires exactly once per outer query.
def test_documents_policy_evaluates_security_definer_call_once_per_query(
db: psycopg.Connection,
) -> None:
load_volume(db, **SMALL_VOLUME)
with auth_as(db, bench_user_id(0, 0)):
plan = explain_analyze(db, "select id from public.documents")
subplans = [
node
for node in iter_plan_nodes(plan)
if node.get("Parent Relationship") == "SubPlan"
]
assert subplans, json.dumps(plan)[:1500]
for node in subplans:
assert node.get("Actual Loops", 0) == 1, node
A behavior test closes the loop. Ada must still see exactly the three Acme documents she saw in step 5; the SECURITY DEFINER swap is a performance refactor, not a permission change, and any drift in visible rows would mean the new policy text has a different semantics from the old one.
Verification
Apply the migration and run the full suite from codebase/:
uv run pytest -q
The fifty-test suite passes — schema and behavior tests from steps 1–2, benchmark and InitPlan tests from steps 3–4, covering-index tests from step 5, and the fourteen new assertions added this step:
.................................................. [100%]
50 passed in 5.44s
The fourteen new tests in test_security_definer.py split into four families. Existence and security-posture tests fix the pg_proc row (security_definer + stable + pinned search_path + locked-down grants). Per-caller behavior tests fix the filter semantics under four different auth contexts. Policy-rewrite tests fix the pg_policies.qual text on both rewritten policies. Plan-shape tests fix the runtime story: no memberships node, a SubPlan that mentions auth.user_org_ids(), and exactly one actual loop per query.
What we built
A single STABLE SECURITY DEFINER function, auth.user_org_ids(), now owns the cross-tenant membership lookup. It runs with the definer's privileges, bypasses the memberships RLS policy that would otherwise re-fire per row, and returns the caller's org ids as a setof uuid. The function is hardened: its search_path is pinned at definition time, execute is revoked from public, and only authenticated and anon hold the new grant.
The two cross-tenant policies — documents_member_select and organizations_member_select — now call the helper instead of subquerying memberships inline. Because the helper is stable and the call is uncorrelated, the planner folds it into a one-shot SubPlan that fires exactly once per outer query, no matter how many rows the policy evaluates against. The outer plan for select id from public.documents no longer contains a memberships node at all.
The fourteen new tests pin the change from four directions. The function's security posture is locked in pg_proc; its filter semantics are locked in via four auth contexts; the rewritten policy text is locked in pg_policies; and the new plan shape is locked in by walking the EXPLAIN tree, asserting both the absence of the memberships relation and the presence of a one-loop SubPlan that mentions the helper by name.
What this unlocks is the final cleanup pass on the read path. Subsequent steps can now move on from "reducing how often the policy re-enters RLS" to denser optimizations — caching at the application layer, pre-computing tenant sets on a JWT claim, or denormalizing user_id directly onto documents — because step 6 has already shrunk the per-query policy cost to a single function call against a covering index.
Repository
The state of the code after this step: ecf25fc
Step 7: Denormalizing org_id Onto a Hot Child Table to Eliminate the Cross-Table Policy Join
After step 6 the read path for documents is a one-shot SubPlan against auth.user_org_ids() and the memberships relation is invisible in the outer plan. That win, however, only covers the parent table. The moment we introduce a typical fan-out child — per-document activity events, comments, audit rows — the natural policy shape becomes document_id in (select id from public.documents), which chains a second RLS evaluation against documents (and transitively against the helper) on every events read.
This step replaces that chain with a denormalization. We add a public.document_events table whose rows carry org_id as a real column, populate it from the parent document via a BEFORE trigger, and write the events policy as org_id in (select auth.user_org_ids()) — the same single-column SubPlan shape that documents itself uses. The trade is explicit: we pay one extra UUID per row plus a small trigger cost on write, and we get a read plan that never references public.documents at all.
Setup
No new third-party dependencies. The deliverable is one additive migration that creates the child table, its two indexes, the synchronizing trigger, the grants, and the two policies, plus a new test file that pins the denormalization from every angle that could regress.
codebase/
├── src/rls_tuning/ # unchanged
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql # unchanged
│ ├── 20260615000002_naive_rls.sql # unchanged
│ ├── 20260615000003_initplan_rewrite.sql # unchanged
│ ├── 20260615000004_covering_indexes.sql # unchanged
│ ├── 20260615000005_security_definer_helpers.sql # unchanged
│ └── 20260615000006_denormalized_tenant.sql # NEW
└── tests/
└── test_denormalized_tenant.py # NEW — 16 assertions
The migration is strictly additive. Nothing in the steps 1–6 schema is dropped, altered, or renamed: the documents/memberships/organizations policies from step 6 keep their auth.user_org_ids() rewrite, the covering indexes from step 5 stay in place, and the security-definer helper from step 6 is reused verbatim by the new policy on the child table.
Implementation
The new child table mirrors the columns we want on a typical event stream — a foreign key to its parent document, the denormalized org_id, a constrained kind, an optional actor, and a timestamp — and applies a check (kind in (...)) to keep the event vocabulary closed. The two foreign keys cascade on delete, which keeps cleanup behaviour aligned with how tenants are torn down in the rest of the schema.
create table if not exists public.document_events (
id uuid primary key default gen_random_uuid(),
document_id uuid not null references public.documents(id) on delete cascade,
org_id uuid not null references public.organizations(id) on delete cascade,
kind text not null check (kind in ('viewed', 'commented', 'shared', 'edited')),
actor_id uuid references auth.users(id) on delete set null,
created_at timestamptz not null default now()
);
The covering index on org_id is the same INCLUDE shape we used in step 5 for the parent table. The leading key is org_id because the policy filter is org_id in (select auth.user_org_ids()), and the INCLUDE (document_id) payload lets the most common join-back to the parent resolve as an Index Only Scan on the events side.
create index if not exists document_events_org_id_idx
on public.document_events (org_id)
include (document_id);
create index if not exists document_events_document_id_idx
on public.document_events (document_id);
A second non-covering index on document_id keeps the foreign-key cascade and any where document_id = ? lookups cheap. We deliberately did not collapse the two indexes into one composite — the access patterns are distinct, and a single (org_id, document_id) index would force document_id-only probes onto a full index scan because of btree leading-column rules.
The trigger is the safety latch. Without it, the denormalized column is a tenant-leak vector: a caller could insert (document_id = a Globex document, org_id = Acme) and the resulting row would be visible to Acme members through the policy. The trigger overrides whatever value the caller passes with the value it reads from public.documents, so the column can never drift from the parent.
create or replace function public.document_events_set_org_id()
returns trigger
language plpgsql
set search_path = public, pg_temp
as $$
declare
parent_org uuid;
begin
select org_id into parent_org
from public.documents
where id = new.document_id;
if parent_org is null then
raise exception
'document_events.document_id % does not reference a visible document',
new.document_id;
end if;
new.org_id := parent_org;
return new;
end;
$$;
drop trigger if exists document_events_set_org_id_biud on public.document_events;
create trigger document_events_set_org_id_biud
before insert or update of document_id on public.document_events
for each row
execute function public.document_events_set_org_id();
Three details in the trigger are load-bearing. The set search_path = public, pg_temp clause prevents a hostile session search_path from redirecting public.documents to a shadow table. The update of document_id clause scopes the trigger to writes that could actually change the tenant — pure kind/actor_id updates skip the lookup. And the explicit raise exception on a missing parent turns dangling references into a hard write-time error instead of a silently-NULL org_id, which would violate the column's NOT NULL constraint anyway but with a less actionable error message.
The two policies on the child table reuse the step-6 helper directly. The select policy filters reads, and the insert policy applies the same predicate via with check so a caller cannot write a row into a tenant they do not belong to — the trigger fixes drift on writes that pass the check, but the policy keeps writes from a non-member out entirely.
alter table public.document_events enable row level security;
create policy document_events_member_select on public.document_events
for select
to authenticated
using (org_id in (select auth.user_org_ids()));
create policy document_events_member_insert on public.document_events
for insert
to authenticated
with check (org_id in (select auth.user_org_ids()));
The new test file in tests/test_denormalized_tenant.py triangulates the change from five angles. Schema tests confirm org_id is NOT NULL uuid and the covering index has the right leading key plus INCLUDE payload. Trigger tests prove the BEFORE hook backfills from the parent, overrides a mismatched caller-supplied value, and rejects an event that references a missing document.
def test_trigger_overrides_caller_supplied_org_id(
db: psycopg.Connection,
) -> None:
acme_doc = _doc_id(db, ACME)
_, org_id = _insert_event(db, document_id=acme_doc, org_id=GLOBEX)
assert org_id == ACME
Policy-text tests read pg_policies.qual and pg_policies.with_check and assert both predicates mention org_id and auth.user_org_ids() while explicitly forbidding the substring documents. That last assertion is the canonical proof that the join was replaced by a single-column filter — any future refactor that pulls the parent table back into the predicate flips this test red on the first run.
def test_document_events_select_policy_uses_denormalized_org_id(
db: psycopg.Connection,
) -> None:
with db.cursor() as cur:
cur.execute(
"""
select qual
from pg_policies
where schemaname = 'public'
and tablename = 'document_events'
and policyname = 'document_events_member_select'
"""
)
qual = cur.fetchone()[0]
assert "org_id" in qual, qual
assert "auth.user_org_ids()" in qual, qual
assert "documents" not in qual.lower(), qual
The plan-shape tests are the load-bearing tripwires. They run EXPLAIN (ANALYZE, FORMAT JSON) on select id from public.document_events, walk every node, and assert there is no node whose relation is documents and no node whose relation is memberships. A verbose variant then asserts the literal string auth.user_org_ids() appears in the plan tree and that every SubPlan reports Actual Loops = 1, proving the helper fires once per outer query no matter how many event rows the policy filters against.
def test_document_events_plan_does_not_scan_documents_relation(
db: psycopg.Connection,
) -> None:
load_volume(db, **SMALL_VOLUME)
acme_doc = _doc_id(db, ACME)
for kind in ("viewed", "commented", "shared"):
_insert_event(db, document_id=acme_doc, kind=kind)
with auth_as(db, ADA):
plan = explain_analyze(db, "select id from public.document_events")
documents_node = find_node(plan, relation="documents")
assert documents_node is None, (
"denormalization is meant to hide documents from this plan: "
f"{json.dumps(plan)[:1500]}"
)
Behaviour tests close the loop. Ada (single-tenant Acme member) sees only Acme events; Grace (member of Acme and Globex) sees events from both orgs; Yukihiro (lone Initech member) sees only Initech events; the anon role sees nothing. A final test fires the check (kind in (...)) constraint by inserting a row with an out-of-vocabulary kind and asserting the resulting CheckViolation.
Verification
Apply the migration and run the full suite from codebase/:
uv run pytest -q
The sixty-six-test suite passes — schema and behaviour tests from steps 1–2, benchmark and InitPlan tests from steps 3–4, covering-index tests from step 5, the fourteen security-definer assertions from step 6, and the sixteen new tests added this step:
.................................................................. [100%]
66 passed in 10.61s
The sixteen new tests in test_denormalized_tenant.py split into five families. Schema tests fix the column shape and the covering index. Trigger tests fix the backfill, the override, and the dangling-parent rejection. Policy-text tests fix the rewritten qual and with_check so neither one re-introduces documents. Plan-shape tests fix the runtime story: no documents node, no memberships node, the helper present in the plan, and a one-loop SubPlan. Behaviour tests fix the per-caller visibility under four auth contexts plus a check-constraint regression test.
What we built
A new hot child table, public.document_events, now carries org_id directly on every row. A BEFORE trigger reads the parent document's org_id on insert and on any update of document_id, overrides whatever value the caller supplied, and rejects events that reference a missing document. The denormalized column is NOT NULL, backed by a covering index keyed on org_id with document_id in INCLUDE, and protected by foreign keys to both documents and organizations.
The two policies on the child table — document_events_member_select and document_events_member_insert — filter on org_id in (select auth.user_org_ids()), the same shape documents uses since step 6. The select policy keeps non-members from reading another tenant's events; the insert policy keeps non-members from writing one in the first place, and the trigger keeps a member from accidentally (or deliberately) flagging an event for the wrong tenant.
The sixteen new tests pin the change from five directions. Schema tests lock in the column and the covering index; trigger tests lock in the backfill semantics and the dangling-parent rejection; policy-text tests lock in the rewritten predicates and forbid any future re-introduction of a documents subquery; plan-shape tests lock in an outer plan that mentions neither documents nor memberships and whose SubPlan fires exactly once; behaviour tests lock in per-caller visibility across single-tenant, multi-tenant, lone-tenant, and anon contexts.
What this unlocks is the shape of the optimization itself. The ladder so far has been about reducing how often a policy re-enters RLS on the parent table; this step shows the next move when fan-out children are involved. By spending one extra UUID per row and a small trigger budget on writes, we collapse the read plan to a single column comparison against the same one-shot SubPlan the parent uses — and the same pattern generalizes to any future hot child (comments, mentions, audit rows) that would otherwise resolve tenancy by joining back to its parent.
Repository
The state of the code after this step: fcda175
Step 8: Short-Circuiting the Policy Machinery for Backend Jobs With a BYPASSRLS service_role
Steps 4 through 7 reshaped the per-user read path from four directions: an InitPlan rewrite of auth.uid(), covering indexes on the policy-filtered columns, a SECURITY DEFINER helper that collapsed the memberships join into a single SubPlan call, and a denormalized org_id on the hot child table. Every one of those wins targeted the authenticated request — a per-user web caller whose visibility must be filtered to their own tenants. Each of them left the policy machinery in the plan; the work was making that machinery cheap.
A second caller class still pays the full policy tax. Backend jobs, cron tasks, ETL workers, and internal admin tools have no per-user JWT, need to read or mutate across every tenant, and currently still trigger the SubPlan into auth.user_org_ids() on every row scan. This step adds the fifth optimization — grant Supabase's service_role the BYPASSRLS role attribute so the planner skips the policy quals entirely, then pin the new shape with a test file that proves both the bypass and the regression guard on the authenticated path.
Setup
No new third-party dependencies. The deliverable is one additive migration that flips BYPASSRLS onto service_role, grants the explicit table privileges the bypass does not cover, and installs a permissive allow-all policy per hot table as a defense-in-depth fallback. Two small additions land in the Python helper module — an auth_as_service context manager that swaps the session to service_role and clears the JWT claim — and a new test file pins the change from every angle.
codebase/
├── src/rls_tuning/
│ └── auth.py # NEW: auth_as_service helper
├── supabase/migrations/
│ ├── 20260615000001_initial_schema.sql # unchanged
│ ├── 20260615000002_naive_rls.sql # unchanged
│ ├── 20260615000003_initplan_rewrite.sql # unchanged
│ ├── 20260615000004_covering_indexes.sql # unchanged
│ ├── 20260615000005_security_definer_helpers.sql # unchanged
│ ├── 20260615000006_denormalized_tenant.sql # unchanged
│ └── 20260615000007_service_role_bypass.sql # NEW
└── tests/
└── test_service_role_bypass.py # NEW — 19 assertions
The migration is strictly additive. Nothing in the steps 1–7 schema is dropped, altered, or renamed: every authenticated-facing policy from earlier steps keeps its predicate, the covering indexes stay in place, the security-definer helper stays the source of truth for membership lookups, and the denormalized org_id on document_events keeps its trigger. The bypass applies to a new role with no overlap with the existing caller path.
Implementation
The role itself is provisioned defensively. Supabase already ships a service_role in hosted projects, but a locally booted database may or may not have it. A DO block creates the role with nologin bypassrls if it does not yet exist and falls through to ALTER ROLE ... BYPASSRLS if it does — the same migration runs cleanly against a fresh database and against an existing one.
do $$
begin
if not exists (select 1 from pg_roles where rolname = 'service_role') then
create role service_role nologin bypassrls;
else
alter role service_role bypassrls;
end if;
end
$$;
BYPASSRLS is a per-role attribute that tells the planner to skip the policy quals for any query executed under that role. With it in force, a plain select id from public.documents collapses to a single scan node — no SubPlan into auth.user_org_ids(), no InitPlan to hoist auth.uid() out of, no policy expression in the qual list at all. The nologin clause is deliberate; the role is assumed via set role from a connection that already authenticated as a higher-privileged user, not used as a primary login identity.
The next chunk handles grants. BYPASSRLS skips policies but does not skip the GRANT system — without explicit privileges, the planner rejects the query before RLS even gets consulted. Every hot table gets the full DML quartet, and the two schemas the backend needs to traverse get USAGE.
grant usage on schema public to service_role;
grant usage on schema auth to service_role;
grant select, insert, update, delete on public.organizations to service_role;
grant select, insert, update, delete on public.memberships to service_role;
grant select, insert, update, delete on public.documents to service_role;
grant select, insert, update, delete on public.document_events to service_role;
grant select, insert, update, delete on auth.users to service_role;
The grants are enumerated explicitly rather than written as a blanket grant all on all tables in schema public to service_role. The explicit form makes future additions a deliberate act — a new table is not silently exposed to backend writers until its migration lists it — and gives a single grep target if the privilege surface ever has to be audited.
The four allow-all policies are the defense-in-depth layer. While BYPASSRLS is in force the planner never consults a policy at all, so these rows in pg_policies are dormant. They earn their keep in two failure modes: a future migration that accidentally clears the role attribute with alter role service_role nobypassrls, and a table that ever gets alter table ... force row level security (which subjects even the table owner to RLS). In either case the explicit allow-all keeps the backend path from silently breaking.
create policy organizations_service_role_all on public.organizations
for all to service_role
using (true)
with check (true);
create policy memberships_service_role_all on public.memberships
for all to service_role
using (true)
with check (true);
create policy documents_service_role_all on public.documents
for all to service_role
using (true)
with check (true);
create policy document_events_service_role_all on public.document_events
for all to service_role
using (true)
with check (true);
On the Python side, the existing auth_as helper from step 2 already knew how to set request.jwt.claim.sub and set role authenticated. A second context manager handles the bypass role. It calls set role service_role and explicitly clears the JWT claim, because the backend path must not depend on a sub ever being present.
@contextmanager
def auth_as_service(conn: psycopg.Connection) -> Iterator[None]:
_apply_service_session(conn)
try:
yield
finally:
conn.rollback()
_reset_session(conn)
def _apply_service_session(conn: psycopg.Connection) -> None:
with conn.cursor() as cur:
cur.execute("set role service_role")
cur.execute("select set_config('request.jwt.claim.sub', '', false)")
The rollback in the finally block mirrors the authenticated helper. A caller may leave the transaction in an error state — a CheckViolation, an aborted insert during a test — and reset role plus set_config must run on a healthy session, or the next test gets an unhelpful current transaction is aborted error.
The new test file in tests/test_service_role_bypass.py triangulates the change from four angles. Role-attribute tests query pg_roles and pin rolbypassrls = true for service_role, false for authenticated, and false for anon — the bypass must not leak onto the per-user path. Grant tests query information_schema.role_table_grants and assert the SELECT/INSERT/UPDATE/DELETE set is present on every hot table.
def test_service_role_has_bypassrls_attribute(db: psycopg.Connection) -> None:
attrs = _role_attrs(db, "service_role")
assert attrs["bypassrls"] is True, attrs
def test_authenticated_role_does_not_have_bypassrls(db: psycopg.Connection) -> None:
attrs = _role_attrs(db, "authenticated")
assert attrs["bypassrls"] is False, attrs
The visibility tests prove the cross-tenant read actually works. service_role must see documents from every org, every membership row regardless of user_id, every organization slug, and every event row regardless of which document it belongs to. Each test enters the bypass context, runs a plain select, and asserts the visible set equals the full seeded universe.
def test_service_role_sees_documents_from_every_tenant(
db: psycopg.Connection,
) -> None:
with auth_as_service(db):
with db.cursor() as cur:
cur.execute("select org_id from public.documents")
visible = {row[0] for row in cur.fetchall()}
assert visible == {ACME, GLOBEX, INITECH}, visible
The plan-shape tests are the load-bearing tripwires. They run EXPLAIN (ANALYZE, FORMAT JSON) against select id from public.documents under the bypass session, walk every node, and assert there is no node whose Parent Relationship is SubPlan or InitPlan, and a verbose variant asserts the literal string auth.user_org_ids() does not appear anywhere in the plan tree. A fourth tripwire confirms the outer plan does not scan memberships at all — the step-6 helper read is gone because the helper is never called.
def test_service_role_plan_has_no_subplan_nodes(db: psycopg.Connection) -> None:
load_volume(db, **SMALL_VOLUME)
with auth_as_service(db):
plan = explain_analyze(db, "select id from public.documents")
subplans = [
node
for node in iter_plan_nodes(plan)
if node.get("Parent Relationship") == "SubPlan"
]
assert not subplans, json.dumps(plan)[:1500]
Two regression tests guard the authenticated path. The first runs the same EXPLAIN query under auth_as(db, ADA) and asserts the plan still mentions auth.user_org_ids() — the bypass must apply only to service_role, or the step-6 helper goes silently uncalled for the path that matters. The second asserts the authenticated plan still contains at least one SubPlan node.
def test_authenticated_plan_still_evaluates_user_org_ids_helper(
db: psycopg.Connection,
) -> None:
load_volume(db, **SMALL_VOLUME)
with auth_as(db, bench_user_id(0, 0)):
plan = explain_analyze(
db,
"select id from public.documents",
verbose=True,
)
assert plan_mentions(plan, "auth.user_org_ids()"), json.dumps(plan)[:1500]
A final pair of behaviour tests closes the loop. One inserts a document into GLOBEX from a service_role session and reads it back to prove cross-tenant writes succeed; another reads pg_policies and asserts the four _service_role_all policies exist on the four hot tables, locking in the defense-in-depth net so a future cleanup pass cannot drop them by accident.
Verification
Apply the migration and run the full suite from codebase/:
uv run pytest -q
The eighty-five-test suite passes — schema and behaviour tests from steps 1–2, benchmark and InitPlan tests from steps 3–4, covering-index tests from step 5, the security-definer assertions from step 6, the sixteen denormalization tests from step 7, and the nineteen new tests added this step:
........................................................................ [ 84%]
............. [100%]
85 passed in 11.02s
The nineteen new tests in test_service_role_bypass.py split into four families. Role-attribute tests fix BYPASSRLS on service_role and forbid it on authenticated and anon. Grant tests fix the explicit DML privileges on every hot table. Visibility tests fix the cross-tenant reads on documents, memberships, organizations, and document_events, plus a cross-tenant insert into documents that proves write-side parity. Plan-shape tests fix the runtime story: no SubPlan node, no InitPlan node, no mention of auth.user_org_ids(), and no scan of memberships — while the authenticated path keeps all three.
What we built
A new role, service_role, now carries the BYPASSRLS attribute. Any session that enters it via set role service_role skips the policy quals on every table in the schema, so backend jobs read and mutate across every tenant without paying the SubPlan-per-policy cost the authenticated path still pays. The role is nologin, which makes it an assumed identity rather than a primary login surface.
The role has explicit SELECT/INSERT/UPDATE/DELETE grants on organizations, memberships, documents, and document_events, plus USAGE on the public and auth schemas. BYPASSRLS skips policies but never skips GRANT, so the enumerated privileges are what actually let the role touch the tables — and the explicit form makes a future audit who can write query a single \dp away.
Four permissive to service_role using (true) with check (true) policies sit dormant on the hot tables as a defense-in-depth net. While the role attribute is in force the policies are never consulted, but they save the backend path from silently breaking if a future migration clears BYPASSRLS or flips a table to force row level security. The Python harness gained auth_as_service, a context manager that swaps to the role, clears the JWT claim, and resets the session cleanly even after an aborted transaction.
What this unlocks is the end state of the optimization ladder. Steps 4 through 7 minimized the cost of evaluating a policy; this step removes the policy from the picture entirely for the one caller class that does not need it. The two paths now have very different cost shapes — the per-user plan still routes through a one-shot helper SubPlan on a covering index, while the backend plan collapses to the raw scan node — and the test suite pins both shapes so neither one can regress without a red bar.
Repository
The state of the code after this step: 0d8941d
Step 9: Closing the Ladder With a Two-Role Benchmark Harness and a Decision Matrix
Steps 4 through 8 walked five distinct optimizations onto the same multi-tenant schema: the InitPlan rewrite of auth.uid(), covering indexes on policy-filtered columns, a SECURITY DEFINER membership helper, a denormalized org_id on the hot child table, and a BYPASSRLS service role for backend jobs. Each one had its own dedicated test file and its own EXPLAIN tripwire, but the article never tied them together: a reader finishing step 8 has eight migrations applied, eighty-five tests passing, and no single place that says "here is what each optimization costs and when you should reach for it."
This final step writes that capstone in code rather than in prose. No new migration ships — the schema stays exactly as step 8 left it. Instead a small comparison module times the same select id from public.documents query under both roles back-to-back so the reader can see the actual gap the bypass opens, and a frozen DECISION_MATRIX tuple records the five-row "when to reach for it / trade-off / how to prove it landed" table that the rest of the article was implicitly building toward. A new test file pins both artifacts so future schema renames cannot silently break the recommendation surface.
Setup
No third-party dependencies, no migration, no schema change. The deliverable is one new Python module that pairs a DECISION_MATRIX data table with a run_comparison benchmark harness, a re-export pass through the package __init__.py so callers see the new symbols, and one new test file that locks the matrix shape and verifies each row's tripwire against a freshly seeded local database.
codebase/
├── src/rls_tuning/
│ ├── __init__.py # re-exports the new symbols
│ └── comparison.py # NEW: DECISION_MATRIX + run_comparison
└── tests/
└── test_comparison.py # NEW — 21 assertions
The comparison module imports from the existing auth and bench helpers landed in earlier steps. auth_as and auth_as_service are the two context managers that flip the session into either role; run_query_loop, explain_analyze, iter_plan_nodes, init_plan_nodes, and plan_mentions are the timing and plan-walk primitives the benchmark file has been carrying since step 3. Reusing them keeps the capstone honest — the harness is not a parallel measurement track, it is the same one previous steps used.
Implementation
The data shape comes first. An Optimization dataclass captures the four columns the decision matrix needs: a stable key for lookups, a human title reused on chart axes, a when_to_use rule of thumb, the trade_off an operator accepts by picking it, and the verification_query whose EXPLAIN plan proves the optimization actually landed in the live schema.
@dataclass(frozen=True)
class Optimization:
"""One row of :data:`DECISION_MATRIX`."""
key: str
title: str
when_to_use: str
trade_off: str
verification_query: str
The dataclass is frozen=True on purpose. The matrix is meant to be authored once, walked many times, and never mutated by a caller — a passing test in test_comparison.py asserts that any attempt to overwrite a field raises, so a future refactor cannot accidentally let a helper "patch" a row and forget. Each field is plain str rather than an enum so the rows render straight into a table without an intermediate mapping layer.
The matrix itself is a tuple of five Optimization entries in optimization-ladder order. The ordering mirrors steps 4 → 8 of the article, which is load-bearing — test_decision_matrix_preserves_optimization_ladder_order re-asserts the order on every test run, because reordering the matrix would silently flip the recommended decision flow without any other test catching it.
DECISION_MATRIX: tuple[Optimization, ...] = (
Optimization(
key="initplan",
title="Wrap auth.uid() in (select auth.uid())",
when_to_use="Any policy that calls auth.uid() on a per-row predicate.",
trade_off="None — the rewrite is functionally identical and cache-friendly.",
verification_query="select id from public.memberships",
),
Optimization(
key="covering_indexes",
title="Add covering indexes on policy-filtered columns",
when_to_use="Policy filters a column that scales with row count.",
trade_off="Index storage + slower writes proportional to row volume.",
verification_query="select id from public.documents",
),
Optimization(
key="security_definer",
title="Move cross-table joins into SECURITY DEFINER helpers",
when_to_use="Policy embeds a join the planner cannot hoist on its own.",
trade_off="Opaque SQL behind a function; search_path must be pinned.",
verification_query="select id from public.documents",
),
Optimization(
key="denormalize_tenant",
title="Denormalize tenant_id onto hot child tables",
when_to_use="Child table has high fan-out from a tenant-owned parent.",
trade_off="A trigger + an extra column + a redundant FK on every row.",
verification_query="select id from public.document_events",
),
Optimization(
key="service_role_bypass",
title="Grant BYPASSRLS to trusted backend roles",
when_to_use="Backend jobs that legitimately read or mutate across tenants.",
trade_off="The role's credentials become an admin-grade trust boundary.",
verification_query="select id from public.documents",
),
)
Two design choices in the matrix are worth calling out. First, the verification_query per row is not always the same — initplan verifies on memberships because that is where the InitPlan rewrite actually appears in the plan tree, while denormalize_tenant verifies on document_events because the whole point of step 7 was that the events read stops scanning documents. Second, the trade_off field is brutally short on purpose: a reader scanning the matrix should be able to disqualify a row at a glance, not parse a paragraph.
The second artifact is the benchmark harness. ComparisonRow records one role's measurement: a label, the raw BenchResult from the existing run_query_loop, and three booleans extracted from a single EXPLAIN (ANALYZE, FORMAT JSON, VERBOSE) call. The two convenience properties avg_ms and per_second delegate straight to BenchResult so the public surface stays small.
@dataclass(frozen=True)
class ComparisonRow:
"""One role's measurements for :func:`run_comparison`."""
label: str
bench: BenchResult
has_subplan: bool
has_initplan: bool
mentions_user_org_ids: bool
@property
def avg_ms(self) -> float:
return self.bench.avg_ms
@property
def per_second(self) -> float:
return self.bench.per_second
The three boolean flags compress the entire EXPLAIN plan into the three signals that actually distinguish the two roles after step 8 landed. has_subplan and has_initplan walk the plan tree looking for the parent-relationship markers the planner emits when a SubPlan or InitPlan node is hoisted out of a qual. mentions_user_org_ids uses the verbose plan's textual qual rendering to confirm the SECURITY DEFINER helper is — or is no longer — being called.
The plan-shape extraction lives in two private helpers so the top-level run_comparison reads as a sequence of "do the same thing under each role" rather than a wall of nested dict walks. _plan_has_subplan calls into the existing iter_plan_nodes traversal; _plan_shape packages all three booleans into a kwargs dict that gets splat-passed into the ComparisonRow constructor.
def _plan_has_subplan(plan: Mapping[str, Any]) -> bool:
return any(
node.get("Parent Relationship") == "SubPlan"
for node in iter_plan_nodes(dict(plan))
)
def _plan_shape(plan: Mapping[str, Any]) -> dict[str, bool]:
return {
"has_subplan": _plan_has_subplan(plan),
"has_initplan": bool(init_plan_nodes(dict(plan))),
"mentions_user_org_ids": plan_mentions(dict(plan), "auth.user_org_ids()"),
}
The two per-role measurement functions are intentionally near-identical. Each one enters the matching context manager, runs the timing loop, captures one verbose plan, and packages the result. Keeping them as siblings rather than a single parameterised function makes a future per-role customisation — different iteration counts, different warm-up — a one-line edit instead of a generic-parameter refactor.
def _measure_under_authenticated(
conn: psycopg.Connection, *, query: str, iterations: int
) -> ComparisonRow:
with auth_as(conn, bench_user_id(0, 0)):
bench = run_query_loop(conn, query, iterations=iterations)
plan = explain_analyze(conn, query, verbose=True)
return ComparisonRow(label="authenticated", bench=bench, **_plan_shape(plan))
def _measure_under_service_role(
conn: psycopg.Connection, *, query: str, iterations: int
) -> ComparisonRow:
with auth_as_service(conn):
bench = run_query_loop(conn, query, iterations=iterations)
plan = explain_analyze(conn, query, verbose=True)
return ComparisonRow(label="service_role", bench=bench, **_plan_shape(plan))
run_comparison is the small public entry point. It accepts a query (defaulting to select id from public.documents, the table all five optimizations converge on) and an iterations count tuned for a local pgserver run, then returns a dict keyed by role name. The docstring nudges the reader toward public.document_events as the alternate proof target for the denormalization win.
def run_comparison(
conn: psycopg.Connection,
*,
query: str = DEFAULT_QUERY,
iterations: int = DEFAULT_ITERATIONS,
) -> dict[str, ComparisonRow]:
return {
"authenticated": _measure_under_authenticated(
conn, query=query, iterations=iterations
),
"service_role": _measure_under_service_role(
conn, query=query, iterations=iterations
),
}
A tiny find_optimization(key) helper closes out the module. It is the only sanctioned way to fetch a matrix row by key — a passing test pins that an unknown key raises KeyError rather than returning None, so a typo in a downstream consumer fails loudly at lookup time instead of silently feeding an empty row into a renderer.
def find_optimization(key: str) -> Optimization:
"""Return the :class:`Optimization` whose ``key`` matches ``key``."""
for entry in DECISION_MATRIX:
if entry.key == key:
return entry
raise KeyError(f"unknown optimization key: {key!r}")
The new test file in tests/test_comparison.py triangulates the change from three angles. Matrix-shape tests pin the row count, key uniqueness, the exact ordered key tuple, the populated-field invariant, and frozen-dataclass immutability. Harness-behaviour tests run run_comparison against a small seeded volume and confirm each role returns a ComparisonRow with positive timing, that the service_role row reports has_subplan=False, has_initplan=False, and mentions_user_org_ids=False, and that the authenticated row keeps the SubPlan + helper signature the step-6 work installed.
def test_service_role_row_reports_no_subplan_or_initplan(
db: psycopg.Connection,
) -> None:
load_volume(db, **SMALL_VOLUME)
rows = run_comparison(db, iterations=3)
service = rows["service_role"]
assert service.has_subplan is False
assert service.has_initplan is False
assert service.mentions_user_org_ids is False
Evidence tests are the third angle and the most important. Five tests — one per matrix row — re-execute the row's verification_query and assert the EXPLAIN evidence that the matching optimization claims to leave behind. The initplan row must produce at least one InitPlan node on the memberships read. The security_definer row must mention auth.user_org_ids() and must not scan memberships in the outer plan. The covering_indexes row checks the pg_indexes schema directly, because pgserver's small dataset makes the planner prefer a Seq Scan even when the index exists. The denormalize_tenant row must not scan documents on the events read. The service_role_bypass row must have no SubPlan, no InitPlan, and no mention of the helper.
def test_service_role_bypass_optimization_evidence_is_observable(
db: psycopg.Connection,
) -> None:
entry = find_optimization("service_role_bypass")
load_volume(db, **SMALL_VOLUME)
with auth_as_service(db):
plan = explain_analyze(db, entry.verification_query, verbose=True)
subplans = [
node
for node in iter_plan_nodes(plan)
if node.get("Parent Relationship") == "SubPlan"
]
assert not subplans, json.dumps(plan)[:1500]
assert not init_plan_nodes(plan), json.dumps(plan)[:1500]
assert not plan_mentions(plan, "auth.user_org_ids()"), json.dumps(plan)[:1500]
A final regression guard runs every verification_query in the matrix under an authenticated session and asserts the EXPLAIN call returns a real plan. That one test would have caught any silent rename of document_events to doc_events between steps 7 and 9, because the matrix entry referencing the old name would produce an EXPLAIN error long before any of the other evidence tests got a chance to fail.
Verification
Apply nothing new — the schema is still at the step-8 shape — and run the full suite from codebase/:
uv run pytest -q
The suite now totals one hundred and six tests: the eighty-five that step 8 left passing, plus the twenty-one new ones added this step. All green:
........................................................................ [ 67%]
.................................. [100%]
106 passed in 12.71s
The twenty-one new tests in test_comparison.py split into three families. Matrix-shape tests (seven) lock the row count, the key tuple, the ordering, the populated-field invariant, the frozen-dataclass immutability, and the lookup helper's success + failure modes. Harness-behaviour tests (six) confirm run_comparison returns one row per role, that the timing is positive, that the ComparisonRow properties delegate to BenchResult correctly, that the default-keyword call signature still works, that an alternate query target is accepted, and that the two roles produce the expected SubPlan / helper signatures. Evidence tests (eight) walk every matrix row's verification query and assert the EXPLAIN tripwire it claims.
What we built
A new comparison module owns two artifacts. The first is DECISION_MATRIX, a frozen five-tuple of Optimization records — one per step from 4 to 8 — that captures each optimization's "when to reach for it" rule, the trade-off it asks the operator to accept, and the SQL query whose EXPLAIN plan proves it landed in the live schema. The second is run_comparison, a small harness that times a configurable query under both the authenticated and service_role sessions and packages each result as a ComparisonRow whose three booleans surface the SubPlan / InitPlan / helper-mention shape of the plan tree.
The harness is deliberately thin. It does not pretty-print, it does not render Markdown, it does not write JSON to disk — it returns a dict keyed by role name and gets out of the way, so a notebook, a CLI, or a future docs-generation script can pick the rendering. The two private per-role measurement functions stay siblings rather than collapsing into one generic helper, which keeps the surface area honest: adding a third measured role later is a copy-paste, not a refactor.
The new invariants the test file installs are the load-bearing part. The matrix's row count, key tuple, and ordering are now pinned, so a future PR that quietly reorders the rows or drops one fails CI. Every row's verification_query is exercised under a real authenticated session on every run, so a schema rename can never desync the matrix from the underlying tables. The service_role plan-shape guarantees and the authenticated-path regression assertion are restated here in the comparison harness, so the gap between the two roles cannot silently close without a red bar.
What this unlocks is the article's conclusion, not the next implementation step. The optimization ladder is now landed in code, tied to its proof, and queryable from a single import. A reader who finishes step 9 can import rls_tuning and walk the five rows of DECISION_MATRIX in a notebook, time their own query under both roles with run_comparison, and see the gap on their own data — without having to re-derive any of the schema work that got them here.
Repository
The state of the code after this step: 36e2f76
Repository
Full source at https://github.com/vytharion/supabase-rls-policy-performance-tuning.
Walk the lessons by stepping through the git commits in the repo — each major step has its own commit you can git checkout and rerun.