Cockroach: sql:Privilege CREATEROLE/DELETEROLE doesn't appear to work

[johnrk]

Describe the problem
As described in this issue, we added the ability to give SQL Roles CREATEROLE and DELTEROLE privileges, but it's not working.

This is important towards our postgres compatibilty.

To Reproduce

1. set up local cluster
2. CREATE ROLE dev;
3. CREATE USER john;
4. GRANT TO dev;

- I tried CREATEROLE, CREATEUSER, DELETEROLE, DELETEUSER, NOCREATEROLE, CREATE ROLE,

Also, tried:
4. Inspired by Postgres docs, CREATE ROLE power WITH option ;
- I tried CREATEROLE, CREATEUSER, DELETEROLE, DELETEUSER, NOCREATEROLE, CREATE ROLE,

Expected behavior
I expected there to be no errors, and I expected to be able to create a role from a role that has createrole privileges.

Actual Results
Errors:
ERROR: role/user createuser does not exist
SQLSTATE: 42704

invalid syntax: statement ignored: at or near "option": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
CREATE ROLE power WITH option CREATE ROLE
^
HINT: try \h CREATE ROLE

Additional data / screenshots

Environment:
Johns-MacBook-Pro:cockroach johnkendall$ ./cockroach version
Build Tag: v20.1.0-beta.2-1010-g7d762f8365
Build Time: 2020/03/23 21:42:31
Distribution: CCL
Platform: darwin amd64 (x86_64-apple-darwin19.3.0)
Go Version: go1.13.7
C Compiler: 4.2.1 Compatible Apple LLVM 11.0.0 (clang-1100.0.33.16)
Build SHA-1: 7d762f83659245c4f14965366f0a1716c86f0f0c
Build Type: development

Additional context
What was the impact?

SQL Docs: https://www.postgresql.org/docs/10/sql-createrole.html

Add any other context about the problem here.

[johnrk]

However, when querying system.role_options, CREATEROLE privilege shows:
root@localhost:26257/system> select * from role_options;
username | option | value
-----------+------------+--------
admin | CREATEROLE | NULL
dev | NOLOGIN | NULL
dev_ops | NOLOGIN | NULL
root | CREATEROLE | NULL

[awoods187]

@RichardJCai can you triage this please? cc @jordanlewis for awareness

[jordanlewis]

@johnrk did you try this on Postgres? If you did, you'd see you've misinterpreted their docs a bit. You don't include the literal word "option". "option" in that doc is a placeholder for the various role options.

So you'd do

CREATE ROLE power WITH CREATEROLE

Does that help? You can close this out if so. Thank you for testing the feature!

[RichardJCai]

@johnrk

Regarding the GRANT CREATEROLE TO dev;
You can't directly "GRANT" a createrole (or any other option) to a role. I believe this is the same behaviour in postgres.

To give a user the option after, you would have to do ALTER ROLE name CREATEROLE.

[awoods187]

Perhaps we could repurpose this issue into a hint that says we need to alter role to grant new capabilities?

[johnrk]

Ah ok, it appears my syntax was off. I will take another look.

[johnrk]

It works!

[johnrk]

@awoods187 , providing that type of hint makes sense to me.

[RichardJCai]

Fixed by #46795 (Added hint when trying to GRANT option directly to user)