CREATE ROLE

On this page Carat arrow pointing down

The CREATE ROLE statement creates a new SQL role. A role acts as a database user or a group of member roles/users. There is no distinct "user" entity in CockroachDB. A role with the LOGIN option enabled can log in to the database and is often called a user.

You can assign privileges to the role and set other roles as members of the role. A role's privileges are inherited by its members and any further members of members (indirect members).

Note:

CREATE USER is equivalent to the statement CREATE ROLE, with one exception: CREATE ROLE sets the NOLOGIN option by default, preventing the new role from being used to log in to the database. You can use CREATE ROLE and specify the LOGIN option to achieve the same result as CREATE USER.

See Authorization for more information on privilege management and role membership.

Considerations

Role name limitations

The following requirements apply to all role names (also known as usernames).

  • Role names are case-insensitive and must be unique.
  • When surrounded by quotes in SQL statements (always recommended)—single or double quotes, depending on the statement—role names:
    • Can contain letters, underscores, digits, periods, and dashes. Letters include a-z, those with diacritical marks, and non-Latin letters.
    • Can begin with a letter, underscore, or digit.
  • When referenced in SQL without quotes, role names:
    • Cannot contain periods or dashes.
    • Cannot begin with a digit.
    • Cannot match the name of a SQL keyword.
  • Role names cannot exceed 63 bytes. This limits them to 63 characters when all are ASCII characters and to fewer characters when a broader character set is used.

Role membership and privileges

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 set.
  • To add the LOGIN capability for other roles so that they may log in as users, a role must also have the CREATELOGIN role option set.
  • To be able to grant or revoke membership to a role for additional roles, a member of the role must be set as a role admin for that role.

Synopsis

CREATE ROLE IF NOT EXISTS name

Parameters

Parameter Description
name The name of the role you want to create. See the Considerations section for important naming guidelines.
CREATELOGIN/NOCREATELOGIN Allow or disallow the 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.
LOGIN/NOLOGIN The LOGIN role option allows a role to login with one of the client authentication methods. Setting the role option to NOLOGIN prevents the role from logging in using any authentication method. NOLOGIN is set by default when using CREATE ROLE, but not when using the otherwise equivalent CREATE USER statement.
password Let the role authenticate their access to a secure cluster using this password. Passwords should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier.

To prevent a role from using password authentication and to mandate certificate-based client authentication, set the password as NULL.
VALID UNTIL The date and time (in the timestamp format) after which the password is not valid.
CREATEROLE/NOCREATEROLE Allow or disallow the new role to create, alter, and drop other non-admin roles.

By default, the role option is set to NOCREATEROLE for all non-admin users.
CREATEDB/NOCREATEDB Allow or disallow the role to create or rename 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.
CONTROLJOB/NOCONTROLJOB Allow or disallow the role to pause, resume, and cancel jobs. Non-admin roles cannot control jobs created by admins.

By default, the role option is set to NOCONTROLJOB for all non-admin roles.
CANCELQUERY/NOCANCELQUERY Allow or disallow the role to cancel queries and sessions of other roles. Without this privilege, roles can only cancel their own queries and sessions. Even with this privilege, non-admins 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.
VIEWACTIVITY/NOVIEWACTIVITY Allow or disallow a role to see other roles' queries and sessions using SHOW QUERIES, SHOW SESSIONS, and the Statements and Transactions pages in the DB Console. Without this privilege, the SHOW commands only show the role's own data and the DB Console pages are unavailable.

By default, the role option is set to NOVIEWACTIVITY for all non-admin roles.
CONTROLCHANGEFEED/NOCONTROLCHANGEFEED Allow or disallow the role to run CREATE CHANGEFEED on tables they have SELECT privileges on.

By default, the role option is set to NOCONTROLCHANGEFEED for all non-admin roles.
MODIFYCLUSTERSETTING/NOMODIFYCLUSTERSETTING Allow or disallow the role to modify the cluster settings with the sql.defaults prefix.

By default, the role option is set to NOMODIFYCLUSTERSETTING for all non-admin roles.

Examples

To run the following examples, start a secure single-node cluster and use the built-in SQL shell:

$ cockroach sql --certs-dir=certs
> SHOW ROLES;
username | options | member_of
---------+---------+------------
admin    |         | {}
root     |         | {admin}
(2 rows)
Note:

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

Create a role

Note the considerations for role names.

root@:26257/defaultdb> CREATE ROLE no_options;
root@:26257/defaultdb> SHOW ROLES;
 username  | options | member_of
 ----------+---------+------------
admin      |         | {}
no_options | NOLOGIN | {}
root       |         | {admin}
(3 rows)

After creating roles, you must grant them privileges to databases.

Create a role that can log in to the database

