CREATE ROLE - CockroachDB

The CREATE ROLE creates SQL , which are groups containing any number of roles and users as members. You can assign to roles, and all members of the role (regardless of whether if they are direct or indirect members) will inherit the role’s privileges. You can use the keywords ROLE and USER interchangeably. is equivalent to CREATE ROLE, with one exception: CREATE ROLE sets the NOLOGIN role option, which prevents the new role from being used to log in to the database. You can use CREATE ROLE and specify the LOGIN role option to achieve the same result as CREATE USER. The CREATE ROLE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Considerations

After creating a role, you must .
All of a role are inherited by all of its members.
Users and roles can be members of roles.
Role options of a role are not inherited by any of its members.
There is no limit to the number of members in a role.
Membership loops are not allowed (direct: A is a member of B is a member of A or indirect: A is a member of B is a member of C... is a member of A ).

Required privileges

Unless a role is a member of the admin role, additional privileges are required to manage other roles.

To create other roles, a role must have the CREATEROLE role option.
To add the LOGIN capability for other roles so that they can log in as users, a role must also have the CREATELOGIN role option.
To be able to grant or revoke membership to a role for additional roles, a member of the role must be set as a for that role.

Synopsis

Parameters

Parameter	Description
`name`	The name of the role to create.
`WITH role\_option`	Apply a role option to a role.

Role names

Are case-insensitive.
Must start with either a letter or underscore.
Must contain only letters, numbers, periods, or underscores.
Must be between 1 and 63 characters.
Cannot be none.
Cannot start with pg_ or crdb_internal. Object names with these prefixes are reserved for .
User and role names share the same namespace and must be unique.

Role options

Role option	Description
	`BYPASSRLS`/`NOBYPASSRLS`	: Allow or disallow a role to bypass policies on a table. This option controls the access from an RLS perspective only; the user also needs sufficient privileges to read or write to the table.
