0
Challenge · 57RLS at Scale

Cross-table policies, SECURITY DEFINER helpers & infinite recursion

Securityused in ~80% of apps

You've written policies that check one row against itself — "is this your post?" Real products ask harder questions: "is this your ORG's post?", which means the policy on one table has to go read a different table to answer. That's ordinary, and it works fine. The trap springs one step later, the day the product manager asks for a "team roster" page — see who else is on your team — and the obvious policy you write to answer that question asks a table to read itself. Postgres refuses, instantly, with an error every working engineer eventually has to debug at 6pm: `infinite recursion detected in policy`.

Core idea. A policy is allowed to query any table it wants, including a completely different one — that's a normal cross-table policy, and it's how tenant isolation is built. Recursion is a much narrower, specific failure: it happens only when evaluating a policy on table T requires re-evaluating T's own RLS, directly or through a chain of views/joins that loops back to T. Postgres detects that cycle and raises `42P17` immediately, rather than hanging. The fix is a `SECURITY DEFINER` helper function: because it runs as its owner rather than the caller, its internal query is not subject to the caller's RLS on that table, so the cycle never closes.
Playground, this isn't a screenshot
Live demointeractive
Real Postgres · in your browseridle
Product wants a team roster: alice should see carol's membership row too, not just her own, since they share Acme. The obvious policy for that — using (org_id in (select org_id from memberships where user_id = (select auth.uid()))) — is already installed on memberships in this fixture. Predict what happens as alice, then run it.
Querying as
Cmd/Ctrl + Enter
↑ play a little first, then dig in