infinite recursion detected in policy for relation

What you see

ERROR:  42P17: infinite recursion detected in policy for relation "team_members"

Through the Data API it's an HTTP 500 with "code": "42P17". Every query on the table fails, not just some rows, for every role the policy applies to.

Why it happens

When Postgres reads a table with RLS on, it adds the table's policies to the query. If a policy on team_members itself selects from team_members, Postgres must apply the same policy to that inner query, which selects from team_members again, and so on. Postgres detects the loop and stops.

The same thing happens indirectly: a policy on channels reads team_members, and a policy on team_members reads channels.

A typical team chat policy that triggers it:

create policy "Members see their teammates"
on public.team_members for select
to authenticated
using (
  team_id in (
    select team_id from public.team_members   -- reads the same table
    where user_id = (select auth.uid())
  )
);

How to fix it

Do the lookup in a security definer function. It runs with the privileges of its owner (postgres, which owns the table), so RLS isn't applied to the query inside it and the loop is broken.

1. Create the function in a schema of its own:

   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.team_members
     where user_id = (select auth.uid());
   $$;
   

   set search_path = '' stops anyone from hijacking unqualified names, which matters for every security definer function.

2. Let only signed-in users call it:

   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;
   

3. Replace the policy:

   drop policy if exists "Members see their teammates" on public.team_members;
   
   create policy "Members see their teammates"
   on public.team_members for select
   to authenticated
   using (team_id in (select private.my_team_ids()));
   

4. Use the same function in policies on related tables (channels, messages), so none of them read team_members directly:

   create policy "Members read their team's messages"
   on public.messages for select
   to authenticated
   using (team_id in (select private.my_team_ids()));
   

5. Test as a real user from your app, since Studio and the SQL editor sign in as postgres, which owns the tables and skips RLS.

Make sure team_members (user_id) is indexed. The function runs on every query that touches these tables.

On SupaHost

• The Data API only exposes the public, storage, graphql_public and pgmq_public schemas, and that list is fixed. A function in private can be used by policies but can never be called through supabase.rpc(). That's what you want for helper functions like this one.
• MCP get_advisors flags functions in public without a fixed search path (function_search_path_mutable). Setting search_path = '' as above keeps it quiet.

Related

• security definer functions and RLS: what to expose
• RLS performance best practices
• Row Level Security
• Roles, custom claims and RBAC