Re: CREATEROLE and role ownership hierarchies

> On Jan 25, 2022, at 12:44 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> As I mentioned in the patch review, having a particular bit set doesn't
> necessarily mean you should be able to pass it on- the existing object
> GRANT system distinguishes those two and it seems like we should too.
> In other words, I'm saying that we should be able to explicitly say just
> what privileges a CREATEROLE user is able to grant to some other role
> rather than basing it on what that user themselves has.

I like the way you are thinking, but I'm not sure I agree with the facts you are asserting.

I agree that "CREATE ROLE.. ROLE .." differs from "CREATE ROLE .. ADMIN ..", and "GRANT..WITH GRANT OPTION" differs from "GRANT..", but those only cover privileges tracked in an aclitem array. The privileges CREATEDB, CREATEROLE, REPLICATION, and BYPASSRLS don't work that way. There isn't a with/without grant option distinction for them. So I'm forced to say that a role without those privileges must not give them away.

I'd be happier if we could get rid of all privileges of that kind, leaving only those that can be granted with/without grant option, tracked in an aclitem, and use that to determine if the user creating the role can give them away. But that's a bigger redesign of the system. Just touching how CREATEROLE works entails backwards compatibility problems. I'd hate to try to change all these other things; we'd be breaking a lot more, and features that appear more commonly used.

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