Re: New default role- 'pg_read_all_data'

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 28 August 2020 15:43, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> - Georgios Kokolatos (gkokolatos(at)protonmail(dot)com) wrote:
>
> > The patch seems to be implementing a useful and requested feature.
> > The patch applies cleanly and passes the basic regress tests. Also the commitfest bot is happy.
> > A first pass at the code, has not revealed any worthwhile comments.
> > Please allow me for a second and more thorough pass. The commitfest has hardly started after all.
>
> Great, thanks!
>
> > Also allow me a series of genuine questions:
> > What would the behaviour be with REVOKE?
> > In a sequence similar to:
> > GRANT ALL ON ...
>
> GRANT ALL would be independently GRANT'ing rights to some role and
> therefore unrelated.
>
> > REVOKE pg_read_all_data FROM ...
>
> This would simply REVOKE that role from the user. Privileges
> independently GRANT'd directly to the user wouldn't be affected. Nor
> would other role membership.
>
> > 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;

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

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.

The first question: Was my expectation wrong?
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?

I hope I am clearer now.

Thank you again for your patience.

>
> > Does the above scenario even make sense?
>
> I definitely believe it makes sense for a given role/user to be a member
> of pg_read_all_data and to be a member of other roles, or to have other
> privileges GRANT'd directly to them.
>
> Thanks,
>
> Stephen