User authorization is the act of defining access policies for authenticated CockroachDB users. CockroachDB allows you to create, manage, and remove your cluster's users and assign SQL-level privileges to the users. Additionally, if you have an Enterprise license, you can use role-based access management (RBAC) for simplified user management.

SQL users

A SQL user can interact with a CockroachDB database using the built-in SQL shell or through an application.

Create and manage users

Use the CREATE USER and DROP USER statements to create and remove users, the ALTER USER statement to add or change a user's password, the GRANT <privileges> and REVOKE <privileges> statements to manage the user’s privileges, and the SHOW USERS statement to list users.

A new user must be granted the required privileges for each database and table that the user needs to access.

Note:

By default, a new user belongs to the public role and has no privileges other than those assigned to the public role. For more information, see Public role.

root user

The root user is created by default for each cluster. The root user is assigned to the admin role and has all privileges across the cluster.

Roles

Note:

Role-based access management is an enterprise feature. To request a 30-day trial license, see Get CockroachDB.

Roles enable you to group users and other roles and grant or revoke privileges to the group as a whole. To simplify access management, create a role and grant privileges to the role, then create SQL users and grant them membership to the role.

Note:

PostgreSQL uses the term "role" to mean either a database users or a group of database users. CockroachDB, however, uses the term "user" to mean an individual database user and "role" to mean a group of database users.

Create and manage roles

To create and manage your cluster's roles, use the following statements:

Statement Description
CREATE ROLE (Enterprise) Create SQL roles.
DROP ROLE (Enterprise) Remove one or more SQL roles.
GRANT <roles> (Enterprise) Add a role or user as a member to a role.
REVOKE <roles> (Enterprise) Revoke a role or user's membership to a role.
GRANT <privileges> Manage each role or user's SQL privileges for interacting with specific databases and tables.
REVOKE <privileges> Revoke privileges from users and/or roles.
SHOW ROLES List the roles for all databases.
SHOW GRANTS List the privileges granted to users.

Default roles

The admin and public roles exist by default for Core as well as Enterprise clusters.

admin role

The admin role is created by default and cannot be dropped. Users belonging to the admin role have all privileges for all database objects across the cluster. The root user belongs to the admin role by default.

An admin user is a member of the admin role. Only admin users can use CREATE ROLE and DROP ROLE.

public role

All new users and roles belong to the public role by default. You can grant and revoke the privileges on the public role.

Terminology

Role admin

A role admin is a member of the role that's allowed to grant or revoke role membership to other users for that specific role. To create a role admin, use WITH ADMIN OPTION.

Tip:

The terms “admin role” and “role admin” can be confusing. A user who is a member of the admin role has all privileges on all database objects across the entire cluster, whereas a role admin has privileges limited to the role they are a member of. Assign the admin role to a SQL user if you want the user to have privileges across the cluster. Make a SQL user the role admin if you want to limit the user’s privileges to its current role, but with an option to grant or revoke role membership to other users.

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.

Privileges

When a user connects to a database, either via the built-in SQL client or a client driver, CockroachDB checks the user and role's privileges for each statement executed. If the user does not have sufficient privileges for a statement, CockroachDB gives an error.

Assign privileges

Use the GRANT <privileges> and REVOKE <privileges> to manage privileges for users and roles (for enterprise customers).

Take the following points into consideration while granting privileges to roles and users:

  • When a role or user is granted privileges for a database, new tables created in the database will inherit the privileges, but the privileges can then be changed. To grant privileges to a user on all existing tables in a database, see Grant privileges on all tables in a database

    Note:

    The user does not get privileges to existing tables in the database.

  • When a role or user is granted privileges for a table, the privileges are limited to the table.

  • In CockroachDB, privileges are granted to users and roles at the database and table levels. They are not yet supported for other granularities such as columns or rows.

  • The root user automatically belongs to the admin role and has the ALL privilege for new databases.

  • For privileges required by specific statements, see the documentation for the respective SQL statement.

You can manage the following privileges for databases and tables:

  • ALL
  • CREATE
  • DROP
  • GRANT
  • SELECT
  • INSERT
  • DELETE
  • UPDATE

Authorization best practices

We recommend the following best practices to set up access control for your clusters:

  • Use the root user only for database administration tasks such as creating and managing other users, creating and managing roles (for enterprise customers), and creating and managing databases. Do not use the root user for applications; instead, create users with specific privileges based on your application’s access requirements.
  • Enterprise customers: Create roles with specific privileges, create users, and then add the users to the relevant roles.
  • Use the "least privilege model" to grant privileges to users and roles.

Example

The following example uses MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

