How to fix infinite recursion in Supabase RLS

You create a multi-tenant database in Supabase, turn on Row-Level Security, and write a policy to protect your organization accounts. Everything looks straightforward in SQL editor. Then you run your frontend application and your API requests stall until Postgres throws error code 42P17:

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

This error halts every database query on that table. The browser shows a blank screen or a generic 500 error, and new signups fail immediately.

Here is why Postgres triggers this error, and how to fix your policies permanently without compromising security.

Why infinite recursion happens in RLS

Infinite recursion occurs when a table’s security policy queries that same table to check whether the current user has permission.

Consider this standard multi-tenant schema:

create table organization_members (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid references organizations(id),
  user_id uuid references auth.users(id),
  role text not null check (role in ('owner', 'admin', 'member'))
);

To prevent random users from reading membership rosters, you might write:

-- DANGEROUS: Causes infinite recursion
create policy "Admins can view team members"
on organization_members
for select
using (
  exists (
    select 1 from organization_members
    where user_id = auth.uid()
      and organization_id = organization_members.organization_id
      and role in ('owner', 'admin')
  )
);

When a user runs select * from organization_members, Postgres checks the policy. To evaluate the policy, Postgres must execute the subquery: select 1 from organization_members.

Evaluating that subquery requires checking the policy on organization_members again. Postgres enters an infinite loop, detects the recursion depth, and aborts the query.

The permanent fix: SECURITY DEFINER helper functions

The cleanest, most performant fix is to break the policy evaluation cycle using a helper function marked with security definer.

A function with security definer executes with the permissions of the database role that created it (usually postgres), bypassing RLS evaluation inside the function body:

create or replace function public.is_org_admin(org_id uuid)
returns boolean
language sql
security definer
set search_path = public
stable
as $$
  select exists (
    select 1
    from public.organization_members
    where organization_id = org_id
      and user_id = auth.uid()
      and role in ('owner', 'admin')
  );
$$;

Notice two critical settings:

1. set search_path = public: Prevents malicious search path overrides when running with elevated permissions.
2. stable: Tells the Postgres query planner that the function returns the same result for identical arguments within a single statement, enabling query optimization.

Now rewrite your table policy to call the helper function:

drop policy if exists "Admins can view team members" on organization_members;

create policy "Admins can view team members"
on organization_members
for select
using (
  user_id = auth.uid() -- Users can always view their own membership row
  or public.is_org_admin(organization_id)
);

When Postgres evaluates this policy, it executes is_org_admin(). Because the function runs as a security definer, Postgres evaluates the subquery without checking RLS on organization_members, completely eliminating the recursion.

Alternative: User claims in JWT app metadata

If your application checks tenant permissions on every request across dozens of tables, database function calls can become a latency bottleneck.

An alternative architectural approach is storing the active organization ID and role directly in the user’s JWT app_metadata.

You update the claims through a database trigger whenever membership changes:

create or replace function public.handle_org_membership_updated()
returns trigger
language plpgsql
security definer
as $$
begin
  -- Write custom claim to user's auth metadata
  update auth.users
  set raw_app_meta_data = raw_app_meta_data || 
    jsonb_build_object('org_role', new.role, 'org_id', new.organization_id)
  where id = new.user_id;
  return new;
end;
$$;

In your RLS policy, extract the claim directly from auth.jwt():

create policy "Admins can view team members via JWT"
on organization_members
for select
using (
  (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid = organization_id
  and (auth.jwt() -> 'app_metadata' ->> 'org_role') in ('owner', 'admin')
);

This evaluation reads memory directly from the authenticated token. It makes zero round-trip table queries, runs in microseconds, and cannot trigger infinite recursion.

Verification checklist before deployment

Before pushing RLS updates to production, run these checks:

1. Test with two distinct user accounts: an owner account and a non-member account. Verify that non-members receive empty arrays rather than 500 errors.
2. Verify that set search_path = public is declared on every security definer function to avoid privilege escalation bugs.
3. Check performance in the Supabase Dashboard Query Performance tab to ensure execution time stays under 10 milliseconds.

If your database policies continue throwing errors or if you need an architecture review before launch, request our production database diagnosis.