On 9/27/21, 11:16 AM, "Mark Dilger" <mark.dil...@enterprisedb.com> wrote: > On Sep 21, 2021, at 12:58 PM, Andrew Dunstan <and...@dunslane.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