Fix 'infinite recursion detected in policy for relation' in Supabase

Your app worked yesterday. Then you asked Cursor, Lovable, or Bolt to add an admin page or team sharing, and now every request to that table fails with infinite recursion detected in policy for relation "profiles" (or "team_members", or "teams"). The SQL editor shows the rows fine. The app shows nothing.

The error means your Row Level Security policies are chasing their own tail. The safe fix is small. The danger is the "fix" an AI tool often suggests first: turn RLS off, swap in using (true), or use the service role key. Each of those makes the error disappear by handing your data to anyone. This guide shows why the loop happens, the fix Supabase documents, two alternatives, and how to prove nobody can read another user's or another team's rows. For the rest of your policies, keep the Supabase RLS checklist open next to this page.

Checked on October 19, 2026 against the Supabase RLS, functions, and advisor docs, the lint SQL in Supabase's open source splinter repo, the PostgREST error reference, and the PostgreSQL docs linked below. We reproduced every broken policy and verified every fix on PostgreSQL 17.11 with Supabase-style anon and authenticated roles and the same auth.uid() and auth.jwt() helpers Supabase uses. VibeLocked is independent and not affiliated with Supabase.

The short version

1. The error is Postgres SQLSTATE 42P17. A policy queries a table whose policies lead back to it, so Postgres refuses to run the query at all.
2. The safe fix is a security definer helper in a private schema. It reads the membership or roles table as its owner, so the inner policy never runs. Pin search_path = '', mark it stable, revoke EXECUTE from public and anon, and call it as (select private.fn()).
3. Never put that helper in public. Anything in an exposed schema is callable at /rest/v1/rpc/<name>.
4. Alternatives: a role claim in app_metadata or a custom access token hook (never user_metadata), or a membership policy that only checks user_id = (select auth.uid()).
5. Do not disable RLS, use using (true), or put the service role key in the client. Each one trades an error for a data leak.
6. Test as anon and as two users from different teams before you ship.

What the error means

Here is the exact message we got on Postgres 17.11:

ERROR:  42P17: infinite recursion detected in policy for relation "profiles"
LOCATION:  fireRIRrules, rewriteHandler.c:2262

The PostgreSQL error codes appendix lists 42P17 as invalid_object_definition. In the browser, supabase-js gives you an error object with code: '42P17' and that message. The current PostgREST error reference maps 42P17 to HTTP 500 and labels it "infinite recursion"; older forum posts show a 400.

Why it happens comes down to one sentence in the PostgreSQL row security docs: "Policy expressions are run as part of the query and with the privileges of the user running the query, although security-definer functions can be used to access data not available to the calling user." So when a policy on profiles contains select ... from profiles, that inner select is also a query on profiles by the same user, and it gets the same policies added to it, which contain the same select, and so on. Postgres notices the loop while it rewrites the query and stops before it reads a single row.

Four things we confirmed in testing that explain the confusing symptoms:

• It fails even on an empty table, and even for explain. The check happens while the query is rewritten, not while rows are read.
• The named table is not always the one you queried. Selecting from posts, whose admin policy reads profiles, failed with relation "profiles". Selecting from projects failed with relation "team_members". Look at the policies on the table in the message.
• It works in the SQL editor. The editor runs as postgres, and the PostgreSQL docs say superusers, roles with BYPASSRLS, and normally table owners skip row security. Our postgres session read all three profiles with no error.
• It only breaks the roles the policy applies to. Supabase's RLS guide says the query "fails for every role the policies apply to." With policies written to authenticated, our anon test returned 0 rows and no error, while every signed-in request failed.

The three shapes AI tools generate

These are the patterns we see in AI-written migrations. All three failed exactly like this in our test database.

1. Admin check inside profiles. The app stores role on profiles, and the admin policy checks it from a profiles policy:

create policy "Admins read all profiles"
on public.profiles for select
to authenticated
using (
  exists (
    select 1 from public.profiles p
    where p.id = (select auth.uid()) and p.role = 'admin'
  )
);
-- ERROR: infinite recursion detected in policy for relation "profiles"

This is the same shape as a Stack Overflow question and a Supabase agent skills feedback issue from someone whose AI build hit it. Any other table whose admin policy reads profiles (orders, posts, invoices) breaks too, with the error naming profiles.

2. A membership table that reads itself. "Members can see the other members of their team":

create policy "Members see their team's members"
on public.team_members for select
to authenticated
using (
  team_id in (
    select tm.team_id from public.team_members tm
    where tm.user_id = (select auth.uid())
  )
);
-- ERROR: infinite recursion detected in policy for relation "team_members"

3. Two tables that read each other. The multi-tenant version: teams (or orgs, projects, lists) checks team_members to see who belongs, and team_members checks teams to see who owns it. Supabase's RLS guide uses lists and list_members as its example of this cycle, and a merged Tamagui pull request fixed the identical loop between projects and project_team_members in production.

