> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Authorization in CockroachDB
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
Authorization, generally, is the control over **who** (users/roles) can perform **which actions** (e.g read, write, update, delete, grant, etc.) to **which resources or targets** (databases, functions, tables, clusters, schemas, rows, users, jobs, etc.).
This page describes authorization of SQL users on particular CockroachDB database clusters. This is distinct from authorization of CockroachDB Cloud Console users on CockroachDB Cloud organizations.
Learn more about the CockroachDB Cloud authorization model
## Authorization models
Authorization in CockroachDB is *unified*, meaning that a given SQL user's permissions on a given cluster are governed by the same policies in different contexts such as accessing the SQL shell or viewing data from the DB Console.
Starting in v22.2, CockroachDB introduces a new granular [system-level privilege model](#supported-privileges) that provides finer control over a user's ability to work with the database. This new system-level privilege model is intended to replace the existing [role options model](#role-options) in a future release of CockroachDB. As such, any legacy role options that now have corresponding system-level privilege versions are **deprecated in CockroachDB v22.2**, though both are supported alongside each other in v22.2. We recommend familiarizing yourself with the new system-level privilege model, and implementing it where possible.
CockroachDB offers two authorization models:
| Authorization Model | Features |
| ------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [System-level Privileges](#supported-privileges) | - Introduced in CockroachDB v22.2.
- Supported in CockroachDB v22.2, alongside the existing role options.
- Apply cluster-wide. A system-level privilege is granted at the cluster level, and is inherited via role membership, similar to how [object-level privileges](#privileges) are inherited.
- Are granted with the `GRANT` statement using the `SYSTEM` parameter, and viewed with the `SHOW SYSTEM GRANTS` statement.
- May be assigned with the `GRANT OPTION`, which allows the assigned user or role to further grant that same system-level privilege to other users or roles.
|
| [Role Options](#role-options) | - [Specific role options](#supported-privileges) which have had corresponding system-level privileges introduced in CockroachDB v22.2 are now **deprecated**.
- Supported in CockroachDB v22.2, alongside the new system-level privileges.
- Apply only to specific users, and are not inheritable via role membership.
- Are granted with the `GRANT` statement, and viewed with the `SHOW GRANTS` statement.
- May be assigned with the `GRANT OPTION`, which allows the assigned user or role to further grant that same role option to other users or roles.
|
If a system-level privilege exists with the same name as a role option, the system-level privilege should be used.
## Users and roles
Both [authorization models](#authorization-models) make use of the concept of user 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](#privileges) to specific actions and database objects.
* Be a member of other users/roles, inheriting their privileges.
* Have other users/roles as members that inherit its privileges.
* Be configured with other [role options](#role-options).
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` and `REVOKE` 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.
By default, a new user belongs to the `public` role and has no privileges other than those assigned to the `public` role.
### Reserved identities
These identities are reserved within CockroachDB. These identities are created automatically and cannot be removed.
| Identity | Description |
| -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `node` | Used for all internode communications and for executing internal SQL operations that are run as part of regular node background processes. The `node` user does not appear when listing a cluster's users. |
| `root` | Used for administrator access in cases where it is required to manage other admins, such as when deploying a new cluster. The `root` user is created by default for each cluster. The `root` user is assigned to the [`admin` role](#admin-role) and has all privileges across the cluster.
For routine administration in production, Cockroach Labs recommends that you:- Assign a password for the `root` user using the `ALTER USER` statement, and restrict access to the password.
- Avoid relying on the `root` user, and instead grant the `admin` role to users.
|
In production, access to the `node` and `root` cluster certificates must be handled with care due to the broad level of access they confer on their holders.
## Roles
This section describes roles. For role options like `CREATEROLE`, see [role options](#role-options).
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.
### 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT admin TO ;
```
#### `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
`Role admin` is a [role option](#role-options) that allows a given user or role to *administrate* itself, by granting and revoking it to other users and roles.
To create a `role admin`, use `WITH ADMIN OPTION`.
The terms “`admin` role” and “`role admin`” can be confusing.
The `admin` role is a role (specifically the role granting all privileges on all database resources across a cluster), whereas `role admin` is a role option that is either enabled or disabled or not on any given role or grant of a role to another user or role.
Learn more about `role options`.
#### 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.
## Object ownership
All CockroachDB objects (such as databases, tables, schemas, and types) must have owners. The user that created the object is the default owner of the object and has `ALL` privileges on the object. Similarly, any roles that are members of the owner role also have all privileges on the object.
All objects that do not have owners (for example, objects created before upgrading to v20.2) have `admin` set as the default owner, with the exception of system objects. System objects without owners have `node` as their owner.
To allow another user to use the object, the owner can [assign privileges](#managing-privileges) to the other user. Members of the `admin` role have `ALL` privileges on all objects.
Users that own objects cannot be dropped until the ownership is transferred to another user.
## 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.
### Supported privileges
System-level privileges (also known as global privileges) offer more granular control over a user's actions when working with CockroachDB, compared to the role options authorization model.
You can work with system-level privileges using the `GRANT ` statement with the `SYSTEM` parameter, and the `SHOW SYSTEM GRANTS` statement.
Roles and users can be granted the following privileges:
| Privilege | Levels | Description |
| ----------------------------------------------------------------- | ----------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `ALL` | System, Database, Schema, Table, Sequence, Type | For the object to which `ALL` is applied, grants all privileges at the system, database, schema, table, sequence, or type level. |
| `BACKUP` | System, Database, Table | Grants the ability to create backups at the system, database, or table level. |
| `CANCELQUERY` | System | Grants the ability to cancel queries. |
| `CHANGEFEED` | Table | Grants the ability to create changefeeds on a table. |
| `CONNECT` | Database | Grants the ability to view a database's metadata, which consists of objects in a database's `information_schema` and `pg_catalog` system catalogs. This allows the role to view the database's table, schemas, user-defined types, and list the database when running `SHOW DATABASES`. The `CONNECT` privilege is also required to run backups of the database. |
| `CONTROLJOB` | System | Grants the ability to pause, resume, and cancel jobs. Non-admin roles cannot control jobs created by admin roles. |
| `CREATE` | Database, Schema, Table, Sequence | Grants the ability to create objects at the database, schema, table, or sequence level. When applied at the database level, grants the ability to configure multi-region zone configs. In CockroachDB v23.2 and later, the cluster setting `sql.auth.public_schema_create_privilege.enabled` controls whether users receive `CREATE` privileges on the public schema or not. The setting applies at the time that the public schema is created, which happens whenever a database is created. The setting is `true` by default, but can be set to `false` for increased compatibility with [PostgreSQL version 15](https://www.postgresql.org/about/news/postgresql-15-released-2526/) as described in [this commit](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b073c3ccd06e4cb845e121387a43faa8c68a7b62). |
| `CREATEDB` | System | Grants the ability to create or rename a database. |
| `CREATELOGIN` | System | Grants the ability to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN`/`NOLOGIN` role options. |
| `CREATEROLE` | System | Grants the ability to create, modify, or delete non-admin roles. |
| `DELETE` | Table, Sequence | Grants the ability to delete objects at the table or sequence level. |
| `DROP` | Database, Table, Sequence | Grants the ability to drop objects at the database, table, or sequence level. |
| `EXECUTE` | Function | Grants the ability to execute functions. |
| `EXTERNALCONNECTION` | System | Grants the ability to connect to external systems such as object stores, key management systems, Kafka feeds, or external file systems. Often used in conjunction with the `BACKUP`, `RESTORE`, and `CHANGEFEED` privilege. |
| `EXTERNALIOIMPLICITACCESS` | System | Grants the ability to interact with external resources that require implicit access. |
| `INSERT` | Table, Sequence | Grants the ability to insert objects at the table or sequence level. |
| `MODIFYCLUSTERSETTING` | System | Grants the ability to modify cluster settings. |
| `MODIFYSQLCLUSTERSETTING` | System | Grants the ability to modify SQL cluster settings (cluster settings prefixed with `sql.`). |
| `NOSQLLOGIN` | System | Prevents roles from connecting to the SQL interface of a cluster. |
| `REPAIRCLUSTER`, `REPAIRCLUSTERMETADATA` | System | Grants the ability to perform cluster debugging and repair operations, including: edit scheduled jobs, edit external connections, reset SQL statistics, update zone configurations, relocate ranges, and check for constraint violations with `SCRUB`. See also: [`VIEWCLUSTERMETADATA`](#viewclustermetadata). |
| `REPLICATION` | System | Grants the ability to create a logical data replication or physical cluster replication stream. |
| `RESTORE` | System, Database | Grants the ability to restore backups at the system or database level. Refer to `RESTORE` Required privileges for more details. |
| `SELECT` | Table, Sequence | Grants the ability to run selection queries at the table or sequence level. |
| `TRIGGER` | Table | Grants the ability to create triggers on a table. |
| `UPDATE` | Table, Sequence | Grants the ability to run update statements at the table or sequence level. |
| `USAGE` | Schema, Sequence, Type | Grants the ability to use schemas, sequences, or user-defined types. |
| `VIEWACTIVITY` | System | Grants the ability to view other user's activity statistics of a cluster. |
| `VIEWACTIVITYREDACTED` | System | Grants the ability to view other user's activity statistics, but prevents the role from accessing the statement diagnostics bundle in the DB Console, and viewing some columns in introspection queries that contain data about the cluster. |
| `VIEWCLUSTERMETADATA` | System | Grants the ability to view range information, data distribution, store information, and Raft information. |
| `VIEWCLUSTERSETTING` | System | Grants the ability to view cluster settings and their values. |
| `VIEWDEBUG` | System | Grants the ability to view the Advanced Debug Page of the DB Console and work with the debugging and profiling endpoints. |
| `VIEWJOB` | System | Grants the ability to view jobs on the cluster. |
| `VIEWSYSTEMTABLE` | System | Grants read-only access (`SELECT`) on all tables in the `system` database, without granting the ability to modify the cluster. This privilege was introduced in v23.1.11. |
| `ZONECONFIG` | Database, Table, Sequence | Grants the ability to configure replication zones at the database, table, and sequence level. |
If a system-level privilege exists with the same name as a role option, the system-level privilege should be used. Some role options do not have a corresponding system-level privilege, since they configure per-user attributes. For those system-level privileges that replace legacy role options (such as `VIEWACTIVITY`), if both the system-level privilege and its legacy role option are specified for a user/role, the system-level privilege will take precedence and the legacy role option will be ignored.
### Managing privileges
Use the `GRANT` and `REVOKE` 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, that role or user is not automatically granted access to any new or existing objects within that database. To change access to those objects, see [Default privileges](#default-privileges). This does not apply to system-level privileges, which apply cluster-wide.
* 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, or cluster-wide at the system level. 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.
### Default privileges
By default, CockroachDB grants the current role/user `ALL` privileges on the objects that they create.
To view the default privileges for a role, or for a set of roles, use the `SHOW DEFAULT PRIVILEGES` statement.
To change the default privileges on objects that a user creates, use the `ALTER DEFAULT PRIVILEGES` statement.
The creator of an object is also the object's [owner](#object-ownership). Any roles that are members of the owner role have `ALL` privileges on the object, independent of the default privileges. Altering the default privileges of objects created by a role does not affect that role's privileges as the object's owner. The default privileges granted to other users/roles are always in addition to the ownership (i.e., `ALL`) privileges given to the creator of the object.
For more examples of default privileges, see the examples on the `SHOW DEFAULT PRIVILEGES` and `ALTER DEFAULT PRIVILEGES` statement pages.
## 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](#sql-users), creating and managing [roles](#roles), and creating and managing databases. Do not use the `root` user for applications; instead, create users or roles with specific [privileges](#managing-privileges) based on your application’s access requirements.
* Use the [Principle of Least Privilege (PoLP)](https://wikipedia.org/wiki/Principle_of_least_privilege) as a golden rule when to designing your system of privilege grants.
For improved performance, CockroachDB securely caches authentication information for users. To limit the authentication latency of users logging into a new session, we recommend the following best practices for `ROLE` operations (`CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`):
* Run bulk `ROLE` operations inside a transaction.
* Run regularly-scheduled `ROLE` operations together, rather than at different times throughout the day.
* Generally, if a [system-level privilege](#supported-privileges) exists with the same name as a [role option](#role-options), the system-level privilege should be used.