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

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.

The changes for adding GUC management roles seem pretty
straightforward and would likely be helpful for service providers.
However, I was kind of surprised that membership to such roles also
provided access to ALTER SYSTEM SET. IMO there's quite a big
difference between allowing a user to set a GUC per-session versus
cluster-wide. With these patches, if I just want to allow a user to
set a GUC like temp_file_limit or log_statement, I also have to give
them the ability to change it (and several other GUCs) for all roles
on the system.

I haven't spent too much time looking at the event trigger and logical
replication changes yet.

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.

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.

Nathan