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 another thread 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 that CREATEROLE 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 role they create.  That's problematic, though, because as things stand, ADMIN OPTION is granted to somebody by granting them membership 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 role creators administration over roles they create, without in so doing giving them role membership.  I don't recall enough details about how hard it is to de-conflate role membership from role administration, and maybe that's a non-starter for reasons I don't recall at the moment.  I expect Robert has already contemplated that idea and instead proposed this patch for good reasons.  Robert?

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