SUBSCRIPTION SERVER ALTER/DROP Operations Stuck When User Mapping Is Dropped

Core Problem

This thread exposes a dependency management gap in PostgreSQL's new CREATE SUBSCRIPTION ... SERVER feature, where subscriptions can reference a foreign server instead of storing a literal connection string in pg_subscription.subconninfo.

Architectural Root Cause

The design relies on runtime resolution of connection information rather than static storage. When a subscription is backed by a SERVER object:

1. pg_subscription.subconninfo is left empty
2. On every catalog read that needs the connection string, the system calls ForeignServerConnectionString()
3. This function internally calls GetUserMapping(owner, server), which performs a two-level lookup: first for the specific subscription owner, then falling back to the PUBLIC mapping
4. GetUserMapping() raises a hard ERROR if neither mapping exists

The critical flaw is that no dependency relationship exists between the subscription and the user mapping, so DROP USER MAPPING succeeds silently. Once the mapping is gone, every subsequent DDL operation on the subscription—including ALTER SUBSCRIPTION ... DISABLE, ALTER ... OWNER TO, DROP SUBSCRIPTION, and even DROP SERVER ... CASCADE—fails because each of these code paths loads the subscription's connection info, triggering the GetUserMapping() error.

This creates an unrecoverable state from the user's perspective: the subscription cannot be altered, disabled, or dropped without first recreating the user mapping.

Proposed Solutions

Option 1: pg_depend Edge from Subscription to User Mapping

Rejected by the reporter. The reasoning is sound:

• GetUserMapping() uses dynamic resolution (per-user → PUBLIC fallback), so there's no single stable target object for the dependency
• ALTER SUBSCRIPTION ... OWNER TO changes which user mapping is consulted at runtime, meaning the dependency edge would need to be updated on ownership changes
• PostgreSQL's pg_depend system has no precedent for tracking objects selected by runtime resolution rather than by name

This would introduce a novel and fragile pattern into the catalog dependency system.

Option 2: Make ALTER/DROP SUBSCRIPTION Tolerant of Regeneration Failures

Preferred approach. The existing code already handles a similar case: when USAGE privilege on the foreign server has been revoked, DropSubscription() falls back gracefully rather than erroring out. The proposal is to extend this same fallback pattern to cover missing user mapping errors.

The pattern would:

• Catch the missing user mapping condition
• Set conninfo = NULL and produce a warning/error message
• Allow the DDL operation to proceed (which will fail later only if a slot needs to be dropped on the remote, i.e., unless slot_name = NONE)

This is architecturally consistent with how the code already handles permission revocation, and avoids the complexity of dependency tracking for dynamically-resolved objects.

Option 3: Documentation Only

Document that dropping a user mapping while a SERVER-backed subscription exists will leave the subscription in an unmodifiable state. This is the weakest option since the failure is non-obvious and recovery requires knowing to recreate the mapping.

Relationship to Existing Work

The issue was immediately identified as a duplicate of ongoing work in another thread. This suggests the problem was already known to the developers working on the SERVER-backed subscription feature, and a fix is in progress.

Technical Implications

This bug reveals a broader design tension in the SERVER-backed subscription feature: the choice to regenerate connection strings dynamically rather than caching them introduces fragile implicit dependencies that the traditional pg_depend mechanism cannot easily model. The existing handling of revoked USAGE privileges demonstrates that the developers anticipated some forms of broken resolution, but the user mapping case was missed.

The correct architectural principle here is: any DDL path that may need to destroy an object must be resilient to failures in resolving that object's transitive dependencies. If you can't connect to the remote to drop a slot, that shouldn't prevent you from dropping the local subscription metadata.