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 ROLES
SHOW 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
root
user, use thecockroach user
command to create a new user,maxroach
:$ cockroach user set maxroach --insecure
As the
root
user, open the built-in SQL client:$ cockroach sql --insecure
Create 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_ops
role 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
maxroach
user to thesystem_ops
role:> GRANT system_ops TO maxroach;
To test the privileges you just added to the
system_ops
role, use\q
orctrl-d
to exit the interactive shell, and then open the shell again as themaxroach
user (who is a member of thesystem_ops
role):$ cockroach sql --user=maxroach --database=test_roles --insecure
As the
maxroach
user, create a table:> CREATE TABLE employees ( id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY, profile JSONB );
We were able to create the table because
maxroach
hasCREATE
privileges.As the
maxroach
user, try to drop the table:> DROP TABLE employees;
pq: user maxroach does not have DROP privilege on relation employees
You cannot drop the table because your current user (
maxroach
) is a member of thesystem_ops
role, which doesn't haveDROP
privileges.maxroach
hasCREATE
andSELECT
privileges, so try aSHOW
statement:> 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
root
user to test more of the SQL statements related to roles. Use\q
orctrl-d
to exit the interactive shell, and then open the shell again as theroot
user:$ cockroach sql --insecure
As the
root
user, revoke privileges and then drop thesystem_ops
role:> 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;