PostgreSQL Logical Replication: Map Each Privilege Hop

Context

PostgreSQL logical replication moves table changes through several identities. A role connects to the publisher, initial synchronization reads table data, and subscriber workers apply changes under documented ownership rules. A permission error at one hop should be fixed at that hop. Granting superuser everywhere obscures the real trust relationship and makes later ownership changes harder to reason about.

Prerequisites

Record the PostgreSQL major versions on both sides, publication and subscription names, table owners, connection role, subscription owner, and any row-security policies. This guide follows PostgreSQL 18 documentation; older versions have different subscription privilege behavior. Obtain authorized access to replication logs and catalog metadata without exposing the subscription connection secret. Prepare a representative test publication and subscriber.

Trace access in order

1. Verify publisher connectivity as the intended replication role. It needs the documented login and replication attributes, plus a matching HBA rule. Review output-plugin restrictions for the installed version. Network reachability and a successful ordinary SQL login do not prove that a logical replication connection can start.
2. Check initial-copy access separately. The connection role needs the required read privileges on published tables for synchronization. Test newly added tables as well as already synchronized ones. A subscription that streams existing tables successfully can still fail when initial copy begins for a table owned by another team or schema.
3. Review publication creation and table ownership against the actual publication design. Do not treat a filtered publication as a complete authorization boundary for a role that can connect to other publications in the same database. Review every available exposure path and the documented effect of row-security policies. Keep the intended data-sharing scope explicit.
4. Inspect subscriber apply ownership and role-switching requirements. Verify that the subscription owner can perform the required ownership transition for target tables. The run_as_owner option changes this trust relationship and should not be enabled merely to silence a permission error. Rehearse an ownership change and a newly published table so the operational procedure covers future schema changes.

Diagnostic table

Illustrative scenario

A reporting subscription works until a new finance table is added. Initial copy fails because its owner differs and the replication role lacks the intended read access. The team grants only the approved table access, checks the subscriber ownership path, and tests that unrelated tables remain outside the sharing design. The existing healthy stream had not exercised this new privilege boundary.

Correct and verify

Apply the narrow, reviewed privilege correction and observe the affected table's synchronization state and subsequent changes. Use representative insert, update, and delete tests only in the controlled test environment. Inspect lag and worker errors after reconnecting; publisher-side privilege checks can have connection-lifecycle implications. Confirm that the business data scope remains correct as well as the mechanics of replication.

Operating procedure

Maintain a role-and-owner map with the replication definition. Review it whenever tables, schemas, policies, or owners change. Monitor synchronization failures separately from steady-state apply failures. Include a permissions test for newly added tables in release acceptance, and document who approves expanded data exposure so a convenient replication fix cannot silently become a broader sharing decision.

Explore Clouvenix cloud security for implementation and operational handover.

Technical reference

• www.postgresql.org documentation
• www.postgresql.org documentation