Re: fixing CREATEROLE

> On Nov 28, 2022, at 11:34 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> No Defaults needed: David J., Mark?, Tom?

As Robert has the patch organized, I think defaults are needed, but I see that as a strike against the patch.

> Defaults needed - attached to role directly: Robert
> Defaults needed - defined within Default Privileges: Walther?
> The capability itself seems orthogonal to the rest of the patch to track these details better.  I think we can "Fix
CREATEROLE"without any feature regarding optional default behaviors and would suggest this patch be so limited and that
anotherthread be started for discussion of (assuming a default specifying mechanism is wanted overall) how it should
look. Let's not let a usability debate distract us from fixing a real problem. 

In Robert's initial email, he wrote, "It seems to me that the root of any fix in this area must be to change the rule
thatCREATEROLE can administer any role whatsoever." 

The obvious way to fix that is to revoke that rule and instead automatically grant ADMIN OPTION to a creator over any
rolethey create.  That's problematic, though, because as things stand, ADMIN OPTION is granted to somebody by granting
themmembership in the administered role WITH ADMIN OPTION, so membership in the role and administration of the role are
conflated.

Robert's patch tries to deal with the (possibly unwanted) role membership by setting up defaults to mitigate the
effects,but that is more confusing to me than just de-conflating role membership from role administration, and giving
rolecreators administration over roles they create, without in so doing giving them role membership.  I don't recall
enoughdetails about how hard it is to de-conflate role membership from role administration, and maybe that's a
non-starterfor reasons I don't recall at the moment.  I expect Robert has already contemplated that idea and instead
proposedthis patch for good reasons.  Robert? 

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