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:sfrost(at)snowman(dot)net]
Sent: Saturday, August 28, 2021 7:34 AM
To: Michael Banck <michael(dot)banck(at)credativ(dot)de>
Cc: gkokolatos(at)pm(dot)me; Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>; pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: New predefined roles- 'pg_read/write_all_data'

Greetings,

* Michael Banck (michael(dot)banck(at)credativ(dot)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;
> > </row>
> > </thead>
> > <tbody>
> > + <row>
> > + <entry>pg_read_all_data</entry>
> > + <entry>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
> > + <literal>BYPASSRLS</literal> set. If RLS is being used, an administrator
> > + may wish to set <literal>BYPASSRLS</literal> on roles which this role is
> > + GRANTed to.</entry>
> > + </row>
> > + <row>
> > + <entry>pg_write_all_data</entry>
> > + <entry>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 <literal>BYPASSRLS</literal> set. If RLS is being used,
> > + an administrator may wish to set <literal>BYPASSRLS</literal> on roles
> > + which this role is GRANTed to.</entry>
> > + </row>
>
> Shouldn't those "SELECT", "INSERT" etc. be wrapped in <command> tags?

Yeah, good point, fixed.

Thanks!

Stephen