The fix Supabase documents: a private security definer helper

Supabase's own troubleshooting article gives two options: change the policies so they do not form a cycle, or wrap the permission check in a SECURITY DEFINER function. The RLS guide's security definer section shows the second one. The function reads the table as its owner, so that table's policies are not applied inside it, and the loop never starts.

For the admin case:

create schema if not exists private;

begin;

create function private.is_admin()
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.profiles
    where id = (select auth.uid()) and role = 'admin'
  );
$$;

revoke execute on function private.is_admin() from public, anon;
grant usage on schema private to authenticated;
grant execute on function private.is_admin() to authenticated;

commit;

drop policy "Admins read all profiles" on public.profiles;

create policy "Admins read all profiles"
on public.profiles for select
to authenticated
using ( (select private.is_admin()) );

For teams, return the caller's team ids and use them in every policy that needs membership:

begin;

create function private.user_team_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select team_id from public.team_members
  where user_id = (select auth.uid());
$$;

revoke execute on function private.user_team_ids() from public, anon;
grant execute on function private.user_team_ids() to authenticated;

commit;

create policy "Members see their team's members"
on public.team_members for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

create policy "Members see their teams"
on public.teams for select
to authenticated
using (
  owner_id = (select auth.uid())
  or id in (select private.user_team_ids())
);

create policy "Members see team projects"
on public.projects for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

After this change, in our test user A saw only Team A, its project, and its two members. User B saw only Team B. anon saw 0 rows in all three tables, and calling the helper as anon failed with permission denied for schema private. No query raised 42P17.

Why each part is there:

• security definer. The function runs as its owner. On Supabase that is postgres, which has bypassrls, so the membership table's policies are skipped inside it. Supabase's guide adds a warning worth knowing: a function owned by a role without bypassrls, or one reading a table set to force row level security, evaluates the membership policy again. We also tried a plain invoker function (no security definer) as the helper, and the loop simply moved: the query failed with 54001: stack depth limit exceeded instead.
• (select auth.uid()) inside the body, and no user id parameter. Supabase's database functions guide says an execute grant "doesn't limit the rows a permitted role gets back," so the function itself must only return the caller's data. A helper like get_user_team_ids(uid uuid) lets any caller ask about any user.
• set search_path = '' and schema-qualified names. Without a pinned path, a caller can point an unqualified name at their own object and have it run with the owner's privileges. With the empty path, every table needs its schema (public.team_members). The PostgreSQL CREATE FUNCTION docs cover this under "Writing SECURITY DEFINER Functions Safely," and Supabase lint 0011_function_search_path_mutable flags functions that skip it.
• stable. The PostgreSQL volatility docs say a stable function "is guaranteed to return the same results given the same arguments for all rows within a single statement," which lets the planner call it once.
• (select ...) around the call. Supabase's RLS guide says wrapping a function in select "causes an initPlan to be run by the Postgres optimizer," so the result is computed once per statement instead of once per row. Our explain showed an InitPlan for is_admin() and a single hashed subplan for user_team_ids(). The RLS performance guide notes that if the returned list passes about 1,000 items, you may need a different approach.
• revoke ... from public, anon in the same transaction. Postgres grants EXECUTE on new functions to PUBLIC, so there should be no moment when everyone can call it.
• The owner_id check on teams. Without it, insert ... returning (what supabase-js .insert().select() sends) failed with new row violates row-level security policy when a user created a team, because the new membership row was not yet visible to the helper in that statement. Adding the owner check fixed it.

Why the helper must not live in public

Supabase's RLS guide is blunt: "A security definer function in an exposed schema is callable over the Data API with the creator's privileges." PostgREST serves every function in an exposed schema at /rest/v1/rpc/<name>. The splinter docs for lint 0028 explain why that is the default state: Postgres grants EXECUTE to PUBLIC, and Supabase also grants new functions to anon, authenticated, and service_role.

We checked it. A public.is_admin_public() function was executable by both anon and authenticated the moment it was created. Worse, an AI-style public.get_user_team_ids(uid uuid) returned user B's team id to a signed-out anon caller. Running Supabase's splinter lint SQL against the test database flagged both under 0028_anon_security_definer_function_executable and 0029_authenticated_security_definer_function_executable, and did not flag the private helpers, because those lints only look at schemas the API exposes. The Security Advisor guide covers both lints, and views and functions that bypass RLS covers auditing every definer function you already have.

Protect the role column itself

The is_admin() helper trusts profiles.role. If the same table has a "users update their own profile" policy, a user can set role = 'admin' on their own row and the helper will believe them. Move roles to a separate table users cannot write, as the client-side auth guide shows with a private.admins table, or restrict which columns the update grant covers.