Let's say we want to create the following access control setup for the movr database:

  • One database admin (named db_admin) who can perform all database operations for existing tables as well as for tables added in the future.
  • One app user (named app_user) who can add, read update, and delete vehicles from the vehicles table.
  • One user (named report_user) who can only read the vehicles table.
  1. Use the cockroach demo command to load the movr database and dataset into a CockroachDB cluster:

    copy
    icon/buttons/copy
    $ cockroach demo
    
  2. Create the database admin (named db_admin) who can perform all database operations for existing tables as well as for tables added in the future:

    copy
    icon/buttons/copy
    > CREATE USER db_admin;
    
  3. Grant all privileges on database movr to user db_admin:

    copy
    icon/buttons/copy
    > GRANT ALL ON DATABASE movr TO db_admin;
    
  4. Grant all privileges on all tables in database movr to user db_admin:

    copy
    icon/buttons/copy
    > GRANT ALL ON TABLE * TO db_admin;
    
  5. Verify that db_admin has all privileges:

    copy
    icon/buttons/copy
    > SHOW GRANTS FOR db_admin;
    
          database_name |    schema_name     |         table_name         | grantee  | privilege_type  
    +---------------+--------------------+----------------------------+----------+----------------+
      movr          | crdb_internal      | NULL                       | db_admin | ALL             
      movr          | information_schema | NULL                       | db_admin | ALL             
      movr          | pg_catalog         | NULL                       | db_admin | ALL             
      movr          | public             | NULL                       | db_admin | ALL             
      movr          | public             | promo_codes                | db_admin | ALL             
      movr          | public             | rides                      | db_admin | ALL             
      movr          | public             | user_promo_codes           | db_admin | ALL             
      movr          | public             | users                      | db_admin | ALL             
      movr          | public             | vehicle_location_histories | db_admin | ALL             
      movr          | public             | vehicles                   | db_admin | ALL             
    (10 rows)
    
  6. As the root user, create a SQL user named app_user with permissions to add, read, update, and delete vehicles in the vehicles table:

    copy
    icon/buttons/copy
    > CREATE USER app_user;
    
    copy
    icon/buttons/copy
    > GRANT INSERT, DELETE, UPDATE, SELECT ON vehicles TO app_user;
    
    copy
    icon/buttons/copy
    > SHOW GRANTS FOR app_user;
    
          database_name | schema_name | table_name | grantee  | privilege_type  
    +---------------+-------------+------------+----------+----------------+
      movr          | public      | vehicles   | app_user | DELETE          
      movr          | public      | vehicles   | app_user | INSERT          
      movr          | public      | vehicles   | app_user | SELECT          
      movr          | public      | vehicles   | app_user | UPDATE          
    (4 rows)
    
  7. As the root user, create a SQL user named report_user with permissions to only read from the vehicles table:

    copy
    icon/buttons/copy
    > CREATE USER report_user;
    
    copy
    icon/buttons/copy
    > GRANT SELECT ON vehicles TO report_user;
    
    copy
    icon/buttons/copy
    > SHOW GRANTS FOR report_user;
    
      database_name | schema_name | table_name |   grantee   | privilege_type  
    +---------------+-------------+------------+-------------+----------------+
      movr          | public      | vehicles   | report_user | SELECT          
    (1 row)
    

The following example uses MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

