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

Hi

On Mon, May 11, 2026 at 12:25 AM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:

>
>
> > 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
> <https://www.highgo.com/>

Thanks, will review that.

>
>
>
>
>