infinite recursion detected in policy: causes and fixes

infinite recursion detected in policy for relation "memberships" appears the moment a row-level security policy has to read the table it protects. In a multi-tenant product that is typically the membership table: users belong to tenants, and the natural rule — you can see the members of the tenants you belong to — asks the membership table who you are before it lets you read the membership table.

We reproduced it on PostgreSQL 17.11, wrote down exactly which statements raise it and which do not, and then measured six ways out on a table of a million invoices. The fixes all make the error go away. They are not equally fast: the same count took 0.09 ms with one and 6.6 seconds with another.

What does “infinite recursion detected in policy for relation” mean?

PostgreSQL found, while rewriting your query, that applying the policies of a table requires reading that same table under those same policies. It is SQLSTATE 42P17, raised before the query runs: in our tests a SELECT … WHERE false, an EXPLAIN without ANALYZE and an INSERT all failed with it, under a policy for all commands. Only policies that apply to reading count — a policy FOR SELECT or for all commands. It is not raised when the policy is created, and not for a role that bypasses row-level security.

The setup

PostgreSQL 17.11 in a throwaway container, default settings, measured on 9 October 2026.

• tenants (1,000 rows), memberships (user_id, tenant_id, role) with 12,000 rows — 10,000 users, one in ten a member of three tenants — and invoices with 1,000,000 rows and an index on (tenant_id, issued_on). Tenant 1 has 200,000 invoices; the others about 800 each.
• The tables belong to a role owner_role. The application connects as app, which is not the owner and has no BYPASSRLS. The user is a session variable, app.user_id, written below as me.
• Two test users: a large user who belongs to three tenants including tenant 1 (201,602 invoices in total), and a small user with one tenant of 801 invoices.
• Timings are planning plus execution time from EXPLAIN (ANALYZE, TIMING OFF), the median of five runs after two warm-up runs, on a warm cache.

When is it raised?

The policy that causes it:

create policy members_of_my_tenants on memberships
  using (tenant_id in (select tenant_id from memberships where user_id = me));

So the error does not stay with the table that has the faulty policy. Every table whose policy looks at memberships fails with it, and the message names memberships, not the table you queried. And it is easy to miss in development: as the owner or as a superuser, the same query works.

Two tables can do it together. With a policy on memberships that reads tenants and a policy on tenants that reads memberships, both tables failed, each message naming the table that was queried.

A self-reference in a write policy alone did not raise it. With a plain select policy (user_id = me) and an update policy that reads memberships to check for an admin role, selects and updates both worked: the sub-select inside the update policy is a read, and reads go through the select policy, which does not refer back. The recursion needs a policy that applies to reading. The reverse held too: with a self-referencing policy FOR SELECT only, a plain INSERT went through, and the same INSERT with RETURNING failed with the recursion error.

The policy that is not recursive

Before the fixes, the version that never had the problem: let users see only their own membership rows, and let other tables look up tenants through that.

create policy own_rows on memberships using (user_id = me);
create policy member on invoices
  using (tenant_id in (select tenant_id from memberships));

That works, and it is what you have until someone needs a member list. It is also slow in the way we measured in row-level security performance: the small user’s 801 invoices took 33.8 ms to count, because the planner checks every invoice against the sub-select instead of looking the tenants up in the index.

Six ways out, measured

Fixes 1 to 5 let a member see the other members of all their tenants — 50 membership rows for the large user. Fix 6 shows one tenant at a time: 10 rows for tenant 1. Each was measured with the same three queries.

The functions:

create function my_tenants() returns setof int
  language sql stable security definer set search_path = ''
  as $$ select tenant_id from public.memberships
        where user_id = nullif(current_setting('app.user_id', true), '')::int $$;

create function my_tenant_arr() returns int[]
  language sql stable security definer set search_path = ''
  as $$ select coalesce(array_agg(tenant_id), '{}') from public.memberships
        where user_id = nullif(current_setting('app.user_id', true), '')::int $$;

Why is the usual fix still slow?

