Non-superuser Subscription Owners: Deep Technical Analysis

Core Problem

PostgreSQL's logical replication system was designed with the assumption that subscriptions would always be owned and operated by superusers. This created two fundamental problems:

1. Security vulnerability: Subscription apply workers execute with superuser privileges, meaning any table owner on a subscriber node can escalate to superuser by attaching malicious triggers, default expressions, or index expressions to replicated tables. The apply worker will execute this code as the subscription owner (superuser), giving the attacker full control.

2. Usability limitation: There is no way to delegate subscription management to non-superusers, which is particularly problematic for cloud database providers (AWS, Azure, EDB) who need to allow customers to manage replication without granting full superuser access.

The thread reveals that this is not merely a convenience issue but a serious architectural security defect. As Robert Haas dramatically put it: if a non-superuser owns a table that is part of a subscription, they can "instantly hack superuser" — making logical replication fundamentally insecure in any multi-tenant environment.

Evolution of the Solution

Phase 1: Permission Checks on Apply Workers (v15, committed Jan 2022)

Mark Dilger's initial work (committed by Jeff Davis) made apply workers respect the privileges of the subscription owner rather than always acting as superuser. Key changes:

• ACL checks (INSERT/UPDATE/DELETE) performed against the subscription owner's privileges for each operation
• Row-level security enforcement: if the target table has RLS policies enforced against the subscription owner, replication errors out (coarse-grained but safe)
• Ownership changes detected at transaction boundaries (not per-row, for performance reasons)
• The maybe_reread_subscription() function extended to detect ownership changes

This closed the gap where revoking superuser from a subscription owner had no effect on the apply worker's actual privileges.

Phase 2: pg_create_subscription Predefined Role (v16, committed Mar 2023)

Robert Haas's patch introduced pg_create_subscription, allowing non-superusers with this role to create and manage subscriptions. Key design decisions:

• password_required attribute: Modelled on postgres_fdw, defaults to true. When true (and owner is not superuser), the connection must use a password, preventing the "wraparound-to-superuser" attack via passwordless loopback connections.
• Only superusers can set password_required=false: This mirrors postgres_fdw's user_mapping security model.
• Connection string validation: Uses check_conninfo() to examine connection parameters that might access local files (sslcert, sslkey, sslrootcert, etc.), requiring pg_read_server_files membership if such parameters are present.
• ALTER SUBSCRIPTION ... OWNER TO requires CREATE privilege on the database and ability to SET ROLE to the new owner (matching CREATE SCHEMA semantics).

Phase 3: Apply-as-Table-Owner (proposed, separate thread)

Robert Haas proposed running replication as the table owner rather than the subscription owner. This addresses the trigger/expression code execution vulnerability by ensuring that code attached to tables runs with the table owner's privileges (who is already exposed to that risk via REINDEX, VACUUM, etc.).

Key Technical Design Decisions and Tradeoffs

Transaction Boundary vs. Per-Row Permission Checking

A significant debate occurred over when to detect privilege changes:

• Amit Kapila argued for per-row checking, noting that interactive sessions reflect privilege revocations immediately within a transaction.
• Robert Haas and Jeff Davis argued that transaction-boundary detection is sufficient, noting that:
  • Logical replication is non-interactive (like COPY FROM or INSERT INTO SELECT)
  • Per-row maybe_reread_subscription() could limit throughput to ~30,000 TPS due to lock manager overhead
  • Locking the subscription per-transaction would block ALTER SUBSCRIPTION during long transactions
  • The cost is acceptable since it's documented behavior

The consensus was transaction-boundary detection, with documentation noting the behavior.

Connection String Security Model

Three competing approaches emerged:

1. Password requirement (implemented): Mirror postgres_fdw's approach — require password-based authentication unless superuser or password_required=false. Blocks loopback-to-superuser attacks.

