Re: New default role- 'pg_read_all_data'

Greetings,

* gkokolatos(at)pm(dot)me (gkokolatos(at)pm(dot)me) wrote:
> On Friday, 28 August 2020 15:43, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > What privileges would the user be left with? Would it be possible to end up in the same privilege only with a GRANT command?
> >
> > I'm not sure what's being asked here.
>
> You are correct. My phrasing is not clear. Please be patient and allow me to try again.
>
> I was playing around with the code and I was trying a bit the opposite of what you have submitted in the test file.
>
> You have, (snipped):
>
> GRANT pg_read_all_data TO regress_priv_user6;
>
> SET SESSION AUTHORIZATION regress_priv_user6;
> SELECT * FROM atest1; -- ok
> INSERT INTO atest2 VALUES ('foo', true); -- fail
>
>
> I was expecting:
> REVOKE pg_read_all_data FROM regress_priv_user6;

Are you sure this REVOKE was successful..?

> SET SESSION AUTHORIZATION regress_priv_user6;
> SELECT * FROM atest1; -- fail
> INSERT INTO atest2 VALUES ('foo', true); -- ok

=# create role r1;
CREATE ROLE
=*# grant pg_read_all_data to r1;
GRANT ROLE
=*# create table t1 (c1 int);
CREATE TABLE
=*# set role r1;
=*> select * from t1;
c1
----
(0 rows)
=*> reset role;
RESET
=*# revoke pg_read_all_data from r1;
REVOKE ROLE
=*# set role r1;
SET
=*> select * from t1;
ERROR: permission denied for table t1

Seems to be working as expected here.

> My expectation was not met since in my manual test (unless I made a mistake which is entirely possible), the SELECT above did not fail. The insert did succeed though.

That the INSERT worked seems pretty odd- could you post the exact
changes you've made to the regression tests, or the exact script where
you aren't seeing what you expect? I've not been able to reproduce the
GRANT allowing a user to INSERT into a table.

> The first question: Was my expectation wrong?

If there aren't any other privileges involved, then REVOKE'ing the role
from a user should prevent that user from being able to SELECT from the
table.

> The second question: Is there a privilege that can be granted to regress_priv_user6 that will not permit the select operation but will permit the insert operation? If no, should there be one?

GRANT INSERT ON atest1 TO regress_prive_user6; would allow just
INSERT'ing.

Magnus also brought up the idea of a 'write_all_data' role, but that's
pretty independent of this, imv. Not against adding it, if we can agree
as to what it means, exactly, but we should probably discuss over in
that sub-thread.

Thanks,

Stephen