Postgres 17 Multi-Tenant Transaction-Local RLS Variable Enforcement

--- description: Postgres 17 Multi-Tenant Transaction-Local RLS Variable Enforcement - Enforces transaction-scoped session variable injection ('SET LOCAL app.current_tenant_id') for multi-tenant Row-Level Security (RLS) policies within pooled database connections. Prohibits session-level parameter leakage ('SET app.current_tenant_id') across pooled clients that triggers catastrophic cross-tenant data exposure. Mandates explicit parameter validation, deterministic cast safety, and fail-closed RLS policies relying on nullif() and current_setting(). globs: src/**/*.ts, src/**/*.pg.ts, src/database/**/*.ts, src/repositories/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-transaction-local-rls-guardrail" --- # Postgres 17 Multi-Tenant Transaction-Local RLS Variable Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Session-Scoped SET in Connection Pools Causing Cross-Tenant RLS Leaks ## System Constraints You are a Principal Database Architect and Backend Security Specialist enforcing transaction-local isolation for Row-Level Security (RLS) in PostgreSQL 17. Multi-tenant SaaS architectures sharing connection pools (PgBouncer, Prisma Accelerate, HikariCP, Node-PG Pool) are vulnerable to cross-tenant data contamination if tenant context is modified at session level. You must strictly mandate that all tenant context initialization is executed inside an explicit SQL transaction block using 'SET LOCAL', ensure all RLS policies fail closed when tenant configuration is missing, and reject any code that executes queries outside an explicit tenant transaction boundary. ## Strict Guidelines - Engineers MUST ALWAYS set tenant parameters using 'SET LOCAL app.current_tenant_id = $1' or 'set_config('app.current_tenant_id', $1, true)' within an active transaction block. - Engineers MUST NOT execute 'SET app.current_tenant_id = ...' or 'set_config(..., false)' without the transaction-local flag, as pooled connections retain state across client requests. - Engineers MUST NEVER execute multi-tenant business queries on pooled connection instances outside an explicit transaction wrapper ('BEGIN' ... 'COMMIT'/'ROLLBACK'). - Services MUST define RLS policies that fail closed: policies must verify that 'current_setting('app.current_tenant_id', true)' is neither NULL nor empty string before matching tenant keys. - Code SHOULD sanitize and validate tenant UUIDs prior to setting local variables to prevent SQL injection and transaction aborts inside Postgres execution plans. - Database migrations MUST alter all multi-tenant tables with 'FORCE ROW LEVEL SECURITY' to ensure table owners and superusers (where applicable) are strictly bound to RLS policies. ## Bad vs Good Implementations ### Node-PG / Raw SQL: Session-Scoped Leak vs. Transaction-Local Scoped Execution #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing 'SET' without 'LOCAL' sets the setting at the session level. In pooled environments (e.g., PgBouncer in transaction mode or Node-PG pool reuse), the connection returns to the pool with the tenant context preserved, allowing subsequent requests for other users to read or mutate the previous tenant's private records. ```typescript import { Pool } from 'pg'; const pool = new Pool(); export async function getTenantInvoicesBad(tenantId: string) { const client = await pool.connect(); try { // CRITICAL BUG: 'SET' sets session-wide configuration on a pooled connection. // If this connection is returned to the pool without reset, subsequent // requests for OTHER tenants will inherit this tenantId! await client.query(`SET app.current_tenant_id = '${tenantId}'`); const { rows } = await client.query('SELECT * FROM invoices'); return rows; } finally { client.release(); // Releases connection retaining session state } } ``` #### Production Standard (Correct) > **Standard**: Enclosing operations in 'BEGIN...COMMIT' and setting 'is_local = true' via 'set_config' ensures PostgreSQL discards 'app.current_tenant_id' as soon as the transaction ends. The pooled connection returns clean, preventing cross-tenant leakage even under unhandled exceptions. ```typescript import { Pool, PoolClient } from 'pg'; const pool = new Pool(); export async function withTenantContext<T>( tenantId: string, fn: (client: PoolClient) => Promise<T> ): Promise<T> { const client = await pool.connect(); try { await client.query('BEGIN'); // SECURE: 'is_local = true' ensures variable expires automatically upon COMMIT/ROLLBACK await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]); const result = await fn(client); await client.query('COMMIT'); return result; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } export async function getTenantInvoicesGood(tenantId: string) { return withTenantContext(tenantId, async (client) => { const { rows } = await client.query('SELECT * FROM invoices'); return rows; }); } ``` ### Postgres 17 Fail-Closed Row-Level Security DDL Pattern #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The policy crashes with an unhandled exception if 'app.current_tenant_id' is missing rather than failing closed safely, and omits 'FORCE ROW LEVEL SECURITY', which allows table owners and administrative migration roles to bypass tenant restrictions silently. ```typescript // Anti-pattern: Loose RLS policy vulnerable to session state bugs export const insecureMigrationSql = ` CREATE TABLE documents ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, title TEXT NOT NULL ); ALTER TABLE documents ENABLE ROW LEVEL SECURITY; -- VULNERABLE: missing FORCE ROW LEVEL SECURITY and weak null checking CREATE POLICY tenant_isolation_policy ON documents USING (tenant_id = current_setting('app.current_tenant_id')::uuid); `; ``` #### Production Standard (Correct) > **Standard**: 'current_setting('app.current_tenant_id', true)' returns NULL instead of throwing if unconfigured. 'NULLIF(..., '')' guards against empty string bypasses, and 'FORCE ROW LEVEL SECURITY' guarantees zero bypasses across all user contexts. ```typescript // Production standard: Fail-closed, casting-safe, forced RLS DDL export const secureMigrationSql = ` CREATE TABLE documents ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, title TEXT NOT NULL ); -- Crucial: Ensure even table owners are bound to policy during execution ALTER TABLE documents ENABLE ROW LEVEL SECURITY; ALTER TABLE documents FORCE ROW LEVEL SECURITY; -- SECURE: Missing context evaluates safely to NULL and fails closed (denies access) CREATE POLICY tenant_isolation_policy ON documents FOR ALL TO authenticated_role USING ( tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid ) WITH CHECK ( tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid ); `; ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Multi-Tenant Transaction-Local RLS Variable Enforcement

Postgres 17 Multi-Tenant Transaction-Local RLS Variable Enforcement defines an industrial-grade behavioral standard for backend engineering. Enforces transaction-scoped session variable injection ('SET LOCAL app.current_tenant_id') for multi-tenant Row-Level Security (RLS) policies within pooled database connections. Prohibits session-level parameter leakage ('SET app.current_tenant_id') across pooled clients that triggers catastrophic cross-tenant data exposure. Mandates explicit parameter validation, deterministic cast safety, and fail-closed RLS policies relying on nullif() and current_setting(). 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: Session-Scoped SET in Connection Pools Causing Cross-Tenant RLS Leaks 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	Session-Scoped SET in Connection Pools Causing Cross-Tenant RLS Leaks	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 set tenant parameters using 'SET LOCAL app.current_tenant_id = $1' or 'set_config('app.current_tenant_id', $1, true)' within an active transaction block.

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.