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 application itself).

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 have the 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 application itself).

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 have the 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 control what 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 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

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@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 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

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 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

[...]

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 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

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