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.

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:
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: 42P17The fixed version replaces the self-reference with a function that runs with the owner's privileges:
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
| Approach | Breaks recursion | Security notes | Use when |
|---|---|---|---|
SECURITY DEFINER helper | Yes | Must pin search_path, revoke public execute | Membership, role, and ownership checks |
Denormalized column (owner_id on the row) | Yes | Simple, but must be kept in sync | One-to-one ownership |
View with security_invoker = false | Sometimes | Owner must bypass RLS; hides columns | Read-only projections |
auth.jwt() claims instead of a table | Yes | Stale until the token refreshes | Role baked into the token |
| RLS disabled on the membership table | Yes | Every row visible to anon | Never |
| Policy command | Needs a WITH CHECK | Recursion risk |
|---|---|---|
FOR SELECT | No | High if it reads its own table |
FOR INSERT | Yes (defaults to USING if omitted) | High in WITH CHECK |
FOR UPDATE | Yes | High, plus needs a SELECT policy to see the row |
FOR DELETE | No | High |
FOR ALL | Yes | Highest: applies to every command |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. Two tables that guard each other
-- 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
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
-- 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:
select proname, prosecdef, proconfig
from pg_proc where proname = 'is_member';
-- prosecdef must be true4. FORCE ROW LEVEL SECURITY and the table owner
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
-- 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
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
| Mistake | Symptom | Correct approach |
|---|---|---|
Policy on members selecting from members | 42P17 | Definer helper or denormalized column |
security invoker function in a policy | 42P17 | security definer plus pinned search_path |
Definer function without search_path | Hijackable, or fails after a schema change | set search_path = public |
Granting execute to public | Any role can call it | revoke all … from public then grant |
No SELECT policy next to UPDATE | returning returns nothing | Add both |
for all policy with a self-query | Recursion on every command | Split into per-command policies |
| Testing as the table owner | Everything works locally | set 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres too many clients: Pool Sizing and Connection Exhaustion
Each process caches its own pool and nothing coordinates them. Divide the ceiling by replicas, put a transaction-mode pooler in front, and stop idle-in-transaction sessions from holding slots.
SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.