Setting up pg_hba.conf for replication users

Configuring pg_hba.conf for a logical replication consumer is the single host-based authentication rule that decides whether a change-data-capture pipeline can open a WAL stream at all, and it is enforced as one of the security boundaries and permissions controls on every publisher. Get one field wrong — the replication pseudo-database name, the CIDR scope, or the authentication method — and the consumer will authenticate cleanly for the initial table snapshot but fail the instant it issues START_REPLICATION, surfacing as FATAL: no pg_hba.conf entry for replication connection.

The asymmetry is what makes this rule dangerous in production. A rule that is too narrow silently breaks continuous decoding on the replication slot, pins restart_lsn, and grows pg_wal until the primary hits disk full. A rule that is too broad exposes the committed row-level change stream — every INSERT, UPDATE, and DELETE on a published table — to any host in the range, in cleartext if host is used instead of hostssl. This page fixes the exact rule syntax, the field-by-field behavior, the diagnostics to prove it works, and a zero-downtime rollout with a revert path.

Rule Semantics & Field-by-Field Behavior #

A pg_hba.conf line has five positional fields — connection type, database, user, address, and method — and the parser evaluates entries strictly top-to-bottom, stopping at the first line whose type, database, user, and address all match. There is no “best match”; a broad host all all rule placed above a specific replication rule wins and can apply the wrong auth method. For a logical replication consumer the fields carry replication-specific meaning that differs from an ordinary client connection.

The canonical production rule for a dedicated consumer role is:

# TYPE     DATABASE      USER               ADDRESS          METHOD
hostssl    replication   repl_etl_svc       10.0.5.0/24      scram-sha-256
hostssl    target_db     repl_etl_svc       10.0.5.0/24      scram-sha-256

The two lines exist because a logical replication consumer makes two structurally different connections, and each matches a different rule:

Two version notes matter here. PG 14+: scram-sha-256 requires password_encryption = 'scram-sha-256' at the time the role’s password is set — a role whose password was hashed under md5 cannot authenticate against a scram-sha-256 line until the password is reset. PG 16+: the all keyword in the database field still excludes replication, but you can grant streaming rights without the REPLICATION role attribute by granting membership in pg_read_all_data and using a replication-database rule; the pg_hba.conf line is unchanged either way.

Diagnostic Patterns #

Prove the rule is correct before you point a consumer at it. The fastest confirmation of the streaming path is to open a replication connection by hand — dbname=replication instructs libpq to request the replication protocol, which is exactly what matches the hostssl replication line:

# Exercises the `replication` pseudo-database rule + TLS + SCRAM in one shot.
psql "host=publisher.internal dbname=replication user=repl_etl_svc sslmode=require" \
  -c "IDENTIFY_SYSTEM"

A clean run returns the system identifier, timeline, and current WAL LSN; FATAL: no pg_hba.conf entry for replication connection means the replication line is missing, out of order, or scoped to the wrong CIDR. To see which rule actually matched (or why none did), enable connection logging and read pg_hba_file_rules:

-- Which line the running server parsed, and any parse errors (PG 10+).
SELECT rule_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE 'replication' = ANY(database) OR database = '{all}'
ORDER BY rule_number;

Any non-null error column means the file failed to parse and the line is being ignored — the server keeps the last good ruleset until a clean reload. Turn on tracing so a rejection shows the source host and matched line:

ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
SELECT pg_reload_conf();

Once a consumer is streaming, confirm the slot it drives is healthy — a pg_hba.conf misconfiguration that intermittently rejects reconnects shows up as an inactive slot with a stalled LSN. Alert when active = false persists beyond about 60 s during steady state, and treat a growing retained_wal as an outage precursor:

SELECT slot_name,
       active,
       restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Safe Deployment Sequence #

pg_hba.conf changes take effect on reload, not restart, and a reload never drops existing sessions — so the rollout below is zero-downtime for already-connected consumers while you validate the new rule.

1. Stage the rule above the broad ones. Append the two hostssl lines for the new role before any general host all / hostssl all line. Order is first-match-wins, so a specific rule below a broad one is dead.

   hostssl  replication  repl_etl_svc  10.0.5.0/24  scram-sha-256
   hostssl  target_db    repl_etl_svc  10.0.5.0/24  scram-sha-256
   

