ion body | 30–50ms | Missed (requires manual audit) |

Why this matters: The optimization is not a policy syntax rule; it is a planner directive. PostgreSQL's executor cannot push InitPlan caching through a function boundary. If the authentication lookup remains unwrapped inside a SECURITY DEFINER routine, the planner treats it as a row-dependent expression regardless of how the policy calls it. Recognizing this forces a shift from surface-level linting to execution-layer verification. It also reveals that authorization abstraction, while valuable for code organization, introduces performance debt unless explicitly optimized at the deepest execution tier.

Core Solution

Resolving the blind spot requires applying the scalar subselect pattern at the point of actual evaluation: inside the function body. The policy wrapper alone is insufficient when logic is delegated to a stored routine.

Step 1: Identify the Execution Boundary

When a policy delegates filtering to a function, the planner treats that function as an opaque unit. It cannot inspect the internal query plan during policy planning. Therefore, the InitPlan optimization must be declared inside the function itself.

Step 2: Refactor the Function Body

Replace the bare context call with a scalar subselect. This forces the function's internal query to cache the session value before row iteration begins.

Problematic Implementation:

CREATE OR REPLACE FUNCTION app_auth.check_tenant_membership(
    target_tenant_id uuid,
    target_user_id uuid
)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1 FROM app_data.tenant_memberships
        WHERE tenant_id = target_tenant_id
          AND user_id = target_user_id
          AND user_id = auth.uid()
    );
$$;

The linter sees (SELECT app_auth.check_tenant_membership(...)) in the policy and marks it as compliant. Internally, auth.uid() executes once per row evaluated by the EXISTS clause.

Optimized Implementation:

CREATE OR REPLACE FUNCTION app_auth.check_tenant_membership(
    target_tenant_id uuid,
    target_user_id uuid
)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1 FROM app_data.tenant_memberships
        WHERE tenant_id = target_tenant_id
          AND user_id = target_user_id
          AND user_id = (SELECT auth.uid())
    );
$$;

The scalar subselect (SELECT auth.uid()) instructs the planner to evaluate the session context once, store it in a temporary InitPlan node, and reuse it for every row the function processes.

Step 3: Verify Planner Behavior

Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on a representative query. Look for the execution node classification:

• SubPlan 1 or InitPlan 1 appears in the output.
• If the auth call is unwrapped, you will see Rows Removed by Filter scaling linearly with table size, and the function execution time will correlate directly with row count.
• If properly wrapped, the InitPlan node will show Startup Cost and Total Cost that remain constant regardless of scanned rows. The Calls column for the auth lookup will read 1.

Architecture Rationale

• Why SECURITY DEFINER changes behavior: Functions marked SECURITY DEFINER execute with the privileges of the owner, not the caller. PostgreSQL isolates their execution context to prevent privilege escalation. This isolation prevents the planner from inlining or pushing down optimizations across the function boundary. The optimization must be self-contained.
• Why STABLE is required: STABLE guarantees that the function returns the same result for the same input within a single transaction. This allows the planner to cache results safely. Using VOLATILE would force re-evaluation regardless of wrapping. Using IMMUTABLE is incorrect for auth functions because session context changes between connections.
• Why scalar subselect works: PostgreSQL's parser treats (SELECT constant_expression) as a candidate for InitPlan promotion. It extracts the expression, evaluates it once during query initialization, and substitutes the cached value during execution. This bypasses per-row function call overhead entirely.

Pitfall Guide

1. Assuming Policy-Level Wrapping Propagates Downward

Explanation: Developers wrap auth.uid() in the policy USING clause but leave the bare call inside a referenced function. The linter passes, but the executor still invokes the function per row. Fix: Apply the scalar subselect at the deepest execution layer. Audit all functions called by RLS policies.

2. Misclassifying STABLE as a Performance Guarantee

Explanation: STABLE allows result caching within a transaction but does not prevent per-row invocation in set-returning or filtered contexts. The planner still calls the function for each row unless the internal expression is optimized. Fix: Combine STABLE with the (SELECT auth.uid()) pattern inside the function body.

3. Using IMMUTABLE for Session Context Functions

Explanation: IMMUTABLE promises that output depends only on input parameters, never on session state. Marking auth.uid() wrappers as IMMUTABLE violates this contract and can cause stale cache reads across different user sessions. Fix: Always use STABLE for functions that read session variables, JWT claims, or auth.uid().

