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

> On May 11, 2026, at 15:12, SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com> wrote:
>
> 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

This issue is being addressed in thread [1].

[1] https://www.postgresql.org/message-id/D908370F-2695-4231-851D-17179A6A6F2A%40gmail.com

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/