AW: Postgres Enhancement Request

Yes, that would be totally ok. Like the "with [grant|admin] option" privilege model in SQL. It should be done with all these predefined top-level database roles like CREATEROLE.

It's doesn't only seem bogus but also a security hole when users can get privileges they have never been granted.

Markus

-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Gesendet: Mittwoch, 20. März 2019 15:30
An: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Betreff: Re: Postgres Enhancement Request

Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> Tom Lane schrieb am 20.03.2019 um 14:59:
>> No, it wouldn't. The point of CREATEROLE is to allow user creation
>> and deletion to be done by a role that's less than full superuser.
>> If we changed it like that, then you'd be right back at needing
>> superuser for very routine role creations. That's *not* an
>> improvement, even if it somehow fit better into the OP's desired
>> security model (which he hasn't explained).

> I didn't take this to be a request to remove the createdb privilege in general, but a request to have finer grained control what kind of privileges the role with createrole can grant to newly created roles (or what it can do in general).

Hmm. Thinking about it a bit more carefully, it does seem bogus that a role that has CREATEROLE but not CREATEDB can make a role that has the latter privilege. It would be more sensible to have a uniform rule that "you can't grant a privilege you don't have yourself", which would mean that the OP's problem could perhaps be solved by making a role that has CREATEROLE but not CREATEDB.

You could imagine going further and applying the full SQL privilege model to these things, which would make it possible to have a role that has CREATEDB (so can make DBs itself) but can't pass that privilege on to others for lack of grant options on CREATEDB.
But that would be a very much bigger chunk of work, and I'm not sure I see the payback.

regards, tom lane