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

#supabase #postgres #security #webdev

infinite recursion detected in policy for relation
code: 42P17

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

Note the relation named in the error: that is the table whose policy loops.
Read every policy on that table and look for a subquery that selects from the same table.
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) );

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.

DEV Community