`CANCELQUERY`/`NOCANCELQUERY`	Deprecated in v22.2: Use the `CANCELQUERY`. Allow or disallow a role to cancel and of other roles. Without this role option, roles can only cancel their own queries and sessions. Even with the `CANCELQUERY` role option, non-`admin` roles cannot cancel `admin` queries or sessions. This option should usually be combined with `VIEWACTIVITY` so that the role can view other roles’ query and session information. By default, the role option is set to `NOCANCELQUERY` for all non-`admin` roles.
`CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED`	Deprecated in v23.1: Use the `CHANGEFEED`. Allow or disallow a role to run on tables they have `SELECT` privileges on. By default, the role option is set to `NOCONTROLCHANGEFEED` for all non-`admin` roles.
`CONTROLJOB`/`NOCONTROLJOB`	Allow or disallow a role to , , and jobs. Non-`admin` roles cannot control jobs created by `admin` roles. By default, the role option is set to `NOCONTROLJOB` for all non-`admin` roles.
`CREATEDB`/`NOCREATEDB`	Allow or disallow a role to or a database. The role is assigned as the owner of the database. By default, the role option is set to `NOCREATEDB` for all non-`admin` roles.
`CREATELOGIN`/`NOCREATELOGIN`	Allow or disallow a role to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN/NOLOGIN` role options. By default, the role option is set to `NOCREATELOGIN` for all non-`admin` roles.
`CREATEROLE`/`NOCREATEROLE`	Allow or disallow the new role to , alter, and other non-`admin` roles. By default, the role option is set to `NOCREATEROLE` for all non-`admin` roles.
`LOGIN`/`NOLOGIN`	Allow or disallow a role to log in with one of the . Setting the role option to `NOLOGIN` prevents the role from logging in using any authentication method.
`MODIFYCLUSTERSETTING`/`NOMODIFYCLUSTERSETTING`	Allow or disallow a role to modify the with the `sql.defaults` prefix. By default, the role option is set to `NOMODIFYCLUSTERSETTING` for all non-`admin` roles.
`PASSWORD password`/`PASSWORD NULL`	The credential the role uses to . A password should be entered as a . For compatibility with PostgreSQL, a password can also be entered as an identifier. To prevent a role from using and to mandate , .
`PROVISIONSRC`	A system-managed role option that identifies the authentication method and identity provider that automatically provisioned this user. The format is `{method}:{provider}` where method is `ldap`, `oidc`, or `jwt\_token`. Examples: `ldap:example.com`, `oidc:https://accounts.google.com`, `jwt\_token:https://auth.example.com`. This option is set automatically during , , or user provisioning and cannot be modified or removed. Users with this option cannot change their own passwords.
`SQLLOGIN`/`NOSQLLOGIN`	Deprecated in v22.2: Use the `NOSQLLOGIN`. Allow or disallow a role to log in using the SQL CLI with one of the . The role option to `NOSQLLOGIN` prevents the role from logging in using the SQL CLI with any authentication method while retaining the ability to log in to DB Console. It is possible to have both `NOSQLLOGIN` and `LOGIN` set for a role and `NOSQLLOGIN` takes precedence on restrictions. Without any role options all login behavior is permitted.
`VALID UNTIL`	The date and time (in the format) after which the password is not valid.
`VIEWACTIVITY`/`NOVIEWACTIVITY`	Deprecated in v22.2: Use the `VIEWACTIVITY`. Allow or disallow a role to see other roles’ and using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the and pages in the DB Console. `VIEWACTIVITY` also permits visibility of node hostnames and IP addresses in the DB Console. With `NOVIEWACTIVITY`, the `SHOW` commands show only the role’s own data, and DB Console pages redact node hostnames and IP addresses. By default, the role option is set to `NOVIEWACTIVITY` for all non-`admin` roles.
`VIEWCLUSTERSETTING` / `NOVIEWCLUSTERSETTING`	Deprecated in v22.2: Use the `VIEWCLUSTERSETTING`. Allow or disallow a role to view the with `SHOW CLUSTER SETTING` or to access the page in the DB Console. By default, the role option is set to `NOVIEWCLUSTERSETTING` for all non-`admin` roles.
`VIEWACTIVITYREDACTED`/`NOVIEWACTIVITYREDACTED`	Deprecated in v22.2: Use the `VIEWACTIVITYREDACTED`. Allow or disallow a role to see other roles’ queries and sessions using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the Statements and Transactions pages in the DB Console. With `VIEWACTIVITYREDACTED`, a user will not have access to the usage of statements diagnostics bundle (which can contain PII information) in the DB Console, and will not be able to list queries containing for other users when using the `listSessions` endpoint through the . It is possible to have both `VIEWACTIVITY` and `VIEWACTIVITYREDACTED`, and `VIEWACTIVITYREDACTED` takes precedence on restrictions. If the user has `VIEWACTIVITY` but doesn’t have `VIEWACTIVITYREDACTED`, they will be able to see DB Console pages and have access to the statements diagnostics bundle. By default, the role option is set to `NOVIEWACTIVITYREDACTED` for all non-`admin` roles.

Examples

To run the following examples, and use the built-in SQL shell:

$ cockroach sql --certs-dir=certs

> SHOW ROLES;

username | options | member_of | estimated_last_login_time
---------+---------+-----------+------------------------------
admin    | {}      | {}        | NULL
root     | {}      | {admin}   | NULL
(2 rows)

The following statements are run by the root user that is a member of the admin role and has ALL privileges.

Create a role

Role names are case-insensitive; must start with a letter, number, or underscore; must contain only letters, numbers, periods, or underscores; and must be between 1 and 63 characters.

CREATE ROLE no_options;

SHOW ROLES;

username   | options   | member_of | estimated_last_login_time
-----------+-----------+-----------+------------------------------
admin      | {}        | {}        | NULL
no_options | {NOLOGIN} | {}        | NULL
root       | {}        | {admin}   | NULL
(3 rows)

After creating roles, you must .

Create a role that can log in to the database

CREATE ROLE can_login WITH LOGIN PASSWORD '$tr0nGpassW0rD' VALID UNTIL '2021-10-10';

SHOW ROLES;

 username  |                     options                     | member_of | estimated_last_login_time
