Security Boundaries & Permissions

Securing the trust boundary of a change-data-capture (CDC) pipeline is a distinct discipline within PostgreSQL logical replication architecture: because logical decoding exposes committed row-level changes rather than opaque WAL blocks, a replication consumer that is over-privileged, unencrypted, or reachable from the wrong network can exfiltrate every INSERT, UPDATE, and DELETE on a published table without ever touching the base tables directly. This page defines the exact roles, host-based authentication rules, TLS controls, and least-privilege grants that keep a publisher safe while still letting a subscriber or Python ETL consumer stream continuously.

Get this layer wrong and the failure modes are concrete, not hypothetical. A replication role granted SUPERUSER turns a leaked connection string into full cluster compromise. A missing hostssl rule lets an attacker on the same VPC read the change stream in cleartext. An unmonitored, over-scoped replication slot left active = false pins restart_lsn and grows pg_wal until the primary hits disk full — a security posture problem that becomes an availability outage. The controls below are enforced at every phase the pipeline passes through: initial snapshot, continuous streaming, and failure recovery.

Prerequisites & Configuration Objects #

Before any subscriber connects, the publisher must be provisioned for logical decoding and hardened. These GUCs live in postgresql.conf; wal_level requires a restart, the rest a reload via SELECT pg_reload_conf();.

-- Publisher prerequisites (postgresql.conf), then restart for wal_level:
-- wal_level = logical
-- max_wal_senders = 10          -- >= active consumers + failover headroom
-- max_replication_slots = 10    -- >= active consumers + 20% rolling-deploy buffer
-- ssl = on
-- ssl_ciphers = 'HIGH:!aNULL:!MD5'
-- password_encryption = scram-sha-256

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

The security objects required on the publisher, in order of provisioning:

• A dedicated, non-superuser replication role — one role per consumer, never shared with application accounts. It needs the REPLICATION attribute (to open a START_REPLICATION connection) and LOGIN, nothing more.
• Explicit SELECT grants on exactly the published tables — logical replication decouples reading rows for the snapshot from streaming WAL, so the role needs table-level SELECT for the initial copy in addition to REPLICATION.
• A scoped pg_hba.conf entry — a hostssl replication rule bound to the consumer’s CIDR and role, evaluated before any broad host all rule. This is detailed in setting up pg_hba.conf for replication users.
• A server certificate and CA chain for hostssl enforcement, ideally issued and rotated by a secrets platform (HashiCorp Vault, AWS Secrets Manager, or Kubernetes cert-manager).

Verify the baseline before proceeding: wal_level must report logical, and max_replication_slots should exceed the count of active consumers plus a buffer for failover rotation.

SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders','max_replication_slots',
               'ssl','password_encryption','max_slot_wal_keep_size');

Step-by-Step Implementation #

The following sequence provisions a hardened consumer end to end. Each step is idempotent so it can run under Terraform, Ansible, or Pulumi without drift on re-apply.

1. Create the least-privilege replication role. Grant REPLICATION and table SELECT only — never SUPERUSER. Use ALTER DEFAULT PRIVILEGES so tables added to the publication later are covered automatically.

CREATE ROLE repl_etl_svc LOGIN REPLICATION PASSWORD 'set-via-secrets-manager';
GRANT CONNECT ON DATABASE target_db TO repl_etl_svc;
GRANT USAGE ON SCHEMA public TO repl_etl_svc;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_etl_svc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repl_etl_svc;

2. Whitelist the consumer network with hostssl. Rules are first-match-wins, so the specific hostssl replication allow rules must precede any broad reject. Manage this file through infrastructure-as-code to prevent drift during automated scaling or cross-region failover.

# pg_hba.conf (managed via IaC)
hostssl replication     repl_etl_svc    10.0.5.0/24     scram-sha-256
hostssl replication     repl_etl_svc    10.0.6.0/24     scram-sha-256
host    replication     repl_etl_svc    0.0.0.0/0       reject

Apply without downtime and confirm the parser accepted every rule:

SELECT pg_reload_conf();
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules WHERE error IS NULL;   -- PG 10+; pg_hba_file_rules