Alternative 1: put the role in the JWT

Policies can read the signed token with auth.jwt() instead of querying a table. No table read means no recursion:

create policy "Admins read all profiles"
on public.profiles for select
to authenticated
using ( ((select auth.jwt()) -> 'app_metadata' ->> 'role') = 'admin' );

Use app_metadata. Supabase's RLS guide says raw_app_meta_data "cannot be updated by the user, so it's a good place to store authorization data," while raw_user_meta_data "can be updated by the authenticated user." Set it from server code with auth.admin.updateUserById, which Supabase says "should only be called on a server." For roles kept in your own table, the custom access token hook adds a claim when each token is issued; Supabase's RBAC guide walks through a user_role claim.

Never read user_metadata in a policy. The lint 0015 docs show the attack: a user calls updateUser({ data: { is_admin: true } }) and passes the check. Splinter reports it as 0015_rls_references_user_metadata at ERROR level, and it flagged our test policy. In our test, a user whose token had role: admin only in user_metadata still saw just their own row under the app_metadata policy.

The trade-off: a claim lives in the token. Supabase's sessions docs say access tokens are "usually between 5 minutes and 1 hour," so a removed admin keeps the claim until the token refreshes. The helper function sees role changes on the next query.

Alternative 2: break the cycle in the policies

Supabase's troubleshooting article's first option is to restructure. Let the membership table check only the caller's own rows, then let other tables read it:

create policy "Users see their own memberships"
on public.team_members for select
to authenticated
using ( user_id = (select auth.uid()) );

create policy "Members see team projects"
on public.projects for select
to authenticated
using (
  team_id in (
    select tm.team_id from public.team_members tm
    where tm.user_id = (select auth.uid())
  )
);

The projects policy still reads team_members, but the team_members policy no longer reads anything, so there is no loop. In our test this worked with no definer function at all: user A saw only Team A's project, user B only Team B's. The cost is that members can no longer list their teammates. If the app needs a member list, use the helper.

One more hole: who can join a team

While you are in these policies, read the insert policy on the membership table. AI tools often write with check (user_id = (select auth.uid())), which sounds safe. In our test it let user B insert a row adding themselves to Team A, and the next query returned Team A's secret project. Only an owner should add members:

create function private.owned_team_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select team_id from public.team_members
  where user_id = (select auth.uid()) and role = 'owner';
$$;

revoke execute on function private.owned_team_ids() from public, anon;
grant execute on function private.owned_team_ids() to authenticated;

create policy "Owners add members"
on public.team_members for insert
to authenticated
with check ( team_id in (select private.owned_team_ids()) );

With that, user B's self-join failed with new row violates row-level security policy, and the owner could still add members. Create the first owner row for a new team in a trigger on teams (a security definer function in private, with EXECUTE revoked from API roles) or in server code, since the creator is not an owner yet.

The wrong fixes AI tools suggest

When you paste the error into a chat, these are the replies that make it go away. Do not accept any of them.

• "Disable RLS on the table." The error stops because no policy runs. Supabase's lint 0013 docs say that without RLS, "anyone with the project's URL can CREATE/READ/UPDATE/DELETE (CRUD) rows." In our test, anon read every membership row and both teams' projects. Your project URL and publishable key are in your frontend bundle, so "anyone" is literal.
• "Change the policy to using (true)." No subquery, no loop, and no protection. With using (true) on team_members, user B read all three membership rows, a map of every customer's team. Put it on projects and every signed-in user reads every project. Lint 0029's own notes say that with open signup, authenticated is in practice "anyone with a throwaway email."
• "Use the service role key." It works because the service role bypasses RLS completely. In a browser or mobile build, anyone can extract it and read or delete everything. See anon key vs service role key.
• "Create a function in public" or "grant select to anon." A definer function in public with a user id argument is an open endpoint, as shown above. A broad grant without a policy is the same as RLS off for that role.

If your tool already did one of these, revert it in a new migration, then apply the real fix.

How to test the fix

The SQL editor runs as postgres, so a plain query there proves nothing. Impersonate the API roles inside a transaction, the way Supabase's testing overview does with set local role authenticated and set local "request.jwt.claims". Run this in your own project's SQL editor or a local copy, with real user ids from two different teams:

-- 1. Signed out: expect 0 rows everywhere, and no 42P17
begin;
set local role anon;
select count(*) from public.profiles;
select count(*) from public.team_members;
select count(*) from public.projects;
rollback;

-- 2. User A: expect only user A's profile and team A's data
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<user-a-uuid>","role":"authenticated"}';
select id, role from public.profiles;
select distinct team_id from public.projects;
select count(*) from public.team_members;
rollback;

