Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wFMlK-0051EB-0F for pgsql-hackers@arkaria.postgresql.org; Wed, 22 Apr 2026 01:52:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wFMlJ-00BBFg-12 for pgsql-hackers@arkaria.postgresql.org; Wed, 22 Apr 2026 01:52:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wFMlI-00BBFX-2T for pgsql-hackers@lists.postgresql.org; Wed, 22 Apr 2026 01:52:21 +0000 Received: from mail-pj1-x1029.google.com ([2607:f8b0:4864:20::1029]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wFMlG-00000002B98-0pqK for pgsql-hackers@postgresql.org; Wed, 22 Apr 2026 01:52:19 +0000 Received: by mail-pj1-x1029.google.com with SMTP id 98e67ed59e1d1-35fc0d7c310so3344762a91.1 for ; Tue, 21 Apr 2026 18:52:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1776822737; x=1777427537; darn=postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=ICPZmabsForBdahw8sxeowny7htFFeT7Mz9IcYKucQw=; b=cntSrL6eK1pMn7mGCzt3EqqNxwpfCkYn8LC0r6TdJ2oHOtzTrXOigA+WSwlaGKpjWb qHcBklb9kwJ3sIjeI8m/svVIs8C6NrvyEuqArHObJ8QQj8cQwCCs08S0mgRO55jVEy9s ttWNEQoPurR+HBXt3Xbz9JGlvX0xrVZ1rmul13XwOjHUQ0WnYmK2bspIsWCu3F8CE9eU rOG7mZW8laaW0o/+nGpT9DxILq0mgzQLMPYy+uuAVJ31Q/x6BvLn5yUJdk3Cf9tpOo+Z pp2q8U+7JM0dIw6rYmq60LjsI5Pe7L/NFNZ6JcGoKIU6c3r05tHRGP67QyucbK6jsv2F UMsg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776822737; x=1777427537; h=to:date:message-id:subject:mime-version:from:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ICPZmabsForBdahw8sxeowny7htFFeT7Mz9IcYKucQw=; b=prKDjyubi/EhpBL0zKFNvmFEa8uQBwJRli2DwYkGMkfBq6nJn6QI4p5OPpQsD0KGaT ZYGO1yszCswFQo2IlreFIcill196pp6WGkz3MRucQC3+/l5HjV538PVJ6q3vSCSWgT2R h2kneQ+oE2yQ/Y5s8oS1xQwW54/acfrGcZSsw0P+bH4ZHFW/YdjrFwTMGlROoxtYurSd jE2K3hrjiSbTvXvxzZ+CSq9bRP1dMsGUEnN3XINhQ3nxXifZ2SKBZO5/PaEBhmboGRID FfZDkIpKJv5aD4iefz3q9F1tbMzm+yX2WvgEpzTFUBj3gvI9kUaymnA+U73o67KiyIJQ zPQA== X-Gm-Message-State: AOJu0YzjgyXHqKOk+KuQhWb8TPiTjjSANCKyIZRrCybVyhEeA+3HLNYI n0Fj3nrtRpv2SfzO1bCymiHJ9VRHsfwiay4jLQdQqFK76UFib3IQz4ohG8cemmwS X-Gm-Gg: AeBDievrt81QFL6KJvyGwKjSk1eCP0VrMI1sftBR15A5B0Ivf3yfBy6aWlYXonPZzVk fUhidj03GnJQsx7pUvRs7J9VovM0ppB9Gavb96ClNnDUxtK8wl5pvZaW0r0Y4YeekKc2IBo0rnN H3U/rWzmvJfvriM78h8IYD8ypfC5/kC2Yd/pKj7L3JkptoCexxkCkR8p2dDN5bttHzfPAbSrQ0j LHVrAl5H2Y8/H9Um9S1CKwgVM/LXpQm445TWGV3qHdv2RakjpIhxhAFXbTJABbUF6NFopaeCY7d PB3g7IuvkuHBQpJeZsKuXcg3zb+oMLmUXUH0tvb7wbgS9d4shmw751uM6nl5e+xl+LOBDFSgBeG 7PGv/in5UAiNXdHOY8az5eIIj9vDLStEA901LdHo6lXLhFXe98dmvb40Zp84DY3aGCJrqjQID+i n4P+MNFdhRNepg8hAkwD+P3I9e/+bphVrP/3glz9SImA== X-Received: by 2002:a17:90a:dfd0:b0:35e:30bc:96ed with SMTP id 98e67ed59e1d1-36140402361mr20336599a91.10.1776822736388; Tue, 21 Apr 2026 18:52:16 -0700 (PDT) Received: from smtpclient.apple ([45.32.121.103]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-361418c3944sm18623514a91.8.2026.04.21.18.52.14 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 21 Apr 2026 18:52:15 -0700 (PDT) From: Chao Li Content-Type: multipart/mixed; boundary="Apple-Mail=_9082CE91-401A-445D-BBC3-9620ED3526DF" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Bug in ALTER SUBSCRIPTION ... SERVER / ... CONNECTION with broken old server Message-Id: Date: Wed, 22 Apr 2026 09:51:36 +0800 To: PostgreSQL-development X-Mailer: Apple Mail (2.3864.400.21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_9082CE91-401A-445D-BBC3-9620ED3526DF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi, I tested the v19 new feature CREATE SUBSCRIPTION ... SERVER yesterday, = and found an issue: once the old server becomes broken, the subscription = cannot be recovered by switching it to a good server. Here is a repro: ``` evantest=3D# CREATE EXTENSION postgres_fdw; CREATE EXTENSION # Create two servers evantest=3D# CREATE SERVER old_srv FOREIGN DATA WRAPPER postgres_fdw evantest-# OPTIONS (host '127.0.0.1', dbname 'postgres', port '5432'); CREATE SERVER evantest=3D# CREATE SERVER new_srv FOREIGN DATA WRAPPER postgres_fdw evantest-# OPTIONS (host '127.0.0.1', dbname 'postgres', port '5432'); CREATE SERVER evantest=3D# CREATE USER MAPPING FOR CURRENT_USER SERVER old_srv evantest-# OPTIONS (user 'dummy', password 'dummy'); CREATE USER MAPPING evantest=3D# CREATE USER MAPPING FOR CURRENT_USER SERVER new_srv evantest-# OPTIONS (user 'dummy', password 'dummy'); CREATE USER MAPPING # Add old_server to a subscription evantest=3D# CREATE SUBSCRIPTION sub_bug SERVER old_srv evantest-# PUBLICATION pub_does_not_matter evantest-# WITH (connect =3D false, slot_name =3D NONE); WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication = slot, enable the subscription, and alter the subscription to refresh = publications. CREATE SUBSCRIPTION # Break old_srv evantest=3D# DROP USER MAPPING FOR CURRENT_USER SERVER old_srv; DROP USER MAPPING # Fail to switch to a good server because old_srv is broken evantest=3D# ALTER SUBSCRIPTION sub_bug SERVER new_srv; ERROR: user mapping not found for user "chaol", server "old_srv" evantest=3D# ALTER SUBSCRIPTION sub_bug CONNECTION 'host=3D127.0.0.1 = dbname=3Dpostgres port=3D5432 user=3Ddummy password=3Ddummy'; ERROR: user mapping not found for user "chaol", server "old_srv" ``` In AlterSubscription(), this comment made me think this is a bug: ``` /* * Skip ACL checks on the subscription's foreign server, if any. = If * changing the server (or replacing it with a raw connection), = then the * old one will be removed anyway. If changing something = unrelated, * there's no need to do an additional ACL check here; that will = be done * by the subscription worker anyway. */ sub =3D GetSubscription(subid, false, false); ``` The comment explicitly says to skip ACL checks on the old server because = it will be removed anyway. But after looking into GetSubscription(), I found that when the aclcheck = parameter is false, it still calls ForeignServerConnectionString(). I = think that is the root cause of the bug. To fix this, I worked out a solution that stores the server OID in = Subscription, and only calls ForeignServerConnectionString()lazily when = sub->conninfo is actually needed. I also added a new test case to cover = this scenario. Without the fix, the new test fails. See attached patch for details. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_9082CE91-401A-445D-BBC3-9620ED3526DF Content-Disposition: attachment; filename=v1-0001-Allow-altering-subscription-server-connection-wit.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v1-0001-Allow-altering-subscription-server-connection-wit.patch" Content-Transfer-Encoding: quoted-printable =46rom=20fd461cf3f5f1c96e5789dd2d3ac113619e776477=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Wed,=2022=20Apr=202026=2009:14:27=20+0800=0ASubject:=20[PATCH=20= v1]=20Allow=20altering=20subscription=20server/connection=20without=0A=20= resolving=20old=20conninfo=0A=0AWhen=20a=20subscription=20uses=20a=20= foreign=20server,=20GetSubscription()=20resolves=20the=0Apublisher=20= connection=20string=20immediately.=20That=20can=20fail=20if=20the=20= subscription=0Aowner=20no=20longer=20has=20USAGE=20on=20the=20server=20= or=20no=20longer=20has=20a=20valid=20user=0Amapping.=0A=0AThis=20becomes=20= a=20problem=20for=20ALTER=20SUBSCRIPTION=20commands=20such=20as=20...=20= SERVER=0Aor=20...=20CONNECTION,=20because=20they=20may=20need=20to=20= update=20the=20subscription=20away=0Afrom=20the=20broken=20= server/conninfo,=20but=20currently=20fail=20while=20trying=20to=20= resolve=0Athe=20old=20one=20first.=0A=0AFix=20this=20by=20storing=20the=20= subscription's=20server=20OID=20in=20the=20in-memory=0ASubscription=20= object=20and=20loading=20conninfo=20lazily=20only=20when=20it=20is=20= actually=0Aneeded.=20Add=20GetSubscriptionConninfo()=20to=20fetch=20the=20= connection=20string=20on=0Ademand=20in=20the=20subscription=20memory=20= context.=0A=0AThis=20allows=20ALTER=20SUBSCRIPTION=20...=20SERVER=20and=20= ...=20CONNECTION=20to=20succeed=0Awithout=20requiring=20access=20to=20= the=20previous=20server=20or=20user=20mapping,=20while=0Apreserving=20= the=20existing=20behavior=20for=20operations=20that=20really=20need=20to=20= connect=0Ato=20the=20publisher.=0A=0AAdd=20regression=20test=20coverage=20= for=20both=20cases.=0A=0AAuthor:=20Chao=20Li=20=0A= Reviewed-by:=0ADiscussion:=20https://postgr.es/m/=0A---=0A=20= src/backend/catalog/pg_subscription.c=20=20=20=20=20=20|=2046=20= +++++++++++++++++-----=0A=20src/backend/commands/subscriptioncmds.c=20=20= =20=20|=20=209=20+++--=0A=20src/include/catalog/pg_subscription.h=20=20=20= =20=20=20|=20=202=20+=0A=20src/test/regress/expected/subscription.out=20= |=2033=20++++++++++++++++=0A=20src/test/regress/regress.c=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=20=202=20+=0A=20= src/test/regress/sql/subscription.sql=20=20=20=20=20=20|=2027=20= +++++++++++++=0A=206=20files=20changed,=20106=20insertions(+),=2013=20= deletions(-)=0A=0Adiff=20--git=20a/src/backend/catalog/pg_subscription.c=20= b/src/backend/catalog/pg_subscription.c=0Aindex=20= 1f1fdc75af6..3f1e6e26066=20100644=0A---=20= a/src/backend/catalog/pg_subscription.c=0A+++=20= b/src/backend/catalog/pg_subscription.c=0A@@=20-118,32=20+118,33=20@@=20= GetSubscription(Oid=20subid,=20bool=20missing_ok,=20bool=20aclcheck)=0A=20= =09sub->retaindeadtuples=20=3D=20subform->subretaindeadtuples;=0A=20=09= sub->maxretention=20=3D=20subform->submaxretention;=0A=20=09= sub->retentionactive=20=3D=20subform->subretentionactive;=0A+=09= sub->serverid=20=3D=20subform->subserver;=0A+=09sub->conninfo=20=3D=20= NULL;=0A=20=0A=20=09/*=20Get=20conninfo=20*/=0A-=09if=20= (OidIsValid(subform->subserver))=0A+=09if=20(OidIsValid(sub->serverid))=0A= =20=09{=0A=20=09=09AclResult=09aclresult;=0A=20=09=09ForeignServer=20= *server;=0A=20=0A-=09=09server=20=3D=20= GetForeignServer(subform->subserver);=0A+=09=09server=20=3D=20= GetForeignServer(sub->serverid);=0A=20=0A=20=09=09/*=20recheck=20ACL=20= if=20requested=20*/=0A=20=09=09if=20(aclcheck)=0A=20=09=09{=0A=20=09=09=09= aclresult=20=3D=20object_aclcheck(ForeignServerRelationId,=0A-=09=09=09=09= =09=09=09=09=09=09subform->subserver,=0A+=09=09=09=09=09=09=09=09=09=09= sub->serverid,=0A=20=09=09=09=09=09=09=09=09=09=09subform->subowner,=20= ACL_USAGE);=0A=20=0A=20=09=09=09if=20(aclresult=20!=3D=20ACLCHECK_OK)=0A=20= =09=09=09=09ereport(ERROR,=0A-=09=09=09=09=09=09= (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A-=09=09=09=09=09=09=20= errmsg("subscription=20owner=20\"%s\"=20does=20not=20have=20permission=20= on=20foreign=20server=20\"%s\"",=0A-=09=09=09=09=09=09=09=09= GetUserNameFromId(subform->subowner,=20false),=0A-=09=09=09=09=09=09=09=09= server->servername)));=0A+=09=09=09=09=09=09= errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A+=09=09=09=09=09=09= errmsg("subscription=20owner=20\"%s\"=20does=20not=20have=20permission=20= on=20foreign=20server=20\"%s\"",=0A+=09=09=09=09=09=09=09=20=20=20= GetUserNameFromId(subform->subowner,=20false),=0A+=09=09=09=09=09=09=09=20= =20=20server->servername));=0A+=09=09=09sub->conninfo=20=3D=20= ForeignServerConnectionString(subform->subowner,=0A+=09=09=09=09=09=09=09= =09=09=09=09=09=09=09=20=20server);=0A=20=09=09}=0A-=0A-=09=09= sub->conninfo=20=3D=20ForeignServerConnectionString(subform->subowner,=0A= -=09=09=09=09=09=09=09=09=09=09=09=09=09=20=20server);=0A=20=09}=0A=20=09= else=0A=20=09{=0A@@=20-197,6=20+198,31=20@@=20GetSubscription(Oid=20= subid,=20bool=20missing_ok,=20bool=20aclcheck)=0A=20=09return=20sub;=0A=20= }=0A=20=0A+/*=0A+=20*=20Return=20the=20subscription's=20connection=20= string,=20loading=20it=20into=20the=0A+=20*=20subscription=20memory=20= context=20if=20necessary.=0A+=20*=0A+=20*=20GetSubscription=20must=20be=20= called=20earlier=20to=20set=20sub->serverid,=20because=20ACL=0A+=20*=20= checks=20are=20performed=20there.=0A+=20*/=0A+char=20*=0A= +GetSubscriptionConnInfo(Subscription=20*sub)=0A+{=0A+=09MemoryContext=20= oldcxt;=0A+=0A+=09if=20(sub->conninfo)=0A+=09=09return=20sub->conninfo;=0A= +=0A+=09Assert(OidIsValid(sub->serverid));=0A+=0A+=09oldcxt=20=3D=20= MemoryContextSwitchTo(sub->cxt);=0A+=09sub->conninfo=20=3D=20= ForeignServerConnectionString(sub->owner,=0A+=09=09=09=09=09=09=09=09=09=09= =09=09=20=20GetForeignServer(sub->serverid));=0A+=09= MemoryContextSwitchTo(oldcxt);=0A+=0A+=09return=20sub->conninfo;=0A+}=0A= +=0A=20/*=0A=20=20*=20Return=20number=20of=20subscriptions=20defined=20= in=20given=20database.=0A=20=20*=20Used=20by=20dropdb()=20to=20check=20= if=20database=20can=20indeed=20be=20dropped.=0Adiff=20--git=20= a/src/backend/commands/subscriptioncmds.c=20= b/src/backend/commands/subscriptioncmds.c=0Aindex=20= d512e87cfe3..478d19d7a26=20100644=0A---=20= a/src/backend/commands/subscriptioncmds.c=0A+++=20= b/src/backend/commands/subscriptioncmds.c=0A@@=20-1026,7=20+1026,8=20@@=20= AlterSubscription_refresh(Subscription=20*sub,=20bool=20copy_data,=0A=20=0A= =20=09/*=20Try=20to=20connect=20to=20the=20publisher.=20*/=0A=20=09= must_use_password=20=3D=20sub->passwordrequired=20&&=20= !sub->ownersuperuser;=0A-=09wrconn=20=3D=20walrcv_connect(sub->conninfo,=20= true,=20true,=20must_use_password,=0A+=09wrconn=20=3D=20= walrcv_connect(GetSubscriptionConnInfo(sub),=20true,=20true,=0A+=09=09=09= =09=09=09=09must_use_password,=0A=20=09=09=09=09=09=09=09sub->name,=20= &err);=0A=20=09if=20(!wrconn)=0A=20=09=09ereport(ERROR,=0A@@=20-1278,7=20= +1279,8=20@@=20AlterSubscription_refresh_seq(Subscription=20*sub)=0A=20=0A= =20=09/*=20Try=20to=20connect=20to=20the=20publisher.=20*/=0A=20=09= must_use_password=20=3D=20sub->passwordrequired=20&&=20= !sub->ownersuperuser;=0A-=09wrconn=20=3D=20walrcv_connect(sub->conninfo,=20= true,=20true,=20must_use_password,=0A+=09wrconn=20=3D=20= walrcv_connect(GetSubscriptionConnInfo(sub),=20true,=20true,=0A+=09=09=09= =09=09=09=09must_use_password,=0A=20=09=09=09=09=09=09=09sub->name,=20= &err);=0A=20=09if=20(!wrconn)=0A=20=09=09ereport(ERROR,=0A@@=20-2129,7=20= +2131,8=20@@=20AlterSubscription(ParseState=20*pstate,=20= AlterSubscriptionStmt=20*stmt,=0A=20=09=09=20*=20available.=0A=20=09=09=20= */=0A=20=09=09must_use_password=20=3D=20sub->passwordrequired=20&&=20= !sub->ownersuperuser;=0A-=09=09wrconn=20=3D=20= walrcv_connect(stmt->conninfo=20?=20stmt->conninfo=20:=20sub->conninfo,=0A= +=09=09wrconn=20=3D=20walrcv_connect(stmt->conninfo=20?=20stmt->conninfo=20= :=0A+=09=09=09=09=09=09=09=09GetSubscriptionConnInfo(sub),=0A=20=09=09=09= =09=09=09=09=09true,=20true,=20must_use_password,=20sub->name,=0A=20=09=09= =09=09=09=09=09=09&err);=0A=20=09=09if=20(!wrconn)=0Adiff=20--git=20= a/src/include/catalog/pg_subscription.h=20= b/src/include/catalog/pg_subscription.h=0Aindex=20= a6a2ad1e49c..4dfc6dfce17=20100644=0A---=20= a/src/include/catalog/pg_subscription.h=0A+++=20= b/src/include/catalog/pg_subscription.h=0A@@=20-164,6=20+164,7=20@@=20= typedef=20struct=20Subscription=0A=20=09=09=09=09=09=09=09=09=09=20*=20= and=20the=20retention=20duration=20has=20not=0A=20=09=09=09=09=09=09=09=09= =09=20*=20exceeded=20max_retention_duration,=20when=0A=20=09=09=09=09=09=09= =09=09=09=20*=20defined=20*/=0A+=09Oid=09=09=09serverid;=09=09/*=20Oid=20= of=20the=20foreign=20server,=20if=20any=20*/=0A=20=09char=09=20=20=20= *conninfo;=09=09/*=20Connection=20string=20to=20the=20publisher=20*/=0A=20= =09char=09=20=20=20*slotname;=09=09/*=20Name=20of=20the=20replication=20= slot=20*/=0A=20=09char=09=20=20=20*synccommit;=09=09/*=20Synchronous=20= commit=20setting=20for=20worker=20*/=0A@@=20-214,6=20+215,7=20@@=20= typedef=20struct=20Subscription=0A=20=0A=20extern=20Subscription=20= *GetSubscription(Oid=20subid,=20bool=20missing_ok,=0A=20=09=09=09=09=09=09= =09=09=09=20bool=20aclcheck);=0A+extern=20char=20= *GetSubscriptionConnInfo(Subscription=20*sub);=0A=20extern=20void=20= DisableSubscription(Oid=20subid);=0A=20=0A=20extern=20int=09= CountDBSubscriptions(Oid=20dbid);=0Adiff=20--git=20= a/src/test/regress/expected/subscription.out=20= b/src/test/regress/expected/subscription.out=0Aindex=20= 7e3cabdb93f..c0db7636713=20100644=0A---=20= a/src/test/regress/expected/subscription.out=0A+++=20= b/src/test/regress/expected/subscription.out=0A@@=20-174,16=20+174,49=20= @@=20WARNING:=20=20subscription=20was=20created,=20but=20is=20not=20= connected=0A=20HINT:=20=20To=20initiate=20replication,=20you=20must=20= manually=20create=20the=20replication=20slot,=20enable=20the=20= subscription,=20and=20alter=20the=20subscription=20to=20refresh=20= publications.=0A=20DROP=20USER=20MAPPING=20FOR=20= regress_subscription_user3=20SERVER=20test_server;=0A=20RESET=20SESSION=20= AUTHORIZATION;=0A+CREATE=20SERVER=20test_server2=20FOREIGN=20DATA=20= WRAPPER=20test_fdw;=0A+GRANT=20USAGE=20ON=20FOREIGN=20SERVER=20= test_server2=20TO=20regress_subscription_user3;=0A+SET=20SESSION=20= AUTHORIZATION=20regress_subscription_user3;=0A+CREATE=20USER=20MAPPING=20= FOR=20regress_subscription_user3=20SERVER=20test_server2=20OPTIONS(user=20= 'bar',=20password=20'secret');=0A+RESET=20SESSION=20AUTHORIZATION;=0A=20= REVOKE=20USAGE=20ON=20FOREIGN=20SERVER=20test_server=20FROM=20= regress_subscription_user3;=0A=20SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A=20--=20fail,=20must=20connect=20but=20= lacks=20USAGE=20on=20server,=20as=20well=20as=20user=20mapping=0A=20DROP=20= SUBSCRIPTION=20regress_testsub6;=0A=20ERROR:=20=20could=20not=20connect=20= to=20publisher=20when=20attempting=20to=20drop=20replication=20slot=20= "dummy":=20subscription=20owner=20"regress_subscription_user3"=20does=20= not=20have=20permission=20on=20foreign=20server=20"test_server"=0A=20= HINT:=20=20Use=20ALTER=20SUBSCRIPTION=20...=20DISABLE=20to=20disable=20= the=20subscription,=20and=20then=20use=20ALTER=20SUBSCRIPTION=20...=20= SET=20(slot_name=20=3D=20NONE)=20to=20disassociate=20it=20from=20the=20= slot.=0A+--=20ok,=20should=20not=20need=20to=20resolve=20conninfo=20for=20= the=20old=20broken=20server=0A+ALTER=20SUBSCRIPTION=20regress_testsub6=20= SERVER=20test_server2;=0A+SELECT=20fs.srvname=0A+=20=20FROM=20= pg_subscription=20s=0A+=20=20JOIN=20pg_foreign_server=20fs=20ON=20fs.oid=20= =3D=20s.subserver=0A+=20WHERE=20s.subname=20=3D=20'regress_testsub6';=0A= +=20=20=20srvname=20=20=20=20=0A+--------------=0A+=20test_server2=0A+(1=20= row)=0A+=0A+DROP=20USER=20MAPPING=20FOR=20regress_subscription_user3=20= SERVER=20test_server2;=0A+RESET=20SESSION=20AUTHORIZATION;=0A+REVOKE=20= USAGE=20ON=20FOREIGN=20SERVER=20test_server2=20FROM=20= regress_subscription_user3;=0A+SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A+--=20ok,=20should=20not=20need=20to=20= resolve=20conninfo=20for=20the=20current=20broken=20server=0A+ALTER=20= SUBSCRIPTION=20regress_testsub6=20CONNECTION=20= 'dbname=3Dregress_doesnotexist2=20password=3Dsecret';=0A+RESET=20SESSION=20= AUTHORIZATION;=0A+SELECT=20subserver=20=3D=200::oid=20AS=20= uses_conninfo,=20subconninfo=0A+=20=20FROM=20pg_subscription=0A+=20WHERE=20= subname=20=3D=20'regress_testsub6';=0A+=20uses_conninfo=20|=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20subconninfo=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=0A= +---------------+----------------------------------------------=0A+=20t=20= =20=20=20=20=20=20=20=20=20=20=20=20|=20dbname=3Dregress_doesnotexist2=20= password=3Dsecret=0A+(1=20row)=0A+=0A+SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A=20ALTER=20SUBSCRIPTION=20regress_testsub6=20= SET=20(slot_name=20=3D=20NONE);=0A=20DROP=20SUBSCRIPTION=20= regress_testsub6;=0A=20SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user;=0A=20REVOKE=20CREATE=20ON=20DATABASE=20= REGRESSION=20FROM=20regress_subscription_user3;=0A+DROP=20SERVER=20= test_server2;=0A=20DROP=20SERVER=20test_server;=0A=20--=20fail,=20FDW=20= is=20dependent=0A=20DROP=20FUNCTION=20test_fdw_connection(oid,=20oid,=20= internal);=0Adiff=20--git=20a/src/test/regress/regress.c=20= b/src/test/regress/regress.c=0Aindex=202bcb5559a45..a758b1e2708=20100644=0A= ---=20a/src/test/regress/regress.c=0A+++=20b/src/test/regress/regress.c=0A= @@=20-31,6=20+31,7=20@@=0A=20#include=20"executor/executor.h"=0A=20= #include=20"executor/functions.h"=0A=20#include=20"executor/spi.h"=0A= +#include=20"foreign/foreign.h"=0A=20#include=20"funcapi.h"=0A=20= #include=20"mb/pg_wchar.h"=0A=20#include=20"miscadmin.h"=0A@@=20-735,6=20= +736,7=20@@=20PG_FUNCTION_INFO_V1(test_fdw_connection);=0A=20Datum=0A=20= test_fdw_connection(PG_FUNCTION_ARGS)=0A=20{=0A+=09= GetUserMapping(PG_GETARG_OID(0),=20PG_GETARG_OID(1));=0A=20=09= PG_RETURN_TEXT_P(cstring_to_text("dbname=3Dregress_doesnotexist=20= user=3Ddoesnotexist=20password=3Dsecret"));=0A=20}=0A=20=0Adiff=20--git=20= a/src/test/regress/sql/subscription.sql=20= b/src/test/regress/sql/subscription.sql=0Aindex=20= 6c3d9632e8a..73cb251e192=20100644=0A---=20= a/src/test/regress/sql/subscription.sql=0A+++=20= b/src/test/regress/sql/subscription.sql=0A@@=20-124,6=20+124,12=20@@=20= SET=20SESSION=20AUTHORIZATION=20regress_subscription_user3;=0A=20CREATE=20= SUBSCRIPTION=20regress_testsub6=20SERVER=20test_server=20PUBLICATION=20= testpub=20WITH=20(slot_name=20=3D=20'dummy',=20connect=20=3D=20false);=0A= =20=0A=20DROP=20USER=20MAPPING=20FOR=20regress_subscription_user3=20= SERVER=20test_server;=0A+RESET=20SESSION=20AUTHORIZATION;=0A+CREATE=20= SERVER=20test_server2=20FOREIGN=20DATA=20WRAPPER=20test_fdw;=0A+GRANT=20= USAGE=20ON=20FOREIGN=20SERVER=20test_server2=20TO=20= regress_subscription_user3;=0A+SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A+CREATE=20USER=20MAPPING=20FOR=20= regress_subscription_user3=20SERVER=20test_server2=20OPTIONS(user=20= 'bar',=20password=20'secret');=0A+=0A=20RESET=20SESSION=20AUTHORIZATION;=0A= =20REVOKE=20USAGE=20ON=20FOREIGN=20SERVER=20test_server=20FROM=20= regress_subscription_user3;=0A=20SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A@@=20-131,12=20+137,33=20@@=20SET=20= SESSION=20AUTHORIZATION=20regress_subscription_user3;=0A=20--=20fail,=20= must=20connect=20but=20lacks=20USAGE=20on=20server,=20as=20well=20as=20= user=20mapping=0A=20DROP=20SUBSCRIPTION=20regress_testsub6;=0A=20=0A+--=20= ok,=20should=20not=20need=20to=20resolve=20conninfo=20for=20the=20old=20= broken=20server=0A+ALTER=20SUBSCRIPTION=20regress_testsub6=20SERVER=20= test_server2;=0A+SELECT=20fs.srvname=0A+=20=20FROM=20pg_subscription=20s=0A= +=20=20JOIN=20pg_foreign_server=20fs=20ON=20fs.oid=20=3D=20s.subserver=0A= +=20WHERE=20s.subname=20=3D=20'regress_testsub6';=0A+=0A+DROP=20USER=20= MAPPING=20FOR=20regress_subscription_user3=20SERVER=20test_server2;=0A= +RESET=20SESSION=20AUTHORIZATION;=0A+REVOKE=20USAGE=20ON=20FOREIGN=20= SERVER=20test_server2=20FROM=20regress_subscription_user3;=0A+SET=20= SESSION=20AUTHORIZATION=20regress_subscription_user3;=0A+=0A+--=20ok,=20= should=20not=20need=20to=20resolve=20conninfo=20for=20the=20current=20= broken=20server=0A+ALTER=20SUBSCRIPTION=20regress_testsub6=20CONNECTION=20= 'dbname=3Dregress_doesnotexist2=20password=3Dsecret';=0A+RESET=20SESSION=20= AUTHORIZATION;=0A+SELECT=20subserver=20=3D=200::oid=20AS=20= uses_conninfo,=20subconninfo=0A+=20=20FROM=20pg_subscription=0A+=20WHERE=20= subname=20=3D=20'regress_testsub6';=0A+SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user3;=0A+=0A=20ALTER=20SUBSCRIPTION=20= regress_testsub6=20SET=20(slot_name=20=3D=20NONE);=0A=20DROP=20= SUBSCRIPTION=20regress_testsub6;=0A=20=0A=20SET=20SESSION=20= AUTHORIZATION=20regress_subscription_user;=0A=20REVOKE=20CREATE=20ON=20= DATABASE=20REGRESSION=20FROM=20regress_subscription_user3;=0A=20=0A+DROP=20= SERVER=20test_server2;=0A=20DROP=20SERVER=20test_server;=0A=20=0A=20--=20= fail,=20FDW=20is=20dependent=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_9082CE91-401A-445D-BBC3-9620ED3526DF--