Manage Roles

On this page

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:

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 the cockroach 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 the system_ops role:
```
GRANT system_ops TO maxroach;
```
To test the privileges you just added to the system_ops role, use \q or ctrl-d to exit the interactive shell, and then open the shell again as the maxroach user (who is a member of the system_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 has CREATE 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 the system_ops role, which doesn't have DROP privileges.

maxroach has CREATE and SELECT privileges, so try a SHOW 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 or ctrl-d to exit the interactive shell, and then open the shell again as the root user:
```
cockroach sql --insecure
```

As the root user, revoke privileges and then drop the system_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;

Pricing

Contact us

Sign In

Manage Roles

Terminology

Example

See also