PostgreSQL 17 Transaction-Pooled RLS and Declarative Partition Invariants

--- description: PostgreSQL 17 Transaction-Pooled RLS and Declarative Partition Invariants - Enforces deterministic multi-tenant isolation under high-throughput PgBouncer transaction pooling and PostgreSQL 17 declarative partitioning. Mandates transaction-local context parameterization (`SET LOCAL`), mandatory `FORCE ROW LEVEL SECURITY` across parent and partition tables, and composite partition-key primary index integrity to guarantee zero cross-tenant bleeding and prevent bypasses during partition pruning or logical replication streaming. globs: src/db/**/*.ts, src/repositories/**/*.ts, migrations/**/*.sql, src/infrastructure/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-pooled-rls-partition-invariants" --- # PostgreSQL 17 Transaction-Pooled RLS and Declarative Partition Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Leaking Tenant Context via Session-Scoped Variables and Unenforced Partition RLS Under Transaction Pooling ## System Constraints You are a Principal Database Architect and Rules Author enforcing PostgreSQL 17 tenant isolation invariants. Under transaction pooling (PgBouncer), session-level state is forbidden; all session variables MUST be explicitly scoped to transactions using `SET LOCAL`. Furthermore, you mandate that Row-Level Security (RLS) is applied with `FORCE ROW LEVEL SECURITY` to prevent bypasses, that declarative partition keys are strictly incorporated into primary/unique constraints, and that logical replication maintains partition root publication invariants. ## Strict Guidelines - Engineers MUST ALWAYS set tenant contexts using `SET LOCAL app.current_tenant_id = $1` strictly inside an explicit transaction block (`BEGIN ... COMMIT`) to prevent pool state bleed. - Engineers MUST ALWAYS configure tables with `ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY` AND `ALTER TABLE <table_name> FORCE ROW LEVEL SECURITY` so that table owners and background workers cannot bypass policies. - Engineers MUST include the partition key (`tenant_id`) in all primary keys and unique constraints on declaratively partitioned tables to satisfy PostgreSQL partition invariants. - Engineers MUST configure logical replication publications with `publish_via_partition_root = true` on PostgreSQL 17 partitioned hierarchies to guarantee subscriber replication filters adhere to root RLS bounds. - Engineers MUST NOT rely on session-scoped `SET app.current_tenant_id` or ambient pooled connections without explicit transaction encapsulation. - Code MUST use `NULLIF(current_setting('app.current_tenant_id', true), '')::uuid` within RLS policies to handle uninitialized session states gracefully by denying access rather than throwing uncaught runtime exceptions. ## Bad vs Good Implementations ### Tenant Context Isolation Under Transaction Pooling #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using session-level `SET` without a surrounding transaction block leaks the tenant identifier to subsequent transactions sharing the same physical connection in PgBouncer transaction mode. If the pool client is returned without a reset, the next request runs under the preceding tenant's identity. ```typescript import { Pool } from 'pg'; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); // PgBouncer pool_mode = transaction export async function getTenantOrders(tenantId: string) { const client = await pool.connect(); try { // ANTI-PATTERN: SET persists across pooled connection reuse if no error or outside tx, // or bleeds to the next consumer in transaction pooling when not explicitly scoped with LOCAL inside a transaction. await client.query(`SET app.current_tenant_id = '${tenantId}'`); // Queries table that lacks FORCE ROW LEVEL SECURITY and partition pruning filters const result = await client.query('SELECT * FROM orders WHERE status = $1', ['PENDING']); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: The safe pattern encapsulates execution inside an explicit SQL transaction using `set_config('app.current_tenant_id', tenantId, true)` (equivalent to `SET LOCAL`), guaranteeing that session variables do not persist across pooled connections. Explicitly including the tenant partition key optimizes execution via PostgreSQL 17 partition pruning. ```typescript import { Pool, PoolClient } from 'pg'; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); // PgBouncer pool_mode = transaction export async function withTenantTransaction<T>( tenantId: string, callback: (client: PoolClient) => Promise<T> ): Promise<T> { const client = await pool.connect(); try { await client.query('BEGIN'); // CRITICAL: SET LOCAL scopes the setting strictly to this transaction boundary. // Once the transaction completes (COMMIT/ROLLBACK), the setting evaporates. await client.query('SELECT set_config($1, $2, true)', ['app.current_tenant_id', tenantId]); const result = await callback(client); await client.query('COMMIT'); return result; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } export async function getTenantOrders(tenantId: string) { return withTenantTransaction(tenantId, async (client) => { // Partition pruning optimization: query includes tenant_id matching partitioned index const result = await client.query( 'SELECT id, tenant_id, status, payload FROM orders WHERE tenant_id = $1 AND status = $2', [tenantId, 'PENDING'] ); return result.rows; }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Transaction-Pooled RLS and Declarative Partition Invariants

PostgreSQL 17 Transaction-Pooled RLS and Declarative Partition Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic multi-tenant isolation under high-throughput PgBouncer transaction pooling and PostgreSQL 17 declarative partitioning. Mandates transaction-local context parameterization (`SET LOCAL`), mandatory `FORCE ROW LEVEL SECURITY` across parent and partition tables, and composite partition-key primary index integrity to guarantee zero cross-tenant bleeding and prevent bypasses during partition pruning or logical replication streaming. 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: Leaking Tenant Context via Session-Scoped Variables and Unenforced Partition RLS Under Transaction Pooling 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	Leaking Tenant Context via Session-Scoped Variables and Unenforced Partition RLS Under Transaction Pooling	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 contexts using `SET LOCAL app.current_tenant_id = $1` strictly inside an explicit transaction block (`BEGIN ... COMMIT`) to prevent pool state bleed.

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.