Breaking PostgreSQL RLS Recursion: The Planner Inlining Trap

Current Situation Analysis

Modern multi-tenant architectures increasingly delegate data isolation to PostgreSQL’s Row-Level Security (RLS). The architectural appeal is straightforward: push authorization logic into the database layer, eliminate application-side filtering, and guarantee that every query respects tenant boundaries regardless of the client or ORM used. However, this model introduces a subtle but critical trap when policies require self-referential checks.

The most frequent manifestation occurs when a policy must verify a user’s role or permissions stored in the same table it protects. Consider a workspace management system where administrators must view all member records within their organization. A developer naturally writes a policy that queries the member table to confirm admin status. PostgreSQL detects this self-reference and immediately throws ERROR: 42P17: infinite recursion detected in policy for relation. The database aborts the transaction to prevent stack exhaustion.

This issue is consistently misunderstood because the intuitive workaround—wrapping the check in a SECURITY DEFINER function—often fails without clear error messaging. The PostgreSQL query planner automatically inlines simple SQL functions during the optimization phase. When inlining occurs, the function’s security context is discarded, and the inner query executes under the original caller’s permissions. RLS re-applies to the inlined subquery, the recursion cycle resumes, and the planner bails out. The root cause isn’t a flaw in RLS; it’s a collision between the planner’s optimization strategy and function security boundaries. Developers frequently assume that SECURITY DEFINER creates an impenetrable execution wall, but the planner’s inlining pass effectively dissolves that wall before execution begins.

WOW Moment: Key Findings

The critical insight lies in how PostgreSQL’s planner handles different function languages and security contexts. The following comparison demonstrates why certain approaches succeed while others fail:

Approach	Recursion Risk	Query Planner Behavior	RLS Enforcement	Execution Overhead
Direct Subquery in Policy	Critical (100%)	Inlined into main plan	Enforced per row	Minimal
LANGUAGE sql + SECURITY DEFINER	Critical (100%)	Auto-inlined during planning	Lost after inlining	Minimal
LANGUAGE plpgsql + SECURITY DEFINER	None	Never inlined	Bypassed intentionally	Low (function call)

This finding matters because it shifts how developers design RLS architectures. Instead of treating policies as pure SQL expressions, you must account for the planner’s inlining behavior. Using plpgsql as a deliberate barrier prevents optimization from stripping away security contexts. This enables safe self-referential checks without compromising data isolation or triggering planner aborts. The trade-off is a negligible function call overhead, which is vastly preferable to transaction failures or application-layer fallbacks.

Core Solution

The reliable pattern requires three coordinated components: a protected table, a non-inlinable security wrapper, and a policy that delegates evaluation to that wrapper. Below is a production-ready implementation using a tenant_users table.

Step 1: Define the Target Table and Enable RLS Start with a standard multi-tenant structure. RLS must be explicitly enabled before policies take effect. Without this step, policies are parsed but never enforced.

CREATE TABLE tenant_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('member', 'admin', 'owner')),
    created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE tenant_users ENABLE ROW LEVEL SECURITY;

Step 2: Build the Security Wrapper Function The function must use plpgsql, declare SECURITY DEFINER, and explicitly set the search path. The STABLE volatility marker is required because the function reads database state but guarantees consistent results within a single transaction.

CREATE OR REPLACE FUNCTION check_tenant_role(
    target_user_id UUID,
    target_tenant_id UUID,
    required_role TEXT
) RETURNS BOOLEAN
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = 'public'
AS $$
DECLARE
    role_match BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM tenant_users
        WHERE id = target_user_id
          AND tenant_id = target_tenant_id
          AND role = required_role
    ) INTO role_match;
    
    RETURN role_match;
END;
$$;

Step 3: Attach the Policy The policy now delegates the self-referential check to the wrapper. Because the function executes as the table owner, RLS is bypassed for the inner query, breaking the recursion cycle.

CREATE POLICY admin_full_access
ON tenant_users
FOR SELECT
TO authenticated
USING (
    tenant_id = current_setting('app.current_tenant', true)::UUID
    OR check_tenant_role(
        auth.uid(),
        tenant_id,
        'admin'
    )
);

Architecture Rationale

• LANGUAGE plpgsql: PostgreSQL’s documentation explicitly states that SQL functions are candidates for inlining, while PL/pgSQL functions are executed as opaque black boxes. This guarantees the security context survives query planning. The planner treats PL/pgSQL as a boundary that cannot be safely optimized away.
• SECURITY DEFINER: Runs the inner query as the function owner (typically a superuser or schema owner). This intentionally bypasses RLS for the helper query, which is safe because the function validates specific conditions before returning a boolean. The elevated privilege is scoped strictly to the function body.
• SET search_path = 'public': Prevents schema injection attacks. Without this, a malicious user could create a function or table in a higher-priority schema to hijack the unqualified tenant_users reference. Explicit path declaration locks resolution to the intended namespace.
• STABLE volatility: Tells the planner the function won’t modify data and will return the same result for the same inputs within a transaction. This allows safe caching without breaking security semantics. Using VOLATILE would disable caching and force re-evaluation on every row, degrading performance. Using IMMUTABLE would incorrectly signal that the function never reads database state, causing the planner to push predicates or cache results across transactions.

Pitfall Guide

1. Omitting SET search_path

   • Explanation: Unqualified table references resolve using the caller’s search path. If a user creates a malicious table in a custom schema, they can intercept the query and return fabricated results.
   • Fix: Always declare SET search_path = 'public' (or your target schema) in SECURITY DEFINER functions. Never rely on default path resolution.