2. Validate before activating. Parse-check without disturbing live traffic; a reload applies a valid file and safely ignores an invalid one while logging the error.

   pg_ctl reload -D /var/lib/postgresql/data
   

   SELECT rule_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;
   

3. Prove the path end-to-end with the IDENTIFY_SYSTEM probe above from a host inside 10.0.5.0/24, then from a host outside it to confirm the rule rejects as intended.

4. Add the deny-all backstop as the final line so an unscoped or misrouted attempt fails explicitly instead of falling through to a legacy rule:

   hostssl  all  all  0.0.0.0/0  reject
   

5. Revert procedure. Because a reload is non-destructive, rollback is a two-line file edit plus pg_ctl reload — remove the new hostssl lines and reload; already-established streaming connections continue until they next reconnect, at which point they will be rejected, giving you a clean, observable cutover rather than an abrupt drop.

For high-availability topologies, apply the identical file to every standby so a promoted node accepts the same consumers without reconfiguration — a divergent pg_hba.conf is the classic reason a CDC pipeline breaks only after failover.

Pipeline Integration #

A Python consumer must treat a pg_hba.conf rejection as a fatal, non-retryable class and everything else as backoff-and-retry — blindly retrying a FATAL: no pg_hba.conf entry burns connection attempts and hides a config error behind a wall of transient-looking logs. Parse the SQLSTATE (28000, invalid authorization) distinctly from operational errors:

import time
import psycopg2

FATAL_AUTH = {"28000", "28P01"}  # invalid_authorization_specification, invalid_password

def open_replication_conn(dsn, attempt=0):
    try:
        conn = psycopg2.connect(
            dsn,
            connection_factory=psycopg2.extras.LogicalReplicationConnection,
            sslmode="require",           # matches the hostssl rule
        )
        return conn
    except psycopg2.OperationalError as exc:
        code = getattr(exc, "pgcode", None)
        if code in FATAL_AUTH:
            # pg_hba scope / method / role is wrong — do NOT retry, page the operator.
            raise
        # transient (network, server restart): exponential backoff, capped at 30 s.
        time.sleep(min(2 ** attempt, 30))
        return open_replication_conn(dsn, attempt + 1)

Two integration rules keep the host-based auth layer from being the thing that breaks a running pipeline. First, never route replication connections through a connection pooler in transaction or statement mode: PgBouncer multiplexing breaks the one-slot-per-session guarantee and produces ERROR: replication slot "..." is active for PID .... Give the streaming connection a direct DSN and reserve the pool for the metadata/snapshot queries only. Second, keep the SCRAM secret in a rotation-aware store (Vault, AWS Secrets Manager, Kubernetes secret) so a credential roll updates the role password and the consumer DSN together — a rotated password with no matching scram-sha-256 line, or vice versa, presents as an auth failure that looks identical to a pg_hba.conf scoping bug.

The same rule governs whether a stream reaches a built-in subscription sync, a custom parser started from building a Python logical decoding plugin, or a Debezium connector — all three open the identical replication-database connection and must match the same hostssl line. Slot provisioning that has to survive these reconnects is covered in initializing replication slots, and the wire-level handshake this rule gates is detailed in WAL stream mechanics.

Authoritative references #

• PostgreSQL: The pg_hba.conf File — canonical field order, the hostssl/hostnossl distinction, and the replication keyword semantics.
• PostgreSQL: Authentication Methods — scram-sha-256 versus deprecated md5, and reject semantics.
• PostgreSQL: pg_hba_file_rules view — programmatic parse-error and rule-order inspection.
• PostgreSQL: Streaming Replication Protocol — START_REPLICATION / IDENTIFY_SYSTEM and why the replication pseudo-database is required.

Related guides #

• Security boundaries and permissions — the parent controls: roles, grants, TLS, and network isolation this rule sits inside.
• Replication slot types — the state anchor a rejected reconnect leaves stranded.
• WAL stream mechanics — the streaming handshake this authentication rule gates.
• Initializing replication slots — provisioning slots that survive consumer reconnects and failover.
• Back to PostgreSQL Logical Replication Architecture & Fundamentals