Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

On Mon, Jul 26, 2021 at 4:28 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> so ... yes and no. There's an awful lot being ascribed to
> 'administrator' without any definition of it being actually given. We
> are working in this thread to explicitly split up superuser privileges
> to allow them to be granted to non-superusers and talking about cases
> where those privileges end up interacting with each other. Is Alice, as
> the 'network' manager considered an 'administrator' of XYZ? Is Bob, as
> the 'database' manager considered an 'administrator'? Perhaps both are,
> perhaps neither are. It doesn't seem helpful to be vague.

XYZ was intended to stand in for something like 'network' or
'database' or whatever other particular part of PostgreSQL Alice might
be charged with administering.

> If Alice is given the right to create event triggers in a given
> database, then that's explicitly giving Alice the right to block anyone
> from dropping tables in that database because that's an inherent part of
> the event trigger system. Should superusers be able to bypass that?
> Yes, they probably should be able to and, ideally, they'd be able to do
> that just in a particular session.

I agree.

> Should a user who has been allowed
> to modify certain GUCs that perhaps Alice hasn't been allowed to modify
> be able to be prevented from modifying those GUCs by Alice, when neither
> is a superuser? That's definitely a trickier question and I don't know
> that I've got an answer offhand.

My answer would be "no".

I concede Mark's point in another email that if Alice can entirely
prevent Bob from connecting to the database then by inference she can
also prevent him from exercising any other privileges he may have. I'm
prepared to say that's OK; if Alice is administering network
connections to the database and cuts everyone else off, then I guess
that's just how it is. But if Bob does somehow succeed in getting a
connection to the database, then he should be able to exercise his
right to change those GUCs which he has permission to change. Alice
shouldn't be able to thwart that.

--
Robert Haas
EDB: http://www.enterprisedb.com