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, tables, clusters, schemas, rows, users, jobs, etc.).
CockroachDB has a unified authorization model, meaning that a given user's permissions are governed by the same policies in different contexts such as accessing the SQL shell or viewing data from the DB Console.
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.
- 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
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
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.
A SQL user can interact with a CockroachDB database using the built-in SQL shell or through an application.
Create and manage users
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
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
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.
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.
public roles exist by default.
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.
To assign a user to the
> GRANT admin TO <username>;
All new users and roles belong to the
public role by default. You can grant and revoke the privileges on the
Role admin is a role option 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.
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
A user or role that is an immediate member of the role.
Example: A is a member of B.
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.
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 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.
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.
Roles and users can be granted the following privileges:
||Database, Schema, Table, Type|
||Database, Schema, Table|
||Database, Schema, Table, Type|
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 databaseNote:
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.
rootuser automatically belongs to the
adminrole and has the
ALLprivilege for new databases.
For privileges required by specific statements, see the documentation for the respective SQL statement.
New in v21.2:
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. 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.
Users' authorization to perform certain actions are governed not by grants but by
role options. These options govern whether users can perform actions such as:
- Viewing or canceling ongoing queries and sessions owned by other roles.
- Pausing, resuming, and canceling jobs.
- Creating or renaming databases.
- Managing authentication for other users.
- Modifying cluster settings.
- Creating changefeeds.
Authorization best practices
We recommend the following best practices to set up access control for your clusters:
- Use the
rootuser 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
rootuser for applications; instead, create users or roles with specific privileges based on your application’s access requirements.
- Use the Principle of Least Privilege (PoLP) as a golden rule when to designing your system of privilege grants.
New in v21.2:
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 (
- Run bulk
ROLEoperations inside a transaction.
- Run regularly-scheduled
ROLEoperations together, rather than at different times throughout the day.