Thread: Postgres Enhancement Request
CREATEROLE allows users to create new roles also having the CREATEDB privilege (at least in version 9.6). We want special users to be able to CREATEROLE without being able to CREATEDB (eg. when usermanagement is done by the applicationitself). Please prevent users with CREATEROLE to create roles having CREATEDB (analogous SUPERUSER and REPLICATION). Thanks
Zwettler Markus (OIZ) schrieb am 20.03.2019 um 11:10: > CREATEROLE allows users to create new roles also having the CREATEDB privilege (at least in version 9.6). > > We want special users to be able to CREATEROLE without being able to CREATEDB (eg. when usermanagement is done by the applicationitself). > > Please prevent users with CREATEROLE to create roles having CREATEDB (analogous SUPERUSER and REPLICATION). I agree that would be a welcome enhancement. As a workaround, you can create a function owned by a superuser (or any other user with the "createrole" privilege) using"security definer" that provides a simple "create user" capability and makes sure that the created user does not havethe createdb privilege. The user/role that should be able to create new roles doesn't need the createrole privilege at all then. All it needs is the execute privilege on the function. Thomas
We already did and use this at the moment. Unfortunately. Some out-of-the-box applications can't use functions for user management. Some users don't want "special" functions for user management. ... Markus -----Ursprüngliche Nachricht----- Von: Thomas Kellerer <spam_eater@gmx.net> Gesendet: Mittwoch, 20. März 2019 11:45 An: pgsql-general@lists.postgresql.org Betreff: Re: Postgres Enhancement Request Zwettler Markus (OIZ) schrieb am 20.03.2019 um 11:10: > CREATEROLE allows users to create new roles also having the CREATEDB privilege (at least in version 9.6). > > We want special users to be able to CREATEROLE without being able to CREATEDB (eg. when usermanagement is done by the applicationitself). > > Please prevent users with CREATEROLE to create roles having CREATEDB (analogous SUPERUSER and REPLICATION). I agree that would be a welcome enhancement. As a workaround, you can create a function owned by a superuser (or any other user with the "createrole" privilege) using"security definer" that provides a simple "create user" capability and makes sure that the created user does not havethe createdb privilege. The user/role that should be able to create new roles doesn't need the createrole privilege at all then. All it needs is the execute privilege on the function. Thomas
Thomas Kellerer <spam_eater@gmx.net> writes:
> Zwettler Markus (OIZ) schrieb am 20.03.2019 um 11:10:
>> Please prevent users with CREATEROLE to create roles having CREATEDB (analogous SUPERUSER and REPLICATION).
> I agree that would be a welcome enhancement.
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).
regards, tom lane
Tom Lane schrieb am 20.03.2019 um 14:59:
>>> Please prevent users with CREATEROLE to create roles having CREATEDB (analogous SUPERUSER and REPLICATION).
>
>> I agree that would be a welcome enhancement.
>
> 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
controlwhat kind of privileges the role with createrole can grant to newly created roles (or what it can do in
general).
Maybe if "createrole" was a regular privilege (like "create table"), then something like this would be possible:
create role user_admin;
grant create role to user_admin;
Thomas
Thomas Kellerer <spam_eater@gmx.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
controlwhat 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
Yes, that would be totally ok. Like the "with [grant|admin] option" privilege model in SQL. It should be done with all
thesepredefined 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@sss.pgh.pa.us>
Gesendet: Mittwoch, 20. März 2019 15:30
An: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-general@lists.postgresql.org
Betreff: Re: Postgres Enhancement Request
Thomas Kellerer <spam_eater@gmx.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
controlwhat 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
makea role that has the latter privilege. It would be more sensible to have a uniform rule that "you can't grant a
privilegeyou don't have yourself", which would mean that the OP's problem could perhaps be solved by making a role that
hasCREATEROLE but not CREATEDB.
You could imagine going further and applying the full SQL privilege model to these things, which would make it possible
tohave a role that has CREATEDB (so can make DBs itself) but can't pass that privilege on to others for lack of grant
optionson CREATEDB.
But that would be a very much bigger chunk of work, and I'm not sure I see the payback.
regards, tom lane
Hi Markus, Please see comment at the bottonm of this email! On 21/03/2019 05:36, Zwettler Markus (OIZ) wrote: > Yes, that would be totally ok. Like the "with [grant|admin] option" privilege model in SQL. It should be done with allthese 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 > > [...] A way of indicating content has been omitted! In ancient times, early 1990's '[ omitted ]' was used, but I started the trend of using '[...]'. > Hmm. Thinking about it a bit more carefully, it does seem bogus that a role that has CREATEROLE but not CREATEDB can makea role that has the latter privilege. It would be more sensible to have a uniform rule that "you can't grant a privilegeyou don't have yourself", which would mean that the OP's problem could perhaps be solved by making a role that hasCREATEROLE but not CREATEDB. > > You could imagine going further and applying the full SQL privilege model to these things, which would make it possibleto have a role that has CREATEDB (so can make DBs itself) but can't pass that privilege on to others for lack ofgrant 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 > > In the postgres groups, please bottom post, as that is the convention here. Bottom posting makes it easier to follow what is happening. You can also intersperse comments an omit chunks that are no longer relevant. Thanks, Gavin