Postgres error 42P17: invalid object definition
infinite recursion detected in policy for relation "x"
A row level security policy on a table queries the same table (directly or through another policy), so checking the policy triggers the policy again.
Common causes
- A policy on members that checks membership with select ... from members.
- Two tables whose policies each query the other.
How to fix it
- Move the lookup into a SECURITY DEFINER function owned by a role that bypasses RLS, with a fixed search_path, and call that function from the policy.
- Or store what the policy needs (such as the team id) in the user's JWT claims and read it with auth.jwt().
create function private.is_team_member(team uuid) returns boolean
language sql security definer set search_path = '' stable as $$
select exists (select 1 from public.members m where m.team_id = team and m.user_id = auth.uid());
$$;
create policy "team reads" on members for select to authenticated
using (private.is_team_member(team_id));Through a REST API
PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 500 for this error and returns the SQLSTATE in the code field of the JSON error body. PostgREST answers 500 for this error, so it looks like a server failure in the client.
Questions
What does Postgres error 42P17 mean?
42P17 is invalid_object_definition in class 42 (Syntax Error or Access Rule Violation). A row level security policy on a table queries the same table (directly or through another policy), so checking the policy triggers the policy again.
How do I fix 42P17?
Move the lookup into a SECURITY DEFINER function owned by a role that bypasses RLS, with a fixed search_path, and call that function from the policy. Or store what the policy needs (such as the team id) in the user's JWT claims and read it with auth.jwt().
What HTTP status does a REST API return for 42P17?
PostgREST, the REST layer behind supabase-js, answers 500.