RLS recursion infinite loop: why I gave up policies and bet everything on a JWT custom claims hook

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":"[email protected]","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.