When admins break everything

You want admins to read every document. The obvious move: write a policy on documents that says "visible if you own it or you're an admin", and look up "admin" in a user_roles table.

Start with the table itself. With RLS off, anyone who can query it sees everything. Flip the switch on and — with no policy yet — the table goes dark: enabling RLS denies by default. (That is not the same as having no RLS — it's deny-all, opt-in; you grant access back row by row.)

The table — and the RLS switch

booting Postgres…

Off: every row is public. On with no policy: nothing comes back — enabling RLS denies by default (deny-all is not the same as no RLS). A policy grants access back, row by row.

A policy grants rows back. RLS is on, so the table starts dark — press Apply to add the policy below and watch only your rows return. Edit it and re-apply to see the set change:

Your first policy

booting Postgres…

USING decides which rows you can see. Edit the policy and Apply — watch the rows change.

The same policy looks different depending on who is asking. Switch the role and watch the rows appear and vanish:

The same policy, different eyes

booting Postgres…

One policy, three roles. anon has no identity and sees nothing; authenticated is you and sees your rows; service_role skips RLS and sees everything — that's the bypass your app server connects with, and how data leaks past RLS if the app over-fetches. Switch the role and watch the rows change.

That last role is the catch. Switch to service_role and every row comes back — it bypasses RLS entirely. That's the role your app server connects as, so if it over-fetches and forwards rows to the client, the database's policies never protected that data.

RLS has two halves. USING decides which rows you can read; WITH CHECK decides which rows you can write. Try inserting a row owned by someone else — WITH CHECK rejects it:

USING vs WITH CHECK

booting Postgres…

USING filters which rows you can read; WITH CHECK validates rows you try to write. Insert a row owned by someone else: USING is irrelevant to the write, but WITH CHECK rejects it.

It works in testing. Then you turn on RLS for user_roles too — because that table is sensitive — and every query against documents starts failing with a 500. Nothing you changed in documents explains it.

The reason is one fact about RLS: a policy is not a filter that runs once at the top. It re-checks on every read of the table — including the read the policy makes inside itself. Flip RLS on user_roles below to replay the exact moment it broke.

the moment it broke

SELECT * FROM documents

documents has RLS on; to check “admin”, its policy reads user_roles.

RLS on user_roles:

documents policy runs → to check admin, it reads user_roles
RLS on user_roles is off → its read isn't policed → it answers
✓ documents policy resolves → the row is returned

This is what worked in your tests: RLS on user_roles was off, so its internal read wasn't policed — the chain ended here. (Off ≠ deny-all: with RLS off there's no policy to enforce.)

Here is what the database actually does. Unroll the requirement level by level and watch what it would take to satisfy the policy. RLS runs on every table access — including the tables your policy reads inside its own subquery.

policy evaluation

policy:

EXPLORING · what would this policy require? (hypothetical)

depth 1

You run: SELECT * FROM documents

depth 2

Before returning a row, the documents policy must hold: you own it OR you're an admin. To check “admin”, it reads user_roles.

create policy doc_access on documents for select using (
  owner_id = auth.uid()
  or exists (select 1 from user_roles ur
             where ur.user_id = auth.uid() and ur.role = 'admin')
);

depth 3

But reading user_roles triggers user_roles' own policy — and that policy reads user_roles again.

create policy ur_self on user_roles for select using (
  exists (select 1 from user_roles where user_id = auth.uid())← reads user_roles, which fires this same policy
);

… ∞ …

Unroll the requirement, level by level — and notice it never bottoms out. Then see what Postgres really does.

The policy on documents has to look in user_roles. But user_roles has its own policy — which reads user_roles again. That read needs the policy, which reads the table, which needs the policy. It never bottoms out, so Postgres does not even try: it detects the cycle statically and aborts with 42P17.

The fix. Flip the toggle to via is_admin(). The admin check now goes through a function marked security definer.

security definer is not a special object, and not service_role. It is a mode of a function. By default a function runs with the rights of whoever called it (security invoker) — and RLS applies to them. security definer says the opposite: run with the rights of whoever created the function — its owner. It is like sudo: you launch it, but the owner's rights apply inside. The owner here is privileged, RLS is not enforced for them — so when is_admin() reads user_roles, that read is not checked by the policy, and the loop never forms. The sanctioned escape hatch: a trusted helper allowed to look without being frisked each time.

Recursion is the dramatic failure — a 500 you cannot miss. The quieter one is cost: a policy is not free. Its USING expression is evaluated again for every row the query scans. If that is a function call like auth.uid(), then on a million-row table it runs a million times — each time returning the same value. Wasted work.

The fix is one of the most-cited RLS tips: wrap the call in (select auth.uid()). Now Postgres sees the value is the same for the whole query, computes it once up front, and just substitutes the ready constant from there on. (That "once up front" is what Postgres calls an InitPlan in the query plan.) Same rows, same result — a very different plan. Flip the predicate and drag the row count:

The cost: a policy runs on every row

booting Postgres…

A policy's USING runs once per scanned row. Flip the predicate and drag the row count — watch the real plan and the time change.