Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Postgres RLS 42P17: infinite recursion detected in policy

A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.

~5 min readSQL
#SQL#PostgreSQL#RLS#Supabase
3D illustration of policy rings looping back into a table cube with a break gate in the loop

Introduction & Quick Summary

Symptom. ERROR: 42P17: infinite recursion detected in policy for relation "members", surfaced by Supabase as a 500 with code: "42P17" and the hint Verify the row level security policies on the table. A plain select * from members fails for the authenticated role, while the same query as postgres (which bypasses RLS) returns rows. The policy looks correct: it checks membership by querying the membership table.

Cause. A policy on table members whose USING or WITH CHECK expression queries members. PostgreSQL applies RLS to that inner query too, which re-evaluates the same policy, which queries again. PostgreSQL detects the loop and stops with 42P17. The same thing happens with two tables that reference each other's policies (projects → members → projects), and with a policy that calls a SECURITY INVOKER function that reads the table it guards.

Fix. Break the cycle at one point. The standard answer is a SECURITY DEFINER helper function that reads the membership table as its owner (bypassing RLS), so the policy calls a function instead of re-entering the table. A second option is to keep the membership check out of the policy entirely by denormalizing the owner id onto the row, and a third is to let a view with security_invoker = false read the table and put the policy on the view's consumer instead.

Core Mechanics, Signatures & Reproducible Setup

Row level security adds a filter to every table reference, in policies as well as in user queries. When a policy's expression references the table it protects, PostgreSQL cannot evaluate the outer policy without evaluating the inner one, and after a bounded depth it raises 42P17 with the relation name. The error is deterministic: the same query always fails, and it fails for every role that does not bypass RLS (BYPASSRLS or table owner, unless FORCE ROW LEVEL SECURITY).

Reproduce it in a local Postgres:

sql
create table projects (id uuid primary key default gen_random_uuid(), owner uuid not null); create table members ( project_id uuid references projects(id), user_id uuid not null, role text not null default 'viewer', primary key (project_id, user_id) ); alter table members enable row level security; -- Broken: the policy asks members whether you are in members create policy members_read on members for select using ( exists (select 1 from members m where m.project_id = members.project_id and m.user_id = auth.uid()) ); set role authenticated; select * from members; -- ERROR: 42P17

The fixed version replaces the self-reference with a function that runs with the owner's privileges:

sql
create or replace function public.is_member(p_project uuid) returns boolean language sql stable security definer set search_path = public as $$ select exists ( select 1 from members m where m.project_id = p_project and m.user_id = (select auth.uid()) ); $$; revoke all on function public.is_member(uuid) from public; grant execute on function public.is_member(uuid) to authenticated; create policy members_read on members for select using (public.is_member(project_id));

security definer makes the function run as its owner, which owns members and therefore bypasses RLS inside it; the recursion ends there. set search_path = public closes the search-path hijack that a definer function otherwise exposes, and stable lets the planner evaluate it once per statement rather than per row.

Key Comparison Table

ApproachBreaks recursionSecurity notesUse when
SECURITY DEFINER helperYesMust pin search_path, revoke public executeMembership, role, and ownership checks
Denormalized column (owner_id on the row)YesSimple, but must be kept in syncOne-to-one ownership
View with security_invoker = falseSometimesOwner must bypass RLS; hides columnsRead-only projections
auth.jwt() claims instead of a tableYesStale until the token refreshesRole baked into the token
RLS disabled on the membership tableYesEvery row visible to anonNever
Policy commandNeeds a WITH CHECKRecursion risk
FOR SELECTNoHigh if it reads its own table
FOR INSERTYes (defaults to USING if omitted)High in WITH CHECK
FOR UPDATEYesHigh, plus needs a SELECT policy to see the row
FOR DELETENoHigh
FOR ALLYesHighest: applies to every command

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Two tables that guard each other

sql
-- projects policy reads members; members policy reads projects create policy projects_read on projects for select using (public.is_member(id)); create policy members_read on members for select using (exists (select 1 from projects p where p.id = project_id and p.owner = auth.uid()));

If is_member is not security definer, this pair loops through two tables and the error names whichever relation the planner entered second, which is misleading. Make exactly one side bypass RLS — here the function — and leave the other as a policy expression.

2. UPDATE needs a SELECT policy too

sql
create policy members_update on members for update using (public.is_admin(project_id)) with check (public.is_admin(project_id));

Without a SELECT policy, an update … returning * returns no rows even though the update succeeded. This is not recursion, but it is the second most common "my policy does not work" report on the same table. Add members_read and the returning clause behaves.

3. The definer function that still recurses

sql
-- Broken: security invoker (the default) re-enters RLS create function is_member(p uuid) returns boolean language sql stable as $$ select exists (...) $$;

A function without security definer executes as the calling role, so its query is filtered by RLS exactly like the policy's own subquery. Check the function's attributes before blaming the policy:

sql
select proname, prosecdef, proconfig from pg_proc where proname = 'is_member'; -- prosecdef must be true

4. FORCE ROW LEVEL SECURITY and the table owner

sql
alter table members force row level security;

With FORCE, even the owner is subject to policies — including a SECURITY DEFINER function owned by that same owner. The fix becomes a role that owns the table but is exempted (alter role app_owner bypassrls), or dropping FORCE for the membership table. If you add FORCE for defense in depth, test every definer function that reads that table.

5. Performance: policies run per row

sql
-- Slow: auth.uid() is a function call per row using (user_id = auth.uid()) -- Faster: one InitPlan per statement using (user_id = (select auth.uid()))

The subquery form is the documented Supabase optimisation: the planner hoists a scalar subquery into an InitPlan, so auth.uid() is evaluated once. Combine it with an index on (project_id, user_id) for the membership lookup, otherwise every policy check becomes a sequential scan of the membership table.

6. Testing policies without breaking the app

sql
begin; set local role authenticated; set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}'; select count(*) from members; rollback;

set local scopes the change to the transaction, so a test session cannot leave the database in a role it did not start in. On Supabase, auth.uid() reads request.jwt.claims; setting the claim by hand is how you reproduce a user's view without a real token.

Common Compilation/Runtime Mistakes & Version Gotchas

MistakeSymptomCorrect approach
Policy on members selecting from members42P17Definer helper or denormalized column
security invoker function in a policy42P17security definer plus pinned search_path
Definer function without search_pathHijackable, or fails after a schema changeset search_path = public
Granting execute to publicAny role can call itrevoke all … from public then grant
No SELECT policy next to UPDATEreturning returns nothingAdd both
for all policy with a self-queryRecursion on every commandSplit into per-command policies
Testing as the table ownerEverything works locallyset role authenticated in the test

Version notes: security_invoker on views exists from PostgreSQL 15, so a view-based workaround silently behaves as a definer view on 14 and earlier — check the server version before relying on it. Supabase's auth.uid() reads the JWT claims set by PostgREST; the request.jwt.claims GUC name is stable across current versions, but the older request.jwt.claim.sub single-value form is not set in newer releases, so tests written against it return null. Since PostgreSQL 15, an INSERT … ON CONFLICT DO UPDATE requires both an INSERT WITH CHECK and an UPDATE policy, and a missing one fails with a permission error rather than 42P17 — a different message for the same class of mistake.

For the surrounding investigation, the SQL formatter makes a multi-line policy readable before you diff two versions, the JSON formatter is useful when a PostgREST error body needs reading, and the UUID tool generates the fixed claim ids used in a policy test. Keep the 42P17 test as a rolled-back transaction in your migration checks: it is the only assertion that catches a self-referencing policy before it reaches production.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.