Demystifying PostgreSQL’s "Infinite Recursion" in Row-Level Security: Causes, Benchmarks, and Six Production-Ready Fixes

Database security at the multi-tenant layer often introduces subtle, high-impact architectural traps. Among the most frustrating obstacles for modern developers leveraging PostgreSQL is the dreaded infinite recursion detected in policy for relation error (SQLSTATE 42P17).

This exception emerges the exact moment a Row-Level Security (RLS) policy attempts to read the very table it is designed to protect. In multi-tenant environments, this pattern typically manifests within the primary membership table: users belong to multiple tenants, and the logical rule—“you can see the members of the tenants you belong to”—requires querying the membership table to determine who you are before granting permission to read that table.

To understand the mechanics of this error, engineers tested PostgreSQL 17.11 under default settings using a controlled benchmark environment containing a million invoices and a corresponding membership matrix. Their findings reveal stark performance divergences among potential workarounds, demonstrating that fixing the recursion does not automatically guarantee acceptable query performance.

Main Facts: Anatomy of SQLSTATE 42P17

PostgreSQL encounters infinite recursion during its query-rewriting phase when evaluating a policy requires reading the target relation under those identical rules.

Key Characteristics of the Error:

• Pre-Execution Failure: The error is raised before the query executes. Operations like SELECT ... WHERE false, an EXPLAIN statement without ANALYZE, and an INSERT all fail instantly under a globally applied policy.
• Select-Bound Scope: Only policies that apply to read operations trigger the error—specifically policies defined FOR SELECT or for all commands. Policies restricted strictly to FOR INSERT, FOR UPDATE, or FOR DELETE do not initiate recursion on read.
• Silent in Development: The error is notoriously easy to miss during early development cycles. Database owners and superusers bypass row-level security by default; consequently, queries execute successfully for administrators while crashing for standard application roles.
• Cross-Table Contagion: The exception is not restricted solely to the faulty table. Any auxiliary table whose RLS policy references the membership table will also fail, with the error message consistently naming the primary table being queried rather than the immediate table in the query text.

Chronology and Diagnostic Setup

The operational dynamics of this issue were mapped out on PostgreSQL 17.11 running inside an isolated container environment.

The Diagnostic Matrix

When deploying a self-referencing RLS policy such as:

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

The database reacts differently depending on the execution context and command type:

The tests confirmed that two separate tables can easily trigger mutual recursion. If a policy on memberships references tenants, and a policy on tenants references memberships, both relations fail validation, with each error message pointing directly to the table actively queried.

Supporting Data: Benchmarking Six Solutions

Breaking the recursion loop is only half the battle; ensuring the query planner can leverage underlying indexes is critical for application scalability. Researchers measured six distinct architectural approaches using a table of 201,602 invoices (for a large user with access to multiple tenants) and 801 invoices (for a small user), alongside membership resolution speeds.

Performance Comparison of the Six Fixes

Analyzing the Performance Bottlenecks

The performance delta between Fix 1 and Fix 2 underscores how the PostgreSQL query planner handles sub-selects versus explicit array evaluations:

• Fix 1 (The Trap): Moving the lookup into a standard SECURITY DEFINER function and writing tenant_id in (select my_tenants()) eliminates the recursion error, but processing the small user’s invoice count takes a sluggish 74.5 ms. The execution plan reveals a sequential scan on the invoices table (Rows Removed by Filter), testing every single row against a hashed sub-plan. Declaring the function STABLE instead of VOLATILE yields no performance improvement here.
• Fix 2 & 3 (The Optimal Index Path): By transforming the call into tenant_id = any (array(select my_tenants())) or returning an array directly, the sub-select collapses into an InitPlan that executes precisely once. The query planner transitions to an efficient Index Only Scan using the corresponding index conditions, plummeting execution time down to 0.15 ms.
• Fix 4 (The Volatility Trap): Omitting the STABLE designation leaves the function marked VOLATILE by default. When called directly within a policy expression without an encapsulating array sub-select, PostgreSQL evaluates the function repeatedly for every single row scanned. This catastrophic execution pattern pushes counts to over 6.5 seconds.

Official Responses and Edge Cases

The FORCE ROW LEVEL SECURITY Complication

When database administrators enable FORCE ROW LEVEL SECURITY on a table, even the table owner becomes bound by policies. If a SECURITY DEFINER function is owned by the table owner, a dangerous runtime loop emerges: the function executes its query, the query triggers the RLS policy, the policy invokes the function, and the process repeats until memory exhaustion occurs:

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

Artificially inflating max_stack_depth fails to resolve the issue because the recursion lacks a terminal condition. The production-ready solution requires creating an isolated auxiliary role to own the helper functions. This role must possess BYPASSRLS privileges and SELECT access on the memberships table, operating independently from the standard application login role.

The Lookup Table Security Trade-off

An alternative architectural pattern involves copying mapping data into a decoupled lookup table devoid of Row-Level Security. However, this trades the recursion error for a severe security risk. Without explicit permission grants, application queries fail with permission denied. Once read access is granted to the application role, executing select count(*) from user_tenants exposes the complete mapping matrix of all system users in a single request, completely breaking multi-tenant data isolation.

Implications and Production Architecture Recommendations

Securing multi-tenant applications requires deliberate design choices across function execution rights, write policies, and schema definitions. Engineering teams should adopt the following best practices:

1. Granular Command Policies: Never write blanket policies without command specifiers unless intended. For membership lists, scope the recursive prevention policy strictly FOR SELECT, ensuring that data modifications (INSERT, UPDATE, DELETE) utilize dedicated, non-recursive validation rules.
2. Secure Function Execution: Revoke EXECUTE permissions from the PUBLIC role on all helper security functions (revoke execute on function my_tenant_arr() from public;), explicitly granting access solely to the designated application role.
3. Explicit Search Paths: Always harden SECURITY DEFINER functions by setting an immutable search path (set search_path = '') and fully qualifying internal table references with their schema name (e.g., public.memberships).
4. Comprehensive Test Suites: Because superusers and table owners inherently bypass RLS, automated integration test suites must execute routine assertions under the exact low-privilege context of the application runtime role to catch recursion regressions prior to production deployments.