SQL: some role options [e.g. CREATEROLE] don't get passed down to the members of roles

[Arivijay]

Describe the problem

Role options given to roles don't get passed down to the members of those roles.
Even though system privileges are inherited by role members, there isn't an equivalent system privilege for role options such as CREATEROLE.

To Reproduce

CREATE ROLE can_create_role WITH CREATEROLE;
CREATE USER can_create_user WITH PASSWORD 'xxx';
GRANT can_create_role TO can_create_user;
Login as USER and create a role

[email protected]:26257/defaultdb> CREATE ROLE test;
ERROR: user can_create_user1 does not have CREATEROLE privilege

Environment:

• CockroachDB version v22.2.x

Additional context
Since customer has more than 50 AppDBAs, assigning permissions at the user level would be quite tedious and could lead to errors.

Jira issue: CRDB-27893

Epic CRDB-27601

[rafiss]

@andyyang890 could you take a look at this request? we will want to backport it to 23.1. i think this should be a a few days of work, and it will be a good issue to learn about how new system privileges are added.

[andyyang890]

To clarify, do we want to add new system-level privileges for all remaining role options or are we making role options inheritable through role membership?

Edit: Confirmed offline that we want to add new system-level privileges since we want role options to remain compatible with Postgres.