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:sfrost(at)snowman(dot)net]
Sent: Sunday, September 5, 2021 8:50 PM
To: Shinoda, Noriyoshi (PN Japan FSIP) <noriyoshi(dot)shinoda(at)hpe(dot)com>
Cc: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>; Michael Banck <michael(dot)banck(at)credativ(dot)de>; gkokolatos(at)pm(dot)me; pgsql-hackers(at)lists(dot)postgresql(dot)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) <noriyoshi(dot)shinoda(at)hpe(dot)com<mailto:noriyoshi(dot)shinoda(at)hpe(dot)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