A shortened version of the full write-up: Postgres RLS 42P17: infinite recursion detected in policy.
ERROR: 42P17: infinite recursion detected in policy for relation "members" — usually wrapped by Supabase as a 500 with code: "42P17". The policy looks correct: it checks membership by querying the membership table. That is exactly the problem.
| Mistake | Symptom | Fix |
|---|---|---|
Policy on members reads members
|
42P17 on every select |
SECURITY DEFINER helper function |
UPDATE policy with no SELECT policy |
update ... returning * returns 0 rows |
add the SELECT policy |
Function without security definer
|
still recurses | check prosecdef in pg_proc
|
security definer without search_path
|
hijackable | set search_path = public |
Why it loops
RLS adds a filter to every reference to a table — including references inside a policy. PostgreSQL cannot evaluate the outer policy without evaluating the inner one, so after a bounded depth it raises 42P17 with the relation name. It fails for every role that does not bypass RLS, and works for postgres — which is why the bug "only happens in production".
-- 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())
);
-- ERROR: 42P17: infinite recursion detected in policy for relation "members"
The fix: one definer function
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 runs the function as its owner, which owns members and therefore bypasses RLS inside it — the recursion ends there. set search_path = public closes the hijack a definer function otherwise exposes, and stable lets the planner evaluate it once per statement instead of per row.
If it still recurses, check the function itself before rewriting the policy:
select proname, prosecdef, proconfig from pg_proc where proname = 'is_member';
-- prosecdef must be true
Two mistakes that look like policy bugs
update ... returning * returns zero rows when the table has an UPDATE policy but no SELECT policy — the update succeeds and you see nothing. And alter table ... force row level security makes even the owner subject to policies, so a SECURITY DEFINER function owned by that same owner starts recursing again.
If the error is about connections instead
Same table, different afternoon: remaining connection slots are reserved or prepared statement "s0" already exists means the pooler, not RLS. A direct :5432 connection from a serverless function opens one connection per instance; use the transaction-mode pooler on :6543 with max: 1 and prepare: false, and keep migrations on the direct host.
Test policies as the real role
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<user-id>","role":"authenticated"}';
select count(*) from members;
rollback;
set local scopes the change to the transaction. Testing as postgres hides the bug, because the owner skips RLS.
Top comments (1)
Small correction on
stable: it doesn't makeis_member(project_id)run once per statement. The argument is a column, so Postgres calls it for every row it checks, and definer functions never get inlined. On a 5,000 rowmemberstable I counted 5,003 calls and 2.6s for onecount(*).Having the helper return the user's project ids and writing
using (project_id in (select public.my_projects()))turns it into an initplan: 1 call, 3ms, same 6 rows.