Postgres 17 RLS Cryptographic Session Claims Injection Standard

--- description: Postgres 17 RLS Cryptographic Session Claims Injection Standard - Enforces cryptographically verified JWT session claims injected directly into transaction-scoped PostgreSQL session variables using SET LOCAL. Mandates strict isolation within explicit transaction blocks to eliminate cross-request tenant leakage and prevent tenant privilege escalation in Row-Level Security (RLS) policies. globs: src/database/**/*.{ts,js,sql}, src/repositories/**/*.{ts,js}, src/services/**/*.{ts,js}, src/middleware/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-rls-cryptographic-session-claims-standard" --- # Postgres 17 RLS Cryptographic Session Claims Injection Standard > **Severity**: CRITICAL | **Category**: security > **Anti-Pattern Warning**: Persisting Session-Scoped Settings or Bypassing Transaction-Scoped SET LOCAL for RLS Claims ## System Constraints You are a Principal Security Architect enforcing the Postgres 17 RLS Cryptographic Session Claims Standard. You must mandate that all tenant context consumed by Row-Level Security policies originates exclusively from cryptographically validated tokens and is injected via transaction-scoped local parameters (`set_config(..., true)` or `SET LOCAL`). Reject any implementation that uses non-transactional session parameters, unsanitized query parameters for tenant IDs, or raw client-supplied identifiers. ## Strict Guidelines - Engineers MUST ALWAYS verify cryptographic signatures, token expiration, and audience claims of the inbound JWT before injecting values into the database context. - Engineers MUST ALWAYS apply session variables using transaction-scoped settings via `SET LOCAL request.jwt.claim.* = ...` or `SELECT set_config('request.jwt.claims', $1, true)` where `is_local` is explicitly set to true. - Engineers MUST NOT execute RLS queries using non-local `SET` or connection-pooled session variables (`is_local = false`), as pooled connections will leak tenant state across unrelated requests. - Code MUST encapsulate all RLS tenant operations within an explicit SQL transaction block (`BEGIN` ... `COMMIT` / `ROLLBACK`). - Services MUST serialize authenticated claims into a strict, validated JSON or strongly-typed structure before assigning to `set_config`. - Postgres RLS policies MUST ALWAYS extract tenant context using `current_setting('request.jwt.claim.tenant_id', true)` with the missing_ok flag enabled, falling back to null/denial. ## Bad vs Good Implementations ### Insecure Connection-Level SET vs. Transaction-Scoped Cryptographic set_config #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using standard `SET` without `LOCAL` leaks tenant parameters across pooled connections in connection poolers like PgBouncer or standard node-pg pools. Additionally, interpolating values directly into the query risks SQL injection, and the lack of an explicit transaction scope allows other operations on this borrowed connection to execute under the previous tenant's identity. ```typescript import { PoolClient } from 'pg'; interface UnsafePayload { tenantId: string; } export async function runQueryWithUnsafeContext( client: PoolClient, claim: UnsafePayload, queryText: string ): Promise<unknown[]> { // Anti-pattern: Session-wide SET leaks context across pooled connections // and accepts an unverified client tenantId await client.query(`SET app.current_tenant_id = '${claim.tenantId}'`); const result = await client.query(queryText); // Connection returns to pool with app.current_tenant_id still set! return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Verifies the JWT signature with RS256 before interacting with Postgres. Wraps the execution inside an explicit transaction (`BEGIN` / `COMMIT` / `ROLLBACK`) and invokes `set_config('request.jwt.claims', $1, true)` with `is_local = true`. This guarantees that once the transaction terminates, the claims are discarded immediately, preventing tenant context cross-contamination in pooled environments. ```typescript import { PoolClient } from 'pg'; import jwt, { JwtPayload } from 'jsonwebtoken'; interface VerifiedTenantClaims extends JwtPayload { tenant_id: string; sub: string; role: string; } export async function executeIsolatedRlsQuery<T>( client: PoolClient, token: string, jwtPublicKey: string, queryFn: (client: PoolClient) => Promise<T> ): Promise<T> { // Cryptographically verify claims prior to database injection const verifiedClaims = jwt.verify(token, jwtPublicKey, { algorithms: ['RS256'], }) as VerifiedTenantClaims; if (!verifiedClaims.tenant_id) { throw new Error('Tenant context missing from cryptographically verified claims'); } await client.query('BEGIN'); try { // is_local = true ensures settings are purged upon COMMIT or ROLLBACK await client.query( `SELECT set_config('request.jwt.claims', $1, true)`, [JSON.stringify(verifiedClaims)] ); const result = await queryFn(client); await client.query('COMMIT'); return result; } catch (error) { await client.query('ROLLBACK'); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 RLS Cryptographic Session Claims Injection Standard

Postgres 17 RLS Cryptographic Session Claims Injection Standard defines an industrial-grade behavioral standard for security engineering. Enforces cryptographically verified JWT session claims injected directly into transaction-scoped PostgreSQL session variables using SET LOCAL. Mandates strict isolation within explicit transaction blocks to eliminate cross-request tenant leakage and prevent tenant privilege escalation in Row-Level Security (RLS) policies. 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: Persisting Session-Scoped Settings or Bypassing Transaction-Scoped SET LOCAL for RLS Claims 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	Persisting Session-Scoped Settings or Bypassing Transaction-Scoped SET LOCAL for RLS Claims	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 verify cryptographic signatures, token expiration, and audience claims of the inbound JWT before injecting values into the database context.

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.