3. Enforce TLS, and mutual TLS across trust boundaries. For multi-VPC, hybrid-cloud, or cross-account deployments, require both peers to validate certificates. In pg_hba.conf, hostssl with clientcert=verify-full forces the subscriber to present a certificate whose CN matches the role. Align cipher suite selection with NIST SP 800-52 Rev. 2, disabling TLS 1.0/1.1 and weak Diffie-Hellman groups. Validate the handshake from the consumer host:

openssl s_client -connect publisher.internal:5432 \
  -starttls postgres -CAfile /etc/postgresql/ssl/ca.crt </dev/null 2>/dev/null \
  | openssl x509 -noout -dates -subject

4. Rotate certificates without dropping the stream. Stage the new certificate, update the postgresql.conf path (or symlink), then reload — pg_reload_conf() re-reads ssl_cert_file/ssl_key_file without restarting the postmaster, so existing WAL sender connections survive and only new handshakes pick up the new material.

# Rotate publisher TLS material with zero replication downtime.
import subprocess, psycopg2

def rotate_publisher_tls(dsn: str, new_cert: str, new_key: str) -> None:
    # 1. Stage new material (already written to /etc/postgresql/ssl/ by cert-manager)
    # 2. Point postgresql.conf at it and reload — no restart, senders stay connected.
    with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute("ALTER SYSTEM SET ssl_cert_file = %s", (new_cert,))
        cur.execute("ALTER SYSTEM SET ssl_key_file  = %s", (new_key,))
        cur.execute("SELECT pg_reload_conf()")
    # 3. Verify the new leaf is being served before retiring the old CA.
    subprocess.run(["openssl", "s_client", "-connect", "publisher.internal:5432",
                    "-starttls", "postgres"], check=True)

5. Scope the consumer connection. For Python ETL pipelines using psycopg2 or asyncpg, pin timeouts so a stuck consumer cannot hold a slot open indefinitely. Slot creation and administrative queries use a regular SQL connection; only WAL streaming uses psycopg2.extras.LogicalReplicationConnection.

import psycopg2

# sslmode=verify-full pins both encryption AND server-identity verification.
conn = psycopg2.connect(
    host="publisher.internal", dbname="target_db", user="repl_etl_svc",
    sslmode="verify-full", sslrootcert="/etc/postgresql/ssl/ca.crt",
    connect_timeout=10,
    options="-c statement_timeout=30s -c idle_in_transaction_session_timeout=60s",
)

Parameter Reference Table #

Diagnostic Queries #

Run these against the publisher to audit the live security posture. Threshold callouts flag what should page the on-call engineer.

Confirm no replication role holds excess privilege. Any row with rolsuper = t here is a critical finding.

SELECT rolname, rolsuper, rolreplication, rolcanlogin, rolbypassrls
FROM pg_roles
WHERE rolreplication OR rolname LIKE 'repl_%';
-- ALERT if rolsuper = true or rolbypassrls = true for any replication role.

Verify every live connection is encrypted. A replication backend with ssl = f is streaming the change set in cleartext.

SELECT a.usename, a.application_name, a.client_addr, s.ssl, s.version, s.cipher
FROM pg_stat_ssl s
JOIN pg_stat_activity a ON a.pid = s.pid
WHERE a.backend_type = 'walsender';
-- ALERT on any walsender where ssl = false.

Audit which tables a role can actually read (the real blast radius of a leaked credential):

SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'repl_etl_svc'
ORDER BY table_schema, table_name;

Catch stalled slots before they exhaust disk — the availability face of a security lapse. See WAL stream mechanics for why restart_lsn pins retention.

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- ALERT if active = false for > 15 min, or retained_wal approaches max_slot_wal_keep_size.

Confirm pg_hba.conf parsed cleanly after any IaC apply — a rule with a non-null error is silently ignored:

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;   -- Must return zero rows.

Failure Modes & Gotchas #