Let's say we want to create the following access control setup for the movr database:

  • Two database admins (named db_admin_1 and db_admin_2) who can perform all database operations for existing tables as well as for tables added in the future.
  • Three app users (named app_user_1, app_user_2, and app_user_3) who can add, read update, and delete vehicles from the vehicles table.
  • Five users (named report_user_1, report_user_2, report_user_3, report_user_4, report_user_5) who can only read the vehicles table.
  1. Use the cockroach demo command to load the movr database and dataset into a CockroachDB cluster.:

    copy
    icon/buttons/copy
    $ cockroach demo
    

    Each cockroach demo instance comes with a temporary enterprise license which enables you to try out enterprise features such as role-based access management. The license expires after an hour.

  2. Create the database admin role (named db_admin_role) whose members can perform all database operations for existing tables as well as for tables added in the future:

    copy
    icon/buttons/copy
    > CREATE ROLE db_admin_role;
    
    copy
    icon/buttons/copy
    > SHOW ROLES;
    
            role_name    
    +---------------+
      admin          
      db_admin_role  
    (2 rows)
    
    copy
    icon/buttons/copy
    > GRANT ALL ON DATABASE movr TO db_admin_role;
    
    copy
    icon/buttons/copy
    > GRANT ALL ON TABLE * TO db_admin_role;
    
    copy
    icon/buttons/copy
    > SHOW GRANTS ON DATABASE movr;
    
          database_name |    schema_name     |    grantee    | privilege_type  
    +---------------+--------------------+---------------+----------------+
      movr          | crdb_internal      | admin         | ALL             
      movr          | crdb_internal      | db_admin_role | ALL             
      movr          | crdb_internal      | root          | ALL             
      movr          | information_schema | admin         | ALL             
      movr          | information_schema | db_admin_role | ALL             
      movr          | information_schema | root          | ALL             
      movr          | pg_catalog         | admin         | ALL             
      movr          | pg_catalog         | db_admin_role | ALL             
      movr          | pg_catalog         | root          | ALL             
      movr          | public             | admin         | ALL             
      movr          | public             | db_admin_role | ALL             
      movr          | public             | root          | ALL             
    (12 rows)
    
  3. Create two database admin users (named db_admin_1 and db_admin_2) and grant them membership to the db_admin_role role:

    copy
    icon/buttons/copy
    > CREATE USER db_admin_1;
    
    copy
    icon/buttons/copy
    > CREATE USER db_admin_2;
    
    copy
    icon/buttons/copy
    > GRANT db_admin_role TO db_admin_1, db_admin_2;
    
  4. Create a role named app_user_role whose members can add, read update, and delete vehicles to the vehicles table.

    copy
    icon/buttons/copy
    > CREATE ROLE app_user_role;
    
    copy
    icon/buttons/copy
    > SHOW ROLES;
    
            role_name    
    +---------------+
      admin          
      app_user_role  
      db_admin_role  
    (3 rows)
    
    copy
    icon/buttons/copy
    > GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE vehicles TO app_user_role;
    
    copy
    icon/buttons/copy
    > SHOW GRANTS ON vehicles;
    
          database_name | schema_name | table_name |    grantee    | privilege_type  
    +---------------+-------------+------------+---------------+----------------+
      movr          | public      | vehicles   | admin         | ALL             
      movr          | public      | vehicles   | app_user_role | DELETE          
      movr          | public      | vehicles   | app_user_role | INSERT          
      movr          | public      | vehicles   | app_user_role | SELECT          
      movr          | public      | vehicles   | app_user_role | UPDATE          
      movr          | public      | vehicles   | db_admin_role | ALL             
      movr          | public      | vehicles   | root          | ALL             
    (7 rows)
    
  5. Create three app users (named app_user_1, app_user_2, and app_user_3) and grant them membership to the app_user_role role:

    copy
    icon/buttons/copy
    > CREATE USER app_user_1;
    
    copy
    icon/buttons/copy
    > CREATE USER app_user_2;
    
    copy
    icon/buttons/copy
    > CREATE USER app_user_3;
    
    copy
    icon/buttons/copy
    > GRANT app_user_role TO app_user_1, app_user_2, app_user_3;
    
  6. Create a role named report_user_role whose members can only read the vehicles table.

    copy
    icon/buttons/copy
    > CREATE ROLE report_user_role;
    
    copy
    icon/buttons/copy
    > SHOW ROLES;
    
             role_name      
    +------------------+
      admin             
      app_user_role     
      db_admin_role     
      report_user_role  
    (4 rows)
    
    copy
    icon/buttons/copy
    > GRANT SELECT ON vehicles TO report_user_role;
    
    copy
    icon/buttons/copy
    > SHOW GRANTS ON vehicles;
    
          database_name | schema_name | table_name |     grantee      | privilege_type  
    +---------------+-------------+------------+------------------+----------------+
      movr          | public      | vehicles   | admin            | ALL             
      movr          | public      | vehicles   | app_user_role    | DELETE          
      movr          | public      | vehicles   | app_user_role    | INSERT          
      movr          | public      | vehicles   | app_user_role    | SELECT          
      movr          | public      | vehicles   | app_user_role    | UPDATE          
      movr          | public      | vehicles   | db_admin_role    | ALL             
      movr          | public      | vehicles   | report_user_role | SELECT          
      movr          | public      | vehicles   | root             | ALL             
    (8 rows)
    
  7. Create five report users (named report_user_1, report_user_2, report_user_3, report_user_4, and report_user_5) and grant them membership to the report_user_role role:

    copy
    icon/buttons/copy
    > CREATE USER report_user_1;
    
    copy
    icon/buttons/copy
    > CREATE USER report_user_2;
    
    copy
    icon/buttons/copy
    > CREATE USER report_user_3;
    
    copy
    icon/buttons/copy
    > CREATE USER report_user_4;
    
    copy
    icon/buttons/copy
    > CREATE USER report_user_5;
    
    copy
    icon/buttons/copy
    > GRANT report_user_role TO report_user_1, report_user_2, report_user_3, report_user_4, report_user_5;
    

See also



Yes No