Roles are SQL groups that contain any number of users and roles as members. To create and manage your cluster's roles, use the following statements:
CREATE ROLE(Enterprise)DROP ROLE(Enterprise)GRANT <roles>REVOKE <roles>GRANT <privileges>REVOKE <privileges>SHOW ROLESSHOW GRANTS
Terminology
To get started, basic role terminology is outlined below:
| Term | Description |
|---|---|
| Role | A group containing any number of users or other roles. Note: All users belong to the public role, to which you can grant and revoke privileges. |
| Role admin | A member of the role that's allowed to modify role membership. To create a role admin, use WITH ADMIN OPTION. |
| Superuser / Admin | A member of the admin role. Only superusers can CREATE ROLE or DROP ROLE. The admin role is created by default and cannot be dropped. |
root |
A user that exists by default as a member of the admin role. The root user must always be a member of the admin role. |
| Inherit | The behavior that grants a role's privileges to its members. |
| Direct member | A user or role that is an immediate member of the role. Example: A is a member of B. |
| Indirect member | A user or role that is a member of the role by association. Example: A is a member of C ... is a member of B where "..." is an arbitrary number of memberships. |
Example
For the purpose of this example, you need an enterprise license and one CockroachDB node running in insecure mode:
$ cockroach start \
--insecure \
--store=roles \
--listen-addr=localhost:26257
As the
rootuser, use thecockroach usercommand to create a new user,maxroach:$ cockroach user set maxroach --insecureAs the
rootuser, open the built-in SQL client:$ cockroach sql --insecureCreate a database and set it as the default:
> CREATE DATABASE test_roles;> SET DATABASE = test_roles;Create a role and then list all roles in your database:
> CREATE ROLE system_ops;> SHOW ROLES;+------------+ | rolename | +------------+ | admin | | system_ops | +------------+Grant privileges to the
system_opsrole you created:> GRANT CREATE, SELECT ON DATABASE test_roles TO system_ops;> SHOW GRANTS ON DATABASE test_roles;+------------+--------------------+------------+------------+ | Database | Schema | User | Privileges | +------------+--------------------+------------+------------+ | test_roles | crdb_internal | admin | ALL | | test_roles | crdb_internal | root | ALL | | test_roles | crdb_internal | system_ops | CREATE | | test_roles | crdb_internal | system_ops | SELECT | | test_roles | information_schema | admin | ALL | | test_roles | information_schema | root | ALL | | test_roles | information_schema | system_ops | CREATE | | test_roles | information_schema | system_ops | SELECT | | test_roles | pg_catalog | admin | ALL | | test_roles | pg_catalog | root | ALL | | test_roles | pg_catalog | system_ops | CREATE | | test_roles | pg_catalog | system_ops | SELECT | | test_roles | public | admin | ALL | | test_roles | public | root | ALL | | test_roles | public | system_ops | CREATE | | test_roles | public | system_ops | SELECT | +------------+--------------------+------------+------------+Add the
maxroachuser to thesystem_opsrole:> GRANT system_ops TO maxroach;To test the privileges you just added to the
system_opsrole, use\qorctrl-dto exit the interactive shell, and then open the shell again as themaxroachuser (who is a member of thesystem_opsrole):$ cockroach sql --user=maxroach --database=test_roles --insecureAs the
maxroachuser, create a table:> CREATE TABLE employees ( id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY, profile JSONB );We were able to create the table because
maxroachhasCREATEprivileges.As the
maxroachuser, try to drop the table:> DROP TABLE employees;pq: user maxroach does not have DROP privilege on relation employeesYou cannot drop the table because your current user (
maxroach) is a member of thesystem_opsrole, which doesn't haveDROPprivileges.maxroachhasCREATEandSELECTprivileges, so try aSHOWstatement:> SHOW GRANTS ON TABLE employees;+------------+--------+-----------+------------+------------+ | Database | Schema | Table | User | Privileges | +------------+--------+-----------+------------+------------+ | test_roles | public | employees | admin | ALL | | test_roles | public | employees | root | ALL | | test_roles | public | employees | system_ops | CREATE | | test_roles | public | employees | system_ops | SELECT | +------------+--------+-----------+------------+------------+Now switch back to the
rootuser to test more of the SQL statements related to roles. Use\qorctrl-dto exit the interactive shell, and then open the shell again as therootuser:$ cockroach sql --insecureAs the
rootuser, revoke privileges and then drop thesystem_opsrole:> REVOKE ALL ON DATABASE test_roles FROM system_ops;> SHOW GRANTS ON DATABASE test_roles;+------------+--------------------+-------+------------+ | Database | Schema | User | Privileges | +------------+--------------------+-------+------------+ | test_roles | crdb_internal | admin | ALL | | test_roles | crdb_internal | root | ALL | | test_roles | information_schema | admin | ALL | | test_roles | information_schema | root | ALL | | test_roles | pg_catalog | admin | ALL | | test_roles | pg_catalog | root | ALL | | test_roles | public | admin | ALL | | test_roles | public | root | ALL | +------------+--------------------+-------+------------+> REVOKE ALL ON TABLE test_roles.* FROM system_ops;> SHOW GRANTS ON TABLE test_roles.*;+------------+--------+-----------+-------+------------+ | Database | Schema | Table | User | Privileges | +------------+--------+-----------+-------+------------+ | test_roles | public | employees | admin | ALL | | test_roles | public | employees | root | ALL | +------------+--------+-----------+-------+------------+Note:All of a role or user's privileges must be revoked before it can be dropped.> DROP ROLE system_ops;
