# infinite recursion detected in policy for relation

> Fix RLS error 42P17 when a policy queries its own table or two tables' policies query each other. Move the lookup into a security definer function in a schema the API doesn't expose.

Source: https://supahost.app/kb/infinite-recursion-policy · Updated 2026-11-11

## 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:

```sql
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:

   ```sql
   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:

   ```sql
   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:

   ```sql
   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:

   ```sql
   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](/kb/security-definer-functions)
- [RLS performance best practices](/kb/rls-performance)
- [Row Level Security](/docs/database/row-level-security)
- [Roles, custom claims and RBAC](/docs/auth/custom-claims-rbac)