1. FATAL: no pg_hba.conf entry ... SSL off during the initial handshake. The consumer matched a host reject (or no rule) instead of the intended hostssl allow. Root cause is almost always rule ordering — a broad rule sits above the specific one. Remediation: move the hostssl replication <role> <cidr> lines above any catch-all, pg_reload_conf(), and re-check pg_hba_file_rules.

2. permission denied for table during snapshot, streaming works later. The role has REPLICATION but no SELECT on a published table, so the initial copy fails even though WAL streaming would succeed. This surfaces when a table is added to a publication after the role was granted. Remediation: GRANT SELECT on the table and set ALTER DEFAULT PRIVILEGES so future tables are covered.

3. A dead consumer fills pg_wal and takes the primary offline. An inactive slot pins restart_lsn; with max_slot_wal_keep_size = -1 (the default), retention is unbounded. This is the most common way a security/monitoring gap becomes a disk full outage. Remediation: set max_slot_wal_keep_size to a bounded value, alert on active = false for >15 min, and reap orphaned slots with pg_drop_replication_slot() once the consumer is confirmed gone.

4. Encrypted-but-unverified connections (sslmode=require). The stream is encrypted, so pg_stat_ssl shows ssl = t, but the consumer never validates the server certificate — a MITM with a self-signed cert on the same network can intercept the change set. Remediation: use sslmode=verify-full with a pinned sslrootcert on every consumer, and clientcert=verify-full on the publisher for mutual authentication.

5. Shared or reused replication credentials. One role serving multiple consumers means a single leaked secret compromises every stream and makes audit attribution impossible. Remediation: one role per consumer, rotate via the secrets platform, and drive log_connections/log_disconnections into the audit pipeline so each walsender is attributable.

Integration Touchpoints #

Security boundaries are not a standalone concern — they thread through every adjacent stage of the pipeline:

• Slot lifecycle. The retention risks above are governed by slot state; the privilege to create and drop slots belongs to the same replication role, so review replication slot types and initializing replication slots alongside this page.
• Subscription bootstrap. When the consumer is a native PostgreSQL subscriber, its apply worker connects as this role during subscription sync procedures; the CONNECTION string in CREATE SUBSCRIPTION must carry sslmode=verify-full, or the whole hardening effort is bypassed at the last hop.
• External CDC consumers. A Debezium connector or a custom Python CDC parser authenticates with exactly the same role and TLS material, so the least-privilege and mTLS decisions here define the security envelope of the downstream pipeline too.
• Continuous auditing. Route log_connections, log_disconnections, and log_statement = 'ddl' into a SIEM through async monitoring integration so unauthorized slot creation, privilege-escalation attempts, and TLS handshake failures raise alerts rather than sitting in a log nobody reads.

Operational Auditing & Compliance Drift Detection #

Security postures degrade without continuous validation. Automate a quarterly (or per-deploy) diff that compares live role grants, publication scope, and pg_hba.conf against a version-controlled baseline, and alert on:

• Unauthorized replication slot creation
• Privilege escalation (GRANT of SUPERUSER/BYPASSRLS, or a new REPLICATION role)
• TLS handshake failures indicating certificate mismatch or an expired CA

Production hardening checklist:

• pg_hba.conf restricts replication to dedicated CIDRs via hostssl, ordered before any catch-all
• mTLS enforced (clientcert=verify-full publisher, sslmode=verify-full consumer) with automated cert rotation
• Replication roles scoped to REPLICATION + explicit SELECT; zero SUPERUSER/BYPASSRLS
• max_slot_wal_keep_size bounded and stalled-slot alerting in place (active = false > 15 min)
• One role per consumer, secrets sourced from a managed platform, log_connections shipped to a SIEM
• Permission and pg_hba.conf audits automated via IaC diffing and scheduled SQL validation

Related #

• PostgreSQL Logical Replication Architecture & Fundamentals — the parent overview this security model fits into.
• Setting up pg_hba.conf for replication users — the host-based authentication rules in depth.
• Replication Slot Types — slot state that drives the WAL-retention risk described here.
• WAL Stream Mechanics — how restart_lsn and confirmed_flush_lsn govern retention.
• Publication/Subscription Models — the object model whose access these grants control.