Postgres 17 Logical Replication Tenant RLS Synchronization and Boundary Enforcement

PostgreSQL logical replication worker processes bypass Row-Level Security (RLS) by default on subscriber tables unless explicitly governed by publication row filters, replica RLS policies, and non-superuser subscription owners. This rule enforces cryptographic tenant boundary propagation across logical replication streams, requiring explicit publication `WHERE` clauses, subscriber-side `FORCE ROW LEVEL SECURITY`, and consumer session context hydration to prevent cross-tenant data leaks in CDC downstream systems.

--- description: Postgres 17 Logical Replication Tenant RLS Synchronization and Boundary Enforcement - PostgreSQL logical replication worker processes bypass Row-Level Security (RLS) by default on subscriber tables unless explicitly governed by publication row filters, replica RLS policies, and non-superuser subscription owners. This rule enforces cryptographic tenant boundary propagation across logical replication streams, requiring explicit publication `WHERE` clauses, subscriber-side `FORCE ROW LEVEL SECURITY`, and consumer session context hydration to prevent cross-tenant data leaks in CDC downstream systems. globs: src/**/*.ts, prisma/**/*.prisma, migrations/**/*.sql, infra/**/*.tf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-replication-rls-sync" --- # Postgres 17 Logical Replication Tenant RLS Synchronization and Boundary Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unfiltered Logical Publications Replicating Global Data to RLS-Disabled Read Replicas ## System Constraints You are a Principal Database Architect and Infrastructure Security Specialist enforcing tenant isolation across PostgreSQL 17 logical replication and Change Data Capture (CDC) pipelines. PostgreSQL logical replication transmits changes at the storage layer: by default, downstream subscription workers apply writes as superusers or table owners, bypassing subscriber-side RLS entirely. Furthermore, publisher-side publications that omit explicit row filters (`WHERE tenant_id = ...`) replicate raw cross-tenant WAL records to downstream nodes. You must mandate: (1) explicit row filter predicates on all publication tables, (2) `ALTER TABLE ... FORCE ROW LEVEL SECURITY` on both publisher and subscriber tables, (3) execution of subscription apply workers under dedicated non-superuser tenant roles, and (4) strict session variable setting (`SET LOCAL app.current_tenant_id`) in any CDC consumer connection pool before processing replicated events. ## Strict Guidelines - Engineers MUST ALWAYS define logical publications with explicit row filters (e.g., `FOR TABLE customer_data WHERE (tenant_id = CURRENT_SETTING('app.current_tenant_id', true)::uuid)`) or publish strictly into tenant-dedicated publication streams. - Engineers MUST ALWAYS execute `ALTER TABLE <table_name> FORCE ROW LEVEL SECURITY` on subscriber databases to prevent table owners or replica background processes from bypassing read policies. - Engineers MUST NOT create logical subscriptions using `SUPERUSER` credentials; subscriptions MUST be owned by a restricted non-superuser role with `BYPASSRLS` explicitly disabled. - Engineers MUST configure Postgres 17 `run_as_owner = true` on subscriptions or ensure the applying role possesses explicit `GRANT SELECT, INSERT, UPDATE, DELETE` confined to the target tenant schema. - Downstream CDC ingestion consumers MUST validate the cryptographic tenant claim in each incoming CDC envelope before routing or persisting downstream. - Downstream database connection checkouts MUST execute `SET LOCAL app.current_tenant_id = :tenantId` inside an explicit transaction prior to any query execution against replicated tables. ## Bad vs Good Implementations ### Postgres 17 Subscriber and Publication RLS Enforcement Script #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a publication FOR ALL TABLES with no row filter exports all tenant data across the replication slot. Creating a subscription as a superuser causes the logical replication worker to bypass Row-Level Security on the subscriber completely, allowing tenant data to leak into untrusted environments or unpartitioned read replicas. ```typescript import { Client } from 'pg'; export async function setupReplicationBad(client: Client) { // FAILS: Creates broad publication without tenant filtering await client.query(`CREATE PUBLICATION tenant_pub FOR ALL TABLES;`); // FAILS: Superuser subscription bypasses subscriber-side RLS await client.query(` CREATE SUBSCRIPTION tenant_sub CONNECTION 'host=primary dbname=prod user=postgres password=secret' PUBLICATION tenant_pub; `); } ``` #### Production Standard (Correct) > **Standard**: The publisher strictly limits replicated WAL payloads via row filters. The subscriber forces RLS via `FORCE ROW LEVEL SECURITY` and executes logical replication under a non-superuser role with `NOBYPASSRLS` and `run_as_owner = true`, preventing replication-time data breaches. ```typescript import { Client } from 'pg'; export async function setupReplicationSecure(publisher: Client, subscriber: Client, tenantId: string) { // 1. Publisher: Explicit row filter bound to tenant boundary await publisher.query(` CREATE PUBLICATION pub_tenant_${tenantId} FOR TABLE tenant_records WHERE (tenant_id = '${tenantId}'::uuid) WITH (publish = 'insert,update,delete'); `); // 2. Subscriber: Force RLS so no process bypasses policies await subscriber.query(` ALTER TABLE tenant_records ENABLE ROW LEVEL SECURITY; ALTER TABLE tenant_records FORCE ROW LEVEL SECURITY; -- Postgres 17 non-superuser replication role DROP ROLE IF EXISTS replica_worker_${tenantId}; CREATE ROLE replica_worker_${tenantId} WITH LOGIN NOSUPERUSER NOBYPASSRLS; GRANT USAGE ON SCHEMA public TO replica_worker_${tenantId}; GRANT ALL ON tenant_records TO replica_worker_${tenantId}; `); // 3. Subscriber: Subscription owned by restricted role with PG17 run_as_owner await subscriber.query(` CREATE SUBSCRIPTION sub_tenant_${tenantId} CONNECTION 'host=primary dbname=prod user=repl_stream_${tenantId} password=secret' PUBLICATION pub_tenant_${tenantId} WITH (copy_data = true, create_slot = true, run_as_owner = true); ALTER SUBSCRIPTION sub_tenant_${tenantId} OWNER TO replica_worker_${tenantId}; `); } ``` ### Downstream CDC Consumer Session Context Isolation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: If the read-replica enforces RLS via `CURRENT_SETTING('app.current_tenant_id')`, querying without initializing the session setting will either return zero rows or fail. If RLS is misconfigured, it risks reading unpartitioned cross-tenant data left over from previous transactions on dirty pooled connections. ```typescript import { Pool } from 'pg'; interface CDCEvent { tenantId: string; table: string; payload: Record<string, unknown>; } export async function processCDCEventBad(pool: Pool, event: CDCEvent) { // FAILS: Connection from pool does not set tenant session variable // Directly queries replicated read-replica table where RLS is active const result = await pool.query( `SELECT * FROM tenant_records WHERE id = $1`, [event.payload.id] ); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Uses an explicit transaction block and Postgres `set_config(..., true)` to guarantee `app.current_tenant_id` is scoped strictly to the checkout transaction. Prevents connection-pool leaking and enforces database-level tenant isolation on the replicated node. ```typescript import { Pool, PoolClient } from 'pg'; interface CDCEvent { tenantId: string; table: string; payload: { id: string }; } export async function processCDCEventSecure(pool: Pool, event: CDCEvent) { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN'); // Mandate isolated session setting scoped strictly to this transaction await client.query( `SELECT set_config('app.current_tenant_id', $1, true)`, [event.tenantId] ); const result = await client.query( `SELECT * FROM tenant_records WHERE id = $1 AND tenant_id = current_setting('app.current_tenant_id')::uuid`, [event.payload.id] ); await client.query('COMMIT'); return result.rows; } catch (err) { await client.query('ROLLBACK'); throw err; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication Tenant RLS Synchronization and Boundary Enforcement

Postgres 17 Logical Replication Tenant RLS Synchronization and Boundary Enforcement defines an industrial-grade behavioral standard for backend engineering. PostgreSQL logical replication worker processes bypass Row-Level Security (RLS) by default on subscriber tables unless explicitly governed by publication row filters, replica RLS policies, and non-superuser subscription owners. This rule enforces cryptographic tenant boundary propagation across logical replication streams, requiring explicit publication `WHERE` clauses, subscriber-side `FORCE ROW LEVEL SECURITY`, and consumer session context hydration to prevent cross-tenant data leaks in CDC downstream systems. 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: Unfiltered Logical Publications Replicating Global Data to RLS-Disabled Read Replicas 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	Unfiltered Logical Publications Replicating Global Data to RLS-Disabled Read Replicas	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 define logical publications with explicit row filters (e.g., `FOR TABLE customer_data WHERE (tenant_id = CURRENT_SETTING('app.current_tenant_id', true)

Engineers MUST ALWAYS define logical publications with explicit row filters (e.g., `FOR TABLE customer_data WHERE (tenant_id = CURRENT_SETTING('app.current_tenant_id', true)::uuid)`) or publish strictly into tenant-dedicated publication streams.

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.