Re: has_privs_of_role vs. is_member_of_role, redux

On Mon, 2022-09-19 at 15:32 -0400, Robert Haas wrote:
> One could take the view that the issue here is that
> pg_read_all_settings shouldn't have the right to create objects in
> the
> first place, and that this INHERIT vs. SET ROLE distinction is just a
> distraction. However, that would require accepting the idea that it's
> possible for a role to lack privileges granted to PUBLIC, which also
> sounds pretty unsatisfying. On the whole, I'm inclined to think it's
> reasonable to suppose that if you want to grant a role to someone
> without letting them create objects owned by that role, it should be
> a
> role that doesn't own any existing objects either. Essentially,
> that's
> legislating that predefined roles should be minimally privileged:
> they
> should hold the ability to do whatever it is that they are there to
> do
> (like read all settings) but not have any other privileges (like the
> ability to do stuff to objects they own).

I like this approach -- the idea that you can create a role that can't
own anything, can't create anything, and to which nobody else can "SET
ROLE".

Creating a "virtual" role like that feels much more declarative and
easy to document: "this isn't a real user, it's just a collection of
inheritable privileges". Even superusers couldn't "SET ROLE
pg_read_all_settings" or "OWNER TO pg_signal_backend".

I wouldn't call it "minimally privileged" (which feels wrong because it
wouldn't even have privileges on PUBLIC, as you say); I'd just say that
it's a type of role where those things just don't make sense.

--
Jeff Davis
PostgreSQL Contributor Team - AWS