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:

copy
icon/buttons/copy
$ cockroach start \
--insecure \
--store=roles \
--listen-addr=localhost:26257
  1. As the root user, open the built-in SQL client:

    copy
    icon/buttons/copy
    $ cockroach sql --insecure
    
  2. Create a user, maxroach:

    copy
    icon/buttons/copy
    > CREATE USER maxroach;
    
  3. Create a database and set it as the default:

    copy
    icon/buttons/copy
    > CREATE DATABASE test_roles;
    
    copy
    icon/buttons/copy
    > SET DATABASE = test_roles;
    
  4. Create a role and then list all roles in your database:

    copy
    icon/buttons/copy
    > CREATE ROLE system_ops;
    
    copy
    icon/buttons/copy
    > SHOW ROLES;
    
    +------------+
    |  rolename  |
    +------------+
    | admin      |
    | system_ops |
    +------------+
    
  5. Grant privileges to the system_ops role you created:

    copy
    icon/buttons/copy
    > GRANT CREATE, SELECT ON DATABASE test_roles TO system_ops;
    
    copy
    icon/buttons/copy
    > 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     |
    +------------+--------------------+------------+------------+
    
  6. Add the maxroach user to the system_ops role:

    copy
    icon/buttons/copy
    > GRANT system_ops TO maxroach;
    
  7. 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):

    copy
    icon/buttons/copy
    $ cockroach sql --user=maxroach --database=test_roles --insecure
    
  8. As the maxroach user, create a table:

    copy
    icon/buttons/copy
    > 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.

  9. As the maxroach user, try to drop the table:

    copy
    icon/buttons/copy
    > 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.

  10. maxroach has CREATE and SELECT privileges, so try a SHOW statement:

    copy
    icon/buttons/copy
    > 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     |
    +------------+--------+-----------+------------+------------+
    
  11. 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:

    copy
    icon/buttons/copy
    $ cockroach sql --insecure
    
  12. As the root user, revoke privileges and then drop the system_ops role:

    copy
    icon/buttons/copy
    > REVOKE ALL ON DATABASE test_roles FROM system_ops;
    
    copy
    icon/buttons/copy
    > 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        |
    +------------+--------------------+-------+------------+
    
    copy
    icon/buttons/copy
    > REVOKE ALL ON TABLE test_roles.* FROM system_ops;
    
    copy
    icon/buttons/copy
    > 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.
    copy
    icon/buttons/copy
    > DROP ROLE system_ops;
    

See also



Yes No