Episode 1/4 — 3 incidents, one root: default GRANTs open more than you think — [CANONICAL URL EPISODE 1: fill in after push]
Episode 2/4 —await mutation()lies when nobody opens the{ error }envelope — [CANONICAL URL EPISODE 2: fill in after push]
The morning Françoise sees zero rows, again
It's a Tuesday in April 2026. I've just added the agent_readonly role to the authenticated membership — a one-liner, meant to share a GRANT for a reporting job. First SELECT on cours, Sentry receives infinite recursion detected in policy for relation "user_roles", code 42P17. From the office next door, Françoise is already on the phone with the Maisons-Laffitte branch: "So they can't see anything over there — is that normal?" Foreman tone, not really a question. I read the error on my screen. The difference from episode 1: this time Postgres is talking.
What came out of Sentry was no longer a silent empty set — it was an explicit error. That difference saved me two days. When Postgres shouts, you listen. The trap is that what it says isn't where you're looking.
I won't pretend this is obscure. A policy on user_roles that queries user_roles to decide who can read user_roles is a loop. You avoid it, you work around it with SECURITY DEFINER, you move on. The problem: my user_roles policy didn't reference user_roles. I had already cleaned it up three weeks earlier. The recursion was coming from somewhere else.
The diagnostic that targets the wrong object
First reflex: re-read the user_roles policy. It's clean, reads auth.email(), never calls itself. Second reflex: disable policies one by one to find the culprit. Wrong angle.
-- supabase/migrations/20260420_admin_write_cours_v1.sql
-- "Admin write cours" policy — original version that loops
CREATE POLICY "Admin write cours" ON public.cours
FOR ALL TO authenticated
USING (
EXISTS (
SELECT 1 FROM public.user_roles
WHERE email = auth.email()
AND role IN ('admin', 'super_admin')
)
);
The recursion doesn't come from a faulty policy. It comes from the fact that Postgres evaluates all permissive policies matching the current role. When I add agent_readonly to authenticated membership, it inherits not only table privileges but the policy evaluation scope of anything posted TO authenticated. The Admin write cours policy, which reads user_roles, now gets evaluated for agent_readonly too. When agent_readonly reads user_roles, Postgres applies user_roles policies — posted TO authenticated, therefore inherited by agent_readonly — which contain an EXISTS (SELECT FROM user_roles) through that other table. The loop is closed.
The recursion isn't a writing bug. It's the structural consequence of coupling RLS with Postgres role membership inheritance. As long as any authenticated role can, through any path in a permissive policy, fall back onto a read of user_roles that itself applies policies, the loop is possible. Cleaning it in one place just moves it.
Three exits, two debts, one switch
I tried all three exits.
The first: SECURITY DEFINER. Wrap the user_roles read in a function that runs with its owner's privileges, bypassing RLS in its body. Other tables' policies call this function instead of EXISTS (SELECT FROM user_roles) directly. The recursion disappears locally. It's the patch that works tomorrow and costs you six months later — when you no longer know which policy reads what, when search_path wasn't locked, when auditing the surface becomes impossible because real permissions live in opaque functions. I kept it for two weeks.
The second: role isolation. Remove agent_readonly from authenticated membership, write dedicated policies per table. For a narrow technical role, that holds. For a dashboard where all authenticated users must read according to their policy, it doesn't scale — you're not going to duplicate every policy per role.
The third, the one I took: if the role is what causes the recursion, pull the role out of the database. Push it into the JWT at login, as a signed claim, and have policies read that claim.
The Custom Access Token Hook
Supabase exposes a Custom Access Token Hook — a Postgres function called by Supabase Auth just before the JWT is issued, which receives standard claims and can return enriched ones.
-- supabase/migrations/20260425170000_auth_hook_security_definer.sql
CREATE OR REPLACE FUNCTION public.auth_hook_add_role(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
claims jsonb;
user_email text;
user_role text;
BEGIN
claims := event->'claims';
user_email := claims->>'email';
SELECT role INTO user_role
FROM public.user_roles
WHERE email = user_email
LIMIT 1;
IF user_role IS NOT NULL THEN
claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
END IF;
event := jsonb_set(event, '{claims}', claims);
RETURN event;
END;
$$;
REVOKE EXECUTE ON FUNCTION public.auth_hook_add_role(jsonb) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.auth_hook_add_role(jsonb) TO supabase_auth_admin;
Three key precautions. SECURITY DEFINER because the function reads user_roles server-side — this is the one place I allow this bypass, executed by Supabase Auth, never by a user session. SET search_path = '' to block schema injection. REVOKE EXECUTE FROM PUBLIC, anon, authenticated then GRANT TO supabase_auth_admin: the function is only callable by the Auth system role.
On the policy side, the switch is terse:
-- supabase/migrations/20260425170234_phase_b_dual_check_policies.sql
-- "Admin write cours" refactored — reads JWT claim, not user_roles
CREATE POLICY "Admin write cours" ON public.cours
FOR ALL TO authenticated
USING (
(auth.jwt() ->> 'user_role') IN ('admin', 'super_admin')
)
WITH CHECK (
(auth.jwt() ->> 'user_role') IN ('admin', 'super_admin')
);
The claim is signed, verified by Supabase, and the policy never touches user_roles again. Recursion is mechanically impossible because the permissions table is no longer queried during permission evaluation. 209 occurrences of auth.jwt() across 8 migration files since this switch — zero returns to EXISTS (SELECT FROM user_roles).
What changes, what stays
What changes. Recursion is no longer possible by design. A material probe confirms it:
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" = '{"email":"test@palissy.fr","user_role":"super_admin"}';
SELECT count(*) FROM public.user_roles;
-- 13 rows, RLS active, no 42P17 error
What stays. The hook becomes a single point of failure. If it's accidentally disabled — Studio manipulation, a migration that redefines it without the claim, a rights rotation that removes supabase_auth_admin — the claim disappears from the JWT, policies fall back on auth.jwt() ->> 'user_role' returning null, and every authenticated session gets denied access. Not a loop — a blackout. A different debt, more visible, that shouts when it falls. Preferable to a recursion that used to masquerade as a 42P17 you chased in vain.
What also stays: discipline. Any new Postgres role added to authenticated membership must be tested under SET LOCAL ROLE before prod, against a table with a non-trivial policy. Not to verify it doesn't trigger recursion — it can't anymore — but to verify that the scope of policies posted TO authenticated is exactly what you want it to inherit. The April incident's real lesson: GRANT membership is never a neutral operation.
Whatever you may think of "RLS best practices"
The doctrine I've read for three years repeats that you must write policies on user_roles. True in theory. In production, on a system with membership inheritance, it's a trap on a six-month fuse. The exit isn't writing better policies. It's recognizing that the permissions table has no business being in the permission-evaluation path, and that the JWT is precisely the place designed to carry what a session knows about itself.
Episode 4/4 — recap of all 4 lies and the Live/Snapshot/Cache doctrine applied to RLS: why the role in the JWT is a Snapshot of login time, not a Live read from the database — [CANONICAL URL EPISODE 4: fill in after push]
Episode 3/4 of "The week Supabase lied to me four times." The hook holds. The discipline remains. The fourth lie — the subtlest one — arrives on Friday.
Top comments (2)
The non-obvious part you nailed: 42P17 names the relation where the loop closes, not the policy that opened it. Postgres ORs every permissive policy that matches the current role, so adding one membership quietly changes which policies execute on tables you never touched. Debugging the named table is exactly the wrong reflex, and the error message actively encourages it.
Claims-in-JWT is the right escape hatch, but it trades a recursion bug for a freshness bug worth naming out loud: claims are minted at token issue time, so a role revocation keeps working until the token expires. If an admin gets demoted, they stay admin for the remainder of their session unless you force a refresh. Short access-token TTL plus a forced refresh on any role mutation covers most of it; for the truly sensitive writes, a single non-recursive DB check on the hot path is still cheaper than the incident. Looking forward to episodes 3 and 4.
Thanks a lot for you comment
Exactly — and the OR-combination is what makes it so hard to localize: the offending policy and the relation Postgres blames usually live on different tables, so the error points you at where the loop closes, never where it opened. Once you internalize that, 42P17 stops being cryptic and starts being a map.
You're right to name the freshness tradeoff — but I'll be honest about where I landed on it. At ~8 active seats, all people I know by name, I deliberately didn't build the forced-refresh-on-role-change machinery: it's real engineering against a threat model I don't have. What bounds me instead is Supabase's default 1-hour access-token TTL — a revoked role is stale for at most an hour — plus the fact that at this scale I can just kill the session by hand the moment it matters, no automation needed. The non-recursive hot-path check you describe is exactly the right escape valve, and it's the first thing I'd add the day this stops being 8 known people and becomes a number I can't hold in my head. Naming that threshold felt more useful than paying for it early.