Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1miMHO-0002Ce-C1 for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Nov 2021 19:50:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1miMHN-0005Y7-79 for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Nov 2021 19:50:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1miMHM-0005XV-3J for pgsql-hackers@lists.postgresql.org; Wed, 03 Nov 2021 19:50:36 +0000 Received: from mail-pg1-x52a.google.com ([2607:f8b0:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1miMHI-0004G3-Hd for pgsql-hackers@postgresql.org; Wed, 03 Nov 2021 19:50:34 +0000 Received: by mail-pg1-x52a.google.com with SMTP id m21so3271197pgu.13 for ; Wed, 03 Nov 2021 12:50:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=pa7MiD5toZMrc4KBTM0eUAAf3BTXbFJPEZF5B3P46XU=; b=NqnhV34Ughg7U4bbuekU3ILamaUah+wLrc36uAsJEXlnR4sO7nd7IxStxwqZT5pWcC r0ooPz3ZLyHBsG4gtQCeADnfaJHCjAr/PpsOvt0kRUheNc8ptpPPnAlCDf3Q0ytq2PTS UVLmjaK+tzRlhhdlHzEXokO1uO3J7Qiq6sqbXgB7C5EcDX2GXYBqfJlcDuzNU/aHwkp8 1PTfnRwdi4BFEML7ZCnhjIdziJdRWR/71gakZ/ac4rsjGXMp3CtsbGFBHcVcl/WKDayq mipLF2444Rqmep5lqAf6Kb1E3kQOuvgQm0AlVFwzf+5ktR7ciwzDuCrBztUnfTiNg7jZ adBA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=pa7MiD5toZMrc4KBTM0eUAAf3BTXbFJPEZF5B3P46XU=; b=72IH5+j8+M4CZ/67ceyLz8PDCS63c3bl+96C/i3WFAik/jCe9Mw0nu0W8yhp0VJvr8 3NQ+80W8o5J5wBzdzFV6kN9du+UNy3M7h4CHlvDD13+Hq8UsO0Uv13Q+mNyopVsBragw P7RHlNtk7YXgtG+1KAwOzBDZtslqQU/C2Y7fNR/KWqxCu4kK3oT+OLTZFhm2QcAAf5JV 4QSNT1nEoKicc5pDpsA69SarO3Hq2pThCYRh9hEoAPXP0yWRIcdv3hjHfEBtd9Z5eKbh +L8exEdxORjU189fWb/4jWEVio1Y9nRkJHZybWDr4Jh6UYL3stMex0Etx4ryPiDo7z1+ DMag== X-Gm-Message-State: AOAM533V1Ca1TQ/JikTTSjQZ01tMbPqTG7EonH53jeBoc1U3Y/q5wwvr 7D2Vr16uc3Qv75PbNT4VJF835UbRhVhjWg11glC7TUBMMcfz2Eve79rKE6jN4H/6d+8me2M+eGx Xy7h2D36WQTUxOZLPt8ykQg+44T+Y97XjpvFMlcob4SMmfDkXDQZyO3XqJr3TAQv8ONzC+/mADH tIliUl6POW5dlObKuZZTG+KgiFQGYjqMZqhBVl X-Google-Smtp-Source: ABdhPJwP+42CA/F23nXcT9vvCXbcxzzO6+QI/XcGX4dEA+VeqzBs4TMcw8isiLwMtBIw/hm9FmbwTw== X-Received: by 2002:a05:6a00:26cd:b0:493:ff3f:8083 with SMTP id p13-20020a056a0026cd00b00493ff3f8083mr1016585pfw.19.1635969030715; Wed, 03 Nov 2021 12:50:30 -0700 (PDT) Received: from laptop280-ma-us.hitronhub.home (24-113-193-150.wavecable.com. [24.113.193.150]) by smtp.gmail.com with ESMTPSA id k5sm2445278pjd.46.2021.11.03.12.50.29 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 03 Nov 2021 12:50:29 -0700 (PDT) From: Mark Dilger Message-Id: <6BB4451E-7B1B-474C-BD1F-DB7531E720C6@enterprisedb.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Non-superuser subscription owners Date: Wed, 3 Nov 2021 12:50:28 -0700 In-Reply-To: Cc: PostgreSQL-development , Robert Haas To: Andrew Dunstan References: <9DFC88D3-1300-4DE8-ACBC-4CEF84399A53@enterprisedb.com> X-Mailer: Apple Mail (2.3608.120.23.2.7) X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Nov 1, 2021, at 10:58 AM, Mark Dilger = wrote: >=20 > ALTER SUBSCRIPTION..[ENABLE | DISABLE] do not synchronously start or = stop subscription workers. The ALTER command updates the catalog's = subenabled field, but workers only lazily respond to that. Disabling = and enabling the subscription as part of the OWNER TO would not reliably = accomplish anything. I have rethought my prior analysis. The problem in the previous patch = was that the subscription apply workers did not check for a change in = ownership the way they checked for other changes, instead only picking = up the new ownership information when the worker restarted for some = other reason. This next patch set fixes that. The application of a = change record may continue under the old ownership permissions when a = concurrent command changes the ownership of the subscription, but the = worker will pick up the new permissions before applying the next record. = I think that is consistent enough with reasonable expectations. The first two patches are virtually unchanged. The third updates the = behavior of the apply workers, and updates the documentation to match. --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209 Content-Disposition: attachment; filename=v2-0001-Handle-non-superuser-subscription-owners-sensibly.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Handle-non-superuser-subscription-owners-sensibly.patch" Content-Transfer-Encoding: quoted-printable =46rom=206225780bd7e4282ec47e153c19ef59fc10648c17=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mark=20Dilger=20=0A= Date:=20Sun,=2026=20Sep=202021=2018:21:26=20-0700=0ASubject:=20[PATCH=20= v2=201/3]=20Handle=20non-superuser=20subscription=20owners=20sensibly=0A=0A= Stop=20pretending=20that=20subscriptions=20are=20always=20owned=20by=20= superusers=0Aand=20instead=20fix=20security=20problems=20that=20arise=20= as=20a=20consequence=20of=0Athem=20not=20being=20superuser.=20=20= Specifically,=20disallow=20a=20non-superuser=0Achanging=20the=20= connection,=20the=20list=20of=20publications,=20or=20the=20options=0Afor=20= their=20subscription.=0A=0AThe=20prior=20behavior=20violated=20the=20= principle=20of=20least=20surprise,=0Aallowing=20a=20non-superuser=20in=20= possession=20of=20a=20subscription=20to=20change=0Aall=20aspects=20of=20= the=20subscription,=20connecting=20it=20to=20a=20different=20server=0A= and=20subscribing=20a=20different=20set=20of=20publications,=20= effectively=0Aamounting=20to=20a=20non-superuser=20creating=20a=20new=20= subscription.=0A=0AThe=20new=20behavior=20restricts=20the=20= non-superuser=20owner=20to=20enabling,=0Adisabling,=20refreshing,=20= renaming,=20and=20further=20assigning=20ownership=20of=0Athe=20= subscription.=0A---=0A=20doc/src/sgml/ref/alter_subscription.sgml=20=20=20= |=2011=20++++-=0A=20src/backend/commands/subscriptioncmds.c=20=20=20=20|=20= 20=20+++++++++=0A=20src/test/regress/expected/subscription.out=20|=2051=20= ++++++++++++++++++++++=0A=20src/test/regress/sql/subscription.sql=20=20=20= =20=20=20|=2035=20+++++++++++++++=0A=204=20files=20changed,=20115=20= insertions(+),=202=20deletions(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/ref/alter_subscription.sgml=20= b/doc/src/sgml/ref/alter_subscription.sgml=0Aindex=20= 0b027cc346..b1fd73f776=20100644=0A---=20= a/doc/src/sgml/ref/alter_subscription.sgml=0A+++=20= b/doc/src/sgml/ref/alter_subscription.sgml=0A@@=20-47,8=20+47,6=20@@=20= ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=20=20=20= You=20must=20own=20the=20subscription=20to=20use=20ALTER=20= SUBSCRIPTION.=0A=20=20=20=20To=20alter=20the=20owner,=20you=20= must=20also=20be=20a=20direct=20or=20indirect=20member=20of=20the=0A=20=20= =20=20new=20owning=20role.=20The=20new=20owner=20has=20to=20be=20a=20= superuser.=0A-=20=20=20(Currently,=20all=20subscription=20owners=20must=20= be=20superusers,=20so=20the=20owner=20checks=0A-=20=20=20will=20be=20= bypassed=20in=20practice.=20=20But=20this=20might=20change=20in=20the=20= future.)=0A=20=20=20=0A=20=0A=20=20=20=0A@@=20-99,6=20+97,9=20= @@=20ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=20=20=20=20= =20=20.=20=20See=20there=20= for=20more=0A=20=20=20=20=20=20=20information.=0A=20=20=20=20=20=20= =0A+=20=20=20=20=20=0A+=20=20=20=20=20=20Only=20superusers=20= may=20change=20the=20connection=20property.=0A+=20=20=20=20=20=0A=20= =20=20=20=20=0A=20=20=20=20=0A=20=0A@@=20= -139,6=20+140,9=20@@=20ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=20=20=20=20= =20=20REFRESH=20PUBLICATION=20may=20be=20specified,=20= to=20control=20the=0A=20=20=20=20=20=20=20implicit=20refresh=20= operation.=0A=20=20=20=20=20=20=0A+=20=20=20=20=20=0A+=20=20= =20=20=20=20Only=20superusers=20may=20change=20the=20list=20of=20= subscribed=20publications.=0A+=20=20=20=20=20=0A=20=20=20=20=20= =0A=20=20=20=20=0A=20=0A@@=20-204,6=20+208,9=20= @@=20ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=20=20=20=20= =20=20binary,=20and=0A=20=20=20=20=20=20=20= streaming.=0A=20=20=20=20=20=20=0A+=20=20=20=20= =20=0A+=20=20=20=20=20=20Only=20superusers=20may=20alter=20= subscription=20parameters.=0A+=20=20=20=20=20=0A=20=20=20=20=20= =0A=20=20=20=20=0A=20=0Adiff=20--git=20= a/src/backend/commands/subscriptioncmds.c=20= b/src/backend/commands/subscriptioncmds.c=0Aindex=20= c47ba26369..6a5d192128=20100644=0A---=20= a/src/backend/commands/subscriptioncmds.c=0A+++=20= b/src/backend/commands/subscriptioncmds.c=0A@@=20-868,6=20+868,11=20@@=20= AlterSubscription(ParseState=20*pstate,=20AlterSubscriptionStmt=20*stmt,=0A= =20=09{=0A=20=09=09case=20ALTER_SUBSCRIPTION_OPTIONS:=0A=20=09=09=09{=0A= +=09=09=09=09if=20(!superuser())=0A+=09=09=09=09=09ereport(ERROR,=0A+=09=09= =09=09=09=09=09(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A+=09=09=09=09=09= =09=09=20errmsg("must=20be=20superuser=20to=20alter=20the=20options=20= for=20a=20subscription")));=0A+=0A=20=09=09=09=09supported_opts=20=3D=20= (SUBOPT_SLOT_NAME=20|=0A=20=09=09=09=09=09=09=09=09=20=20= SUBOPT_SYNCHRONOUS_COMMIT=20|=20SUBOPT_BINARY=20|=0A=20=09=09=09=09=09=09= =09=09=20=20SUBOPT_STREAMING);=0A@@=20-946,6=20+951,11=20@@=20= AlterSubscription(ParseState=20*pstate,=20AlterSubscriptionStmt=20*stmt,=0A= =20=09=09=09}=0A=20=0A=20=09=09case=20ALTER_SUBSCRIPTION_CONNECTION:=0A+=09= =09=09if=20(!superuser())=0A+=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("must=20be=20superuser=20to=20alter=20the=20connection=20for=20a=20= subscription")));=0A+=0A=20=09=09=09/*=20Load=20the=20library=20= providing=20us=20libpq=20calls.=20*/=0A=20=09=09=09= load_file("libpqwalreceiver",=20false);=0A=20=09=09=09/*=20Check=20the=20= connection=20info=20string.=20*/=0A@@=20-959,6=20+969,11=20@@=20= AlterSubscription(ParseState=20*pstate,=20AlterSubscriptionStmt=20*stmt,=0A= =20=0A=20=09=09case=20ALTER_SUBSCRIPTION_SET_PUBLICATION:=0A=20=09=09=09= {=0A+=09=09=09=09if=20(!superuser())=0A+=09=09=09=09=09ereport(ERROR,=0A= +=09=09=09=09=09=09=09(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A+=09=09= =09=09=09=09=09=20errmsg("must=20be=20superuser=20to=20alter=20= publications=20for=20a=20subscription")));=0A+=0A=20=09=09=09=09= supported_opts=20=3D=20SUBOPT_COPY_DATA=20|=20SUBOPT_REFRESH;=0A=20=09=09= =09=09parse_subscription_options(pstate,=20stmt->options,=0A=20=09=09=09=09= =09=09=09=09=09=09=20=20=20supported_opts,=20&opts);=0A@@=20-1006,6=20= +1021,11=20@@=20AlterSubscription(ParseState=20*pstate,=20= AlterSubscriptionStmt=20*stmt,=0A=20=09=09=09=09List=09=20=20=20= *publist;=0A=20=09=09=09=09bool=09=09isadd=20=3D=20stmt->kind=20=3D=3D=20= ALTER_SUBSCRIPTION_ADD_PUBLICATION;=0A=20=0A+=09=09=09=09if=20= (!superuser())=0A+=09=09=09=09=09ereport(ERROR,=0A+=09=09=09=09=09=09=09= (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A+=09=09=09=09=09=09=09=20= errmsg("must=20be=20superuser=20to=20alter=20publications=20for=20a=20= subscription")));=0A+=0A=20=09=09=09=09supported_opts=20=3D=20= SUBOPT_REFRESH=20|=20SUBOPT_COPY_DATA;=0A=20=09=09=09=09= parse_subscription_options(pstate,=20stmt->options,=0A=20=09=09=09=09=09=09= =09=09=09=09=20=20=20supported_opts,=20&opts);=0Adiff=20--git=20= a/src/test/regress/expected/subscription.out=20= b/src/test/regress/expected/subscription.out=0Aindex=20= 15a1ac6398..a05e6f1795=20100644=0A---=20= a/src/test/regress/expected/subscription.out=0A+++=20= b/src/test/regress/expected/subscription.out=0A@@=20-144,6=20+144,57=20= @@=20HINT:=20=20The=20owner=20of=20a=20subscription=20must=20be=20a=20= superuser.=0A=20ALTER=20ROLE=20regress_subscription_user2=20SUPERUSER;=0A= =20--=20now=20it=20works=0A=20ALTER=20SUBSCRIPTION=20regress_testsub=20= OWNER=20TO=20regress_subscription_user2;=0A+--=20revoke=20superuser=20= from=20new=20owner=0A+ALTER=20ROLE=20regress_subscription_user2=20= NOSUPERUSER;=0A+SET=20SESSION=20AUTHORIZATION=20= regress_subscription_user2;=0A+--=20fail=20-=20non-superuser=20owner=20= cannot=20change=20connection=20parameter=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20CONNECTION=20'dbname=3Dsomethingelse';=0A+ERROR:=20=20= must=20be=20superuser=20to=20alter=20the=20connection=20for=20a=20= subscription=0A+--=20fail=20-=20non-superuser=20owner=20cannot=20alter=20= the=20publications=20list=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20= ADD=20PUBLICATION=20somepub;=0A+ERROR:=20=20must=20be=20superuser=20to=20= alter=20publications=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20DROP=20PUBLICATION=20otherpub;=0A+ERROR:=20=20must=20= be=20superuser=20to=20alter=20publications=20for=20a=20subscription=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20PUBLICATION=20somepub,=20= otherpub;=0A+ERROR:=20=20must=20be=20superuser=20to=20alter=20= publications=20for=20a=20subscription=0A+--=20fail=20-=20non-superuser=20= owner=20cannot=20change=20subscription=20parameters=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(copy_data=20=3D=20true);=0A= +ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20options=20for=20= a=20subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (copy_data=20=3D=20false);=0A+ERROR:=20=20must=20be=20superuser=20to=20= alter=20the=20options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(create_slot=20=3D=20true);=0A+ERROR:=20=20must=20= be=20superuser=20to=20alter=20the=20options=20for=20a=20subscription=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(create_slot=20=3D=20= false);=0A+ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20= options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(slot_name=20=3D=20'somethingelse');=0A+ERROR:=20= =20must=20be=20superuser=20to=20alter=20the=20options=20for=20a=20= subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (synchronous_commit=20=3D=20on);=0A+ERROR:=20=20must=20be=20superuser=20= to=20alter=20the=20options=20for=20a=20subscription=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(synchronous_commit=20=3D=20off);=0A= +ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20options=20for=20= a=20subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (synchronous_commit=20=3D=20local);=0A+ERROR:=20=20must=20be=20superuser=20= to=20alter=20the=20options=20for=20a=20subscription=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(synchronous_commit=20=3D=20= remote_write);=0A+ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20= options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(synchronous_commit=20=3D=20remote_apply);=0A= +ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20options=20for=20= a=20subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (binary=20=3D=20on);=0A+ERROR:=20=20must=20be=20superuser=20to=20alter=20= the=20options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(binary=20=3D=20off);=0A+ERROR:=20=20must=20be=20= superuser=20to=20alter=20the=20options=20for=20a=20subscription=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(connect=20=3D=20on);=0A+ERROR:=20= =20must=20be=20superuser=20to=20alter=20the=20options=20for=20a=20= subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(connect=20= =3D=20off);=0A+ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20= options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(streaming=20=3D=20on);=0A+ERROR:=20=20must=20be=20= superuser=20to=20alter=20the=20options=20for=20a=20subscription=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(streaming=20=3D=20off);=0A= +ERROR:=20=20must=20be=20superuser=20to=20alter=20the=20options=20for=20= a=20subscription=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (two_phase=20=3D=20on);=0A+ERROR:=20=20must=20be=20superuser=20to=20= alter=20the=20options=20for=20a=20subscription=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(two_phase=20=3D=20off);=0A+ERROR:=20=20must=20= be=20superuser=20to=20alter=20the=20options=20for=20a=20subscription=0A= +SET=20SESSION=20AUTHORIZATION=20'regress_subscription_user';=0A=20--=20= fail=20-=20cannot=20do=20DROP=20SUBSCRIPTION=20inside=20transaction=20= block=20with=20slot=20name=0A=20BEGIN;=0A=20DROP=20SUBSCRIPTION=20= regress_testsub;=0Adiff=20--git=20= a/src/test/regress/sql/subscription.sql=20= b/src/test/regress/sql/subscription.sql=0Aindex=207faa935a2a..8f66709441=20= 100644=0A---=20a/src/test/regress/sql/subscription.sql=0A+++=20= b/src/test/regress/sql/subscription.sql=0A@@=20-105,6=20+105,41=20@@=20= ALTER=20ROLE=20regress_subscription_user2=20SUPERUSER;=0A=20--=20now=20= it=20works=0A=20ALTER=20SUBSCRIPTION=20regress_testsub=20OWNER=20TO=20= regress_subscription_user2;=0A=20=0A+--=20revoke=20superuser=20from=20= new=20owner=0A+ALTER=20ROLE=20regress_subscription_user2=20NOSUPERUSER;=0A= +=0A+SET=20SESSION=20AUTHORIZATION=20regress_subscription_user2;=0A+=0A= +--=20fail=20-=20non-superuser=20owner=20cannot=20change=20connection=20= parameter=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20CONNECTION=20= 'dbname=3Dsomethingelse';=0A+=0A+--=20fail=20-=20non-superuser=20owner=20= cannot=20alter=20the=20publications=20list=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20ADD=20PUBLICATION=20somepub;=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20DROP=20PUBLICATION=20otherpub;=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20PUBLICATION=20somepub,=20otherpub;=0A+=0A+--=20= fail=20-=20non-superuser=20owner=20cannot=20change=20subscription=20= parameters=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(copy_data=20= =3D=20true);=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (copy_data=20=3D=20false);=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20= SET=20(create_slot=20=3D=20true);=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(create_slot=20=3D=20false);=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(slot_name=20=3D=20= 'somethingelse');=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (synchronous_commit=20=3D=20on);=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(synchronous_commit=20=3D=20off);=0A+ALTER=20= SUBSCRIPTION=20regress_testsub=20SET=20(synchronous_commit=20=3D=20= local);=0A+ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20= (synchronous_commit=20=3D=20remote_write);=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20SET=20(synchronous_commit=20=3D=20remote_apply);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(binary=20=3D=20on);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(binary=20=3D=20off);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(connect=20=3D=20on);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(connect=20=3D=20off);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(streaming=20=3D=20on);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(streaming=20=3D=20off);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(two_phase=20=3D=20on);=0A= +ALTER=20SUBSCRIPTION=20regress_testsub=20SET=20(two_phase=20=3D=20off);=0A= +=0A+SET=20SESSION=20AUTHORIZATION=20'regress_subscription_user';=0A+=0A=20= --=20fail=20-=20cannot=20do=20DROP=20SUBSCRIPTION=20inside=20transaction=20= block=20with=20slot=20name=0A=20BEGIN;=0A=20DROP=20SUBSCRIPTION=20= regress_testsub;=0A--=20=0A2.21.1=20(Apple=20Git-122.3)=0A=0A= --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209 Content-Disposition: attachment; filename=v2-0002-Allow-subscription-ownership-by-non-superusers.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0002-Allow-subscription-ownership-by-non-superusers.patch" Content-Transfer-Encoding: quoted-printable =46rom=20f4cae21ee2c7045fe5c89fbba5ecf7a3de3e2283=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mark=20Dilger=20=0A= Date:=20Sun,=2026=20Sep=202021=2018:24:07=20-0700=0ASubject:=20[PATCH=20= v2=202/3]=20Allow=20subscription=20ownership=20by=20non-superusers=0A=0A= Non-superusers=20can=20already=20end=20up=20owning=20subscriptions=20if=20= superuser=0Ais=20revoked=20from=20a=20subscription=20owner,=20so=20quit=20= the=20pretense=20and=0Asimply=20allow=20the=20ownership=20transfer=20= overtly=20by=20ALTER=20SUBSCRIPTION.=0A=0AThe=20prior=20situation=20did=20= more=20to=20give=20a=20false=20sense=20that=0Asubscriptions=20would=20= never=20be=20owned=20by=20non-superusers=20than=20it=20did=20to=0A= actually=20prevent=20that=20scenario=20from=20arising.=0A---=0A=20= doc/src/sgml/ref/alter_subscription.sgml=20=20=20|=20=202=20+-=0A=20= src/backend/commands/subscriptioncmds.c=20=20=20=20|=20=209=20++-------=0A= =20src/test/regress/expected/subscription.out=20|=2012=20++++--------=0A=20= src/test/regress/sql/subscription.sql=20=20=20=20=20=20|=2011=20= ++++-------=0A=204=20files=20changed,=2011=20insertions(+),=2023=20= deletions(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/ref/alter_subscription.sgml=20= b/doc/src/sgml/ref/alter_subscription.sgml=0Aindex=20= b1fd73f776..bc52339eba=20100644=0A---=20= a/doc/src/sgml/ref/alter_subscription.sgml=0A+++=20= b/doc/src/sgml/ref/alter_subscription.sgml=0A@@=20-46,7=20+46,7=20@@=20= ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=20=20= =0A=20=20=20=20You=20must=20own=20the=20subscription=20to=20use=20= ALTER=20SUBSCRIPTION.=0A=20=20=20=20To=20alter=20the=20= owner,=20you=20must=20also=20be=20a=20direct=20or=20indirect=20member=20= of=20the=0A-=20=20=20new=20owning=20role.=20The=20new=20owner=20has=20to=20= be=20a=20superuser.=0A+=20=20=20new=20owning=20role.=0A=20=20=20=0A= =20=0A=20=20=20=0Adiff=20--git=20= a/src/backend/commands/subscriptioncmds.c=20= b/src/backend/commands/subscriptioncmds.c=0Aindex=20= 6a5d192128..d75183cd12=20100644=0A---=20= a/src/backend/commands/subscriptioncmds.c=0A+++=20= b/src/backend/commands/subscriptioncmds.c=0A@@=20-1476,13=20+1476,8=20@@=20= AlterSubscriptionOwner_internal(Relation=20rel,=20HeapTuple=20tup,=20Oid=20= newOwnerId)=0A=20=09=09aclcheck_error(ACLCHECK_NOT_OWNER,=20= OBJECT_SUBSCRIPTION,=0A=20=09=09=09=09=09=20=20=20= NameStr(form->subname));=0A=20=0A-=09/*=20New=20owner=20must=20be=20a=20= superuser=20*/=0A-=09if=20(!superuser_arg(newOwnerId))=0A-=09=09= ereport(ERROR,=0A-=09=09=09=09(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A= -=09=09=09=09=20errmsg("permission=20denied=20to=20change=20owner=20of=20= subscription=20\"%s\"",=0A-=09=09=09=09=09=09NameStr(form->subname)),=0A= -=09=09=09=09=20errhint("The=20owner=20of=20a=20subscription=20must=20be=20= a=20superuser.")));=0A+=09/*=20Must=20be=20able=20to=20assign=20= ownership=20to=20the=20target=20role=20*/=0A+=09= check_is_member_of_role(GetUserId(),=20newOwnerId);=0A=20=0A=20=09= form->subowner=20=3D=20newOwnerId;=0A=20=09CatalogTupleUpdate(rel,=20= &tup->t_self,=20tup);=0Adiff=20--git=20= a/src/test/regress/expected/subscription.out=20= b/src/test/regress/expected/subscription.out=0Aindex=20= a05e6f1795..9b4dac9c0e=20100644=0A---=20= a/src/test/regress/expected/subscription.out=0A+++=20= b/src/test/regress/expected/subscription.out=0A@@=20-137,16=20+137,12=20= @@=20HINT:=20=20Available=20values:=20local,=20remote_write,=20= remote_apply,=20on,=20off.=0A=20=0A=20--=20rename=20back=20to=20keep=20= the=20rest=20simple=0A=20ALTER=20SUBSCRIPTION=20regress_testsub_foo=20= RENAME=20TO=20regress_testsub;=0A---=20fail=20-=20new=20owner=20must=20= be=20superuser=0A+--=20superuser=20can=20assign=20the=20ownership=20to=20= a=20non-superuser=0A=20ALTER=20SUBSCRIPTION=20regress_testsub=20OWNER=20= TO=20regress_subscription_user2;=0A-ERROR:=20=20permission=20denied=20to=20= change=20owner=20of=20subscription=20"regress_testsub"=0A-HINT:=20=20The=20= owner=20of=20a=20subscription=20must=20be=20a=20superuser.=0A-ALTER=20= ROLE=20regress_subscription_user2=20SUPERUSER;=0A---=20now=20it=20works=0A= -ALTER=20SUBSCRIPTION=20regress_testsub=20OWNER=20TO=20= regress_subscription_user2;=0A---=20revoke=20superuser=20from=20new=20= owner=0A-ALTER=20ROLE=20regress_subscription_user2=20NOSUPERUSER;=0A=20= SET=20SESSION=20AUTHORIZATION=20regress_subscription_user2;=0A+--=20fail=20= -=20not=20a=20member=20of=20the=20target=20role=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20OWNER=20TO=20regress_subscription_user;=0A+ERROR:=20=20= must=20be=20member=20of=20role=20"regress_subscription_user"=0A=20--=20= fail=20-=20non-superuser=20owner=20cannot=20change=20connection=20= parameter=0A=20ALTER=20SUBSCRIPTION=20regress_testsub=20CONNECTION=20= 'dbname=3Dsomethingelse';=0A=20ERROR:=20=20must=20be=20superuser=20to=20= alter=20the=20connection=20for=20a=20subscription=0Adiff=20--git=20= a/src/test/regress/sql/subscription.sql=20= b/src/test/regress/sql/subscription.sql=0Aindex=208f66709441..6c68d547d1=20= 100644=0A---=20a/src/test/regress/sql/subscription.sql=0A+++=20= b/src/test/regress/sql/subscription.sql=0A@@=20-99,17=20+99,14=20@@=20= ALTER=20SUBSCRIPTION=20regress_testsub_foo=20SET=20(synchronous_commit=20= =3D=20foobar);=0A=20--=20rename=20back=20to=20keep=20the=20rest=20simple=0A= =20ALTER=20SUBSCRIPTION=20regress_testsub_foo=20RENAME=20TO=20= regress_testsub;=0A=20=0A---=20fail=20-=20new=20owner=20must=20be=20= superuser=0A-ALTER=20SUBSCRIPTION=20regress_testsub=20OWNER=20TO=20= regress_subscription_user2;=0A-ALTER=20ROLE=20regress_subscription_user2=20= SUPERUSER;=0A---=20now=20it=20works=0A+--=20superuser=20can=20assign=20= the=20ownership=20to=20a=20non-superuser=0A=20ALTER=20SUBSCRIPTION=20= regress_testsub=20OWNER=20TO=20regress_subscription_user2;=0A=20=0A---=20= revoke=20superuser=20from=20new=20owner=0A-ALTER=20ROLE=20= regress_subscription_user2=20NOSUPERUSER;=0A-=0A=20SET=20SESSION=20= AUTHORIZATION=20regress_subscription_user2;=0A=20=0A+--=20fail=20-=20not=20= a=20member=20of=20the=20target=20role=0A+ALTER=20SUBSCRIPTION=20= regress_testsub=20OWNER=20TO=20regress_subscription_user;=0A+=0A=20--=20= fail=20-=20non-superuser=20owner=20cannot=20change=20connection=20= parameter=0A=20ALTER=20SUBSCRIPTION=20regress_testsub=20CONNECTION=20= 'dbname=3Dsomethingelse';=0A=20=0A--=20=0A2.21.1=20(Apple=20Git-122.3)=0A= =0A= --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209 Content-Disposition: attachment; filename=v2-0003-Respect-permissions-within-logical-replication.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0003-Respect-permissions-within-logical-replication.patch" Content-Transfer-Encoding: quoted-printable =46rom=20cc8e820799d4423f329346e43b82e3bf0216842c=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mark=20Dilger=20=0A= Date:=20Tue,=202=20Nov=202021=2015:08:54=20-0700=0ASubject:=20[PATCH=20= v2=203/3]=20Respect=20permissions=20within=20logical=20replication=0A=0A= Prevent=20logical=20replication=20workers=20from=20performing=20insert,=20= update,=0Adelete,=20truncate,=20or=20copy=20commands=20on=20tables=20= unless=20the=20subscription=0Aowner=20has=20permission=20to=20do=20so.=20= =20This=20makes=20it=20much=20safer=20to=20allow=0Anon-superusers=20to=20= create=20subscriptions,=20since=20they=20can=20only=20cause=0Achanges=20= to=20replicate=20into=20schemas=20and=20tables=20that=20they=20would=20= be=20able=0Ato=20directly=20make=20themselves.=20=20This=20also=20= reduces=20the=20amount=20of=20trust=0Athat=20a=20subscriber=20must=20= have=20in=20a=20publisher;=20if=20the=20subscription=20is=0Aset=20up=20= by=20a=20DBA=20using=20an=20account=20that=20is=20intentionally=20= restricted=20to=0Aonly=20those=20tables=20or=20schemas=20that=20the=20= DBA=20expects=20the=20publication=20to=0Atouch,=20then=20the=20DBA=20can=20= be=20create=20and=20refresh=20the=20subscription=0Awithout=20fear=20that=20= a=20malicious=20publisher=20will=20have=20added=20other=0Acritical=20= tables=20to=20the=20publication=20and=20thereby=20overwrite=20them=20on=20= the=0Asubscriber=20side.=20=20In=20many=20setups=20this=20is=20likely=20= a=20non-issue,=20as=20the=0Asame=20DBA=20manages=20both=20the=20= publisher=20and=20the=20subscriber,=20but=20it=20seems=0Awiser=20not=20= to=20hard-code=20that=20assumption=20into=20the=20security=20model=20of=0A= logical=20replication.=0A---=0A=20doc/src/sgml/logical-replication.sgml=20= =20=20=20=20=20=20|=20=2035=20++++--=0A=20= doc/src/sgml/ref/alter_subscription.sgml=20=20=20=20|=20=20=207=20+-=0A=20= src/backend/commands/subscriptioncmds.c=20=20=20=20=20|=20=20=202=20+=0A=20= src/backend/replication/logical/tablesync.c=20|=20=2013=20+++=0A=20= src/backend/replication/logical/worker.c=20=20=20=20|=20=2030=20+++++=0A=20= src/test/subscription/t/026_nosuperuser.pl=20=20|=20118=20= ++++++++++++++++++++=0A=206=20files=20changed,=20194=20insertions(+),=20= 11=20deletions(-)=0A=20create=20mode=20100644=20= src/test/subscription/t/026_nosuperuser.pl=0A=0Adiff=20--git=20= a/doc/src/sgml/logical-replication.sgml=20= b/doc/src/sgml/logical-replication.sgml=0Aindex=2045b2e1e28f..5968958f03=20= 100644=0A---=20a/doc/src/sgml/logical-replication.sgml=0A+++=20= b/doc/src/sgml/logical-replication.sgml=0A@@=20-330,6=20+330,15=20@@=0A=20= =20=20=20will=20simply=20be=20skipped.=0A=20=20=20=0A=20=0A+=20=20= =0A+=20=20=20Logical=20replication=20operations=20are=20performed=20= with=20the=20privileges=20of=20the=20role=0A+=20=20=20which=20owns=20the=20= subscription.=20=20Only=20superusers=20may=20create=20subscriptions,=20= but=0A+=20=20=20if=20the=20ownership=20of=20the=20subscription=20is=20= transferred,=20or=20if=20the=20owner=0A+=20=20=20subsequently=20has=20= superuser=20privileges=20revoked,=20permissions=20failures=20can=0A+=20=20= =20cause=20conflicts.=20=20(Note=20that=20= PostgreSQL=20prior=20to=0A+=20=20=20version=20= 15.0=20did=20no=20permissions=20checking=20when=20applying=20changes.)=0A= +=20=20=0A+=0A=20=20=20=0A=20=20=20=20A=20conflict=20will=20= produce=20an=20error=20and=20will=20stop=20the=20replication;=20it=20= must=20be=0A=20=20=20=20resolved=20manually=20by=20the=20user.=20=20= Details=20about=20the=20conflict=20can=20be=20found=20in=0A@@=20-337,7=20= +346,7=20@@=0A=20=20=20=0A=20=0A=20=20=20=0A-=20=20=20The=20= resolution=20can=20be=20done=20either=20by=20changing=20data=20on=20the=20= subscriber=20so=0A+=20=20=20The=20resolution=20can=20be=20done=20either=20= by=20changing=20data=20or=20permissions=20on=20the=20subscriber=20so=0A=20= =20=20=20that=20it=20does=20not=20conflict=20with=20the=20incoming=20= change=20or=20by=20skipping=20the=0A=20=20=20=20transaction=20that=20= conflicts=20with=20the=20existing=20data.=20=20The=20transaction=20can=20= be=0A=20=20=20=20skipped=20by=20calling=20the=20=0A@@=20-530,9=20+539,9=20@@=0A= =20=0A=20=20=20=0A=20=20=20=20A=20user=20able=20to=20modify=20the=20= schema=20of=20subscriber-side=20tables=20can=20execute=0A-=20=20=20= arbitrary=20code=20as=20a=20superuser.=20=20Limit=20ownership=0A-=20=20=20= and=20TRIGGER=20privilege=20on=20such=20tables=20to=20= roles=20that=0A-=20=20=20superusers=20trust.=20=20Moreover,=20if=20= untrusted=20users=20can=20create=20tables,=20use=20only=0A+=20=20=20= arbitrary=20code=20as=20the=20role=20which=20owns=20any=20subscription=20= which=20modifies=20those=20tables.=20=20Limit=20ownership=0A+=20=20=20= and=20TRIGGER=20privilege=20on=20such=20tables=20to=20= trusted=20roles.=0A+=20=20=20Moreover,=20if=20untrusted=20users=20can=20= create=20tables,=20use=20only=0A=20=20=20=20publications=20that=20list=20= tables=20explicitly.=20=20That=20is=20to=20say,=20create=20a=0A=20=20=20=20= subscription=20FOR=20ALL=20TABLES=20or=0A=20=20=20=20= FOR=20ALL=20TABLES=20IN=20SCHEMA=20only=20when=20= superusers=20trust=0A@@=20-571,18=20+580,26=20@@=0A=20=20=20=0A=20= =0A=20=20=20=0A-=20=20=20To=20create=20a=20subscription,=20the=20= user=20must=20be=20a=20superuser.=0A+=20=20=20To=20create=20a=20= subscription,=20the=20user=20must=20be=20a=20superuser.=20=20The=20= ownership=20can=0A+=20=20=20subsequently=20be=20transferred=20or=20the=20= role=20have=20superuser=20privilege=20removed.=0A=20=20=20=0A=20=0A= =20=20=20=0A=20=20=20=20The=20subscription=20apply=20process=20= will=20run=20in=20the=20local=20database=20with=20the=0A-=20=20=20= privileges=20of=20a=20superuser.=0A+=20=20=20privileges=20of=20the=20= subscription=20owner.=0A+=20=20=0A+=0A+=20=20=0A+=20=20=20= On=20the=20publisher,=20privileges=20are=20only=20checked=20once=20at=20= the=20start=20of=20a=0A+=20=20=20replication=20connection=20and=20are=20= not=20re-checked=20as=20each=20change=20record=20is=20read.=0A=20=20=20= =0A=20=0A=20=20=20=0A-=20=20=20Privileges=20are=20only=20= checked=20once=20at=20the=20start=20of=20a=20replication=20connection.=0A= -=20=20=20They=20are=20not=20re-checked=20as=20each=20change=20record=20= is=20read=20from=20the=20publisher,=0A-=20=20=20nor=20are=20they=20= re-checked=20for=20each=20change=20when=20applied.=0A+=20=20=20On=20the=20= subscriber,=20the=20subscription=20owner's=20privileges=20are=20= re-checked=20for=0A+=20=20=20each=20change=20record=20when=20applied.=20= If=20a=20worker=20is=20in=20the=20process=20of=20applying=20a=0A+=20=20=20= change=20record=20when=20the=20ownership=20of=20the=20subscription=20is=20= changed=20by=20a=0A+=20=20=20concurrent=20transaction,=20the=20= application=20of=20the=20change=20record=20may=20finish=0A+=20=20=20= under=20the=20old=20permissions.=0A=20=20=20=0A=20=20=0A=20= =0Adiff=20--git=20a/doc/src/sgml/ref/alter_subscription.sgml=20= b/doc/src/sgml/ref/alter_subscription.sgml=0Aindex=20= bc52339eba..41bf16e740=20100644=0A---=20= a/doc/src/sgml/ref/alter_subscription.sgml=0A+++=20= b/doc/src/sgml/ref/alter_subscription.sgml=0A@@=20-45,8=20+45,11=20@@=20= ALTER=20SUBSCRIPTION=20name=20RENAME=20TO=20<=0A=20=0A=20=20=20= =0A=20=20=20=20You=20must=20own=20the=20subscription=20to=20use=20= ALTER=20SUBSCRIPTION.=0A-=20=20=20To=20alter=20the=20= owner,=20you=20must=20also=20be=20a=20direct=20or=20indirect=20member=20= of=20the=0A-=20=20=20new=20owning=20role.=0A+=20=20=20To=20alter=20the=20= owner,=20you=20must=20also=20be=20a=20direct=20or=20indirect=20member=20= of=20the=20new=0A+=20=20=20owning=20role.=20Replicated=20changes=20will=20= be=20applied=20to=20the=20target=20relations=20as=0A+=20=20=20the=20= subscription=20owner.=20=20If=20the=20subscription=20owner=20lacks=20= sufficient=0A+=20=20=20privileges=20on=20the=20target=20relations,=20the=20= replication=20workers=20will=20fail=20to=0A+=20=20=20apply=20the=20= changes.=0A=20=20=20=0A=20=0A=20=20=20=0Adiff=20--git=20= a/src/backend/commands/subscriptioncmds.c=20= b/src/backend/commands/subscriptioncmds.c=0Aindex=20= d75183cd12..0b17a582dd=20100644=0A---=20= a/src/backend/commands/subscriptioncmds.c=0A+++=20= b/src/backend/commands/subscriptioncmds.c=0A@@=20-1489,6=20+1489,8=20@@=20= AlterSubscriptionOwner_internal(Relation=20rel,=20HeapTuple=20tup,=20Oid=20= newOwnerId)=0A=20=0A=20=09= InvokeObjectPostAlterHook(SubscriptionRelationId,=0A=20=09=09=09=09=09=09= =09=20=20form->oid,=200);=0A+=0A+=09ApplyLauncherWakeupAtCommit();=0A=20= }=0A=20=0A=20/*=0Adiff=20--git=20= a/src/backend/replication/logical/tablesync.c=20= b/src/backend/replication/logical/tablesync.c=0Aindex=20= f07983a43c..2400ef8c45=20100644=0A---=20= a/src/backend/replication/logical/tablesync.c=0A+++=20= b/src/backend/replication/logical/tablesync.c=0A@@=20-111,6=20+111,7=20= @@=0A=20#include=20"replication/origin.h"=0A=20#include=20= "storage/ipc.h"=0A=20#include=20"storage/lmgr.h"=0A+#include=20= "utils/acl.h"=0A=20#include=20"utils/builtins.h"=0A=20#include=20= "utils/lsyscache.h"=0A=20#include=20"utils/memutils.h"=0A@@=20-924,6=20= +925,7=20@@=20LogicalRepSyncTableStart(XLogRecPtr=20*origin_startpos)=0A=20= =09char=09=09relstate;=0A=20=09XLogRecPtr=09relstate_lsn;=0A=20=09= Relation=09rel;=0A+=09AclResult=09aclresult;=0A=20=09WalRcvExecResult=20= *res;=0A=20=09char=09=09originname[NAMEDATALEN];=0A=20=09RepOriginId=20= originid;=0A@@=20-1042,6=20+1044,17=20@@=20= LogicalRepSyncTableStart(XLogRecPtr=20*origin_startpos)=0A=20=09=20*/=0A=20= =09rel=20=3D=20table_open(MyLogicalRepWorker->relid,=20= RowExclusiveLock);=0A=20=0A+=09/*=0A+=09=20*=20Check=20that=20our=20= table=20sync=20worker=20has=20permission=20to=20insert=20into=20the=0A+=09= =20*=20target=20table.=0A+=09=20*/=0A+=09aclresult=20=3D=20= pg_class_aclcheck(RelationGetRelid(rel),=20GetUserId(),=0A+=09=09=09=09=09= =09=09=09=20=20ACL_INSERT);=0A+=09if=20(aclresult=20!=3D=20ACLCHECK_OK)=0A= +=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09=20=20=20= get_relkind_objtype(rel->rd_rel->relkind),=0A+=09=09=09=09=09=20=20=20= RelationGetRelationName(rel));=0A+=0A=20=09/*=0A=20=09=20*=20Start=20a=20= transaction=20in=20the=20remote=20node=20in=20REPEATABLE=20READ=20mode.=20= =20This=0A=20=09=20*=20ensures=20that=20both=20the=20replication=20slot=20= we=20create=20(see=20below)=20and=20the=0Adiff=20--git=20= a/src/backend/replication/logical/worker.c=20= b/src/backend/replication/logical/worker.c=0Aindex=20= 8d96c926b4..6ae0b3fb21=20100644=0A---=20= a/src/backend/replication/logical/worker.c=0A+++=20= b/src/backend/replication/logical/worker.c=0A@@=20-179,6=20+179,7=20@@=0A= =20#include=20"storage/proc.h"=0A=20#include=20"storage/procarray.h"=0A=20= #include=20"tcop/tcopprot.h"=0A+#include=20"utils/acl.h"=0A=20#include=20= "utils/builtins.h"=0A=20#include=20"utils/catcache.h"=0A=20#include=20= "utils/dynahash.h"=0A@@=20-1537,6=20+1538,7=20@@=20= apply_handle_insert(StringInfo=20s)=0A=20=09LogicalRepRelMapEntry=20= *rel;=0A=20=09LogicalRepTupleData=20newtup;=0A=20=09LogicalRepRelId=20= relid;=0A+=09AclResult=09aclresult;=0A=20=09ApplyExecutionData=20*edata;=0A= =20=09EState=09=20=20=20*estate;=0A=20=09TupleTableSlot=20*remoteslot;=0A= @@=20-1559,6=20+1561,12=20@@=20apply_handle_insert(StringInfo=20s)=0A=20=09= =09end_replication_step();=0A=20=09=09return;=0A=20=09}=0A+=09aclresult=20= =3D=20pg_class_aclcheck(RelationGetRelid(rel->localrel),=20GetUserId(),=0A= +=09=09=09=09=09=09=09=09=20=20ACL_INSERT);=0A+=09if=20(aclresult=20!=3D=20= ACLCHECK_OK)=0A+=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09=20=20=20= get_relkind_objtype(rel->localrel->rd_rel->relkind),=0A+=09=09=09=09=09=20= =20=20get_rel_name(rel->localreloid));=0A=20=0A=20=09/*=20Set=20relation=20= for=20error=20callback=20*/=0A=20=09apply_error_callback_arg.rel=20=3D=20= rel;=0A@@=20-1659,6=20+1667,7=20@@=20apply_handle_update(StringInfo=20s)=0A= =20{=0A=20=09LogicalRepRelMapEntry=20*rel;=0A=20=09LogicalRepRelId=20= relid;=0A+=09AclResult=09aclresult;=0A=20=09ApplyExecutionData=20*edata;=0A= =20=09EState=09=20=20=20*estate;=0A=20=09LogicalRepTupleData=20oldtup;=0A= @@=20-1686,6=20+1695,12=20@@=20apply_handle_update(StringInfo=20s)=0A=20=09= =09end_replication_step();=0A=20=09=09return;=0A=20=09}=0A+=09aclresult=20= =3D=20pg_class_aclcheck(RelationGetRelid(rel->localrel),=20GetUserId(),=0A= +=09=09=09=09=09=09=09=09=20=20ACL_UPDATE);=0A+=09if=20(aclresult=20!=3D=20= ACLCHECK_OK)=0A+=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09=20=20=20= get_relkind_objtype(rel->localrel->rd_rel->relkind),=0A+=09=09=09=09=09=20= =20=20get_rel_name(rel->localreloid));=0A=20=0A=20=09/*=20Set=20relation=20= for=20error=20callback=20*/=0A=20=09apply_error_callback_arg.rel=20=3D=20= rel;=0A@@=20-1826,6=20+1841,7=20@@=20apply_handle_delete(StringInfo=20s)=0A= =20=09LogicalRepRelMapEntry=20*rel;=0A=20=09LogicalRepTupleData=20= oldtup;=0A=20=09LogicalRepRelId=20relid;=0A+=09AclResult=09aclresult;=0A=20= =09ApplyExecutionData=20*edata;=0A=20=09EState=09=20=20=20*estate;=0A=20=09= TupleTableSlot=20*remoteslot;=0A@@=20-1848,6=20+1864,12=20@@=20= apply_handle_delete(StringInfo=20s)=0A=20=09=09end_replication_step();=0A= =20=09=09return;=0A=20=09}=0A+=09aclresult=20=3D=20= pg_class_aclcheck(RelationGetRelid(rel->localrel),=20GetUserId(),=0A+=09=09= =09=09=09=09=09=09=20=20ACL_DELETE);=0A+=09if=20(aclresult=20!=3D=20= ACLCHECK_OK)=0A+=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09=20=20=20= get_relkind_objtype(rel->localrel->rd_rel->relkind),=0A+=09=09=09=09=09=20= =20=20get_rel_name(rel->localreloid));=0A=20=0A=20=09/*=20Set=20relation=20= for=20error=20callback=20*/=0A=20=09apply_error_callback_arg.rel=20=3D=20= rel;=0A@@=20-2220,6=20+2242,7=20@@=20apply_handle_truncate(StringInfo=20= s)=0A=20=09{=0A=20=09=09LogicalRepRelId=20relid=20=3D=20lfirst_oid(lc);=0A= =20=09=09LogicalRepRelMapEntry=20*rel;=0A+=09=09AclResult=09aclresult;=0A= =20=0A=20=09=09rel=20=3D=20logicalrep_rel_open(relid,=20lockmode);=0A=20=09= =09if=20(!should_apply_changes_for_rel(rel))=0A@@=20-2231,6=20+2254,12=20= @@=20apply_handle_truncate(StringInfo=20s)=0A=20=09=09=09= logicalrep_rel_close(rel,=20lockmode);=0A=20=09=09=09continue;=0A=20=09=09= }=0A+=09=09aclresult=20=3D=20= pg_class_aclcheck(RelationGetRelid(rel->localrel),=0A+=09=09=09=09=09=09=09= =09=09=20=20GetUserId(),=20ACL_TRUNCATE);=0A+=09=09if=20(aclresult=20!=3D=20= ACLCHECK_OK)=0A+=09=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09=09=20= =20=20get_relkind_objtype(rel->localrel->rd_rel->relkind),=0A+=09=09=09=09= =09=09=20=20=20get_rel_name(rel->localreloid));=0A=20=0A=20=09=09= remote_rels=20=3D=20lappend(remote_rels,=20rel);=0A=20=09=09rels=20=3D=20= lappend(rels,=20rel->localrel);=0A@@=20-2912,6=20+2941,7=20@@=20= maybe_reread_subscription(void)=0A=20=09=09strcmp(newsub->slotname,=20= MySubscription->slotname)=20!=3D=200=20||=0A=20=09=09newsub->binary=20!=3D= =20MySubscription->binary=20||=0A=20=09=09newsub->stream=20!=3D=20= MySubscription->stream=20||=0A+=09=09newsub->owner=20!=3D=20= MySubscription->owner=20||=0A=20=09=09!equal(newsub->publications,=20= MySubscription->publications))=0A=20=09{=0A=20=09=09ereport(LOG,=0Adiff=20= --git=20a/src/test/subscription/t/026_nosuperuser.pl=20= b/src/test/subscription/t/026_nosuperuser.pl=0Anew=20file=20mode=20= 100644=0Aindex=200000000000..03a5ac8c52=0A---=20/dev/null=0A+++=20= b/src/test/subscription/t/026_nosuperuser.pl=0A@@=20-0,0=20+1,118=20@@=0A= +=0A+#=20Copyright=20(c)=202021,=20PostgreSQL=20Global=20Development=20= Group=0A+=0A+#=20Test=20subscriptions=20owned=20by=20non-superusers=0A= +use=20strict;=0A+use=20warnings;=0A+use=20PostgreSQL::Test::Cluster;=0A= +use=20PostgreSQL::Test::Utils;=0A+use=20Test::More=20tests=20=3D>=203;=0A= +=0A+#=20Setup=0A+=0A+my=20$node_publisher=20=3D=20= PostgreSQL::Test::Cluster->new('publisher');=0A= +$node_publisher->init(allows_streaming=20=3D>=20'logical');=0A= +$node_publisher->start;=0A+=0A+my=20$node_subscriber=20=3D=20= PostgreSQL::Test::Cluster->new('subscriber');=0A= +$node_subscriber->init(allows_streaming=20=3D>=20'logical');=0A= +$node_subscriber->start;=0A+=0A+my=20$publisher_connstr=20=3D=20= $node_publisher->connstr=20.=20'=20dbname=3Dpostgres';=0A+=0A+#=20Create=20= the=20same=20schema,=20roles=20and=20permissions=20on=20both=20nodes.=0A= +my=20$sql=20=3D=20qq(=0A+CREATE=20ROLE=20regress_role_alice=20= NOSUPERUSER=20NOLOGIN;=0A+GRANT=20CREATE=20ON=20DATABASE=20postgres=20TO=20= regress_role_alice;=0A+SET=20ROLE=20regress_role_alice;=0A+CREATE=20= SCHEMA=20alice;=0A+CREATE=20TABLE=20alice.tbl1=20(i1=20INTEGER);=0A= +ALTER=20TABLE=20alice.tbl1=20REPLICA=20IDENTITY=20FULL;=0A+RESET=20= ROLE;=0A+=0A+CREATE=20ROLE=20regress_role_bob=20NOSUPERUSER=20NOLOGIN;=0A= +GRANT=20CREATE=20ON=20DATABASE=20postgres=20TO=20regress_role_bob;=0A= +SET=20ROLE=20regress_role_bob;=0A+CREATE=20SCHEMA=20bob;=0A+CREATE=20= TABLE=20bob.tbl1=20(i1=20INTEGER);=0A+CREATE=20TABLE=20bob.tbl2=20(i2=20= INTEGER);=0A+ALTER=20TABLE=20bob.tbl1=20REPLICA=20IDENTITY=20FULL;=0A= +ALTER=20TABLE=20bob.tbl2=20REPLICA=20IDENTITY=20FULL;=0A+RESET=20ROLE;=0A= +);=0A+$node_publisher->safe_psql('postgres',=20$sql);=0A= +$node_subscriber->safe_psql('postgres',=20$sql);=0A+=0A+#=20Bob=20= publishes.=0A+$node_publisher->safe_psql('postgres',=20qq(=0A+SET=20ROLE=20= regress_role_bob;=0A+CREATE=20PUBLICATION=20bob_pub=20FOR=20TABLE=20= bob.tbl1;=0A+INSERT=20INTO=20bob.tbl1=20VALUES=20(1);=0A+));=0A+=0A+#=20= Bob=20gets=20DBA=20to=20create=20a=20subscribe=20for=20him.=0A= +$node_subscriber->safe_psql('postgres',=20qq(=0A+CREATE=20SUBSCRIPTION=20= bob_sub=20CONNECTION=20'$publisher_connstr'=20PUBLICATION=20bob_pub;=0A= +ALTER=20SUBSCRIPTION=20bob_sub=20OWNER=20TO=20regress_role_bob;=0A+));=0A= +=0A+#=20Wait=20for=20initial=20sync=20of=20all=20subscriptions.=0A+my=20= $synced_query=20=3D=0A+=20=20"SELECT=20count(1)=20=3D=200=20FROM=20= pg_subscription_rel=20WHERE=20srsubstate=20NOT=20IN=20('r',=20's');";=0A= +$node_subscriber->poll_query_until('postgres',=20$synced_query)=0A+=20=20= or=20die=20"Timed=20out=20while=20waiting=20for=20subscriber=20to=20= synchronize=20data";=0A+=0A+#=20Check=20that=20the=20data=20came=20= through.=0A+my=20$result=20=3D=20$node_subscriber->safe_psql('postgres',=20= qq(=0A+SET=20ROLE=20regress_role_bob;=0A+SELECT=20i1=20FROM=20bob.tbl1;=0A= +));=0A+is(=20$result,=20'1',=20'data=20from=20schema=20bob=20was=20= replicated');=0A+=0A+#=20A=20malicious=20dba=20on=20publisher=20adds=20= alice's=20tables=20to=20publication=20"bob_pub".=0A= +$node_publisher->safe_psql('postgres',=20qq(=0A+ALTER=20PUBLICATION=20= bob_pub=20ADD=20TABLE=20alice.tbl1;=0A+INSERT=20INTO=20alice.tbl1=20= VALUES=20(1);=0A+));=0A+=0A+#=20Bob=20adds=20another=20table=20on=20= publisher=20side=20to=20the=20publication=20and=20performs=0A+#=20DML=20= on=20the=20new=20table.=0A+$node_publisher->safe_psql('postgres',=20qq(=0A= +SET=20ROLE=20regress_role_bob;=0A+ALTER=20PUBLICATION=20bob_pub=20ADD=20= TABLE=20bob.tbl2;=0A+INSERT=20INTO=20bob.tbl2=20VALUES=20(2);=0A+));=0A+=0A= +#=20Bob=20refreshes=20on=20subscriber=20side=20to=20get=20the=20new=20= data=20from=20"bob.tbl2",=20not=0A+#=20knowing=20that=20alice's=20tables=20= have=20been=20added.=20=20An=20error=20during=20tablesync=20or=0A+#=20= apply=20should=20prevent=20the=20data=20in=20schema=20"alice"=20being=20= replicated.=0A+#=0A+$node_subscriber->psql('postgres',=20qq(=0A+SET=20= ROLE=20regress_role_bob;=0A+ALTER=20SUBSCRIPTION=20bob_sub=20REFRESH=20= PUBLICATION;=0A+));=0A+=0A+#=20Wait=20for=20sync=20of=20all=20= subscriptions.=0A+$node_subscriber->poll_query_until('postgres',=20= $synced_query)=0A+=20=20or=20die=20"Timed=20out=20while=20waiting=20for=20= subscriber=20to=20synchronize=20data";=0A+=0A+#=20The=20data=20for=20= schema=20"bob"=20might=20not=20be=20replicated,=20because=20the=20= replication=0A+#=20may=20abort=20first=20on=20a=20permissions=20error=20= for=20schema=20"alice".=20=20We=20insist=20that=0A+#=20data=20for=20= "alice"=20not=20be=20replicated,=20but=20we=20only=20insist=20that=20if=20= data=20from=0A+#=20"bob"=20gets=20replicated=20that=20it=20be=20the=20= correct=20new=20data=20and=20not=20corrupted.=0A+$result=20=3D=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+SELECT=20a.i1::text=20= ||=20'_'=20||=0A+=09=20=20=20coalesce(b.i2::text,'null')=20||=20'_'=20||=0A= +=09=20=20=20coalesce(c.i1::text,'null')=0A+=09FROM=20bob.tbl1=20a=0A+=09= LEFT=20JOIN=20bob.tbl2=20b=20ON=20TRUE=0A+=09LEFT=20JOIN=20alice.tbl1=20= c=20ON=20TRUE;=0A+));=0A+like=20($result,=20= qr/^(?:1_null_null|1_2_null)$/,=0A+=09'subscriber=20side=20schema=20bob=20= data=20is=20reasonable=20and=20alice=20data=20is=20unreplicated');=0A+=0A= +#=20check=20that=20the=20logs=20on=20the=20subscriber=20side=20contain=20= the=20expected=20permissions=0A+#=20error=20associated=20with=20a=20= failed=20attempt=20to=20replicate=20into=20schema=20"alice".=0A+my=20= $subscriber_log=20=3D=20slurp_file($node_subscriber->logfile);=0A+like=20= ($subscriber_log,=20qr/ERROR:=20=20permission=20denied=20for=20schema=20= alice/msi,=0A+=09'subscriber=20lacks=20permission=20for=20schema=20= alice');=0A--=20=0A2.21.1=20(Apple=20Git-122.3)=0A=0A= --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 =E2=80=94 Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --Apple-Mail=_CEA8AB37-FA79-4F4E-A3A5-CACB1D66B209--