Mahdi BEN RHOUMA

Posted on Sep 11 Originally published at iloveblogs.blog

Fix Supabase RLS Infinite Recursion (Production 2026)

#supabase #rls #rowlevelsecurity #postgres

This is one of the most-searched Supabase errors and one of the most-misunderstood. The error message is precise but the actual cause is subtle — Postgres isn't broken, your policy is calling itself.

Shortcut: paste the error into our free Supabase RLS Policy Debugger to get the SECURITY DEFINER fix pattern directly.

The problem

You add an RLS policy that lets users read rows they have access to. The first query against that table returns:

ERROR:  infinite recursion detected in policy for relation "projects"
LINE 1: SELECT * FROM projects WHERE ...

The query never executes. Worse, the error fires before any application logic runs, so your app sees a generic 500 with no actionable context.

Symptoms

You're hitting this if:

The error mentions a specific relation (for relation "X") and infinite recursion.
You recently added or modified an RLS policy on that table.
The policy's USING or WITH CHECK clause contains a SELECT ... FROM X against the same table.
The same query works when you connect as the postgres role (which bypasses RLS).

Root cause

Here's a textbook trigger — sharing rows in a projects table when the owner can also see members:

CREATE POLICY "members can read projects" ON projects
FOR SELECT
USING (
  auth.uid() = owner_id
  OR auth.uid() IN (
    SELECT user_id FROM projects WHERE id = projects.id  -- recursion!
  )
);

Postgres has to evaluate the policy for the inner SELECT FROM projects too. That inner select re-applies the same policy, which contains another inner select, which re-applies the policy, and so on. Postgres detects the loop and aborts.

Key insight: RLS runs on every read, including reads inside your own policy. A policy on table X that reads from X is always a recursion risk unless you bypass RLS explicitly.

Fix 1 — SECURITY DEFINER function (recommended)

Move the lookup into a function that runs with elevated privileges and skips RLS:

-- Returns true if the current user is a member of the given project.
-- SECURITY DEFINER means it runs as the function owner, bypassing RLS.
CREATE OR REPLACE FUNCTION public.is_project_member(project_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM project_members
    WHERE project_members.project_id = is_project_member.project_id
      AND project_members.user_id = auth.uid()
  );
$$;

REVOKE EXECUTE ON FUNCTION public.is_project_member(uuid) FROM public;
GRANT EXECUTE ON FUNCTION public.is_project_member(uuid) TO authenticated;

Then rewrite the policy to call the function:

DROP POLICY "members can read projects" ON projects;

CREATE POLICY "members can read projects" ON projects
FOR SELECT
USING (
  auth.uid() = owner_id
  OR public.is_project_member(id)
);

No recursion. The function reads from project_members (a different table), and even if it didn't, SECURITY DEFINER skips RLS evaluation.

Three rules when using SECURITY DEFINER:

Always set search_path — without it, a malicious user can hijack the function by creating a schema with the same name. The SET search_path = public line is non-negotiable.
Keep the WHERE clause narrow — only return what the caller is allowed to know.
Revoke from public, grant to authenticated — never let anonymous users call it unless that's the intent.

Fix 2 — Restructure the policy to avoid the protected table

If you can express access in terms of auth.uid(), a JWT claim, or a different table, you don't need SECURITY DEFINER at all.

-- Membership lives in a separate table → no recursion possible.
CREATE POLICY "members can read projects" ON projects
FOR SELECT
USING (
  auth.uid() = owner_id
  OR EXISTS (
    SELECT 1 FROM project_members
    WHERE project_members.project_id = projects.id
      AND project_members.user_id = auth.uid()
  )
);

This only works because project_members is a different relation. If project_members itself has an RLS policy that queries projects, you re-introduce the loop. Keep membership tables RLS-simple — usually just user_id = auth.uid().

Fix 3 — Use JWT claims for tenant scoping

If you store the user's tenant/org in the JWT, the policy can read it directly without touching any table:

CREATE POLICY "users see only their org's projects" ON projects
FOR SELECT
USING (
  org_id = (auth.jwt() ->> 'org_id')::uuid
);

Zero recursion risk — auth.jwt() is in-memory, not a query. Add the org_id claim via a Supabase Auth Hook on token issuance.

Verification

After applying the fix:

-- 1. Confirm the policy compiles
SELECT polname, polqual FROM pg_policy WHERE polrelid = 'projects'::regclass;

-- 2. Test as a real user (use Supabase SQL editor with "Run as authenticated user")
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "00000000-0000-0000-0000-000000000001"}';
SELECT count(*) FROM projects;
RESET ROLE;

The count should return without an error. Then test from the application client to confirm end-to-end.

Debug checklist

Read the error message — note the exact relation name. That's the recursion source.
Inspect every RLS policy on that table: SELECT * FROM pg_policies WHERE tablename = 'X';
Look in each policy's qual and with_check for a SELECT ... FROM X against the same table.
Decide which fix applies:
- Same-table self-reference required → Fix 1 (SECURITY DEFINER)
- Access derived from another table → Fix 2 (restructure)
- Access derived from JWT/claims → Fix 3 (claims)
Apply the fix in a migration, not the SQL editor — RLS changes must be versioned.
Run the verification queries above before redeploying the app.

Prevention

Never reference the protected table inside its own policy. Treat this as a hard rule. If you need to, that's a sign the access model belongs in a separate _members or _acl table.
Test RLS policies in CI with pgTAP or a Supabase-local test harness — SELECT count(*) as different roles, and assert expected rows.
Document every SECURITY DEFINER function in a comment block explaining why RLS is being bypassed and what the safety boundary is.
Keep policies pure — auth.uid(), auth.jwt(), equality checks, and joins to membership tables. If a policy is more than 4 lines, it's probably hiding bugs.
Watch the pg_policies system view in code review. Any new policy that mentions its own table name is a recursion candidate.

The recursion error feels like a bug in Supabase the first time you see it. It's not — Postgres is protecting you from an infinite loop you wrote. Fix the policy shape and it goes away.

DEV Community