infinite recursion detected in policy for relation (42P17)

The fix

Stop the policy from querying its own table (or a chain of tables that loops back). Move the lookup into a SECURITY DEFINER function in a private schema, pin search_path, then call that function from the policy. Keep RLS on.

create schema if not exists private;

-- Look up the caller's team ids without re-entering the policy
create or replace function private.my_team_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
  select team_id
  from public.members
  where user_id = (select auth.uid())
$$;

revoke all on function private.my_team_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.my_team_ids() to authenticated;

drop policy if exists "members read their teams" on public.members;
create policy "members read their teams"
on public.members
for select
to authenticated
using ( team_id in (select private.my_team_ids()) );

That shape follows Supabase's security-definer guidance: run as the creator (which bypasses RLS on the lookup), pin search_path = '', schema-qualify names, and keep the function out of an exposed schema (Supabase: RLS).

Tested October 19, 2026 on a throwaway local Postgres 17 (not a full Supabase stack). A self-referencing SELECT policy on members raised ERROR: infinite recursion detected in policy for relation "members". After the private helper above (with a stand-in JWT claim for auth.uid()), a user saw only their team's rows; a second user saw both teams they belonged to.

Why it happens

Supabase's troubleshooting page says this error is "infinite recursion in your Row-Level Security (RLS) policies." It happens when a policy queries a table that has RLS, and that table's policies end up triggering the original policy again: either the same table, or another table that loops back (Supabase troubleshooting: RLS policy causes infinite recursion). Postgres raises SQLSTATE 42P17 and aborts the query (PostgreSQL error codes).

A common pattern is "show every member of any team I belong to" written as a subquery on the same members table. Evaluating the policy runs the subquery, which re-enters the policy.

What not to do

• Don't disable RLS to clear the error.
• Don't put a secret or service_role key in the client to "bypass" it (Supabase: API keys).
• Don't leave a SECURITY DEFINER function in an exposed schema, and don't skip set search_path = ''. Supabase warns both are risky (Supabase: RLS).

AI prompt

My app hits: infinite recursion detected in policy for relation (42P17).
Find any RLS policy that queries its own table or forms a policy loop.
Propose a private SECURITY DEFINER helper with set search_path = '', revoke public execute, and a rewritten policy that calls it.
Do not disable RLS. Do not use a secret or service_role key in client code.
Show the SQL first and wait for my OK before applying anything.

How to verify (two accounts)

1. Sign in as user A. Confirm you can read the rows you should (for example your team's members).
2. Sign in as user B on another team. Confirm you see B's teams, not A's private rows.
3. Sign out. Confirm signed-out requests get no private rows.

Related

Our Supabase RLS guide · new row violates RLS · empty array with RLS on

Your turn

Was the loop on the same table, or across two tables? Tell us. We'll add the patterns we see most.