Fixing recursive policy errors with a definer helper

Recognising the recursion class

The pattern usually appears after membership is extracted into its own table. A policy on documents asks whether the caller belongs to the owning organisation, so it selects from org_members. Someone then adds a sensible-sounding policy to org_members limiting members to organisations they belong to, which necessarily selects back across into documents. Each policy's evaluation triggers the other, forever, and the planner halts the loop with 42P17.

Every error in this class shares the signature: two or more tables whose row security policies reference one another, directly or through views. The individual policies are often individually reasonable; the harm comes from the cycle they form together.

The error surfaces at query time, not at policy creation time, which is why it can appear days after the change that caused it: the first anonymous visitor whose request walks the cycle is the one who meets it, and the scan reproduces that path on every run until the cycle is broken.

The helper-function pattern

The standard remedy moves the membership check into a small function declared security definer, so it executes with the table owner's rights and bypasses row security on the membership table it reads. The policy on documents then calls the helper instead of selecting from org_members directly, breaking the cycle:

Because the helper reads org_members under owner rights, no policy on org_members fires during evaluation, and the recursion disappears. The policy on org_members remains free to reference documents if needed, since the cycle is gone rather than merely lengthened.

CREATE OR REPLACE FUNCTION public.is_org_member(target_org uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.org_members m
    WHERE m.org_id = is_org_member.target_org
      AND m.user_id = auth.uid()
  );
$$;

REVOKE EXECUTE ON FUNCTION public.is_org_member(uuid) FROM public;
GRANT EXECUTE ON FUNCTION public.is_org_member(uuid) TO anon, authenticated;

CREATE POLICY "members read organisation documents"
ON public.documents FOR SELECT
USING (public.is_org_member(documents.org_id));

Hardening the helper

A security definer function is powerful, so pin its moving parts. The search_path is set to the empty string and every reference is schema-qualified, which prevents a hostile object earlier in the path from shadowing your tables or functions. Marking the function stable lets the planner cache its result within a statement instead of recomputing it per row.

The default public execute privilege is revoked and then granted explicitly to the client roles, so evaluation inside policies succeeds while the surface stays intentional: callers can ask only about their own membership, and the function reveals nothing beyond that answer.

After applying the pattern, rerun the failing query as the anon role and confirm the error is gone and the visible row set matches expectations. Recursion-class errors reaching the interface after a fix usually mean the helper was created in a different schema than the one the policy qualifies, or that a second cycle remains elsewhere in the graph.