How to fix 'infinite recursion detected in policy' in Supabase RLS

TL;DR — infinite recursion detected in policy for relation "team_members" (SQLSTATE 42P17) means an RLS policy on a table runs a SELECT against that same table. Evaluating the policy fires the subquery, the subquery re-triggers the same policy, and Postgres aborts the loop. It's a runtime error, not a migration error. Fix it by moving the same-table lookup into a SECURITY DEFINER function — which runs as the table owner and so bypasses RLS for that one read — and calling it from the policy. The helper must pin set search_path = ''.

What does "infinite recursion detected in policy" mean?

It's a Postgres runtime error (SQLSTATE 42P17, invalid_object_definition), raised during query planning — not when you run the migration, but when a client query touches the table. Postgres is telling you a policy can't be evaluated because evaluating it requires evaluating itself.

Here is the shape that causes it. You have a team_members(team_id, user_id, role) table and you want "a user can see every member of any team they belong to":

create policy "View members of my teams"
on public.team_members
for select
using (
  team_id in (
    select team_id from public.team_members   -- selects the SAME table
    where user_id = auth.uid()                 -- the policy protects
  )
);

To decide whether you can see a row, Postgres runs the subquery. The subquery reads team_members — which fires this policy again — whose subquery reads team_members again, and so on. Postgres detects the cycle and raises 42P17.

The single most useful clarifying fact: recursion only happens when a policy queries its own table. A policy on documents that selects from team_members is a different relation and does not recurse — that pattern is completely fine. Keep the fix below only for the genuine self-join.

How do I fix infinite recursion in a Supabase RLS policy?

Move the self-referential lookup into a SECURITY DEFINER function. A definer function executes with its owner's rights; on Supabase that owner is postgres, which owns the table and bypasses RLS by default. So the SELECT inside the function never re-invokes the policy — the loop is broken. The policy is reduced to a single function call.

-- A private schema keeps the helper off the Data API (it can't be called
-- over RPC), but policies — evaluated inside the DB — can still use it.
create schema if not exists private;

create or replace function private.is_team_member(_team_id uuid)
returns boolean
language sql
security definer          -- runs as owner (postgres) -> owner bypasses RLS
set search_path = ''       -- REQUIRED: pin the path, then qualify every name
stable
as $$
  select exists (
    select 1
    from public.team_members
    where team_id = _team_id
      and user_id = (select auth.uid())   -- (select ...) => planner caches it
  );
$$;

-- EXECUTE defaults to PUBLIC — lock it down:
revoke all on function private.is_team_member(uuid) from public, anon;
grant execute on function private.is_team_member(uuid) to authenticated;

Now the policy just calls it — no self-reference, no recursion:

create policy "View members of my teams"
on public.team_members
for select
to authenticated
using ( private.is_team_member(team_id) );

Because the function's internal SELECT runs with the owner's rights, it is not subject to team_members' RLS, so the policy is never re-entered. And because it still reads the caller's identity via auth.uid(), the check stays per-user — it does not fall into the policy-that-isn't-user-scoped trap, which would leak every team's rows.

Two details that matter: set search_path = '' is not decoration. A SECURITY DEFINER function with a mutable search_path is a real privilege-escalation hole — an attacker can shadow an unqualified name and run it with the owner's rights. Pin the path and schema-qualify every reference (public.team_members, auth.uid()). The full mechanism is in why SECURITY DEFINER functions need a pinned search_path. Also wrap the auth call as (select auth.uid()) and mark the helper stable so Postgres caches it once per statement instead of re-evaluating it per row — the same RLS performance pattern you want everywhere.

Are there fixes that don't use a helper function?

Two, depending on your needs.

Alternative 1 — put the role/team in a JWT claim. Use a Custom Access Token auth hook to stamp the user's role into the JWT, then read it in the policy. Nothing queries the table, so nothing can recurse:

create policy "Admins manage members"
on public.team_members
for all to authenticated
using ( (auth.jwt() ->> 'user_role') = 'admin' );

The tradeoff: the claim is only refreshed when the access token rolls over (default ~1 hour), so a demotion or membership change lags until then. Great for coarse RBAC, risky for revocation-sensitive checks, and poor for large or fast-changing membership sets.

Alternative 2 — don't self-reference at all. A user reading only their own membership row needs no lookup and never recurses:

create policy "See my own membership"
on public.team_members
for select to authenticated
using ( user_id = (select auth.uid()) );

Only the "see my teammates" case needs the self-join. Split your policies so the expensive definer path applies to just that query.

Why does it work in the SQL editor but still fail from my app?

Because the Supabase SQL editor and Studio run as the postgres role, which owns your tables and is not subject to their RLS — so the recursion may not reproduce there, and a broken fix can look fine. Always test as the authenticated role with a forged JWT claim; see how to test Supabase RLS.

One more gotcha: the definer trick relies on the owner bypassing RLS. If you ran alter table team_members force row level security, even postgres is subject to the policy — the helper will still recurse. Don't use FORCE on a table whose recursion fix depends on owner-bypass.

FAQ

Is infinite recursion detected in policy a syntax error? No. It's SQLSTATE 42P17, raised at runtime during query planning when a client hits the table — not when the migration runs. Your migration applies cleanly; the error only surfaces on the first SELECT/INSERT that triggers the recursive policy.

Why doesn't my documents policy recurse when it reads team_members? Recursion only occurs when a policy queries the same table it protects. A policy on documents that checks team_members references a different relation, so there's no loop — leave those policies as-is.

Can I just disable RLS or use USING (true) to make the error go away? Don't. alter table team_members disable row level security and swapping the predicate for using (true) both stop the error — by making the table world-readable. They fail open silently. The recursion is the loud, safe failure; those "fixes" are the quiet, unsafe one.

Does the helper function open a new security hole? Only if you're careless. Keep it in a non-exposed schema (private), pin set search_path = '' with fully-qualified names, and revoke ... from public, anon so it isn't a callable RPC. And make sure it still ties back to auth.uid().

Why (select auth.uid()) instead of auth.uid()? Postgres evaluates the subquery once per statement (an InitPlan) instead of once per row, a real speedup on large tables. It doesn't change the security behavior.

Where GuardLayer fits

Be clear about the honest limit: infinite recursion detected in policy is a runtime Postgres error, and GuardLayer is a static scanner — it reads your code and SQL migrations, it does not execute your policies. So it cannot detect the recursion itself, and it does not parse function bodies, so it won't verify your new helper pins its search_path either. For that, run Supabase's own Database Linter (function_search_path_mutable, lint 0011).

Where GuardLayer earns its place is the anti-fix. When the recursion error frustrates people, the two things they paste from forums are exactly the two that quietly open the table: disabling RLS and USING (true). Those don't throw an error — they fail open — and those are real rules GuardLayer ships: supabase/rls-explicitly-disabled (critical, flags DISABLE ROW LEVEL SECURITY in a migration) and supabase/policy-using-true (warning, flags constant-true predicates). Adjacent rules catch the not-user-scoped policy (supabase/policy-no-user-scope) and tables shipped without RLS at all (supabase/rls-missing-on-table). Fix the 42P17 in ten minutes with a definer function; let GuardLayer make sure you didn't reach for the dangerous shortcut instead.