-----------+-------------------------------------------------+-----------+------------------------------
admin      | {}                                              | {}        | NULL
can_login  | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
no_options | {NOLOGIN}                                       | {}        | NULL
root       | {}                                              | {admin}   | NULL
(4 rows)

Prevent a role from using password authentication

The following statement prevents the role from using password authentication and mandates certificate-based client authentication:

CREATE ROLE no_password WITH PASSWORD NULL;

SHOW ROLES;

 username  |                     options                     | member_of | estimated_last_login_time
-----------+-------------------------------------------------+-----------+------------------------------
admin      | {}                                              | {}        | NULL
can_login  | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
no_options | {NOLOGIN}                                       | {}        | NULL
no_password| {NOLOGIN}                                       | {}        | NULL
root       | {}                                              | {admin}   | NULL
(5 rows)

Create a role that can create other roles and manage authentication methods for the new roles

The following example allows the role to and for them:

CREATE ROLE can_create_role WITH CREATEROLE CREATELOGIN;

SHOW ROLES;

   username     |                     options                     | member_of | estimated_last_login_time
----------------+-------------------------------------------------+-----------+------------------------------
admin           | {}                                              | {}        | NULL
can_create_role | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login       | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
no_options      | {NOLOGIN}                                       | {}        | NULL
no_password     | {NOLOGIN}                                       | {}        | NULL
root            | {}                                              | {admin}   | NULL
(6 rows)

Create a role that can create and rename databases

The following example allows the role to or databases:

CREATE ROLE can_create_db WITH CREATEDB;

SHOW ROLES;

      username        |                     options                     | member_of | estimated_last_login_time
----------------------+-------------------------------------------------+-----------+------------------------------
admin                 | {}                                              | {}        | NULL
can_create_db         | {CREATEDB,NOLOGIN}                              | {}        | NULL
can_create_role       | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login             | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
no_options            | {NOLOGIN}                                       | {}        | NULL
no_password           | {NOLOGIN}                                       | {}        | NULL
root                  | {}                                              | {admin}   | NULL
(7 rows)

Create a role that can pause, resume, and cancel non-admin jobs

The following example allows the role to , , and jobs:

CREATE ROLE can_control_job WITH CONTROLJOB;

SHOW ROLES;

      username        |                     options                     | member_of | estimated_last_login_time
----------------------+-------------------------------------------------+-----------+------------------------------
admin                 | {}                                              | {}        | NULL
can_control_job       | {CONTROLJOB,NOLOGIN}                            | {}        | NULL
can_create_db         | {CREATEDB,NOLOGIN}                              | {}        | NULL
can_create_role       | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login             | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
manage_auth_for_roles | {CREATELOGIN,NOLOGIN}                           | {}        | NULL
no_options            | {NOLOGIN}                                       | {}        | NULL
no_password           | {NOLOGIN}                                       | {}        | NULL
root                  | {}                                              | {admin}   | NULL
(8 rows)

Create a role that can see and cancel non-admin queries and sessions

The following example allows the role to cancel and for other non-admin roles:

CREATE ROLE can_manage_queries WITH CANCELQUERY VIEWACTIVITY;

SHOW ROLES;

      username        |                     options                     | member_of | estimated_last_login_time
----------------------+-------------------------------------------------+-----------+------------------------------
admin                 | {}                                              | {}        | NULL
can_control_job       | {CONTROLJOB,NOLOGIN}                            | {}        | NULL
can_create_db         | {CREATEDB,NOLOGIN}                              | {}        | NULL
can_create_role       | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login             | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
can_manage_queries    | {CANCELQUERY,NOLOGIN,VIEWACTIVITY}              | {}        | NULL
no_options            | {NOLOGIN}                                       | {}        | NULL
no_password           | {NOLOGIN}                                       | {}        | NULL
root                  | {}                                              | {admin}   | NULL
(9 rows)

Create a role that can control changefeeds

The following example allows the role to run :

CREATE ROLE can_control_changefeed WITH CONTROLCHANGEFEED;

SHOW ROLES;

       username        |                     options                     | member_of | estimated_last_login_time