2. Separate connection object (Jeff Davis's proposal): Introduce CREATE SUBSCRIPTION ... SERVER using existing foreign server infrastructure. Separates "who can create subscriptions" from "who can specify connection strings." Would use existing USAGE grants on SERVER objects.

3. Reverse pg_hba.conf (Robert Haas's proposal): A configuration file controlling outbound connections, analogous to how pg_hba.conf controls inbound connections. Would allow subnet-based filtering, authentication method requirements, and per-component rules.

4. require_auth libpq option (Jacob Champion/Andres Freund): Force specific authentication methods from the client side, preventing ambient authentication from being used. Landed as a separate feature in PG16.

The password requirement was implemented as the pragmatic choice, with acknowledgment that it's imperfect (blocks legitimate passwordless use cases while not covering all attack vectors like GSS/Kerberos credential theft).

Predefined Role Granularity

Jeff Davis argued strongly for two separate roles:

• pg_create_subscription — for subscription mechanics
• pg_create_connection — for connection string usage

Robert Haas rejected this, arguing that having two roles both required to do a single thing with no independent utility is confusing. He committed with only pg_create_subscription, noting that pg_create_connection should be introduced alongside whatever patch adds CREATE SUBSCRIPTION ... SERVER.

SECURITY INVOKER vs. SECURITY DEFINER Debate

A major tangent (but architecturally important) explored how code attached to tables executes:

• Jeff Davis argued that SECURITY INVOKER is fundamentally the source of most security problems — the invoker (Bob) cannot protect themselves from malicious code written by the definer (Alice), and SET ROLE/SESSION AUTHORIZATION can be trivially bypassed by the definer's code.
• Robert Haas proposed a "sandboxing" model with two levels:
  • Full sandbox: Code can only compute, call other code (with permission checks), read session state, and produce messages. Cannot access tables, modify state, or exercise most privileges.
  • Partial sandbox: Can do almost anything except modify session state (no SET, DEALLOCATE, COMMIT, etc.).
• Rules based on "trust" relationships between users.

Provenances Mechanism (2026 proposal)

Robert Haas's most ambitious proposal introduces a Provenances object tracking the chain of custody for code execution:

• Every expression node gets stamped with a "provenance index" identifying who provided it and why it's being executed
• Allows enforcement policies like "block DDL unless entire provenance chain is trusted"
• Requires pervasive changes throughout planner, executor, rewriter, and utility commands
• Addresses the "confused deputy" problem where an attacker induces a trusted user to execute their own (legitimate but powerful) code at an attacker-chosen time

Unresolved Issues

1. RLS enforcement gap: COPY FROM (used in table sync) doesn't support RLS. The current solution errors out for RLS-enabled tables with non-superuser subscription owners.

2. Partition routing: ExecUpdate converts updates to DELETE+INSERT when partition constraints fail; subscriber-side handling is incomplete.

3. Connection security completeness: The password_required mechanism doesn't address GSS/Kerberos credential forwarding, peer/ident authentication ambient authority, or file access via service files.

4. Trigger security in replication: Any table owner can still compromise the subscription owner by attaching malicious triggers, unless apply-as-table-owner is adopted.

5. The provenances infrastructure: Requires massive code changes touching nearly every expression evaluation path. Verification is extremely difficult. The 2026 discussion shows this remains work-in-progress.

Bugs Found and Fixed

• Assert failure in apply worker (March 2023): superuser_arg() called outside a transaction context, triggering Assert(IsTransactionState()) in catcache. Fixed by moving the check inside the transaction boundary.
• Same bug in table sync worker (May 2023): Identical issue in LogicalRepSyncTableStart. Fixed by moving the call to where GetSubscriptionRelState() already has a transaction.
• Test performance (January 2022): Tom Lane complained that the new test took 22 seconds (vs. 2-7 seconds for other tests). Jeff Davis reduced test cases from 100 to 14.
• Missing \dRs+ display (April 2023): password_required not shown in psql's subscription display. Fixed by Vignesh.