Thread: pgsql: Respect permissions within logical replication.
Respect permissions within logical replication. Prevent logical replication workers from performing insert, update, delete, truncate, or copy commands on tables unless the subscription owner has permission to do so. Prevent subscription owners from circumventing row-level security by forbidding replication into tables with row-level security policies which the subscription owner is subject to, without regard to whether the policy would ordinarily allow the INSERT, UPDATE, DELETE or TRUNCATE which is being replicated. This seems sufficient for now, as superusers, roles with bypassrls, and target table owners should still be able to replicate despite RLS policies. We can revisit the question of applying row-level security policies on a per-row basis if this restriction proves too severe in practice. Author: Mark Dilger Reviewed-by: Jeff Davis, Andrew Dunstan, Ronan Dunklau Discussion: https://postgr.es/m/9DFC88D3-1300-4DE8-ACBC-4CEF84399A53%40enterprisedb.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/a2ab9c06ea15fbcb2bfde570986a06b37f52bcca Modified Files -------------- doc/src/sgml/logical-replication.sgml | 36 ++- src/backend/commands/subscriptioncmds.c | 2 + src/backend/replication/logical/tablesync.c | 28 +++ src/backend/replication/logical/worker.c | 42 ++++ src/test/perl/PostgreSQL/Test/Cluster.pm | 36 +++ src/test/subscription/t/027_nosuperuser.pl | 363 ++++++++++++++++++++++++++++ 6 files changed, 499 insertions(+), 8 deletions(-)
Hi Jeff, On Sat, Jan 08, 2022 at 02:40:56AM +0000, Jeff Davis wrote: > Respect permissions within logical replication. The buildfarm is not completely happy after this commit: 1) massasauga is failing the new test: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=massasauga&dt=2022-01-08%2002%3A45%3A21 # Failed test 'superuser admin replicates insert into unpartitioned' # at t/027_nosuperuser.pl line 46. # got: '2|1|3' # expected: '3|1|5' # Looks like you failed 1 test of 100. 2) crake fails perl-check https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2022-01-08%2002%3A47%3A26 All the failures are the same, because of the shape of the subroutine definitions: Jan 07 21:53:04 ./src/test/subscription/t/027_nosuperuser.pl: Subroutine prototypes used at line 13, column 1. See page 194 of PBP. ([Subroutines::ProhibitSubroutinePrototypes] Severity: 5) Thanks, -- Michael
Attachment
On Sat, Jan 8, 2022 at 10:52 AM Michael Paquier <michael@paquier.xyz> wrote: > > Hi Jeff, > > On Sat, Jan 08, 2022 at 02:40:56AM +0000, Jeff Davis wrote: > > Respect permissions within logical replication. > > The buildfarm is not completely happy after this commit: > 1) massasauga is failing the new test: > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=massasauga&dt=2022-01-08%2002%3A45%3A21 > # Failed test 'superuser admin replicates insert into unpartitioned' > # at t/027_nosuperuser.pl line 46. > # got: '2|1|3' > # expected: '3|1|5' > # Looks like you failed 1 test of 100. > One possible reason for this failure could be that the test doesn't wait for the initial sync to complete after CREATE SUBSCRIPTION which can allow more than expected operations to be replicated. Also, the reason it passes in other machines is that in those runs initial sync could get finished before we start to publish other operations. -- With Regards, Amit Kapila.
On Sat, Jan 08, 2022 at 02:22:23PM +0900, Michael Paquier wrote: > 2) crake fails perl-check > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2022-01-08%2002%3A47%3A26 > > All the failures are the same, because of the shape of the subroutine > definitions: > Jan 07 21:53:04 ./src/test/subscription/t/027_nosuperuser.pl: > Subroutine prototypes used at line 13, column 1. See page 194 of > PBP. ([Subroutines::ProhibitSubroutinePrototypes] Severity: 5) This issue is just caused by the way the subroutines are defined, meaning that the attached cools down pgperlcritic. Any objections if I apply that to cool down crake? Jeff, if you wish to do so, please feel free of course. -- Michael
Attachment
On 1/8/22 04:25, Michael Paquier wrote: > On Sat, Jan 08, 2022 at 02:22:23PM +0900, Michael Paquier wrote: >> 2) crake fails perl-check >> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2022-01-08%2002%3A47%3A26 >> >> All the failures are the same, because of the shape of the subroutine >> definitions: >> Jan 07 21:53:04 ./src/test/subscription/t/027_nosuperuser.pl: >> Subroutine prototypes used at line 13, column 1. See page 194 of >> PBP. ([Subroutines::ProhibitSubroutinePrototypes] Severity: 5) > This issue is just caused by the way the subroutines are defined, > meaning that the attached cools down pgperlcritic. Any objections if > I apply that to cool down crake? Jeff, if you wish to do so, please > feel free of course. Please do this. Subroutine prototypes should only be used in very restricted circumstances in Perl. I can't recall when I've ever needed them. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Sat, 2022-01-08 at 11:56 +0530, Amit Kapila wrote: > One possible reason for this failure could be that the test doesn't > wait for the initial sync to complete after CREATE SUBSCRIPTION which > can allow more than expected operations to be replicated. Also, the > reason it passes in other machines is that in those runs initial sync > could get finished before we start to publish other operations. You're right, even though the initial table is empty. I copied some code from a different test to wait for the sync, and it appears to be succeeding now. Thanks, Jeff Davis
On Sat, 2022-01-08 at 18:25 +0900, Michael Paquier wrote: > This issue is just caused by the way the subroutines are defined, > meaning that the attached cools down pgperlcritic. Any objections if > I apply that to cool down crake? Jeff, if you wish to do so, please > feel free of course. Thank you, committed. Regards, Jeff Davis