-- 3. User B: expect only team B's data, never team A's
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<user-b-uuid>","role":"authenticated"}';
select distinct team_id from public.projects;
rollback;

-- 4. The helper is not callable by signed-out visitors
select pg_catalog.has_function_privilege('anon', 'private.user_team_ids()', 'execute');

What passes: no query raises 42P17 or 54001, anon sees nothing, user A and user B never see each other's teams, an admin (if you have one) sees everything the admin policy allows, and the last query returns false. The set local lines end with the transaction, so nothing leaks into your session. For an app_metadata policy, add the claim to the JSON, for example "app_metadata":{"role":"admin"}.

Then make it permanent. Testing Supabase RLS in CI turns these checks into pgTAP tests that run on every pull request, including a member, a non-member, and a guardrail that fails if any definer function in public is callable by anon. Re-run the Security Advisor after the migration and confirm no 0011, 0015, 0028, or 0029 findings point at your new functions.

Checklist

• No policy on a table queries that same table directly
• No two tables' policies query each other
• Membership and role lookups go through a security definer helper in a non-exposed schema
• Every helper sets search_path = '', is stable, uses schema-qualified names, and filters by (select auth.uid()) in its body
• EXECUTE revoked from public and anon, granted to authenticated only
• Policies call helpers as (select private.fn()) or in (select private.fn())
• Users cannot change their own role column; role claims come from app_metadata or a hook, never user_metadata
• Membership inserts are limited to team owners
• RLS is on for every table, with no using (true) added to silence the error
• No service role or secret key in frontend or mobile code
• Tested as anon, user A, and user B, with no 42P17 and no cross-team reads

Then work through the ship checklist for a vibe-coded app before you share the link.

Copy-paste prompt for your AI coding tool

Paste this into Cursor, Claude Code, Lovable, or Bolt. Paste the error message and table names only, never keys, passwords, or production data.

My Supabase app fails with "infinite recursion detected in policy for relation [table name]". Read every SQL migration and list each RLS policy that queries its own table or a table whose policies query back. Then write one new migration that fixes it without editing old migrations. Rules: do not disable RLS, do not use using (true), do not use the service role or secret key anywhere in client code, and do not create any function in the public schema. Create the schema private if missing, and add SECURITY DEFINER helper functions there with language sql, stable, set search_path = '', schema-qualified table names, no user id parameters, and filtering by (select auth.uid()) inside the body. In the same transaction, revoke execute on each helper from public and anon, grant usage on schema private and execute on the helper to authenticated. Rewrite the policies to call the helpers as (select private.fn()) or column in (select private.fn()), with to authenticated. Never read user_metadata in a policy. Make sure users cannot update their own role column and only team owners can insert team members. Finally, give me SQL editor tests using begin, set local role, set local request.jwt.claims, and rollback for anon and two users on different teams. Show me the migration before running anything.

Read the migration before it runs, test it on a branch or local database, and run the tests above. More review prompts are in the security review prompt guide.

Sources

• Supabase: Row Level Security (avoid recursive policies)
• Supabase: Row Level Security (use security definer functions, wrap functions in select, app_metadata vs user_metadata)
• Supabase troubleshooting: RLS policy causes infinite recursion
• Supabase: Row Level Security performance (security definer on the join table, large lists)
• Supabase: Database functions (security definer, ownership checks, function privileges)
• Supabase: Securing your API (exposed schemas, grants)
• Supabase: Database advisors
• Supabase: lint 0013_rls_disabled_in_public
• splinter docs: 0028_anon_security_definer_function_executable
• splinter lint SQL: 0029_authenticated_security_definer_function_executable (exposed schemas only)
• splinter docs: 0011_function_search_path_mutable
• splinter docs: 0015_rls_references_user_metadata
• Supabase: Custom Access Token Hook
• Supabase: Custom claims and role-based access control (RBAC)
• Supabase JavaScript reference: auth.admin.updateUserById (app_metadata, server only)
• Supabase: User sessions (access token lifetime)
• Supabase: Testing overview (set local role, request.jwt.claims)
• PostgREST: Error reference (42P17 maps to HTTP 500)
• PostgreSQL: Row Security Policies (policies run with the caller's privileges, BYPASSRLS)
• PostgreSQL: CREATE POLICY
• PostgreSQL: CREATE FUNCTION (Writing SECURITY DEFINER Functions Safely)
• PostgreSQL: Function volatility categories (STABLE)
• PostgreSQL: Error codes appendix (42P17, 54001)
• PostgreSQL: SET ROLE
• Stack Overflow: RLS rule leads to infinite recursion detected in policy for relation "profiles"
• supabase/agent-skills issue 63: missing guidance on RLS recursion
• tamagui/tamagui pull request 4059: fix projects and project_team_members RLS infinite recursion