Re: CREATEROLE and role ownership hierarchies

> On Jan 25, 2022, at 12:44 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> I agree that CREATEROLE is overpowered and that the goal of this should
> be to provide a way for roles to be created and dropped that doesn't
> give the user who has that power everything that CREATEROLE currently
> does.

I'm attaching a patch that attempts to fix CREATEROLE without any connection to role ownership.

>  The point I was making is that the concept of role ownership
> isn't intrinsically linked to that and is, therefore, as you say, gravy.

I agree, they aren't intrinsically linked, though the solution to one might interact in some ways with the solution to
theother. 

> That isn't to say that I'm entirely against the role ownership idea but
> I'd want it to be focused on the goal of providing ways of creating and
> dropping users and otherwise performing that kind of administration and
> that doesn't require the specific change to make owners be members of
> all roles they own and automatically have all privileges of those roles
> all the time.

The attached WIP patch attempts to solve most of the CREATEROLE problems but not the problem of which role who can drop
whichother role.  That will likely require an ownership concept. 

The main idea here is that having CREATEROLE doesn't give you ADMIN on roles, nor on role attributes.  For role
attributes,the syntax has been extended.  An excerpt from the patch's regression test illustrates some of that concept: 

-- ok, superuser can create a role that can create login replication users, but
-- cannot itself login, nor perform replication
CREATE ROLE regress_role_repladmin
    CREATEROLE WITHOUT ADMIN OPTION     -- can create roles, but cannot give it away
    NOCREATEDB WITHOUT ADMIN OPTION     -- cannot create db, nor give it away
    NOLOGIN WITH ADMIN OPTION           -- cannot log in, but can give it away
    NOREPLICATION WITH ADMIN OPTION     -- cannot replicate, but can give it away
    NOBYPASSRLS WITHOUT ADMIN OPTION;   -- cannot bypassrls, nor give it away

-- ok, superuser can create a role with CREATEROLE but restrict give-aways
CREATE ROLE regress_role_minoradmin
    NOSUPERUSER                         -- WITHOUT ADMIN OPTION is implied
    CREATEROLE WITHOUT ADMIN OPTION
    NOCREATEDB WITHOUT ADMIN OPTION
    NOLOGIN WITHOUT ADMIN OPTION
    NOREPLICATION                       -- WITHOUT ADMIN OPTION is implied
    NOBYPASSRLS                         -- WITHOUT ADMIN OPTION is implied
    NOINHERIT WITHOUT ADMIN OPTION
    CONNECTION LIMIT NONE WITHOUT ADMIN OPTION
    VALID ALWAYS WITHOUT ADMIN OPTION
    PASSWORD NULL WITHOUT ADMIN OPTION;

-- fail, having CREATEROLE is not enough to create roles in privileged roles
SET SESSION AUTHORIZATION regress_role_minoradmin;
CREATE ROLE regress_nosuch_read_all_data IN ROLE pg_read_all_data;
ERROR:  must have admin option on role "pg_read_all_data"

-- fail, cannot change attributes without ADMIN for them
SET SESSION AUTHORIZATION regress_role_minoradmin;
ALTER ROLE regress_role_login LOGIN;
ERROR:  must have admin on login to change login attribute
ALTER ROLE regress_role_login NOLOGIN;
ERROR:  must have admin on login to change login attribute


Whether "WITH ADMIN OPTION" or "WITHOUT ADMIN OPTION" is implied hinges on whether the role is given CREATEROLE.  That
hackeryis necessary to preserve backwards compatibility.  If we don't care about compatibility, I could change the
patchto make "WITHOUT ADMIN OPTION" implied for all attributes when not specified. 

I'd appreciate feedback on the direction this patch is going.




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