What does error 42P17 mean in Supabase?

42P17 is PostgreSQL's code for invalid_object_definition. With the message "infinite recursion detected in policy for relation", a row level security policy needs itself to evaluate, usually because it reads its own table or two tables' policies read each other.

Why does the same query work in the SQL editor?

The dashboard runs queries as the postgres role, which owns your tables, and PostgreSQL lets table owners skip row security, so the policy that loops is never evaluated there. Your signed-in users do go through it.

Is a security definer function safe here?

Yes, when it lives in a schema your API does not expose, sets an empty search_path, schema-qualifies what it reads, returns only what the policy needs, and is executable by signed-in users only.

Supabase error 42P17

Infinite recursion detected in policy.

A policy on a table asked that same table a question, so answering it needed the policy again. The fix is to answer the question in a function that runs outside the policy, kept where your API cannot reach it.

the error

What you are looking at.

{
  "code": "42P17",
  "message": "infinite recursion detected in policy for relation \"members\""
}

What is error 42P17? 42P17 is the PostgreSQL error code invalid_object_definition. Supabase's own RLS guide names the cause: "Two tables whose policies read each other never resolve. Postgres raises 42P17, infinite recursion detected in policy for relation."

why it happens

Two shapes, one loop.

A policy that reads its own table. The classic case is team membership: to decide which rows of members you may see, the policy looks up your teams in members, which needs the policy, which looks up your teams in members.

create policy "members read their teams" on public.members
for select to authenticated
using ( team_id in (select m.team_id from public.members m where m.user_id = (select auth.uid())) );

Two tables that read each other. A policy on projects checks members, and a policy on members checks projects. Each one is reasonable on its own; together they never finish.

the fix, tested

Answer the lookup outside the policy.

Supabase's guide is direct about it: "Break the cycle with a security definer function." A security definer function runs with the rights of its owner. The owner of your tables is the role that created them, and PostgreSQL lets table owners skip row security ("Table owners normally bypass row security as well"), so the lookup inside the function never re-enters the policy. The whole fix:

create schema if not exists private;

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

Why

A schema of its own

Supabase: "Never create one in a schema listed under 'Exposed schemas' in your API settings." A security definer function in an exposed schema can be called through your API, with its owner's rights.

Why

An empty search path

Supabase: "Set search_path = '' on every security definer function and schema-qualify the names inside it." That is why the function says public.members and not members.

Why

Signed-in users only

Functions are executable by everyone by default. The revoke and the two grants limit this one to signed-in users.

Why

Wrapped in a select

Supabase notes that wrapping a function in a select lets the optimizer "'cache' the results per-statement, rather than calling the function on each row."

We ran it. On a real PostgreSQL set up the way Supabase sets it up, a signed-in user's query against members raised 42P17 before this fix. After it, that user saw exactly the rows of their own team, and a member of another team saw only theirs.

what not to do

Two shortcuts that open the table.

Do not switch row level security off to stop the error. Supabase gives its API roles access to tables in the public schema by default, so the table becomes readable and writable by anyone holding your public key.

Do not put the function in public to save a step. That is the exposed schema, and a function that skips row security has no business being callable through your API.

questions

Straight answers.

What does error 42P17 mean in Supabase?: 42P17 is PostgreSQL's code for invalid_object_definition. With the message "infinite recursion detected in policy for relation", a row level security policy needs itself to evaluate, usually because it reads its own table or two tables' policies read each other.
Why does the same query work in the SQL editor?: The dashboard runs queries as the postgres role, which owns your tables, and PostgreSQL lets table owners skip row security, so the policy that loops is never evaluated there. Your signed-in users do go through it.
Is a security definer function safe here?: Yes, when it lives in a schema your API does not expose, sets an empty search_path, schema-qualifies what it reads, returns only what the policy needs, and is executable by signed-in users only. The fix above does all five.

Recursion announces itself. The other holes do not.

Our free auditor does not look for recursion; PostgreSQL already tells you about that one. It finds the policy mistakes that fail silently: tables with row security off, write checks that let a user write as someone else, views that skip your policies. It runs in your own SQL editor and never touches your rows.

Get the free RLS auditor

Also seeing 42501, new row violates row-level security policy? Building tenants? The membership-table pattern keeps the membership policy a column comparison, so it never loops.