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 1mxanx-0008Iv-Gn for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Dec 2021 20:23:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mxanw-0004b4-Bi for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Dec 2021 20:23:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mxanv-0004av-L2 for pgsql-hackers@lists.postgresql.org; Wed, 15 Dec 2021 20:23:12 +0000 Received: from mail-pl1-x62a.google.com ([2607:f8b0:4864:20::62a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mxanr-0002bP-AO for pgsql-hackers@postgresql.org; Wed, 15 Dec 2021 20:23:11 +0000 Received: by mail-pl1-x62a.google.com with SMTP id z6so17503331plk.6 for ; Wed, 15 Dec 2021 12:23:06 -0800 (PST) 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=n1H/VPx6jCvxE6/MyF7rMvZC28QWdfN7Y+aUvMxKdiM=; b=iAGMh+5kiSS+4iEB1FlNwFu3KRVxZyf7Aoj0vbjyk3P0hDbP7ICQtGm7BTqzv8yAuR bGqw7L+/X5ddCyRu4vjlvF6L+YYVBWTn8OIb60xKZKBMbfTPnXhAvKVpbk8sME8vor50 ZU/uGYJ45WNLR1Rvv0stLrIZtb1JIX4rfn36qarXC1Dkxv9L/eBF3nPcbSLxF33C/X17 Vl1Vc1ZdZfbRi8aflyr4fUIIZZsBcD9rfYHyupGqJoGSMFXzKK8sE1VrBf1fp3QUS1Ru OmGLr67c1BnyXg755fyPyG1tQPw4H1/3a6vuOJgXv7szV+QWaZzrdvsomHKg+UZl1X/n Nang== 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=n1H/VPx6jCvxE6/MyF7rMvZC28QWdfN7Y+aUvMxKdiM=; b=P46hNhW7jsXRVsLOisfgqdIxiRRP27wWBu5yaRwzUXktaj7cH9xvEyNzA3YUvGDF9U W1pTP3IqZVOVtv/Bsv/G4xYCfyAX/YCEixq94Wcb+aUQtcL0OjXiI7jLM9nHQiJwI9iP WFPhVf8cgfmzhXanmPNH8/re1Hnd+ZCuKDaBltN+vkWfhA6FLO+1fGU6E/XgzD9ABzY4 8+T/QHlgg5++hTbW9JKCGD/58Eb1FN3UCaOgOxmX7KHP4Pj9/NNxsTs/sr9XhK2wn1Ly +wtx6dTjTsrMx+KvzG5O1iXhQSiB49nS68fLSEIs8LG6IbJnlqb86qs+EmUHIbi6GUjv qaGA== X-Gm-Message-State: AOAM531Wce5LwQx1uy3C+jFlp5BnPVoikDd2VLS+ULCEzq0sSoCktrJL WMSxw5k7E2qMXH4XYAFol6HzOWZX2eRS36D1YtpGHEePMrKyb/32J1I2uugcoi5uxuxDRIJaUyM G+1B5QbcegU0lR+3bV5PEdHVprvwZujkEU3yjBtQZqVa8xUNe4HZ1wfqntGsTpd7YhTb2VHKZ0W wZFSK1DdkYC77VCmvyAjfrJSTTq0cFmgW7f2hp X-Google-Smtp-Source: ABdhPJwg58Ca6j458lpDvzJpI+TnmZWILrf5nLXGojKeNKG+EBbpE9iqsZ588NC5q/IF6umBPrl/Og== X-Received: by 2002:a17:902:7085:b0:148:adf5:f5fe with SMTP id z5-20020a170902708500b00148adf5f5femr3242685plk.85.1639599784589; Wed, 15 Dec 2021 12:23:04 -0800 (PST) Received: from laptop280-ma-us.hitronhub.home (24-113-193-150.wavecable.com. [24.113.193.150]) by smtp.gmail.com with ESMTPSA id mi18sm3167805pjb.13.2021.12.15.12.23.03 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 15 Dec 2021 12:23:03 -0800 (PST) From: Mark Dilger Message-Id: <92BEDE2A-8043-4F50-9BD8-46977A9A2972@enterprisedb.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_7C6BF487-AFC4-43FE-8D7D-35B2F3336BD2" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Non-superuser subscription owners Date: Wed, 15 Dec 2021 12:23:02 -0800 In-Reply-To: <66f55a6b9ff7bdd6bfd0d05e8fd8351690e69e23.camel@j-davis.com> Cc: Amit Kapila , Andrew Dunstan , PostgreSQL-development , Robert Haas To: Jeff Davis References: <9DFC88D3-1300-4DE8-ACBC-4CEF84399A53@enterprisedb.com> <6BB4451E-7B1B-474C-BD1F-DB7531E720C6@enterprisedb.com> <6A2B0FF6-CC86-48CE-B0D3-5401AA5CFEA9@enterprisedb.com> <1BB0A553-3FE9-4A91-A975-6F9D8C157FBD@enterprisedb.com> <7C62876F-5D19-4B5C-96AC-5590B10A49B2@enterprisedb.com> <66f55a6b9ff7bdd6bfd0d05e8fd8351690e69e23.camel@j-davis.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=_7C6BF487-AFC4-43FE-8D7D-35B2F3336BD2 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Nov 24, 2021, at 4:30 PM, Jeff Davis wrote: >=20 > We need to do permission checking for WITH CHECK OPTION and RLS. The > patch right now allows the subscription to write data that an RLS > policy forbids. Version 4 of the patch, attached, no longer allows RLS to be = circumvented, but does so in a course-grained fashion. If the target = table has row-level security policies which are enforced against the = subscription owner, the replication draws an error, much as with a = permissions failure. This seems sufficient for now, as superusers, = roles with bypassrls, and target table owners should be able to = replicate as before. We may want to revisit this later, perhaps if/when = we address your ExecInsert question, below. >=20 > A couple other points: >=20 > * We shouldn't refer to the behavior of previous versions in the docs > unless there's a compelling reason Fixed. > * Do we need to be smarter about partitioned tables, where an insert > can turn into an update? Indeed, the logic of apply_handle_tuple_routing() required a bit of = refactoring. Fixed in v4. > * Should we refactor to borrow logic from ExecInsert so that it's less > likely that we miss something in the future? Let's just punt on this for now. --Apple-Mail=_7C6BF487-AFC4-43FE-8D7D-35B2F3336BD2 Content-Disposition: attachment; filename=v4-0001-Respect-permissions-within-logical-replication.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v4-0001-Respect-permissions-within-logical-replication.patch" Content-Transfer-Encoding: quoted-printable =46rom=2003ee011cc72768efbdd3e69eb2dcac7ed21ca0c7=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mark=20Dilger=20=0A= Date:=20Fri,=2019=20Nov=202021=2016:08:40=20-0800=0ASubject:=20[PATCH=20= v4]=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.=0A= =0APrevent=20subscription=20owners=20from=20circumventing=20row-level=20= security=20by=0Aforbidding=20replication=20into=20tables=20with=20= row-level=20security=20policies=0Awhich=20the=20subscription=20owner=20= is=20subject=20to,=20without=20regard=20to=0Awhether=20the=20policy=20= would=20ordinary=20allow=20the=20INSERT,=20UPDATE,=20DELETE=0Aor=20= TRUNCATE=20which=20is=20being=20replicated.=20=20This=20seems=20= sufficient=20for=0Anow,=20as=20superusers,=20roles=20with=20bypassrls,=20= and=20target=20table=20owners=0Ashould=20still=20be=20able=20to=20= replicate=20despite=20RLS=20policies.=20=20We=20can=0Arevisit=20the=20= question=20of=20applying=20row-level=20security=20policies=20on=20a=0A= per-row=20basis=20if=20this=20restriction=20proves=20too=20severe=20in=20= practice.=0A---=0A=20doc/src/sgml/logical-replication.sgml=20=20=20=20=20= =20=20|=20=2036=20+-=0A=20src/backend/commands/subscriptioncmds.c=20=20=20= =20=20|=20=20=202=20+=0A=20src/backend/replication/logical/tablesync.c=20= |=20=2028=20++=0A=20src/backend/replication/logical/worker.c=20=20=20=20= |=20=2042=20+++=0A=20src/test/perl/PostgreSQL/Test/Cluster.pm=20=20=20=20= |=20=2036=20++=0A=20src/test/subscription/t/027_nosuperuser.pl=20=20|=20= 363=20++++++++++++++++++++=0A=206=20files=20changed,=20499=20= insertions(+),=208=20deletions(-)=0A=20create=20mode=20100644=20= src/test/subscription/t/027_nosuperuser.pl=0A=0Adiff=20--git=20= a/doc/src/sgml/logical-replication.sgml=20= b/doc/src/sgml/logical-replication.sgml=0Aindex=2045b2e1e28f..4f70b1f4b8=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,19=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=20Permissions=20failures=20on=20target=20tables=20will=0A= +=20=20=20cause=20replication=20conflicts,=20as=20will=20enabled=0A+=20=20= =20row-level=20security=20on=20= target=20tables=0A+=20=20=20that=20the=20subscription=20owner=20is=20= subject=20to,=20without=20regard=20to=20whether=20any=0A+=20=20=20policy=20= would=20ordinary=20reject=20the=20INSERT,=0A+=20=20=20= UPDATE,=20DELETE=20or=0A+=20=20=20= TRUNCATE=20which=20is=20being=20replicated.=20=20This=20= restriction=20on=0A+=20=20=20row-level=20security=20may=20be=20lifted=20= in=20a=20future=20version=20of=0A+=20=20=20= PostgreSQL.=0A+=20=20=0A+=0A=20=20=20= =0A=20=20=20=20A=20conflict=20will=20produce=20an=20error=20and=20= will=20stop=20the=20replication;=20it=20must=20be=0A=20=20=20=20resolved=20= manually=20by=20the=20user.=20=20Details=20about=20the=20conflict=20can=20= be=20found=20in=0A@@=20-337,7=20+350,7=20@@=0A=20=20=20=0A=20=0A=20= =20=20=0A-=20=20=20The=20resolution=20can=20be=20done=20either=20= by=20changing=20data=20on=20the=20subscriber=20so=0A+=20=20=20The=20= resolution=20can=20be=20done=20either=20by=20changing=20data=20or=20= permissions=20on=20the=20subscriber=20so=0A=20=20=20=20that=20it=20does=20= not=20conflict=20with=20the=20incoming=20change=20or=20by=20skipping=20= the=0A=20=20=20=20transaction=20that=20conflicts=20with=20the=20existing=20= data.=20=20The=20transaction=20can=20be=0A=20=20=20=20skipped=20by=20= calling=20the=20=0A@@=20= -530,9=20+543,9=20@@=0A=20=0A=20=20=20=0A=20=20=20=20A=20user=20= able=20to=20modify=20the=20schema=20of=20subscriber-side=20tables=20can=20= execute=0A-=20=20=20arbitrary=20code=20as=20a=20superuser.=20=20Limit=20= ownership=0A-=20=20=20and=20TRIGGER=20privilege=20on=20= such=20tables=20to=20roles=20that=0A-=20=20=20superusers=20trust.=20=20= Moreover,=20if=20untrusted=20users=20can=20create=20tables,=20use=20only=0A= +=20=20=20arbitrary=20code=20as=20the=20role=20which=20owns=20any=20= subscription=20which=20modifies=20those=20tables.=20=20Limit=20ownership=0A= +=20=20=20and=20TRIGGER=20privilege=20on=20such=20= tables=20to=20trusted=20roles.=0A+=20=20=20Moreover,=20if=20untrusted=20= users=20can=20create=20tables,=20use=20only=0A=20=20=20=20publications=20= that=20list=20tables=20explicitly.=20=20That=20is=20to=20say,=20create=20= a=0A=20=20=20=20subscription=20FOR=20ALL=20TABLES=20= or=0A=20=20=20=20FOR=20ALL=20TABLES=20IN=20SCHEMA=20= only=20when=20superusers=20trust=0A@@=20-576,13=20+589,20=20@@=0A=20=0A=20= =20=20=0A=20=20=20=20The=20subscription=20apply=20process=20will=20= run=20in=20the=20local=20database=20with=20the=0A-=20=20=20privileges=20= of=20a=20superuser.=0A+=20=20=20privileges=20of=20the=20subscription=20= owner.=0A+=20=20=0A+=0A+=20=20=0A+=20=20=20On=20the=20= publisher,=20privileges=20are=20only=20checked=20once=20at=20the=20start=20= of=20a=0A+=20=20=20replication=20connection=20and=20are=20not=20= re-checked=20as=20each=20change=20record=20is=20read.=0A=20=20=20=0A= =20=0A=20=20=20=0A-=20=20=20Privileges=20are=20only=20checked=20= once=20at=20the=20start=20of=20a=20replication=20connection.=0A-=20=20=20= They=20are=20not=20re-checked=20as=20each=20change=20record=20is=20read=20= from=20the=20publisher,=0A-=20=20=20nor=20are=20they=20re-checked=20for=20= each=20change=20when=20applied.=0A+=20=20=20On=20the=20subscriber,=20the=20= subscription=20owner's=20privileges=20are=20re-checked=20for=0A+=20=20=20= each=20transaction=20when=20applied.=20If=20a=20worker=20is=20in=20the=20= process=20of=20applying=20a=0A+=20=20=20transaction=20when=20the=20= ownership=20of=20the=20subscription=20is=20changed=20by=20a=0A+=20=20=20= concurrent=20transaction,=20the=20application=20of=20the=20current=20= transaction=20will=0A+=20=20=20continue=20under=20the=20old=20owner's=20= privileges.=0A=20=20=20=0A=20=20=0A=20=0Adiff=20--git=20= a/src/backend/commands/subscriptioncmds.c=20= b/src/backend/commands/subscriptioncmds.c=0Aindex=20= 2b658080fe..e33f26e6cf=20100644=0A---=20= a/src/backend/commands/subscriptioncmds.c=0A+++=20= b/src/backend/commands/subscriptioncmds.c=0A@@=20-1481,6=20+1481,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..d44ccdc0de=20100644=0A---=20= a/src/backend/replication/logical/tablesync.c=0A+++=20= b/src/backend/replication/logical/tablesync.c=0A@@=20-111,9=20+111,11=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+#include=20= "utils/rls.h"=0A=20#include=20"utils/snapmgr.h"=0A=20#include=20= "utils/syscache.h"=0A=20=0A@@=20-924,6=20+926,7=20@@=20= LogicalRepSyncTableStart(XLogRecPtr=20*origin_startpos)=0A=20=09char=09=09= relstate;=0A=20=09XLogRecPtr=09relstate_lsn;=0A=20=09Relation=09rel;=0A+=09= AclResult=09aclresult;=0A=20=09WalRcvExecResult=20*res;=0A=20=09char=09=09= originname[NAMEDATALEN];=0A=20=09RepOriginId=20originid;=0A@@=20-1042,6=20= +1045,31=20@@=20LogicalRepSyncTableStart(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+=09/*=0A+=09=20*=20COPY=20FROM=20= does=20not=20honor=20RLS=20policies.=20=20That=20is=20not=20a=20problem=20= for=0A+=09=20*=20subscriptions=20owned=20by=20roles=20with=20BYPASSRLS=20= privilege=20(or=20superuser,=20who=0A+=09=20*=20has=20it=20implicitly),=20= but=20other=20roles=20should=20not=20be=20able=20to=20circumvent=0A+=09=20= *=20RLS.=20=20Disallow=20logical=20replication=20into=20RLS=20enabled=20= relations=20for=20such=0A+=09=20*=20roles.=0A+=09=20*/=0A+=09if=20= (check_enable_rls(RelationGetRelid(rel),=20InvalidOid,=20false)=20=3D=3D=20= RLS_ENABLED)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),=0A+=09=09=09=09=20= errmsg("\"%s\"=20cannot=20replicate=20into=20relation=20with=20row-level=20= security=20enabled:=20\"%s\"",=0A+=09=09=09=09=09=09= GetUserNameFromId(GetUserId(),=20true),=0A+=09=09=09=09=09=09= 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= 2e79302a48..188eeb80aa=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-189,6=20+190,7=20@@=0A=20#include=20= "utils/lsyscache.h"=0A=20#include=20"utils/memutils.h"=0A=20#include=20= "utils/rel.h"=0A+#include=20"utils/rls.h"=0A=20#include=20= "utils/syscache.h"=0A=20#include=20"utils/timeout.h"=0A=20=0A@@=20= -1530,6=20+1532,38=20@@=20GetRelationIdentityOrPK(Relation=20rel)=0A=20=09= return=20idxoid;=0A=20}=0A=20=0A+/*=0A+=20*=20Check=20that=20we=20(the=20= subscription=20owner)=20have=20sufficient=20privileges=20on=20the=0A+=20= *=20target=20relation=20to=20perform=20the=20given=20operation.=0A+=20*/=0A= +static=20void=0A+TargetPrivilegesCheck(Relation=20rel,=20AclMode=20= mode)=0A+{=0A+=09Oid=09=09=09=09relid;=0A+=09AclResult=09=09aclresult;=0A= +=0A+=09relid=20=3D=20RelationGetRelid(rel);=0A+=09aclresult=20=3D=20= pg_class_aclcheck(relid,=20GetUserId(),=20mode);=0A+=09if=20(aclresult=20= !=3D=20ACLCHECK_OK)=0A+=09=09aclcheck_error(aclresult,=0A+=09=09=09=09=09= =20=20=20get_relkind_objtype(rel->rd_rel->relkind),=0A+=09=09=09=09=09=20= =20=20get_rel_name(relid));=0A+=0A+=09/*=0A+=09=20*=20We=20lack=20the=20= infrastructure=20to=20honor=20RLS=20policies.=20=20It=20might=20be=20= possible=0A+=09=20*=20to=20add=20such=20infrastructure=20here,=20but=20= tablesync=20workers=20lack=20it,=20too,=20so=0A+=09=20*=20we=20don't=20= bother.=20=20RLS=20does=20not=20ordinarily=20apply=20to=20TRUNCATE=20= commands,=0A+=09=20*=20but=20it=20seems=20dangerous=20to=20replicate=20a=20= TRUNCATE=20and=20then=20refuse=20to=0A+=09=20*=20replicate=20subsequent=20= INSERTs,=20so=20we=20forbid=20all=20commands=20the=20same.=0A+=09=20*/=0A= +=09if=20(check_enable_rls(relid,=20InvalidOid,=20false)=20=3D=3D=20= RLS_ENABLED)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),=0A+=09=09=09=09=20= errmsg("\"%s\"=20cannot=20replicate=20into=20relation=20with=20row-level=20= security=20enabled:=20\"%s\"",=0A+=09=09=09=09=09=09= GetUserNameFromId(GetUserId(),=20true),=0A+=09=09=09=09=09=09= RelationGetRelationName(rel))));=0A+}=0A+=0A=20/*=0A=20=20*=20Handle=20= INSERT=20message.=0A=20=20*/=0A@@=20-1613,6=20+1647,7=20@@=20= apply_handle_insert_internal(ApplyExecutionData=20*edata,=0A=20=09= ExecOpenIndices(relinfo,=20false);=0A=20=0A=20=09/*=20Do=20the=20insert.=20= */=0A+=09TargetPrivilegesCheck(relinfo->ri_RelationDesc,=20ACL_INSERT);=0A= =20=09ExecSimpleRelationInsert(relinfo,=20estate,=20remoteslot);=0A=20=0A= =20=09/*=20Cleanup.=20*/=0A@@=20-1796,6=20+1831,7=20@@=20= apply_handle_update_internal(ApplyExecutionData=20*edata,=0A=20=09=09= EvalPlanQualSetSlot(&epqstate,=20remoteslot);=0A=20=0A=20=09=09/*=20Do=20= the=20actual=20update.=20*/=0A+=09=09= TargetPrivilegesCheck(relinfo->ri_RelationDesc,=20ACL_UPDATE);=0A=20=09=09= ExecSimpleRelationUpdate(relinfo,=20estate,=20&epqstate,=20localslot,=0A=20= =09=09=09=09=09=09=09=09=20remoteslot);=0A=20=09}=0A@@=20-1917,6=20= +1953,7=20@@=20apply_handle_delete_internal(ApplyExecutionData=20*edata,=0A= =20=09=09EvalPlanQualSetSlot(&epqstate,=20localslot);=0A=20=0A=20=09=09= /*=20Do=20the=20actual=20delete.=20*/=0A+=09=09= TargetPrivilegesCheck(relinfo->ri_RelationDesc,=20ACL_DELETE);=0A=20=09=09= ExecSimpleRelationDelete(relinfo,=20estate,=20&epqstate,=20localslot);=0A= =20=09}=0A=20=09else=0A@@=20-2110,6=20+2147,8=20@@=20= apply_handle_tuple_routing(ApplyExecutionData=20*edata,=0A=20=09=09=09=09= =09ExecOpenIndices(partrelinfo,=20false);=0A=20=0A=20=09=09=09=09=09= EvalPlanQualSetSlot(&epqstate,=20remoteslot_part);=0A+=09=09=09=09=09= TargetPrivilegesCheck(partrelinfo->ri_RelationDesc,=0A+=09=09=09=09=09=09= =09=09=09=09=20=20ACL_UPDATE);=0A=20=09=09=09=09=09= ExecSimpleRelationUpdate(partrelinfo,=20estate,=20&epqstate,=0A=20=09=09=09= =09=09=09=09=09=09=09=09=20localslot,=20remoteslot_part);=0A=20=09=09=09=09= =09ExecCloseIndices(partrelinfo);=0A@@=20-2236,6=20+2275,7=20@@=20= apply_handle_truncate(StringInfo=20s)=0A=20=09=09}=0A=20=0A=20=09=09= remote_rels=20=3D=20lappend(remote_rels,=20rel);=0A+=09=09= TargetPrivilegesCheck(rel->localrel,=20ACL_TRUNCATE);=0A=20=09=09rels=20= =3D=20lappend(rels,=20rel->localrel);=0A=20=09=09relids=20=3D=20= lappend_oid(relids,=20rel->localreloid);=0A=20=09=09if=20= (RelationIsLogicallyLogged(rel->localrel))=0A@@=20-2273,6=20+2313,7=20@@=20= apply_handle_truncate(StringInfo=20s)=0A=20=09=09=09=09=09continue;=0A=20= =09=09=09=09}=0A=20=0A+=09=09=09=09TargetPrivilegesCheck(childrel,=20= ACL_TRUNCATE);=0A=20=09=09=09=09rels=20=3D=20lappend(rels,=20childrel);=0A= =20=09=09=09=09part_rels=20=3D=20lappend(part_rels,=20childrel);=0A=20=09= =09=09=09relids=20=3D=20lappend_oid(relids,=20childrelid);=0A@@=20= -2915,6=20+2956,7=20@@=20maybe_reread_subscription(void)=0A=20=09=09= strcmp(newsub->slotname,=20MySubscription->slotname)=20!=3D=200=20||=0A=20= =09=09newsub->binary=20!=3D=20MySubscription->binary=20||=0A=20=09=09= newsub->stream=20!=3D=20MySubscription->stream=20||=0A+=09=09= newsub->owner=20!=3D=20MySubscription->owner=20||=0A=20=09=09= !equal(newsub->publications,=20MySubscription->publications))=0A=20=09{=0A= =20=09=09ereport(LOG,=0Adiff=20--git=20= a/src/test/perl/PostgreSQL/Test/Cluster.pm=20= b/src/test/perl/PostgreSQL/Test/Cluster.pm=0Aindex=20= c061e850fb..6f38be031d=20100644=0A---=20= a/src/test/perl/PostgreSQL/Test/Cluster.pm=0A+++=20= b/src/test/perl/PostgreSQL/Test/Cluster.pm=0A@@=20-2599,6=20+2599,42=20= @@=20sub=20wait_for_slot_catchup=0A=20=0A=20=3Dpod=0A=20=0A+=3Ditem=20= $node->wait_for_log(regexp,=20offset)=0A+=0A+Waits=20for=20the=20= contents=20of=20the=20server=20log=20file,=20starting=20at=20the=20given=20= offset,=20to=0A+match=20the=20supplied=20regular=20expression.=20=20= Checks=20the=20entire=20log=20if=20no=20offset=20is=0A+given.=20=20Times=20= out=20after=20180=20seconds.=0A+=0A+If=20successful,=20returns=20the=20= length=20of=20the=20entire=20log=20file,=20in=20bytes.=0A+=0A+=3Dcut=0A+=0A= +sub=20wait_for_log=0A+{=0A+=09my=20($self,=20$regexp,=20$offset)=20=3D=20= @_;=0A+=09$offset=20=3D=200=20unless=20defined=20$offset;=0A+=0A+=09my=20= $max_attempts=20=3D=20180=20*=2010;=0A+=09my=20$attempts=20=20=20=20=20=3D= =200;=0A+=0A+=09while=20($attempts=20<=20$max_attempts)=0A+=09{=0A+=09=09= my=20$log=20=3D=20PostgreSQL::Test::Utils::slurp_file($self->logfile,=20= $offset);=0A+=0A+=09=09return=20$offset+length($log)=20if=20($log=20=3D~=20= m/$regexp/);=0A+=0A+=09=09#=20Wait=200.1=20second=20before=20retrying.=0A= +=09=09usleep(100_000);=0A+=0A+=09=09$attempts++;=0A+=09}=0A+=0A+=09#=20= The=20logs=20didn't=20match=20within=20180=20seconds.=20Give=20up.=0A+=09= croak=20"timed=20out=20waiting=20for=20match:=20$regexp";=0A+}=0A+=0A= +=3Dpod=0A+=0A=20=3Ditem=20$node->query_hash($dbname,=20$query,=20= @columns)=0A=20=0A=20Execute=20$query=20on=20$dbname,=20replacing=20any=20= appearance=20of=20the=20string=20__COLUMNS__=0Adiff=20--git=20= a/src/test/subscription/t/027_nosuperuser.pl=20= b/src/test/subscription/t/027_nosuperuser.pl=0Anew=20file=20mode=20= 100644=0Aindex=200000000000..742a745cf7=0A---=20/dev/null=0A+++=20= b/src/test/subscription/t/027_nosuperuser.pl=0A@@=20-0,0=20+1,363=20@@=0A= +=0A+#=20Copyright=20(c)=202021,=20PostgreSQL=20Global=20Development=20= Group=0A+=0A+#=20Test=20that=20logical=20replication=20respects=20= permissions=0A+use=20strict;=0A+use=20warnings;=0A+use=20= PostgreSQL::Test::Cluster;=0A+use=20Test::More=20tests=20=3D>=20100;=0A+=0A= +my=20($node_publisher,=20$node_subscriber,=20$publisher_connstr,=20= $result,=20$offset);=0A+$offset=20=3D=200;=0A+=0A+sub=20= publish_insert($$)=0A+{=0A+=20=20my=20($tbl,=20$new_i)=20=3D=20@_;=0A+=20= =20$node_publisher->safe_psql('postgres',=20qq(=0A+=20=20SET=20SESSION=20= AUTHORIZATION=20regress_alice;=0A+=20=20INSERT=20INTO=20$tbl=20(i)=20= VALUES=20($new_i);=0A+=20=20));=0A+}=0A+=0A+sub=20publish_update($$$)=0A= +{=0A+=20=20my=20($tbl,=20$old_i,=20$new_i)=20=3D=20@_;=0A+=20=20= $node_publisher->safe_psql('postgres',=20qq(=0A+=20=20SET=20SESSION=20= AUTHORIZATION=20regress_alice;=0A+=20=20UPDATE=20$tbl=20SET=20i=20=3D=20= $new_i=20WHERE=20i=20=3D=20$old_i;=0A+=20=20));=0A+}=0A+=0A+sub=20= publish_delete($$)=0A+{=0A+=20=20my=20($tbl,=20$old_i)=20=3D=20@_;=0A+=20= =20$node_publisher->safe_psql('postgres',=20qq(=0A+=20=20SET=20SESSION=20= AUTHORIZATION=20regress_alice;=0A+=20=20DELETE=20FROM=20$tbl=20WHERE=20i=20= =3D=20$old_i;=0A+=20=20));=0A+}=0A+=0A+sub=20expect_replication($$$$$)=0A= +{=0A+=20=20my=20($tbl,=20$cnt,=20$min,=20$max,=20$testname)=20=3D=20@_;=0A= +=20=20$node_publisher->wait_for_catchup('admin_sub');=0A+=20=20$result=20= =3D=20$node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20SELECT=20= COUNT(i),=20MIN(i),=20MAX(i)=20FROM=20$tbl));=0A+=20=20is=20($result,=20= "$cnt|$min|$max",=20$testname);=0A+}=0A+=0A+sub=20expect_failure($$$$$$)=0A= +{=0A+=20=20my=20($tbl,=20$cnt,=20$min,=20$max,=20$re,=20$testname)=20=3D=20= @_;=0A+=20=20$offset=20=3D=20$node_subscriber->wait_for_log($re,=20= $offset);=0A+=20=20$result=20=3D=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20SELECT=20= COUNT(i),=20MIN(i),=20MAX(i)=20FROM=20$tbl));=0A+=20=20is=20($result,=20= "$cnt|$min|$max",=20$testname);=0A+}=0A+=0A+sub=20revoke_superuser($)=0A= +{=0A+=20=20my=20($role)=20=3D=20@_;=0A+=20=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20ALTER=20ROLE=20= $role=20NOSUPERUSER));=0A+}=0A+=0A+sub=20grant_superuser($)=0A+{=0A+=20=20= my=20($role)=20=3D=20@_;=0A+=20=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20ALTER=20ROLE=20= $role=20SUPERUSER));=0A+}=0A+=0A+sub=20revoke_bypassrls($)=0A+{=0A+=20=20= my=20($role)=20=3D=20@_;=0A+=20=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20ALTER=20ROLE=20= $role=20NOBYPASSRLS));=0A+}=0A+=0A+sub=20grant_bypassrls($)=0A+{=0A+=20=20= my=20($role)=20=3D=20@_;=0A+=20=20= $node_subscriber->safe_psql('postgres',=20qq(=0A+=20=20ALTER=20ROLE=20= $role=20BYPASSRLS));=0A+}=0A+=0A+#=20Create=20publisher=20and=20= subscriber=20nodes=20with=20schemas=20owned=20and=20published=20by=0A+#=20= "regress_alice"=20but=20subscribed=20and=20replicated=20by=20different=20= role=0A+#=20"regress_admin".=20=20For=20partitioned=20tables,=20layout=20= the=20partitions=20differently=0A+#=20on=20the=20publisher=20than=20on=20= the=20subscriber.=0A+#=0A+$node_publisher=20=3D=20= PostgreSQL::Test::Cluster->new('publisher');=0A+$node_subscriber=20=3D=20= PostgreSQL::Test::Cluster->new('subscriber');=0A= +$node_publisher->init(allows_streaming=20=3D>=20'logical');=0A= +$node_subscriber->init;=0A+$node_publisher->start;=0A= +$node_subscriber->start;=0A+$publisher_connstr=20=3D=20= $node_publisher->connstr=20.=20'=20dbname=3Dpostgres';=0A+my=20%range_a=20= =3D=20(=0A+=20=20publisher=20=3D>=20'FROM=20(0)=20TO=20(15)',=0A+=20=20= subscriber=20=3D>=20'FROM=20(0)=20TO=20(5)');=0A+my=20%range_b=20=3D=20(=0A= +=20=20publisher=20=3D>=20'FROM=20(15)=20TO=20(30)',=0A+=20=20subscriber=20= =3D>=20'FROM=20(5)=20TO=20(30)');=0A+my=20%list_a=20=3D=20(=0A+=20=20= publisher=20=3D>=20'IN=20(1,=203,=205,=207,=209,=2011,=2013,=2015,=2017,=20= 19,=2021,=2023,=2025,=2027,=2029)',=0A+=20=20subscriber=20=3D>=20'IN=20= (1,=202,=203,=204,=205,=206,=207,=208,=209,=2010,=2011,=2012,=2013,=20= 14,=2015,=2016)',=0A+);=0A+my=20%list_b=20=3D=20(=0A+=20=20publisher=20= =3D>=20'IN=20(2,=204,=206,=208,=2010,=2012,=2014,=2016,=2018,=2020,=20= 22,=2024,=2026,=2028,=2030)',=0A+=20=20subscriber=20=3D>=20'IN=20(17,=20= 18,=2019,=2020,=2021,=2022,=2023,=2024,=2025,=2026,=2027,=2028,=2029,=20= 30)');=0A+my=20%remainder_a=20=3D=20(=0A+=20=20publisher=20=3D>=200,=0A+=20= =20subscriber=20=3D>=201);=0A+my=20%remainder_b=20=3D=20(=0A+=20=20= publisher=20=3D>=201,=0A+=20=20subscriber=20=3D>=200);=0A+=0A+for=20my=20= $node=20($node_publisher,=20$node_subscriber)=0A+{=0A+=20=20my=20= $range_a=20=3D=20$range_a{$node->name};=0A+=20=20my=20$range_b=20=3D=20= $range_b{$node->name};=0A+=20=20my=20$list_a=20=3D=20= $list_a{$node->name};=0A+=20=20my=20$list_b=20=3D=20= $list_b{$node->name};=0A+=20=20my=20$remainder_a=20=3D=20= $remainder_a{$node->name};=0A+=20=20my=20$remainder_b=20=3D=20= $remainder_b{$node->name};=0A+=20=20$node->safe_psql('postgres',=20qq(=0A= +=20=20CREATE=20ROLE=20regress_admin=20SUPERUSER=20LOGIN;=0A+=20=20= CREATE=20ROLE=20regress_alice=20NOSUPERUSER=20LOGIN;=0A+=20=20GRANT=20= CREATE=20ON=20DATABASE=20postgres=20TO=20regress_alice;=0A+=20=20SET=20= SESSION=20AUTHORIZATION=20regress_alice;=0A+=20=20CREATE=20SCHEMA=20= alice;=0A+=20=20GRANT=20USAGE=20ON=20SCHEMA=20alice=20TO=20= regress_admin;=0A+=0A+=20=20CREATE=20TABLE=20alice.unpartitioned=20(i=20= INTEGER);=0A+=20=20ALTER=20TABLE=20alice.unpartitioned=20REPLICA=20= IDENTITY=20FULL;=0A+=20=20GRANT=20SELECT=20ON=20TABLE=20= alice.unpartitioned=20TO=20regress_admin;=0A+=0A+=20=20CREATE=20TABLE=20= alice.rangepart=20(i=20INTEGER)=20PARTITION=20BY=20RANGE=20(i);=0A+=20=20= ALTER=20TABLE=20alice.rangepart=20REPLICA=20IDENTITY=20FULL;=0A+=20=20= GRANT=20SELECT=20ON=20TABLE=20alice.rangepart=20TO=20regress_admin;=0A+=20= =20CREATE=20TABLE=20alice.rangepart_a=20PARTITION=20OF=20alice.rangepart=0A= +=20=20=20=20FOR=20VALUES=20$range_a;=0A+=20=20ALTER=20TABLE=20= alice.rangepart_a=20REPLICA=20IDENTITY=20FULL;=0A+=20=20CREATE=20TABLE=20= alice.rangepart_b=20PARTITION=20OF=20alice.rangepart=0A+=20=20=20=20FOR=20= VALUES=20$range_b;=0A+=20=20ALTER=20TABLE=20alice.rangepart_b=20REPLICA=20= IDENTITY=20FULL;=0A+=0A+=20=20CREATE=20TABLE=20alice.listpart=20(i=20= INTEGER)=20PARTITION=20BY=20LIST=20(i);=0A+=20=20ALTER=20TABLE=20= alice.listpart=20REPLICA=20IDENTITY=20FULL;=0A+=20=20GRANT=20SELECT=20ON=20= TABLE=20alice.listpart=20TO=20regress_admin;=0A+=20=20CREATE=20TABLE=20= alice.listpart_a=20PARTITION=20OF=20alice.listpart=0A+=20=20=20=20FOR=20= VALUES=20$list_a;=0A+=20=20ALTER=20TABLE=20alice.listpart_a=20REPLICA=20= IDENTITY=20FULL;=0A+=20=20CREATE=20TABLE=20alice.listpart_b=20PARTITION=20= OF=20alice.listpart=0A+=20=20=20=20FOR=20VALUES=20$list_b;=0A+=20=20= ALTER=20TABLE=20alice.listpart_b=20REPLICA=20IDENTITY=20FULL;=0A+=0A+=20=20= CREATE=20TABLE=20alice.hashpart=20(i=20INTEGER)=20PARTITION=20BY=20HASH=20= (i);=0A+=20=20ALTER=20TABLE=20alice.hashpart=20REPLICA=20IDENTITY=20= FULL;=0A+=20=20GRANT=20SELECT=20ON=20TABLE=20alice.hashpart=20TO=20= regress_admin;=0A+=20=20CREATE=20TABLE=20alice.hashpart_a=20PARTITION=20= OF=20alice.hashpart=0A+=20=20=20=20FOR=20VALUES=20WITH=20(MODULUS=202,=20= REMAINDER=20$remainder_a);=0A+=20=20ALTER=20TABLE=20alice.hashpart_a=20= REPLICA=20IDENTITY=20FULL;=0A+=20=20CREATE=20TABLE=20alice.hashpart_b=20= PARTITION=20OF=20alice.hashpart=0A+=20=20=20=20FOR=20VALUES=20WITH=20= (MODULUS=202,=20REMAINDER=20$remainder_b);=0A+=20=20ALTER=20TABLE=20= alice.hashpart_b=20REPLICA=20IDENTITY=20FULL;=0A+=20=20));=0A+}=0A= +$node_publisher->safe_psql('postgres',=20qq(=0A+SET=20SESSION=20= AUTHORIZATION=20regress_alice;=0A+=0A+CREATE=20PUBLICATION=20alice=0A+=20= =20FOR=20TABLE=20alice.unpartitioned,=20alice.rangepart,=20= alice.listpart,=20alice.hashpart=0A+=20=20WITH=20= (publish_via_partition_root=20=3D=20true);=0A+));=0A= +$node_subscriber->safe_psql('postgres',=20qq(=0A+SET=20SESSION=20= AUTHORIZATION=20regress_admin;=0A+CREATE=20SUBSCRIPTION=20admin_sub=20= CONNECTION=20'$publisher_connstr'=20PUBLICATION=20alice;=0A+));=0A+=0A+#=20= Verify=20that=20"regress_admin"=20can=20replicate=20into=20the=20tables=0A= +#=0A+my=20@tbl=20=3D=20(qw(unpartitioned=20rangepart=20listpart=20= hashpart));=0A+for=20my=20$tbl=20(@tbl)=0A+{=0A+=20=20= publish_insert("alice.$tbl",=201);=0A+=20=20publish_insert("alice.$tbl",=20= 3);=0A+=20=20publish_insert("alice.$tbl",=205);=0A+=20=20= expect_replication(=0A+=20=20=20=20"alice.$tbl",=203,=201,=205,=0A+=20=20= =20=20"superuser=20admin=20replicates=20insert=20into=20$tbl");=0A+=20=20= publish_update("alice.$tbl",=201=20=3D>=207);=0A+=20=20= expect_replication(=0A+=20=20=20=20"alice.$tbl",=203,=203,=207,=0A+=20=20= =20=20"superuser=20admin=20replicates=20update=20into=20$tbl");=0A+=20=20= publish_delete("alice.$tbl",=203);=0A+=20=20expect_replication(=0A+=20=20= =20=20"alice.$tbl",=202,=205,=207,=0A+=20=20=20=20"superuser=20admin=20= replicates=20delete=20into=20$tbl");=0A+}=0A+=0A+#=20Repeatedly=20revoke=20= and=20restore=20superuser=20privilege=20for=20"regress_admin",=20= verifying=0A+#=20that=20replication=20fails=20while=20superuser=20= privilege=20is=20missing,=20but=20works=20again=20and=0A+#=20catches=20= up=20once=20superuser=20is=20restored.=0A+#=0A+for=20my=20$tbl=20(@tbl)=0A= +{=0A+=20=20revoke_superuser("regress_admin");=0A+=20=20= publish_insert("alice.$tbl",=203);=0A+=20=20expect_failure("alice.$tbl",=20= 2,=205,=207,=0A+=20=20=20=20qr/ERROR:=20=20permission=20denied=20for=20= table=20$tbl/msi,=0A+=20=20=20=20"non-superuser=20admin=20fails=20to=20= replicate=20insert");=0A+=20=20grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=203,=207,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20insert");=0A+=0A+=20= =20revoke_superuser("regress_admin");=0A+=20=20= publish_update("alice.$tbl",=203=20=3D>=209);=0A+=20=20= expect_failure("alice.$tbl",=203,=203,=207,=0A+=20=20=20=20qr/ERROR:=20=20= permission=20denied=20for=20table=20$tbl/msi,=0A+=20=20=20=20= "non-superuser=20admin=20fails=20to=20replicate=20update");=0A+=20=20= grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=205,=209,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20update");=0A+=0A+=20= =20revoke_superuser("regress_admin");=0A+=20=20= publish_delete("alice.$tbl",=205);=0A+=20=20expect_failure("alice.$tbl",=20= 3,=205,=209,=0A+=20=20=20=20qr/ERROR:=20=20permission=20denied=20for=20= table=20$tbl/msi,=0A+=20=20=20=20"non-superuser=20admin=20fails=20to=20= replicate=20delete");=0A+=20=20grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=202,=207,=209,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20delete");=0A+}=0A= +=0A+#=20Grant=20privileges=20on=20the=20target=20tables=20to=20= "regress_admin"=20so=20that=20superuser=0A+#=20privileges=20are=20not=20= necessary=20for=20replication.=0A+#=0A= +$node_subscriber->safe_psql('postgres',=20qq(=0A+ALTER=20ROLE=20= regress_admin=20NOSUPERUSER;=0A+SET=20SESSION=20AUTHORIZATION=20= regress_alice;=0A+GRANT=20ALL=20PRIVILEGES=20ON=0A+=20=20= alice.unpartitioned,=0A+=20=20alice.rangepart,=20alice.rangepart_a,=20= alice.rangepart_b,=0A+=20=20alice.listpart,=20alice.listpart_a,=20= alice.listpart_b,=0A+=20=20alice.hashpart,=20alice.hashpart_a,=20= alice.hashpart_b=0A+=20=20TO=20regress_admin;=0A+));=0A+for=20my=20$tbl=20= (@tbl)=0A+{=0A+=20=20publish_insert("alice.$tbl",=2011);=0A+=20=20= publish_update("alice.$tbl",=207=20=3D>=2013);=0A+=20=20= publish_delete("alice.$tbl",=209);=0A+=20=20= expect_replication("alice.$tbl",=202,=2011,=2013,=0A+=20=20=20=20= "nosuperuser=20admin=20with=20all=20table=20privileges=20can=20replicate=20= into=20$tbl");=0A+}=0A+=0A+#=20Enable=20RLS=20on=20the=20target=20tables=20= and=20check=20that=20"regress_admin"=20can=20only=0A+#=20replicate=20= into=20them=20when=20superuser.=20=20Note=20that=20RLS=20must=20be=20= enabled=20on=20the=0A+#=20partitions,=20not=20the=20partitioned=20= tables,=20since=20the=20partitions=20are=20the=20targets=0A+#=20of=20the=20= replication.=0A+#=0A+$node_subscriber->safe_psql('postgres',=20qq(=0A= +SET=20SESSION=20AUTHORIZATION=20regress_alice;=0A+ALTER=20TABLE=20= alice.unpartitioned=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.rangepart_a=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.rangepart_b=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.listpart_a=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.listpart_b=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.hashpart_a=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+ALTER=20TABLE=20= alice.hashpart_b=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+));=0A+for=20my=20= $tbl=20(@tbl)=0A+{=0A+=20=20revoke_superuser("regress_admin");=0A+=20=20= publish_insert("alice.$tbl",=2015);=0A+=20=20= expect_failure("alice.$tbl",=202,=2011,=2013,=0A+=20=20=20=20qr/ERROR:=20= =20"regress_admin"=20cannot=20replicate=20into=20relation=20with=20= row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "non-superuser=20admin=20fails=20to=20replicate=20insert=20into=20rls=20= enabled=20table");=0A+=20=20grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=2011,=2015,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20insert=20into=20= rls=20enabled=20$tbl");=0A+=0A+=20=20revoke_superuser("regress_admin");=0A= +=20=20publish_update("alice.$tbl",=2011=20=3D>=2017);=0A+=20=20= expect_failure("alice.$tbl",=203,=2011,=2015,=0A+=20=20=20=20qr/ERROR:=20= =20"regress_admin"=20cannot=20replicate=20into=20relation=20with=20= row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "non-superuser=20admin=20fails=20to=20replicate=20update=20into=20rls=20= enabled=20$tbl");=0A+=0A+=20=20grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=2013,=2017,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20update=20into=20= rls=20enabled=20$tbl");=0A+=0A+=20=20revoke_superuser("regress_admin");=0A= +=20=20publish_delete("alice.$tbl",=2013);=0A+=20=20= expect_failure("alice.$tbl",=203,=2013,=2017,=0A+=20=20=20=20qr/ERROR:=20= =20"regress_admin"=20cannot=20replicate=20into=20relation=20with=20= row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "non-superuser=20admin=20fails=20to=20replicate=20delete=20into=20rls=20= enabled=20$tbl");=0A+=20=20grant_superuser("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=202,=2015,=2017,=0A+=20=20=20=20"admin=20= with=20restored=20superuser=20privilege=20replicates=20delete=20into=20= rls=20enabled=20$tbl");=0A+}=0A+=0A+#=20Revoke=20superuser=20from=20= "regress_admin".=20=20Check=20that=20the=20admin=20can=20now=20only=0A+#=20= replicate=20into=20alice's=20table=20when=20admin=20has=20the=20= bypassrls=20privilege.=0A+#=0A+for=20my=20$tbl=20(@tbl)=0A+{=0A+=20=20= revoke_superuser("regress_admin");=0A+=20=20= revoke_bypassrls("regress_admin");=0A+=20=20publish_insert("alice.$tbl",=20= 19);=0A+=20=20expect_failure("alice.$tbl",=202,=2015,=2017,=0A+=20=20=20=20= qr/ERROR:=20=20"regress_admin"=20cannot=20replicate=20into=20relation=20= with=20row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "nobypassrls=20admin=20fails=20to=20replicate=20insert=20into=20rls=20= enabled=20$tbl");=0A+=20=20grant_bypassrls("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=2015,=2019,=0A+=20=20=20=20"admin=20= with=20bypassrls=20privilege=20replicates=20insert=20into=20rls=20= enabled=20$tbl");=0A+=0A+=20=20revoke_bypassrls("regress_admin");=0A+=20=20= publish_update("alice.$tbl",=2015=20=3D>=2021);=0A+=20=20= expect_failure("alice.$tbl",=203,=2015,=2019,=0A+=20=20=20=20qr/ERROR:=20= =20"regress_admin"=20cannot=20replicate=20into=20relation=20with=20= row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "nobypassrls=20admin=20fails=20to=20replicate=20update=20into=20rls=20= enabled=20$tbl");=0A+=0A+=20=20grant_bypassrls("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=203,=2017,=2021,=0A+=20=20=20=20"admin=20= with=20restored=20bypassrls=20privilege=20replicates=20update=20into=20= rls=20enabled=20$tbl");=0A+=0A+=20=20revoke_bypassrls("regress_admin");=0A= +=20=20publish_delete("alice.$tbl",=2017);=0A+=20=20= expect_failure("alice.$tbl",=203,=2017,=2021,=0A+=20=20=20=20qr/ERROR:=20= =20"regress_admin"=20cannot=20replicate=20into=20relation=20with=20= row-level=20security=20enabled:=20"$tbl\w*"/msi,=0A+=20=20=20=20= "nobypassrls=20admin=20fails=20to=20replicate=20delete=20into=20rls=20= enabled=20$tbl");=0A+=20=20grant_bypassrls("regress_admin");=0A+=20=20= expect_replication("alice.$tbl",=202,=2019,=2021,=0A+=20=20=20=20"admin=20= with=20restored=20bypassrls=20privilege=20replicates=20delete=20into=20= rls=20enabled=20$tbl");=0A+}=0A+=0A+#=20Alter=20the=20subscription=20= owner=20to=20"regress_alice".=20=20She=20has=20neither=20superuser=0A+#=20= nor=20bypassrls,=20but=20as=20the=20table=20owner=20should=20be=20able=20= to=20replicate.=0A+#=0A+$node_subscriber->safe_psql('postgres',=20qq(=0A= +ALTER=20SUBSCRIPTION=20admin_sub=20DISABLE;=0A+ALTER=20ROLE=20= regress_alice=20SUPERUSER;=0A+ALTER=20SUBSCRIPTION=20admin_sub=20OWNER=20= TO=20regress_alice;=0A+ALTER=20ROLE=20regress_alice=20NOSUPERUSER;=0A= +ALTER=20SUBSCRIPTION=20admin_sub=20ENABLE;=0A+));=0A+for=20my=20$tbl=20= (@tbl)=0A+{=0A+=20=20publish_insert("alice.$tbl",=2023);=0A+=20=20= expect_replication(=0A+=20=20=20=20"alice.$tbl",=203,=2019,=2023,=0A+=20=20= =20=20"nosuperuser=20nobypassrls=20table=20owner=20can=20replicate=20= insert=20into=20$tbl=20despite=20rls");=0A+=20=20= publish_update("alice.$tbl",=2019=20=3D>=2025);=0A+=20=20= expect_replication(=0A+=20=20=20=20"alice.$tbl",=203,=2021,=2025,=0A+=20=20= =20=20"nosuperuser=20nobypassrls=20table=20owner=20can=20replicate=20= update=20into=20$tbl=20despite=20rls");=0A+=20=20= publish_delete("alice.$tbl",=2021);=0A+=20=20expect_replication(=0A+=20=20= =20=20"alice.$tbl",=202,=2023,=2025,=0A+=20=20=20=20"nosuperuser=20= nobypassrls=20table=20owner=20can=20replicate=20delete=20into=20$tbl=20= despite=20rls");=0A+}=0A--=20=0A2.21.1=20(Apple=20Git-122.3)=0A=0A= --Apple-Mail=_7C6BF487-AFC4-43FE-8D7D-35B2F3336BD2 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=_7C6BF487-AFC4-43FE-8D7D-35B2F3336BD2--