4. Over-Reliance on Static Linters Without Execution Verification

Explanation: Linters parse syntax, not execution plans. They cannot detect performance regressions caused by function boundaries or planner limitations. Fix: Integrate EXPLAIN ANALYZE into CI pipelines for RLS-heavy schemas. Compare InitPlan vs SubPlan metrics before merging policy changes.

5. Ignoring Cascading Function Calls

Explanation: A policy calls Function A, which calls Function B, which contains the bare auth.uid(). The optimization is lost at the deepest layer. Fix: Trace the entire call chain. Apply the scalar subselect to every function that directly references session context, regardless of its position in the hierarchy.

6. Confusing EXPLAIN Output Nodes

Explanation: Developers mistake SubPlan for InitPlan. SubPlan executes per row; InitPlan executes once. Misreading the output leads to false confidence. Fix: Filter EXPLAIN output for InitPlan. Verify that Calls = 1 and that the node appears before the main scan operation.

7. Neglecting current_setting() Optimization

Explanation: Teams using current_setting('request.jwt.claims')::json->>'sub' instead of auth.uid() often forget to apply the same wrapping pattern. The performance penalty is identical. Fix: Wrap the entire setting extraction: (SELECT current_setting('request.jwt.claims')::json->>'sub').

Production Bundle

Action Checklist

• Audit all SECURITY DEFINER functions referenced by RLS policies for bare auth.uid() or current_setting() calls.
• Apply scalar subselect wrapping (SELECT auth.uid()) inside every function body that reads session context.
• Verify function volatility is set to STABLE, never IMMUTABLE or VOLATILE for auth lookups.
• Run EXPLAIN (ANALYZE, BUFFERS) on high-traffic RLS queries and confirm InitPlan nodes show Calls = 1.
• Update CI/CD pipelines to include query plan regression tests for policies exceeding 100k rows.
• Document the wrapping pattern in team style guides to prevent future abstraction drift.
• Monitor pg_stat_statements for functions with high mean_time scaling linearly with row count.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Simple policy with direct auth.uid()	Wrap at policy level: (SELECT auth.uid())	Minimal abstraction, linter catches it, planner optimizes immediately	Near-zero overhead, immediate latency reduction
Policy delegates to SECURITY DEFINER function	Wrap inside function body	Planner cannot push optimization across function boundary; must be self-contained	Requires code change, eliminates 15–30s latency spikes
Multi-tenant SaaS with 10M+ rows	Wrap at deepest layer + partition tables	Prevents per-row evaluation at scale; partitioning reduces scan scope further	Higher initial migration cost, linear performance scaling
Legacy schema with 50+ auth functions	Batch refactor with pg_dump grep + automated replacement scripts	Manual audit is error-prone; script ensures consistency across call chains	One-time engineering investment, prevents future regressions

Configuration Template

-- Reusable authorization function template
-- Apply this pattern to all SECURITY DEFINER routines used in RLS policies

CREATE OR REPLACE FUNCTION app_auth.validate_resource_access(
    p_resource_owner uuid,
    p_requesting_user uuid
)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
PARALLEL SAFE
AS $$
    -- Scalar subselect forces InitPlan evaluation
    -- Prevents per-row execution on large datasets
    SELECT (p_requesting_user = (SELECT auth.uid()))
       AND (p_resource_owner = (SELECT auth.uid()));
$$;

-- Example policy leveraging the optimized function
CREATE POLICY resource_isolation ON app_data.documents
    FOR SELECT
    USING (
        SELECT app_auth.validate_resource_access(owner_id, auth.uid())
    );

Quick Start Guide

1. Extract function definitions: Run pg_dump --schema-only your_database | grep -A 10 'SECURITY DEFINER' > auth_functions.sql to isolate all security-boundary routines.
2. Search for bare calls: Execute grep -n 'auth\.uid()\|current_setting' auth_functions.sql to locate unwrapped context lookups.
3. Apply scalar wrapping: Replace auth.uid() with (SELECT auth.uid()) inside each function body. Preserve STABLE and SECURITY DEFINER attributes.
4. Deploy and verify: Apply the changes to a staging environment. Run EXPLAIN ANALYZE on a representative query and confirm InitPlan nodes report Calls = 1.
5. Promote to production: Merge the refactored functions. Monitor query latency and pg_stat_statements for 24 hours to validate performance stability.