pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Respect permissions within logical replication.
7+ messages / 5 participants
[nested] [flat]

Commits from this thread:
  2022-01-09 96a6f11c06  More cleanup of a2ab9c06ea.
* pgsql: Respect permissions within logical replication.
@ 2022-01-08 02:40  Jeff Davis <jdavis@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Jeff Davis @ 2022-01-08 02:40 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-08 05:22  Michael Paquier <michael@paquier.xyz>
  parent: Jeff Davis <jdavis@postgresql.org>
  0 siblings, 2 replies; 7+ messages in thread

From: Michael Paquier @ 2022-01-08 05:22 UTC (permalink / raw)
  To: Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-08 06:26  Amit Kapila <amit.kapila16@gmail.com>
  parent: Michael Paquier <michael@paquier.xyz>
  1 sibling, 1 reply; 7+ messages in thread

From: Amit Kapila @ 2022-01-08 06:26 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Jeff Davis <jdavis@postgresql.org>; pgsql-committers <pgsql-committers@lists.postgresql.org>

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.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-08 09:25  Michael Paquier <michael@paquier.xyz>
  parent: Michael Paquier <michael@paquier.xyz>
  1 sibling, 2 replies; 7+ messages in thread

From: Michael Paquier @ 2022-01-08 09:25 UTC (permalink / raw)
  To: Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

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

Attachments:

crake-perlcritic.patch (text/x-diff, 2.0K) download diff --git a/src/test/subscription/t/027_nosuperuser.pl b/src/test/subscription/t/027_nosuperuser.pl index 742a745cf7..c9e4aeba06 100644 --- a/src/test/subscription/t/027_nosuperuser.pl +++ b/src/test/subscription/t/027_nosuperuser.pl @@ -10,7 +10,7 @@ use Test::More tests => 100; my ($node_publisher, $node_subscriber, $publisher_connstr, $result, $offset); $offset = 0; -sub publish_insert($$) +sub publish_insert { my ($tbl, $new_i) = @_; $node_publisher->safe_psql('postgres', qq( @@ -19,7 +19,7 @@ sub publish_insert($$) )); } -sub publish_update($$$) +sub publish_update { my ($tbl, $old_i, $new_i) = @_; $node_publisher->safe_psql('postgres', qq( @@ -28,7 +28,7 @@ sub publish_update($$$) )); } -sub publish_delete($$) +sub publish_delete { my ($tbl, $old_i) = @_; $node_publisher->safe_psql('postgres', qq( @@ -37,7 +37,7 @@ sub publish_delete($$) )); } -sub expect_replication($$$$$) +sub expect_replication { my ($tbl, $cnt, $min, $max, $testname) = @_; $node_publisher->wait_for_catchup('admin_sub'); @@ -46,7 +46,7 @@ sub expect_replication($$$$$) is ($result, "$cnt|$min|$max", $testname); } -sub expect_failure($$$$$$) +sub expect_failure { my ($tbl, $cnt, $min, $max, $re, $testname) = @_; $offset = $node_subscriber->wait_for_log($re, $offset); @@ -55,28 +55,28 @@ sub expect_failure($$$$$$) is ($result, "$cnt|$min|$max", $testname); } -sub revoke_superuser($) +sub revoke_superuser { my ($role) = @_; $node_subscriber->safe_psql('postgres', qq( ALTER ROLE $role NOSUPERUSER)); } -sub grant_superuser($) +sub grant_superuser { my ($role) = @_; $node_subscriber->safe_psql('postgres', qq( ALTER ROLE $role SUPERUSER)); } -sub revoke_bypassrls($) +sub revoke_bypassrls { my ($role) = @_; $node_subscriber->safe_psql('postgres', qq( ALTER ROLE $role NOBYPASSRLS)); } -sub grant_bypassrls($) +sub grant_bypassrls { my ($role) = @_; $node_subscriber->safe_psql('postgres', qq(
signature.asc (application/pgp-signature, 832B) download ^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-08 16:30  Andrew Dunstan <andrew@dunslane.net>
  parent: Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 7+ messages in thread

From: Andrew Dunstan @ 2022-01-08 16:30 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org


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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-09 18:02  Jeff Davis <pgsql@j-davis.com>
  parent: Amit Kapila <amit.kapila16@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Jeff Davis @ 2022-01-09 18:02 UTC (permalink / raw)
  To: Amit Kapila <amit.kapila16@gmail.com>; Michael Paquier <michael@paquier.xyz>; +Cc: Jeff Davis <jdavis@postgresql.org>; pgsql-committers <pgsql-committers@lists.postgresql.org>

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







^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: pgsql: Respect permissions within logical replication.
@ 2022-01-09 18:08  Jeff Davis <pgsql@j-davis.com>
  parent: Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 7+ messages in thread

From: Jeff Davis @ 2022-01-09 18:08 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

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







^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2022-01-09 18:08 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-08 02:40 pgsql: Respect permissions within logical replication. Jeff Davis <jdavis@postgresql.org>
2022-01-08 05:22 ` Michael Paquier <michael@paquier.xyz>
2022-01-08 06:26   ` Amit Kapila <amit.kapila16@gmail.com>
2022-01-09 18:02     ` Jeff Davis <pgsql@j-davis.com>
2022-01-08 09:25   ` Michael Paquier <michael@paquier.xyz>
2022-01-08 16:30     ` Andrew Dunstan <andrew@dunslane.net>
2022-01-09 18:08     ` Jeff Davis <pgsql@j-davis.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox