Thread

SUBSCRIPTION SERVER ALTER/DROP operations stuck when user mapping is dropped

SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-05-11T07:12:29Z

Hi Hackers,

My test script ran into usability issues with the new
CREATE SUBSCRIPTION ... SERVER feature.

A SERVER-backed subscription does not store a connection string in
pg_subscription.subconninfo but instead is regenerated on
every catalog read by calling ForeignServerConnectionString() which
in turn calls GetUserMapping(). GetUserMapping() raises an ERROR if
neither a per-user mapping nor a PUBLIC mapping exists for (owner, server).

DROP USER MAPPING is not blocked by the subscription, so a user
can drop it freely. Once they do, every DDL path that loads the
subscription propagates the error and the subscription is stuck.
The only way to unblock the operation is to add the mapping back.

CREATE EXTENSION postgres_fdw;
CREATE SERVER s FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'h', port '5432', dbname 'd');
CREATE USER MAPPING FOR CURRENT_USER SERVER s
OPTIONS (user 'foo', password 'bar');

CREATE SUBSCRIPTION sub SERVER s PUBLICATION p
WITH (connect = false, slot_name = NONE);

DROP USER MAPPING FOR CURRENT_USER SERVER s;

-- All these commands fail.

ALTER SUBSCRIPTION sub DISABLE;
ALTER SUBSCRIPTION sub OWNER TO bob;
DROP SUBSCRIPTION sub;
DROP SERVER s CASCADE;

We have a few options to address this:

(1) Add a pg_Depend edge from subscription to user mapping.
I rejected this idea because GetUserMapping() does dynamic resolution.
It first tries to connect with per-user mapping first and then fall back
to the PUBLIC mapping if no specific one exists. It also doesn't compose
with
ALTER SUBSCRIPTION ... OWNER TO,
which changes which mapping is consulted. We don't have an example
today that pg_depend edge points at an object that's chosen
by runtime resolution rather than by name.

(2) Make ALTER/DROP SUBSCRIPTION tolerant of regeneration failures
DropSubscription() already does this when USAGE on the foreign server
has been revoked. We could extend the same fallback to cover
missing user mapping failures.

if (aclresult != ACLCHECK_OK)
{
/*
* Unable to generate connection string because permissions on the
* foreign server have been removed. Follow the same logic as an
* unusable subconninfo (which will result in an ERROR later
* unless slot_name = NONE).
*/
err = psprintf(_("subscription owner \"%s\" does not have permission on
foreign server \"%s\""),
  GetUserNameFromId(form->subowner, false),
  server->servername);
conninfo = NULL;
}

(3) Document this behavior in drop user mapping documentation.
The failure is silent at DROP USER MAPPING time and only manifests later
when someone tries to modify the subscription. By then the user is already
stuck.

From a usability standpoint I expect either the user mapping should be
blocked or
ALTER/DROP subscription operations continue to succeed even if the user
mapping
doesn't exist. I am thinking option 2 is reasonable. Thoughts?

Thanks,
Satya