Horton

Thread

  1. 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

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

    Chao Li <li.evan.chao@gmail.com> — 2026-05-11T07:25:31Z

    
> On May 11, 2026, at 15:12, SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.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/

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

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

    Hi

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

>
>
> > On May 11, 2026, at 15:12, SATYANARAYANA NARLAPURAM <
> satyanarlapuram@gmail.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.

>
>
>
>
>