Fixing Postgres RLS Recursion with a JWT Custom Claims Hook
A single GRANT line triggered an infinite RLS recursion (42P17) in Postgres. How moving role checks out of user_roles into a signed JWT claim fixed it—and its trade-offs.
In a Supabase project, adding a new sub-role to the authenticated group unexpectedly triggers Postgres error 42P17: infinite recursion detected in policy. The root cause isn't a badly written policy but how RLS interacts with Postgres role inheritance—any policy written TO authenticated automatically applies to every sub-role granted that membership, so an indirect reference to user_roles in another table's policy is enough to close a loop. Fixing it in one place simply relocates the problem.
The author tries all three escape routes: a SECURITY DEFINER function bypasses RLS but creates an opaque, hard-to-audit technical debt; role isolation works for narrow roles but doesn't scale for widely shared tables. The lasting fix is removing the permissions table from the evaluation path entirely: using Supabase's Custom Access Token Hook, the user's role is embedded as a signed JWT claim at login, and policies read auth.jwt()->>'user_role' instead of querying user_roles.
The result makes recursion structurally impossible, since the permissions table is no longer queried during policy evaluation. But this introduces a new single point of failure: if the hook is disabled or misconfigured, the claim vanishes from the JWT and every authenticated session gets denied access—a loud, visible outage instead of a silent recursion bug, which the author considers the better failure mode.
The broader engineering lesson: granting a new role authenticated membership is never a neutral operation, and permissions tables should never sit inside the permission-evaluation path—the JWT is exactly where a session's self-knowledge belongs.