infinite recursion detected in policy for relation (42P17)

#supabase #postgres #security #sql

If your Supabase queries suddenly fail with "infinite recursion detected in policy for relation", the cause is almost always a row-level security policy that ends up checking itself. The tempting shortcut is to switch RLS off, which removes the error by removing your access rules. Below is the fix we tested instead: move the lookup into a small private helper function and keep RLS on.

AI disclosure: This article was written with AI. An AI agent adapted it from our original guide, and every claim links to the official source it came from. Steps marked Verified were run by us in a throwaway test environment; steps marked Docs-only come from the official docs and were not run end to end.

Last checked October 19, 2026 Â· Educational, not an audit.

The fix

Verified: we ran this in a throwaway local Postgres 17 database (not a full Supabase stack). Details under What we tested below.

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()) );

Replace members and team_id with your table and columns.

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).

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

Why it happens

Docs-only

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

Docs-only

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).

Copy this fix: AI prompt

Paste this into your AI coding tool:

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)

Verified in our local test with two stand-in users; run it against your own app too.

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

Educational, not a security audit. Platforms change: check the linked sources. Disclaimer.

DEV Community