RE: New predefined roles- 'pg_read/write_all_data'
Thank you for your quick response. I understood the specifications from your explanation. Regards, Noriyoshi Shinoda From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Sunday, September 5, 2021 8:50 PM To: Shinoda, Noriyoshi (PN Japan FSIP) Cc: Anastasia Lubennikova ; Michael Banck ; gkokola...@pm.me; pgsql-hackers@lists.postgresql.org Subject: Re: New predefined roles- 'pg_read/write_all_data' Greetings, On Sun, Sep 5, 2021 at 07:43 Shinoda, Noriyoshi (PN Japan FSIP) mailto:noriyoshi.shin...@hpe.com>> wrote: I have tested this new feature with PostgreSQL 14 Beta 3 environment. I created a user granted with pg_write_all_data role and executed UPDATE and DELETE statements on tables owned by other users. If there is no WHERE clause, it can be executed as expected, but if the WHERE clause is specified, an error of permission denied will occur. Is this the expected behavior? A WHERE clause requires SELECT rights on the table/columns referenced and if no SELECT rights were granted then a permission denied error is the correct result, yes. Note that pg_write_all_data, as documented, does not include SELECT rights. Thanks, Stephen
Re: New predefined roles- 'pg_read/write_all_data'
Greetings, On Sun, Sep 5, 2021 at 07:43 Shinoda, Noriyoshi (PN Japan FSIP) < noriyoshi.shin...@hpe.com> wrote: > I have tested this new feature with PostgreSQL 14 Beta 3 environment. > I created a user granted with pg_write_all_data role and executed UPDATE > and DELETE statements on tables owned by other users. > If there is no WHERE clause, it can be executed as expected, but if the > WHERE clause is specified, an error of permission denied will occur. > Is this the expected behavior? A WHERE clause requires SELECT rights on the table/columns referenced and if no SELECT rights were granted then a permission denied error is the correct result, yes. Note that pg_write_all_data, as documented, does not include SELECT rights. Thanks, Stephen
RE: New predefined roles- 'pg_read/write_all_data'
Hi hackers, I have tested this new feature with PostgreSQL 14 Beta 3 environment. I created a user granted with pg_write_all_data role and executed UPDATE and DELETE statements on tables owned by other users. If there is no WHERE clause, it can be executed as expected, but if the WHERE clause is specified, an error of permission denied will occur. Is this the expected behavior? The WHERE clause is not specified in the regression test (privileges.sql). Below is the execution log. postgres=# CREATE USER owner1 PASSWORD 'owner1'; CREATE ROLE postgres=# CREATE USER write1 PASSWORD 'write1'; CREATE ROLE postgres=# GRANT pg_write_all_data TO write1; GRANT ROLE postgres=# SET SESSION AUTHORIZATION owner1; SET postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)); CREATE TABLE postgres=> INSERT INTO data1 VALUES (generate_series(1, 10), 'data1'); INSERT 0 10 postgres=> SET SESSION AUTHORIZATION write1; SET postgres=> INSERT INTO data1 VALUES (0, 'data1'); -- success INSERT 0 1 postgres=> UPDATE data1 SET c2='update' WHERE c1=0; -- fail ERROR: permission denied for table data1 postgres=> DELETE FROM data1 WHERE c1=0;-- fail ERROR: permission denied for table data1 postgres=> UPDATE data1 SET c2='update';-- success UPDATE 11 postgres=> DELETE FROM data1; -- success DELETE 11 postgres=> SELECT version(); version PostgreSQL 14beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit (1 row) - Regards, Noriyoshi Shinoda -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Saturday, August 28, 2021 7:34 AM To: Michael Banck Cc: gkokola...@pm.me; Anastasia Lubennikova ; pgsql-hackers@lists.postgresql.org Subject: Re: New predefined roles- 'pg_read/write_all_data' Greetings, * Michael Banck (michael.ba...@credativ.de) wrote: > On Thu, Apr 01, 2021 at 04:00:06PM -0400, Stephen Frost wrote: > > diff --git a/doc/src/sgml/user-manag.sgml > > b/doc/src/sgml/user-manag.sgml index d171b13236..fe0bdb7599 100644 > > --- a/doc/src/sgml/user-manag.sgml > > +++ b/doc/src/sgml/user-manag.sgml > > @@ -518,6 +518,24 @@ DROP ROLE doomed_role; > > > > > > > > + > > + pg_read_all_data > > + Read all data (tables, views, sequences), as if having SELECT > > + rights on those objects, and USAGE rights on all schemas, even > > without > > + having it explicitly. This role does not have the role attribute > > + BYPASSRLS set. If RLS is being used, an > > administrator > > + may wish to set BYPASSRLS on roles which this > > role is > > + GRANTed to. > > + > > + > > + pg_write_all_data > > + Write all data (tables, views, sequences), as if having > > INSERT, > > + UPDATE, and DELETE rights on those objects, and USAGE rights on all > > + schemas, even without having it explicitly. This role does not > > have the > > + role attribute BYPASSRLS set. If RLS is being > > used, > > + an administrator may wish to set BYPASSRLS on > > roles > > + which this role is GRANTed to. > > + > > Shouldn't those "SELECT", "INSERT" etc. be wrapped in tags? Yeah, good point, fixed. Thanks! Stephen
Re: New predefined roles- 'pg_read/write_all_data'
Greetings, * Michael Banck (michael.ba...@credativ.de) wrote: > On Thu, Apr 01, 2021 at 04:00:06PM -0400, Stephen Frost wrote: > > diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml > > index d171b13236..fe0bdb7599 100644 > > --- a/doc/src/sgml/user-manag.sgml > > +++ b/doc/src/sgml/user-manag.sgml > > @@ -518,6 +518,24 @@ DROP ROLE doomed_role; > > > > > > > > + > > + pg_read_all_data > > + Read all data (tables, views, sequences), as if having SELECT > > + rights on those objects, and USAGE rights on all schemas, even > > without > > + having it explicitly. This role does not have the role attribute > > + BYPASSRLS set. If RLS is being used, an > > administrator > > + may wish to set BYPASSRLS on roles which this > > role is > > + GRANTed to. > > + > > + > > + pg_write_all_data > > + Write all data (tables, views, sequences), as if having > > INSERT, > > + UPDATE, and DELETE rights on those objects, and USAGE rights on all > > + schemas, even without having it explicitly. This role does not > > have the > > + role attribute BYPASSRLS set. If RLS is being > > used, > > + an administrator may wish to set BYPASSRLS on > > roles > > + which this role is GRANTed to. > > + > > Shouldn't those "SELECT", "INSERT" etc. be wrapped in tags? Yeah, good point, fixed. Thanks! Stephen signature.asc Description: PGP signature
Re: New predefined roles- 'pg_read/write_all_data'
Hi, On Thu, Apr 01, 2021 at 04:00:06PM -0400, Stephen Frost wrote: > diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml > index d171b13236..fe0bdb7599 100644 > --- a/doc/src/sgml/user-manag.sgml > +++ b/doc/src/sgml/user-manag.sgml > @@ -518,6 +518,24 @@ DROP ROLE doomed_role; > > > > + > + pg_read_all_data > + Read all data (tables, views, sequences), as if having SELECT > + rights on those objects, and USAGE rights on all schemas, even without > + having it explicitly. This role does not have the role attribute > + BYPASSRLS set. If RLS is being used, an > administrator > + may wish to set BYPASSRLS on roles which this role > is > + GRANTed to. > + > + > + pg_write_all_data > + Write all data (tables, views, sequences), as if having INSERT, > + UPDATE, and DELETE rights on those objects, and USAGE rights on all > + schemas, even without having it explicitly. This role does not have > the > + role attribute BYPASSRLS set. If RLS is being > used, > + an administrator may wish to set BYPASSRLS on roles > + which this role is GRANTed to. > + Shouldn't those "SELECT", "INSERT" etc. be wrapped in tags? Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.ba...@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
Re: New predefined roles- 'pg_read/write_all_data'
Greetings, * Stephen Frost (sfr...@snowman.net) wrote: > Updated patch attached. Will be playing with it a bit more but > generally feel like it's in pretty good shape. Unless there's anything > further on this, I'll likely commit it over the weekend. Weekend ended up being a bit busy, but I've now pushed this. Thanks! Stephen signature.asc Description: PGP signature
Re: New predefined roles- 'pg_read/write_all_data'
Greetings, * gkokola...@pm.me (gkokola...@pm.me) wrote: > On Monday, November 23, 2020 11:31 PM, Stephen Frost > wrote: > > - Anastasia Lubennikova (a.lubennik...@postgrespro.ru) wrote: > > > > > On 29.10.2020 17:19, Stephen Frost wrote: > > > > > > > - Georgios Kokolatos (gkokola...@protonmail.com) wrote: > > > > > > > > > this patch is in "Ready for committer" state and the Cfbot is happy. > > > > > Glad that's still the case. :) > > > > > > > > > Is there any committer that is available for taking a look at it? > > > > > If there aren't any objections or further comments, I'll take another > > > > > look through it and will commit it during the upcoming CF. > > > > > > CFM reminder. Just in case you forgot about this thread) > > > The commitfest is heading to the end. And there was a plenty of time for > > > anyone to object. > > > > Thanks, I've not forgotten, but it's a bit complicated given that I've > > another patch in progress to rename default roles to be predefined > > roles which conflicts with this one. Hopefully we'll have a few > > comments on that and I can get it committed and this one updated with > > the new naming. I'd rather not commit this one and then immediately > > commit changes over top of it. > > May I enquire about the status of the current? The patch to rename default roles to predefined roles for v14 has gone in, and so I've come back to this patch to add the pg_read/write_all_data roles. Having contemplated a bit further, I ended up deciding that it made more sense for these predefined roles to *not* have BYPASSRLS, which gives admins the flexibilty to choose if they actually want RLS to be bypassed, or not, on the roles who they GRANT these roles to (if we just always had bypassrls set, then they wouldn't have any choice but to accept that, which doesn't seem very kind). I've updated the documentation to make a note of that and to encourage admins who use these roles to consider if they want to set BYPASSRLS on the actual login role which they'll have to create in order to use these roles (since they can't be used to login directly). Updated patch attached. Will be playing with it a bit more but generally feel like it's in pretty good shape. Unless there's anything further on this, I'll likely commit it over the weekend. Thanks! Stephen From 3032fcf79d162c8e170d648af26d9230609c7b29 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Fri, 26 Mar 2021 17:24:18 -0400 Subject: [PATCH] Add pg_read_all_data and pg_write_all_data roles A commonly requested use-case is to have a role who can run an unfettered pg_dump without having to explicitly GRANT that user access to all tables, schemas, et al, without that role being a superuser. This address that by adding a "pg_read_all_data" role which implicitly gives any member of this role SELECT rights on all tables, views and sequences, and USAGE rights on all schemas. As there may be cases where it's also useful to have a role who has write access to all objects, pg_write_all_data is also introduced and gives users implicit INSERT, UPDATE and DELETE rights on all tables, views and sequences. These roles can not be logged into directly but instead should be GRANT'd to a role which is able to log in. As noted in the documentation, if RLS is being used then an administrator may (or may not) wish to set BYPASSRLS on the login role which these predefined roles are GRANT'd to. Reviewed-by: Georgios Kokolatos Discussion: https://postgr.es/m/20200828003023.gu29...@tamriel.snowman.net --- doc/src/sgml/user-manag.sgml | 18 +++ src/backend/catalog/aclchk.c | 30 +++ src/include/catalog/pg_authid.dat| 10 +++ src/test/regress/expected/privileges.out | 38 +++- src/test/regress/sql/privileges.sql | 20 + 5 files changed, 115 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index d171b13236..fe0bdb7599 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -518,6 +518,24 @@ DROP ROLE doomed_role; + + pg_read_all_data + Read all data (tables, views, sequences), as if having SELECT + rights on those objects, and USAGE rights on all schemas, even without + having it explicitly. This role does not have the role attribute + BYPASSRLS set. If RLS is being used, an administrator + may wish to set BYPASSRLS on roles which this role is + GRANTed to. + + + pg_write_all_data + Write all data (tables, views, sequences), as if having INSERT, + UPDATE, and DELETE rights on those objects, and USAGE rights on all + schemas, even without having it explicitly. This role does not have the + role attribute BYPASSRLS set. If RLS is being used, + an administrator may wish to set BYPASSRLS on roles + which this role is GRANTed to. +