2. Using IMMUTABLE Volatility

   • Explanation: IMMUTABLE tells the planner the function depends only on its inputs and never reads database state. The planner may cache results aggressively or push predicates incorrectly, breaking security checks and causing stale authorization decisions.
   • Fix: Use STABLE for functions that read table data, or VOLATILE if they modify state. Match volatility to actual I/O behavior.

3. Forgetting to Enable RLS

   • Explanation: Policies are inert until ALTER TABLE ... ENABLE ROW LEVEL SECURITY is executed. Developers often create policies and wonder why they aren’t enforced, especially when testing as table owners.
   • Fix: Always pair policy creation with explicit RLS enablement. Consider using FORCE ROW LEVEL SECURITY for table owners if you need to test policies without bypassing them.

4. Over-Bypassing RLS in Helper Functions

   • Explanation: SECURITY DEFINER functions run with elevated privileges. If reused in other contexts without proper input validation, they can expose unauthorized data or become attack vectors.
   • Fix: Scope helper functions narrowly. Add explicit parameter validation, avoid granting EXECUTE on the function to untrusted roles, and document the security boundary clearly.

5. Transaction Visibility Mismatches

   • Explanation: Because the inner query runs as the function owner, it sees all rows, including uncommitted ones from other transactions if isolation levels differ. This is usually fine for RLS checks, but can cause logical errors if the application expects strict visibility boundaries.
   • Fix: Document the visibility behavior. Use READ COMMITTED or REPEATABLE READ consistently across the application layer. Avoid mixing isolation levels when RLS helpers are involved.

6. Planner Cache Invalidation with PREPARE

   • Explanation: Prepared statements cache execution plans. If the underlying table structure or function definition changes, cached plans may reference stale security contexts or fail to adapt to new indexes.
   • Fix: Use DEALLOCATE or connection pooling reset strategies after schema migrations. Avoid long-lived prepared statements in migration-heavy environments. Test plan regeneration after function alterations.

7. Assuming LANGUAGE sql Always Inlines

   • Explanation: While simple SQL functions inline automatically, complex SQL functions with multiple statements or certain constructs may not. Relying on this behavior is fragile and version-dependent.
   • Fix: Treat LANGUAGE plpgsql as the default for any RLS helper that queries protected tables. Consistency prevents edge-case failures across PostgreSQL minor versions.

Production Bundle

Action Checklist

• Enable RLS explicitly on every tenant-isolated table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• Replace all self-referential policy subqueries with dedicated helper functions
• Declare LANGUAGE plpgsql and SECURITY DEFINER on every RLS helper function
• Add SET search_path = 'public' to prevent schema injection in security wrappers
• Mark helper functions as STABLE to align with transaction-scoped reads
• Revoke EXECUTE on helper functions from public and untrusted roles
• Test policies under SET ROLE to verify RLS enforcement and bypass behavior
• Document visibility semantics for all SECURITY DEFINER functions in your schema

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Simple tenant filter (tenant_id = current_tenant())	Direct policy expression	No self-reference, planner optimizes efficiently	Zero overhead
Cross-table role verification	SECURITY DEFINER PL/pgSQL function	Breaks recursion, maintains isolation	Low function call overhead
Complex multi-tenant joins	Materialized view or application-layer filtering	RLS recursion risk increases with join depth	Higher storage or app compute
Audit logging with RLS	VOLATILE function with SECURITY DEFINER	Requires write access, bypasses RLS safely	Moderate transaction overhead

Configuration Template

-- 1. Target table setup
CREATE TABLE IF NOT EXISTS workspace_members (
    member_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    user_email TEXT NOT NULL,
    permission_level TEXT NOT NULL DEFAULT 'viewer',
    CONSTRAINT valid_role CHECK (permission_level IN ('viewer', 'editor', 'admin'))
);

ALTER TABLE workspace_members ENABLE ROW LEVEL SECURITY;

-- 2. Security wrapper
CREATE OR REPLACE FUNCTION verify_workspace_permission(
    check_user_id UUID,
    check_workspace_id UUID,
    required_level TEXT
) RETURNS BOOLEAN
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = 'public'
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM workspace_members
        WHERE member_id = check_user_id
          AND workspace_id = check_workspace_id
          AND permission_level = required_level
    );
END;
$$;

-- 3. Policy attachment
CREATE POLICY admin_workspace_access
ON workspace_members
FOR SELECT
TO authenticated
USING (
    workspace_id = current_setting('app.active_workspace', true)::UUID
    OR verify_workspace_permission(
        auth.uid(),
        workspace_id,
        'admin'
    )
);

-- 4. Permission hardening
REVOKE EXECUTE ON FUNCTION verify_workspace_permission(UUID, UUID, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION verify_workspace_permission(UUID, UUID, TEXT) TO authenticated;

Quick Start Guide

1. Identify any RLS policy that queries its own table using EXISTS, JOIN, or subqueries.
2. Extract the self-referential logic into a new plpgsql function with SECURITY DEFINER and SET search_path.
3. Replace the inline subquery in the policy with a call to the new function, passing auth.uid() and relevant tenant/workspace identifiers.
4. Execute ALTER TABLE ... ENABLE ROW LEVEL SECURITY if not already active, then test with SET ROLE to confirm recursion is eliminated and access controls hold.