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

Greetings,

* Bossart, Nathan (bossartn(at)amazon(dot)com) wrote:
> On 9/27/21, 11:16 AM, "Mark Dilger" <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> > On Sep 21, 2021, at 12:58 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >> I do like the basic thrust of reducing the power of CREATEROLE. There's
> >> an old legal maxim I learned in my distant youth that says "nemo dat
> >> quod non habet" - Nobody can give something they don't own. This seems
> >> to be in that spirit, and I approve :-)
> >
> > Great! I'm glad to hear the approach has some support.
>
> I'd also like to voice my support for this effort. I haven't been
> following this thread too closely, but I did take a gander at the
> latest patch set. There is a lot to unpack here. I think this could
> easily be split into 3 or 4 threads.

I tend to agree. I'm also generally supportive but following everything
that's going on in this particular patch set isn't easy.

> For the CREATEROLE changes, the main thing on my mind is how this
> might impact upgrades. IIUC roles with CREATEROLE will lose many
> privileges after pg_upgrade. I think one way to deal with this would
> be to have such upgrades grant all the privileges they are losing, but
> most CREATEROLE roles likely aren't using the full extent of their
> powers, so that approach may be a little extreme. Perhaps it is okay
> to just add a blurb in the release notes about this backwards-
> incompatible change.

This is definitely a pretty big change. There needs to be a bigger and
independent discussion about the general concept of role 'self
administration' as we talk about it in the comments of the role system
and which this doesn't really address too. I've been planning for a
while to start a specific thread about that and I'll try to do that so
that we can discuss that specifically, as it's quite relevant to all of
this, in my view.

> Another interesting thing I found is that if a role has ownership of
> a role that later obtains SUPERUSER, the owning role basically loses
> all control of the role. It makes sense to avoid letting non-
> superusers mess with superusers, but this led me to wonder if there
> should be a mechanism for transferring role ownership (e.g., ALTER
> ROLE or REASSIGNED OWNED BY). Presently, REASSIGNED OWNED BY fails
> with an "unexpected classid" ERROR. Such functionality might also
> come in handy for the pg_dump changes for maintaining role ownership.

I really think we need to stop addressing roles explicitly as
'superuser' vs. 'non-superuser', because a non-superuser role can be
GRANT'd a superuser role, which makes that distinction really not
sensible. This has continued to be a problem and we need to cleanly
address it. Not sure exactly how to do that today but it's certainly an
issue.

Thanks,

Stephen