DEV Community

R (RYUSLOG DEV)
R (RYUSLOG DEV)

Posted on Originally published at ryuslog.com

Postgres RLS 42P17: infinite recursion in a policy, and the fix that keeps the policy

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"
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

set local scopes the change to the transaction. Testing as postgres hides the bug, because the owner skips RLS.

Top comments (1)

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

Small correction on stable: it doesn't make is_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 row members table I counted 5,003 calls and 2.6s for one count(*).

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.