Cockroach: sql: add CREATEUSER and DELETEUSER grants

[BramGruneir]

From a customer:

In order to comply with separation of duties compliance policies, we are required to use a separate system (RSAM) to provision new users / grant roles in each Cockroach Cluster we build and provide to customers.
RSAM has an API which we can call programmatically to connect to a target CockroachDB and create a requested user or execute a grant. We need the ability to provide RSAM a separate role just to manage the IAM aspects of each target Cockroach cluster, without granting RSAM "admin" or "root" level privileges.

This ticket tracks adding CREATEUSER and DELETEUSER grants, and threading permissions checks for them throughout the user management code.

[RoachietheSupportRoach]

Zendesk ticket #3670 has been linked to this issue.

[solongordon]

Let's review Postgres's treatment of these privileges before diving in. At first glance it looks like the relevant privilege is CREATEROLE.

[solongordon]

@awoods187 @nstewart Would you mind checking with the customer on what privileges are required here? We're imagining adding a single grant type which would include all of the following:

• Create users
• Delete users
• Create roles
• Delete roles
• Assign roles to users
• Remove roles from users

Does that sound right?

[knz]

Having a single "grant type" that does everything above is likely inadequate because once a user has it it, that user can administrate every other role.

It does not achieve the user's expectation to partition the privileges of users across subsets of the data. For example the user story:

If a human user needs to request RO access to one of the app-db user roles (eg: for troubleshooting, monitoring etc),
they should be able to request access to those roles via the standard RSAM DB request process with associated
approval workflow

(See linked requirement PDF in ZD.)
The implication is clear that the access must be granted via the standard RSAM not just any RSAM that happens to have the permission.

So in order to better match the user demand I would suggest to also understand how pg does this.

1) In postgres each role can have one or more users designated to have "admin option". The admin option is a property of the (role, member) pair and enables those users to assign that role to other users (i.e. invite them to the role) or remove that role from members. CockroachDB currently stores the admin option, and I think it handles it, but I am not sure whether this is fully tested.

2) the permission to grant/revoke privileges to other members/roles on a database or table is subject to the "WITH GRANT OPTION" bit which is present on every combination of (table, user/role, privilege). Technically this would correspond to a new bool field on the UserPrivileges struct.

So the proper approach to the customer need is to implement what pg does and do this as follows:

• when a new db "AppDB" is created, also create a new role AppDB_RO, AppDB_RW and AppDB_Admins.
• assign the RSAM as a member of the 3 roles with the admin option bit set so they can add other users to that group.
• grant AppDB_Admins privilege to create/drop tables in that db only.
• grant AppDB_RO SELECT privilege.
• grant AppDB_RW UPDATE/etc privilege

I think (but I am not sure) that only feature 1 above (WITH ADMIN OPTION) is sufficient to achieve the user's requirements. WITH GRANT OPTION may not be necessary. This needs to be checked.

[solongordon]

@knz After discussing with the customer I'm fairly sure that the level of granularity you're describing is not necessary right now. They just have a single RSAM application which is responsible for creating users, creating roles, and assigning users to roles. They said it is expected for the RSAM user to be able to administer all roles and users. So I still feel that mimicking Postgres's CREATEROLE privilege will be sufficient for their use case.

Note that the ability for a user with CREATEROLE to grant privileges to a user or role would still be governed by our existing GRANT privilege.

[solongordon]

@knz pointed out that this privilege should not bestow the ability to grant the admin role to users.