Fix: infinite recursion detected in policy

Postgres 15 · Supabase✓ code tested against a real database

TL;DR

Your policy on a table queries that same table, so checking the policy triggers the policy again. Move the lookup into a security definer function in a private schema and call it from the policy. The function reads the table without RLS, so the loop is gone.

01 Why you're seeing this

Postgres raises infinite recursion detected in policy for relation "team_members" (SQLSTATE 42P17, HTTP 500 from the Data API) when evaluating a policy requires reading the same table the policy protects. Reading that table applies the policy again, which reads the table again, and so on.

It almost always comes from a membership check like this one:

the broken policy

create policy "members see their team" on team_members for select to authenticated
  using (team_id in (
    select team_id from team_members   -- reads the table it protects
    where user_id = (select auth.uid())
  ));

The same thing happens with an "is admin?" check that looks up profiles from a policy on profiles, or with two tables whose policies query each other.

02 The fix: a security definer helper

Put the lookup in a function that runs as its owner (security definer). The owner isn't subject to the table's RLS, so the inner read doesn't re-enter the policy.

migration.sql

create schema if not exists private;

create function private.my_team_ids()
returns setof int
language sql security definer set search_path = '' stable as $$
  select team_id from public.team_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 "members see their team" on team_members;
create policy "members see their team" on team_members for select to authenticated
  using (team_id in (select private.my_team_ids()));

03 The details that keep it safe

Private schema. Keep the function out of public (or any schema exposed by the Data API), so nobody can call it as an RPC endpoint.
set search_path = '' and schema-qualified names (public.team_members). A security definer function with a loose search path can be tricked into reading a look-alike table.
Filter by auth.uid() inside the function. It bypasses RLS, so it must only ever return the caller's own rows.
Revoke from public, grant to authenticated only. anon then gets permission denied calling it.

04 Prove it

✗ broken policy, user A selects → 42P17 infinite recursion

✓ helper policy, user A (team 1) → only team 1's rows

✓ helper policy, user B (teams 1 and 2) → rows of both teams

✓ anon calls private.my_team_ids() → permission denied

Run these as real roles against a database with your migrations applied, so a policy that loops or leaks fails the build instead of production.

◆ FREE · MIT · npm + GitHub Action

Catch this before it ships

airlock-rls is a CI gate that fails your build when a table ships exposed or a policy is permissive: the same class of bug, caught in CI against a database with your migrations applied, before it reaches prod.

npx airlock-rls→

Or start from nextjs-supabase-starter : auth + a table with RLS + an isolation test, so you start from a pattern that's already sealed.

FREE PDF

Grab the Supabase RLS cheat sheet

The golden rules, the footguns that leak in prod, correct policy snippets, and the isolation test — on one page.

FAQ

Can I just turn off RLS on the membership table?

That stops the error and makes every membership row public to anyone with your anon key, including who belongs to which team. Keep RLS on and use the helper function.

Does a security definer function make my data less safe?

Only if it returns more than the caller should see. Filter by auth.uid() inside it, keep it in a private schema, set search_path to empty, and grant execute only to the roles that need it.

Why does it work in the SQL editor?

The SQL editor runs as a role that bypasses RLS, so the policy never runs there. Test as authenticated, the way your app calls the API.