Because breaking the recursion and giving the planner something to look up are two different things. Fix 1 is the one you will find first: move the lookup into a SECURITY DEFINER function and write tenant_id in (select my_tenants()). The error is gone, and the small user’s count took 74.5 ms. The plan shows why: a Seq Scan on invoices with Rows Removed by Filter: 999199, every row tested against a hashed sub-plan. Declaring the function STABLE instead of the default VOLATILE changed nothing here: 74.5 ms either way.

Fix 2 uses the same function and changes only how the policy calls it: tenant_id = any (array(select my_tenants())). The sub-select becomes an InitPlan that runs once, the result is an array, and the plan is an Index Only Scan with Index Cond: (tenant_id = ANY (…)): 0.15 ms. Fix 3 does the same with a function that returns the array itself.

Fix 4 is fix 3 without the word STABLE. A function is VOLATILE unless you say otherwise, and a volatile function called directly in the policy expression, without a sub-select around it, ran once for every row: 6.6 seconds to count 801 invoices, and 85 ms to list 50 memberships. Wrapped in a sub-select it did not matter: fix 2 used the index with the function left VOLATILE as well. The same trap, measured on a larger table, is in the performance article linked above.

Why does the SECURITY DEFINER function fail with FORCE?

Because the function runs as its owner, and with FORCE ROW LEVEL SECURITY the owner is subject to the policy again. The PostgreSQL documentation: “Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE … FORCE ROW LEVEL SECURITY.”

With FORCE on memberships and the function owned by the table owner, all three test queries failed — but not with the recursion message, and not before running: a WHERE false returned 0. The function calls the policy, the policy calls the function, and that loop is only found when it runs:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), …

Raising max_stack_depth to 7 MB gave the same error; the loop has no end. What worked was a separate role that owns the function, has BYPASSRLS and SELECT on memberships, and is not the role the application logs in as. With that, FORCE stayed on and the timings were the same as without it (85.0 and 74.4 ms for fix 1). Without BYPASSRLS on that role the stack error came back.

The view, fix 5, has the same weakness in a third spelling. A view reads its tables as the view’s owner, so a view owned by the table owner works without FORCE. With FORCE, or with security_invoker = true on the view, the invoice queries failed with infinite recursion detected in rules for relation "my_tenant_ids" and the membership query with the original policy message.

What about a lookup table without row-level security?

Without row-level security on it, it trades the error for a leak. We copied (user_id, tenant_id) into a table user_tenants without row-level security and pointed the policy at it. Without SELECT on that table for app, the query failed with permission denied for table user_tenants: a policy’s sub-select runs with the rights of the user running the query. With the grant, the policy worked — and select count(*) from user_tenants as app returned all 12,000 rows. Every user’s memberships were readable by the role that every request runs as, in one query.

The policies above also allow writes

We wrote every policy on this page without FOR, so it applies to all commands, and its USING expression doubles as the check on new rows. With fix 2 in place the large user could insert a membership row for another user into tenant 1, with the role admin, and delete nine co-members. “Can see the members of my tenants” had become “can add, promote and remove them”. For a member list, write the policy FOR SELECT and give inserts, updates and deletes their own policies.

The functions are open as well. EXECUTE on a new function is granted to every role: a role with no rights on any table got {1,4,6} from my_tenant_arr() after setting app.user_id, while a select on memberships gave it permission denied. Revoke EXECUTE from PUBLIC and grant it to the application role only.

What we would build

The function trusts app.user_id, as every policy on this page trusts a session variable. Whoever can run arbitrary SQL as app can set it to another user. That is the same limit as with a tenant in a session variable, described in a role per tenant vs a session variable.

The error you get once reading works and a write is refused is a different one, with four spellings of its own: new row violates row-level security policy: the causes.

What we did not measure

Versions other than PostgreSQL 17.11, membership tables larger than 12,000 rows, users with hundreds of tenants (where the array gets long), policies that also check a role per membership, hosted platforms that add their own roles in front of the table, and concurrent load. The timings come from one machine with default settings and a warm cache; read them as proportions.

Sources: the PostgreSQL 17 documentation on row security policies (table owners and FORCE), read on 9 October 2026. All measurements are our own, on PostgreSQL 17.11, on 9 October 2026.