We have the need to separate user (role) management from infrastructure (database) management.
Granting CREATEROLE to any role also allows this role to create other roles having CREATEDB privileges and therefore also getting CREATEDB privileges.
My use case would have been to grant CREATEROLE to any role while still restricting "create database".
I also which for my granular privileges around ROLEs.
Lite a CREATEROLE role that can only DROP the ROLEs it created (or created by other ROLEs its a member of).
Or a (NOLOGIN) ROLE that's restricted to have table privileges in some SCHEMAs only,
or in SCHEMAs owned by a given ROLE only. or ROLEs local to a given DATABASE only. These kind of things.
An idea I'm toying with is having a SCHEMA with (DEFINER RIGHTS) functions that acts as a wrapper around CREATE/DROP ROLE to impose custom restrictions.
It would record in private tables more context at creating times, and use that context to restrict the DROPs.
Could also solve your CREATEROLE vs CREATEDB conundrum maybe.
I have no time to develop that idea ATM though... Nor am I sure it would work.
And it would force my code to rip out it's current direct SQL DDLs, by equivalent functions from that mediator "admin" schema.
It would also not solve all my issues, like some ROLEs being restricted to GRANTs from a given SCHEMA.
(but maybe event trigger would allow to intercept that to check those too?)
Just thinking aloud :). --DD
