# Authorization

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

Note:

New in v20.1: Role-based access management (RBAC) is no longer an enterprise feature and is now freely available in the core version of CockroachDB. Also, for enhanced PostgreSQL compatibility, the keywords `ROLE` and `USER` can now be used interchangeably in SQL statements. Note that even though the keywords are now interchangeable, it is still helpful to understand the distinction between the concepts (a "user" refers to an individual database user and a "role" refers to a group of database users).

## Users and roles

There is no technical distinction between a role or user in CockroachDB. A role/user can:

- be permitted to log in to the SQL shell.
- be granted privileges to specific actions and database objects.
- belong to other users or roles, inheriting their privileges.

We refer to these as "roles" when they are created for managing the privileges of their member "users" and not for logging in directly, which is typically reserved for "users".

The SQL statements `CREATE USER` and `CREATE ROLE` will create the same entity with one exception: `CREATE ROLE` will add the `NOLOGIN` option by default, preventing the user/role from being used to log in. Otherwise, for enhanced PostgreSQL compatibility, the keywords `ROLE` and `USER` can be used interchangeably in SQL statements.

Throughout the documentation, however, we will refer to a "user" or "role" based on the intended purpose of the entity.

## 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 and role options, 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.

For secure clusters, in addition to generating the client certificate for the `root` user, you can assign or change the password for the `root` user using the `ALTER USER` statement.

## Roles

Note:

New in v20.1 Role-based access management is no longer an enterprise feature and is now freely available in the core version of CockroachDB.

A role is a group of users and/or other roles for which you can grant or revoke privileges 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.

### Create and manage roles

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

Statement | Description
--- | ---
`CREATE ROLE` | Create SQL roles.
`DROP ROLE` | Remove one or more SQL roles.
`GRANT <roles>` | Add a role or user as a member to a role.
`REVOKE <roles>` | 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.

#### `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`.

To assign a user to the `admin` role:

    > GRANT admin TO <username>;

#### `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. This applies to the `admin` role as well - only admin users with the `WITH ADMIN OPTION` can add or remove other users from the `admin` role.

#### 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>` statements to manage privileges for users and roles.

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, and creating and managing databases. Do not use the `root` user for applications; instead, create users or roles with specific privileges based on your application’s access requirements.
- 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:

       $ 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:

       > CREATE USER db_admin;

3. Grant all privileges on database `movr` to user `db_admin`:

       > GRANT ALL ON DATABASE movr TO db_admin;

4. Grant all privileges on all tables in database `movr` to user `db_admin`:

       > GRANT ALL ON TABLE * TO db_admin;

5. Verify that `db_admin` has all privileges:

       > 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:

       > CREATE USER app_user;

       > GRANT INSERT, DELETE, UPDATE, SELECT ON vehicles TO app_user;

       > 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:

       > CREATE USER report_user;

       > GRANT SELECT ON vehicles TO report_user;

       > 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.:

       $ cockroach demo

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:

       > CREATE ROLE db_admin_role;

       > SHOW ROLES;

               username    |  options   | member_of
       ----------------+------------+------------
         admin         | CREATEROLE | {}
         db_admin_role | NOLOGIN    | {}
         root          | CREATEROLE | {admin}
       (3 rows)

       > GRANT ALL ON DATABASE movr TO db_admin_role;

       > GRANT ALL ON TABLE * TO db_admin_role;

       > 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:

       > CREATE USER db_admin_1;

       > CREATE USER db_admin_2;

       > 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.

       > CREATE ROLE app_user_role;

       > SHOW ROLES;

               username    |  options   |    member_of
       ----------------+------------+------------------
         admin         | CREATEROLE | {}
         app_user_role | NOLOGIN    | {}
         db_admin_1    |            | {db_admin_role}
         db_admin_2    |            | {db_admin_role}
         db_admin_role | NOLOGIN    | {}
         root          | CREATEROLE | {admin}
       (6 rows)

       > GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE vehicles TO app_user_role;

       > 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:

       > CREATE USER app_user_1;

       > CREATE USER app_user_2;

       > CREATE USER app_user_3;

       > 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.

       > CREATE ROLE report_user_role;

       > SHOW ROLES;

                 username     |  options   |    member_of
       -------------------+------------+------------------
         admin            | CREATEROLE | {}
         app_user_1       |            | {app_user_role}
         app_user_2       |            | {app_user_role}
         app_user_3       |            | {app_user_role}
         app_user_role    | NOLOGIN    | {}
         db_admin_1       |            | {db_admin_role}
         db_admin_2       |            | {db_admin_role}
         db_admin_role    | NOLOGIN    | {}
         report_user_role | NOLOGIN    | {}
         root             | CREATEROLE | {admin}
       (10 rows)

       > GRANT SELECT ON vehicles TO report_user_role;

       > 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:

       > CREATE USER report_user_1;

       > CREATE USER report_user_2;

       > CREATE USER report_user_3;

       > CREATE USER report_user_4;

       > CREATE USER report_user_5;

       > GRANT report_user_role TO report_user_1, report_user_2, report_user_3, report_user_4, report_user_5;

## See also

- Client Connection Parameters
- SQL Statements
- `CREATE USER`
- `ALTER USER`
- `DROP USER`
- `SHOW USERS`
- `CREATE ROLE`
- `DROP ROLE`
- `SHOW ROLES`
- `GRANT <privileges>`
- `GRANT <roles>`
- `REVOKE <privileges>`
- `REVOKE <roles>`
- `SHOW GRANTS`