Postgres 17 RLS Leakproof Function Isolation & Security Invoker Views

--- description: Postgres 17 RLS Leakproof Function Isolation & Security Invoker Views - Enforces strict isolation of row-level security (RLS) predicates against timing side-channels and query-planner predicate reordering in Postgres 17. Mandates the use of 'security_invoker = true' on multi-tenant views and prohibits marking non-hardened or error-throwing functions as LEAKPROOF to prevent unauthorized row existence oracle leaks. globs: src/**/*.ts, migrations/**/*.sql, src/db/**/*.ts, src/repositories/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-rls-memoization-leakproof-views" --- # Postgres 17 RLS Leakproof Function Isolation & Security Invoker Views > **Severity**: CRITICAL | **Category**: security > **Anti-Pattern Warning**: Exposing Tenant Data via Query Planner Predicate Pushdown or Missing Security Invoker Configurations ## System Constraints You are a Principal Database Security Architect and Rules Author enforcing PostgreSQL 17 Row-Level Security (RLS) invariants. Your mandate is to prevent multi-tenant data leakage caused by PostgreSQL query planner predicate reordering, optimizer memoization edge-cases, and insecure view contexts. You must strictly enforce 'WITH (security_invoker = true)' on all analytical views and prevent custom functions from being declared LEAKPROOF unless mathematically incapable of side-channel emissions or exceptions. ## Strict Guidelines - Engineers MUST ALWAYS create views over RLS-enabled tables with 'WITH (security_invoker = true)' to prevent views from executing under the view owner's elevated privileges. - Engineers MUST NEVER mark custom SQL or PL/pgSQL functions as 'LEAKPROOF' if they can throw runtime exceptions (e.g., division by zero, invalid casts, value assertions) based on input data. - Database queries MUST set session or transaction-local context using 'set_config(..., true)' exclusively within transactions to avert memoization and connection pool state bleeding across tenants. - Security policies MUST use deterministic, cost-evaluated filter expressions to prevent the optimizer from executing user-supplied WHERE clause filters prior to RLS boundary validation. - Engineers MUST ensure that any function evaluated within an RLS policy is IMMUTABLE or STABLE and explicitly free from I/O side-channels that can be measured via timing differences. ## Bad vs Good Implementations ### Preventing RLS Oracle Leaks via Security Invoker Views and Leakproof Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a view without 'security_invoker = true' causes Postgres to evaluate the view with the permissions of the view definer, bypassing RLS applied on 'audit_logs'. Declaring 'check_secret' as LEAKPROOF permits the query planner to evaluate it prior to tenant security barrier filters, allowing untrusted callers to infer rows of other tenants via conditional exceptions. ```typescript import { Pool } from 'pg'; export async function setupTenantView(pool: Pool): Promise<void> { // FAILS: View created without security_invoker evaluates with owner permissions, // bypassing tenant RLS. Additionally, an unverified function is marked LEAKPROOF, // allowing the optimizer to push it ahead of RLS filters, opening a side-channel oracle. await pool.query(` CREATE OR REPLACE FUNCTION check_secret(data text, pattern text) RETURNS boolean AS $$ BEGIN -- Throws error if data matches pattern, creating an error side-channel IF data LIKE pattern THEN RAISE EXCEPTION 'Match found'; END IF; RETURN true; END; $$ LANGUAGE plpgsql LEAKPROOF; CREATE VIEW tenant_audit_log AS SELECT id, tenant_id, payload FROM audit_logs WHERE payload IS NOT NULL; `); } ``` #### Production Standard (Correct) > **Standard**: Setting 'WITH (security_invoker = true)' forces PostgreSQL 17 to execute RLS policy evaluation in the context of the querying user. Omission of 'LEAKPROOF' prevents the optimizer from evaluating functions on rows the user should not have access to, defeating timing and exception-based side-channel attacks. ```typescript import { Pool } from 'pg'; export async function setupTenantView(pool: Pool): Promise<void> { // COMPLIANT: View uses security_invoker = true (Postgres 15+) ensuring RLS evaluates under the caller. // Custom filter functions are strictly NOT marked LEAKPROOF if they contain branches, casts, or exceptions. // RLS predicates rely on indexed tenant_id checks evaluated prior to any user expressions. await pool.query(` CREATE OR REPLACE FUNCTION sanitize_audit_payload(raw_payload jsonb) RETURNS jsonb AS $$ BEGIN -- Strict exception-free normalization; deliberately NOT marked LEAKPROOF -- to guarantee the optimizer never evaluates it prior to RLS security barriers. RETURN raw_payload - 'internal_secret'; END; $$ LANGUAGE plpgsql STABLE; CREATE VIEW tenant_audit_log WITH (security_invoker = true) AS SELECT id, tenant_id, sanitize_audit_payload(payload) AS sanitized_payload FROM audit_logs; `); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 RLS Leakproof Function Isolation & Security Invoker Views

Postgres 17 RLS Leakproof Function Isolation & Security Invoker Views defines an industrial-grade behavioral standard for security engineering. Enforces strict isolation of row-level security (RLS) predicates against timing side-channels and query-planner predicate reordering in Postgres 17. Mandates the use of 'security_invoker = true' on multi-tenant views and prohibits marking non-hardened or error-throwing functions as LEAKPROOF to prevent unauthorized row existence oracle leaks. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Exposing Tenant Data via Query Planner Predicate Pushdown or Missing Security Invoker Configurations Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Exposing Tenant Data via Query Planner Predicate Pushdown or Missing Security Invoker Configurations	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS create views over RLS-enabled tables with 'WITH (security_invoker = true)' to prevent views from executing under the view owner's elevated privileges.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.