Hi On Mon, May 11, 2026 at 12:25 AM Chao Li <[email protected]> wrote:
> > > > On May 11, 2026, at 15:12, SATYANARAYANA NARLAPURAM < > [email protected]> 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. > > > > >
