DEV Community

Cover image for Fixing "infinite recursion detected in policy" in Supabase (42P17)
Ahmed Hassan Yaseen
Ahmed Hassan Yaseen

Posted on Originally published at stacksmesh.com AI-assisted

Fixing "infinite recursion detected in policy" in Supabase (42P17)

infinite recursion detected in policy for relation
code: 42P17
Enter fullscreen mode Exit fullscreen mode

A row-level security policy on a table runs a query against the same table, or against a table whose policy queries back. To check the policy, Postgres needs the policy again, so it stops with 42P17. The fix I use: move the membership lookup into a security definer function in a schema the API does not expose, and call that function from the policy.

It turns up in Lovable, Bolt and Supabase apps. In our work, team, organisation and role models are where AI builders most often leave data open or loop.

Why it happens

  • A membership table checks itself. A policy on team_members asks "is this user a member of this team?" by selecting from team_members, which triggers the same policy.
  • Two tables point at each other. projects checks team_members, and team_members checks projects. Each policy needs the other first.
  • A roles table guards itself. A policy on profiles reads profiles.role to decide who is an admin.

Find the cause

  1. Note the relation named in the error: that is the table whose policy loops.
  2. Read every policy on that table and look for a subquery that selects from the same table.
  3. Follow any subquery to another table and read that table's policies. A cycle of two tables loops the same way.

Fix 1: look the membership up in a security definer function

  • Create a private schema that is not listed under Exposed schemas in the API settings.
  • Create the function as security definer with an empty search_path, and schema-qualify every name inside it.
  • Replace the subquery in the policy with a call to the function.
create schema if not exists private;

create or replace function private.is_team_member(team uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.team_members m
    where m.team_id = team and m.user_id = (select auth.uid())
  );
$$;

drop policy if exists "Members can read their team" on public.team_members;
create policy "Members can read their team"
on public.team_members for select
to authenticated
using ( private.is_team_member(team_id) );
Enter fullscreen mode Exit fullscreen mode

Fix 2: keep admin flags out of a table users can edit

  • Store roles in a separate table that only the server writes, or in app_metadata on the user, which users cannot change.
  • Read the role through the same kind of function, never from a column the user can update.

Check the fix worked

  • Run the failing query again as a signed-in member: it returns rows.
  • Run it as a user who is not a member: it returns no rows, without errors.
  • Confirm the private schema is not in the API's exposed schemas, so the function cannot be called directly.

Stop it coming back

  • Draw which policy reads which table before you write them. Any cycle will loop.
  • Put every cross-table check behind one small function per question (is member, is admin).

Quick answers

Is a security definer function safe? Yes, when it lives in a schema the API does not expose, sets an empty search path, qualifies every name and returns only a yes or no. It runs as its owner, so keep it small.

Why not just disable RLS on the membership table? Then anyone with your publishable key can list every team and its members. The function keeps the table protected.

Source: Supabase: Row Level Security (security definer functions).


I first published this fix on our site: Fix "infinite recursion detected in policy for relation" in Supabase. For the gaps anyone can see from outside your live app (exposed keys, security headers, what Google reads), run the free Launch Check.

I wrote this with AI assistance and reviewed it myself.

Top comments (1)

Collapse
 
cekuu35 profile image
Cenk KURTOĞLU •

Good breakdown of the error — one addition from the apps I scan: the recursion is almost never mysterious once you see the trigger pattern. The error means your policy's USING expression queries a table that is itself protected by a policy involving that same lookup — directly, through a view, or through a function. The most common shape in the wild is a policy on profiles (or a memberships table) that runs EXISTS (SELECT 1 FROM profiles ...) inside its own USING:

-- 42P17 waiting to happen: policy on profiles reads profiles
create policy "members see profiles" on profiles
  for select using (
    exists (select 1 from profiles p
            where p.id = auth.uid() and p.member = true)
  );
Enter fullscreen mode Exit fullscreen mode

Two fixes worth separating:

  1. Restructure first. If the predicate only needs columns from the row being checked, reference them directly (member = true and id = auth.uid()) — no subquery, no recursion, nothing to maintain.
  2. Security-definer wrapper when you genuinely need the cross-table lookup:
create function public.is_member(classroom_id uuid)
returns boolean
language sql security definer stable set search_path = public as $$
  select exists (
    select 1 from memberships m
    where m.classroom_id = is_member.classroom_id
      and m.user_id = auth.uid()
  );
$$;
Enter fullscreen mode Exit fullscreen mode

The security definer is what breaks the cycle — the function evaluates with the owner's rights, so it doesn't re-enter the policy.

And the step most fixes skip: after it stops erroring, verify it actually restricts. 42P17 disappearing can also mean the policy now quietly returns rows it shouldn't. Keep a red/green check next to the migration — user A reads user B's rows and gets zero rows, A reads their own and gets them — so CI proves the fix instead of assuming it.