All packsMulti-Tenant Auth & RBAC CoreSolve
postgres rls policy infinite recursion membership subquery
A membership check inside its own table's policy recurses
A naive RLS policy on org_members that subqueries org_members to check membership triggers infinite recursion the moment Postgres tries to evaluate it.
What goes wrong
Checking "is this user a member of this org" by writing a policy on org_members that itself queries org_members to answer the question creates a circular evaluation: Postgres has to apply the policy to check the policy. Depending on the exact shape, this either errors outright or silently degrades into a much more expensive query plan than anyone intended.
Why agents get it wrong
The subquery is the obvious, direct translation of the English rule ("check the membership table") into SQL, and it's the kind of policy that looks correct on paper and in a quick manual test with one org and one user, before the recursive evaluation cost or the outright recursion error shows up under a slightly more realistic policy shape.
What Multi-Tenant Auth & RBAC Core pre-decides
Core decision 6 routes the membership check through a SECURITY DEFINER helper function (is_org_member, running as the table owner, exempt from the policy it's being called from) instead of a subquery inside the policy itself. The same decision keeps org_members at RLS ENABLE (not FORCE) specifically so this definer function -- which the owner-exemption trick depends on -- keeps working on managed Postgres where the owner isn't a superuser.
Source: ARCHITECTURE.md core decision 6