-----------------------+-------------------------------------------------+-----------+------------------------------
admin                  | {}                                              | {}        | NULL
can_control_changefeed | {CONTROLCHANGEFEED,NOLOGIN}                     | {}        | NULL
can_control_job        | {CONTROLJOB,NOLOGIN}                            | {}        | NULL
can_create_db          | {CREATEDB,NOLOGIN}                              | {}        | NULL
can_create_role        | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login              | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
can_manage_queries     | {CANCELQUERY,NOLOGIN,VIEWACTIVITY}              | {}        | NULL
no_options             | {NOLOGIN}                                       | {}        | NULL
no_password            | {NOLOGIN}                                       | {}        | NULL
root                   | {}                                              | {admin}   | NULL
(10 rows)

Create a role that can modify cluster settings

The following example allows the role to modify :

CREATE ROLE can_modify_cluster_setting WITH MODIFYCLUSTERSETTING;

SHOW ROLES;

         username          |                     options                     | member_of | estimated_last_login_time
---------------------------+-------------------------------------------------+-----------+------------------------------
admin                      | {}                                              | {}        | NULL
can_control_changefeed     | {CONTROLCHANGEFEED,NOLOGIN}                     | {}        | NULL
can_control_job            | {CONTROLJOB,NOLOGIN}                            | {}        | NULL
can_create_db              | {CREATEDB,NOLOGIN}                              | {}        | NULL
can_create_role            | {CREATELOGIN,CREATEROLE,NOLOGIN}                | {}        | NULL
can_login                  | {VALID UNTIL=2025-10-10 00:00:00+00:00}         | {}        | NULL
can_manage_queries         | {CANCELQUERY,NOLOGIN,VIEWACTIVITY}              | {}        | NULL
can_modify_cluster_setting | {MODIFYCLUSTERSETTING,NOLOGIN}                  | {}        | NULL
no_options                 | {NOLOGIN}                                       | {}        | NULL
no_password                | {NOLOGIN}                                       | {}        | NULL
root                       | {}                                              | {admin}   | NULL
(11 rows)

Create a role that can bypass row-level security (RLS)

To create a that can bypass , execute the following statement to grant the privilege:

CREATE ROLE can_bypassrls WITH BYPASSRLS;

For instructions showing how to alter a role to add or remove the BYPASSRLS privilege, refer to .

Set the `SUBJECT` role option for certificate based authentication

You can associate an X.509 certificate’s Subject with a as shown below. Note that the Subject fields in the certificate have to be an exact match with what you pass in via the SQL statement. By exact match, we mean that the order of attributes passed in via the SQL statement must match the order of attributes in the certificate.

CREATE ROLE maxroach WITH SUBJECT 'CN=myName,OU=myOrgUnit,O=myOrg,L=myLocality,ST=myState,C=myCountry' LOGIN;

If you manage your own Certificate Authority (CA) infrastructure, CockroachDB supports mapping between the Subject field of your X.509 certificates and SQL . For more information, see .

​Considerations

​Required privileges

​Synopsis

​Parameters

​Role names

​Role options

​Examples

​Create a role

​Create a role that can log in to the database

​Prevent a role from using password authentication

​Create a role that can create other roles and manage authentication methods for the new roles

​Create a role that can create and rename databases

​Create a role that can pause, resume, and cancel non-admin jobs

​Create a role that can see and cancel non-admin queries and sessions

​Create a role that can control changefeeds

​Create a role that can modify cluster settings

​Create a role that can bypass row-level security (RLS)

​Set the SUBJECT role option for certificate based authentication

​See also

Considerations

Required privileges

Synopsis

Parameters

Role names

Role options

Examples

Create a role

Create a role that can log in to the database

Prevent a role from using password authentication

Create a role that can create other roles and manage authentication methods for the new roles

Create a role that can create and rename databases

Create a role that can pause, resume, and cancel non-admin jobs

Create a role that can see and cancel non-admin queries and sessions

Create a role that can control changefeeds

Create a role that can modify cluster settings

Create a role that can bypass row-level security (RLS)

Set the `SUBJECT` role option for certificate based authentication

See also