root@:26257/defaultdb> CREATE ROLE can_login WITH LOGIN PASSWORD '$tr0nGpassW0rD' VALID UNTIL '2021-10-10';
root@:26257/defaultdb> SHOW ROLES;
 username  |                options                | member_of
-----------+---------------------------------------+------------
admin      |                                       | {}
can_login  | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN                               | {}
root       |                                       | {admin}
(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:

icon/buttons/copy
> CREATE ROLE no_password WITH PASSWORD NULL;
root@:26257/defaultdb> SHOW ROLES;
 username  |                options                | member_of
-----------+---------------------------------------+------------
admin      |                                       | {}
can_login  | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN                               | {}
no_password| NOLOGIN                               | {}
root       |                                       | {admin}
(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 create other users and manage authentication methods for them:

root@:26257/defaultdb> CREATE ROLE can_create_role WITH CREATEROLE CREATELOGIN;
root@:26257/defaultdb> SHOW ROLES;
   username     |                options                | member_of
----------------+---------------------------------------+------------
admin           |                                       | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login       | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options      | NOLOGIN                               | {}
no_password     | NOLOGIN                               | {}
root            |                                       | {admin}
(6 rows)

Create a role that can create and rename databases

The following example allows the role to create or rename databases:

root@:26257/defaultdb> CREATE ROLE can_create_db WITH CREATEDB;
root@:26257/defaultdb> SHOW ROLES;
      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_create_db         | CREATEDB, NOLOGIN                     | {}
can_create_role       | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login             | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options            | NOLOGIN                               | {}
no_password           | NOLOGIN                               | {}
root                  |                                       | {admin}
(7 rows)

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

The following example allows the role to pause, resume, and cancel jobs:

root@:26257/defaultdb> CREATE ROLE can_control_job WITH CONTROLJOB;
root@:26257/defaultdb> SHOW ROLES;
      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB, NOLOGIN                   | {}
can_create_db         | CREATEDB, NOLOGIN                     | {}
can_create_role       | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login             | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
manage_auth_for_roles | CREATELOGIN, NOLOGIN                  | {}
no_options            | NOLOGIN                               | {}
no_password           | NOLOGIN                               | {}
root                  |                                       | {admin}
(8 rows)

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

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

root@:26257/defaultdb> CREATE ROLE can_manage_queries WITH CANCELQUERY VIEWACTIVITY;
root@:26257/defaultdb> SHOW ROLES;
      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB, NOLOGIN                   | {}
can_create_db         | CREATEDB, NOLOGIN                     | {}
can_create_role       | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login             | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries    | CANCELQUERY, NOLOGIN, VIEWACTIVITY    | {}
no_options            | NOLOGIN                               | {}
no_password           | NOLOGIN                               | {}
root                  |                                       | {admin}
(9 rows)

Create a role that can control changefeeds

The following example allows the role to run CREATE CHANGEFEED:

root@:26257/defaultdb> CREATE ROLE can_control_changefeed WITH CONTROLCHANGEFEED;
root@:26257/defaultdb> SHOW ROLES;
       username        |                options                | member_of
-----------------------+---------------------------------------+------------
admin                  |                                       | {}
can_control_changefeed | CONTROLCHANGEFEED, NOLOGIN            | {}
can_control_job        | CONTROLJOB, NOLOGIN                   | {}
can_create_db          | CREATEDB, NOLOGIN                     | {}
can_create_role        | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login              | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries     | CANCELQUERY, NOLOGIN, VIEWACTIVITY    | {}
no_options             | NOLOGIN                               | {}
no_password            | NOLOGIN                               | {}
root                   |                                       | {admin}
(10 rows)

Create a role that can modify cluster settings

The following example allows the role to modify cluster settings:

root@:26257/defaultdb> CREATE ROLE can_modify_cluster_setting WITH MODIFYCLUSTERSETTING;
root@:26257/defaultdb> SHOW ROLES;
         username          |                options                | member_of
---------------------------+---------------------------------------+------------
admin                      |                                       | {}
can_control_changefeed     | CONTROLCHANGEFEED, NOLOGIN            | {}
can_control_job            | CONTROLJOB, NOLOGIN                   | {}
can_create_db              | CREATEDB, NOLOGIN                     | {}
can_create_role            | CREATELOGIN, CREATEROLE, NOLOGIN      | {}
can_login                  | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries         | CANCELQUERY, NOLOGIN, VIEWACTIVITY    | {}
can_modify_cluster_setting | MODIFYCLUSTERSETTING, NOLOGIN         | {}
no_options                 | NOLOGIN                               | {}
no_password                | NOLOGIN                               | {}
root                       |                                       | {admin}
(11 rows)

See also


Yes No
On this page

Yes No