Re: allowing for control over SET ROLE - Mailing list pgsql-hackers

Greetings,

* Nathan Bossart (nathandbossart@gmail.com) wrote:
> On Fri, Sep 30, 2022 at 04:34:32PM -0400, Robert Haas wrote:
> > That thread has not reached an entirely satisfying conclusion.
> > However, the behavior that was deemed outright buggy over there has
> > been fixed. The remaining question is what to do about commands that
> > allow you to give objects to other users (like ALTER <whatever> ..
> > OWNER TO) or commands that allow you to create objects owned by other
> > users (like CREATE DATABASE ... OWNER). I have, in this version,
> > adopted the proposal by Wolfgang Walther on the other thread to make
> > this controlled by the new SET option. This essentially takes the view
> > that the ability to create objects owned by another user is not
> > precisely a privilege, and is thus not inherited just because the
> > INHERIT option is set on the GRANT, but it is something you can do if
> > you could SET ROLE to that role, so we make it dependent on the SET
> > option. This logic is certainly debatable, but it does have the
> > practical advantage of making INHERIT TRUE, SET FALSE a useful
> > combination of settings for predefined roles. It's also 100%
> > backward-compatible, whereas if we made the behavior dependent on the
> > INHERIT option, users could potentially notice behavior changes after
> > upgrading to v16.
>
> I'm not sure about tying the ownership stuff to this new SET privilege.
> While you noted some practical advantages, I'd expect users to find it kind
> of surprising.  Also, for predefined roles, I think you need to be careful
> about distributing ADMIN, as anyone with ADMIN on a predefined role can
> just GRANT SET to work around the restrictions.  I don't have a better
> idea, though, so perhaps neither of these things is a deal-breaker.  I was
> tempted to suggest using ADMIN instead of SET for the ownership stuff, but
> that wouldn't be backward-compatible, and you'd still be able to work
> around it to some extent with SET (e.g., SET ROLE followed by CREATE
> DATABASE).

As we work through splitting up the privileges and managing them in a
more fine-grained way, it seems clear that we'll need to have a similar
split for ADMIN rights on roles- that is, we'll need to be able to
say "role X is allowed to GRANT INHERIT for role Y to other roles, but
not SET".

I'm still half-tempted to say that predefined roles should just be dealt
with as a special case.. but if we split ADMIN in the manner as
described above then maybe we could get away with not having to, but it
would depend a great deal of people actually reading the documentation
and I'm concerned that's a bit too much to ask in this case.

That is- the first person who is likely to GRANT out ADMIN rights in a
predefined role is going to be a superuser.  To avoid breaking backwards
compatibility, GRANT'ing of ADMIN needs to GRANT all the partial-ADMIN
rights that exist, or at least exist today, which includes both SET and
INHERIT.  Unless we put some kind of special case for predefined roles
where we throw an error or at least a warning when a superuser
(presumably) inadvertantly does a simple GRANT ADMIN for $predefined
role, we're going to end up in the situation where folks can SET ROLE to
a predefined role and do things that they really shouldn't be allowed
to.

We could, of course, very clearly document that the way to GRANT ADMIN
rights for a predefined role is to always make sure to *only* GRANT
ADMIN/INHERIT, but again I worry that it simply wouldn't be followed in
many cases.  Perhaps we could arrange for the bootstrap superuser to
only be GRANT'd ADMIN/INHERIT for predefined roles and then not have an
explicit cut-out for superuser doing a GRANT on predefined roles or
perhaps having such be protected under allow_system_table_mods under the
general consideration that modifying of predefined roles isn't something
that folks should be doing post-initdb.

Just a few thoughts on this, not sure any of these ideas are great but
perhaps this helps move us forward.

Thanks,

Stephen