Re: role self-revocation

> On Mar 10, 2022, at 2:01 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> It sounds like you prefer a behavior where CREATEROLE gives power over
> all non-superusers, but that seems pretty limiting to me. Why can't
> someone want to create a user with power over some users but not
> others?

I agree with Robert on this.

Over at [1], I introduced a patch series to (a) change CREATEROLE and (b) introduce role ownership.  Part (a) wasn't
thatcontroversial.  The patch series failed to make it for postgres 15 on account of (b).  The patch didn't go quite
farenough, but with it applied, this is an example of a min-superuser "lord" operating within database "fiefdom": 

fiefdom=# -- mini-superuser who can create roles and write all data
fiefdom=# CREATE ROLE lord
fiefdom-#     WITH CREATEROLE
fiefdom-#     IN ROLE pg_write_all_data;
CREATE ROLE
fiefdom=#
fiefdom=# -- group which "lord" belongs to
fiefdom=# CREATE GROUP squire
fiefdom-#     ROLE lord;
CREATE ROLE
fiefdom=#
fiefdom=# -- group which "lord" has no connection to
fiefdom=# CREATE GROUP paladin;
CREATE ROLE
fiefdom=#
fiefdom=# SET SESSION AUTHORIZATION lord;
SET
fiefdom=>
fiefdom=> -- fail, merely a member of "squire"
fiefdom=> CREATE ROLE peon IN ROLE squire;
ERROR:  must have admin option on role "squire"
fiefdom=>
fiefdom=> -- fail, no privilege to grant CREATEDB
fiefdom=> CREATE ROLE peon CREATEDB;
ERROR:  must have createdb privilege to create createdb users
fiefdom=>
fiefdom=> RESET SESSION AUTHORIZATION;
RESET
fiefdom=#
fiefdom=# -- grant admin over "squire" to "lord"
fiefdom=# GRANT squire
fiefdom-#     TO lord
fiefdom-#     WITH ADMIN OPTION;
GRANT ROLE
fiefdom=#
fiefdom=# SET SESSION AUTHORIZATION lord;
SET
fiefdom=>
fiefdom=> -- ok, have both "CREATEROLE" and admin option for "squire"
fiefdom=> CREATE ROLE peon IN ROLE squire;
CREATE ROLE
fiefdom=>
fiefdom=> -- fail, no privilege to grant CREATEDB
fiefdom=> CREATE ROLE peasant CREATEDB IN ROLE squire;
ERROR:  must have createdb privilege to create createdb users
fiefdom=>
fiefdom=> RESET SESSION AUTHORIZATION;
RESET
fiefdom=#
fiefdom=# -- Give lord the missing privilege
fiefdom=# GRANT CREATEDB TO lord;
ERROR:  role "createdb" does not exist
fiefdom=#
fiefdom=# RESET SESSION AUTHORIZATION;
RESET
fiefdom=#
fiefdom=# -- ok, have "CREATEROLE", "CREATEDB", and admin option for "squire"
fiefdom=# CREATE ROLE peasant CREATEDB IN ROLE squire;
CREATE ROLE

The problem with this is that "lord" needs CREATEDB to grant CREATEDB, but really it should need something like grant
optionon "CREATEDB".  But that's hard to do with the existing system, given the way these privilege bits are
represented. If we added a few more built-in pg_* roles, such as pg_create_db, it would just work.  CREATEROLE itself
couldbe reimagined as pg_create_role, and then users could be granted into this role with or without admin option,
meaningthey could/couldn't further give it away.  I think that would be a necessary component to Joshua's "bot"
use-case,since the bot must itself have the privilege to create roles, but shouldn't necessarily be trusted with the
privilegeto create additional roles who have it. 

[1] https://www.postgresql.org/message-id/53C7DF4C-8463-4647-9DFD-779B5E1861C4@amazon.com

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company