Re: CREATEROLE and role ownership hierarchies - Mailing list pgsql-hackers

> On Oct 21, 2021, at 4:04 PM, Bossart, Nathan <bossartn@amazon.com> wrote:
>
> Regarding the "attack vector misfeature" comment, I remember being
> surprised when I first learned how much roles with CREATEROLE can do.
> When I describe CREATEROLE to others, I am sure to emphasize the note
> in the docs about such roles being "almost-superuser" roles.
> CREATEROLE is a rather big hammer at the moment, so I certainly think
> there is value in reducing its almost-superuser-ness.

It is hard to know how many people are using CREATEROLE currently.  There isn't much reason to give it out, since if
youcare enough about security to not give out superuser, you probably care too much about security to give away
CREATEROLE.

> I mentioned this in the other thread [0] already, but the first thing
> that comes to mind when I look at these patches is how upgrades might
> work.  Will we just make the bootstrap superuser the owner for all
> roles when you first upgrade to v15?

Yes, that's the idea.  After upgrade, all roles will form a tree, with the bootstrap superuser at the root of the tree.
The initial tree structure isn't very interesting, with all other roles directly owned by it, but from there the
superusercan rearrange the tree, and after that non-superuser roles can manage whatever subtree of roles they are the
rootof. 

>  Also, are we just going to strip
> the current CREATEROLE roles of much of their powers?  Maybe it's
> worth keeping a legacy CREATEROLE role attribute for upgraded clusters
> that could eventually be removed down the road.

The patch as written drastically reduces the power of the CREATEROLE attribute, in a non-backwards compatible way.  I
wonderedif there would be complaints about that.  If so, we could instead leave CREATEROLE alone, and create some other
privilegedrole for the same thing, but it does start to look funny having a CREATEROLE privilege bit and also a
privilegedrole named, perhaps, pg_can_create_roles. 

> I'd also like to bring up my note about allowing users to transfer
> role ownership.  When I tested the patches earlier, REASSIGN OWNED BY
> was failing with an "unexpected classid" ERROR.  Besides REASSIGN
> OWNED BY, perhaps there should be another mechanism for transferring
> ownership on a role-by-role basis (i.e., ALTER ROLE OWNER TO).  I
> haven't looked at this new patch set too closely, so my apologies if
> this has already been added.

Yes, I completely agree with you on that.  Both REASSIGN OWNED BY and ALTER ROLE OWNER TO should work.  I'll take a
lookat the patches and repost with any adjustments that I find necessary to make those work. 

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