CREATE USER - CockroachDB

The CREATE USER creates SQL users, which let you control on your databases and tables. You can use the keywords ROLE and USER interchangeably. CREATE USER is equivalent to , 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 USER statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Considerations

After creating users, you must .
All users belong to the public role, to which you can and privileges.
On secure clusters, you must and users must authenticate their access to the cluster.

Required privileges

To create other users, the user must be a member of the admin role or have the CREATEROLE parameter set.

Synopsis

See .

Parameters

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

User names

Are case-insensitive.
Must start with 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
`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 , .
`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.

User authentication

Secure clusters require users to authenticate their access to databases and tables. CockroachDB offers three methods for this:

, which is available to all users. To ensure the highest level of security, we recommend only using client certificate and key authentication.
Password authentication, which is available to users and roles who you’ve created passwords for. To create a user with a password, use the WITH PASSWORD clause of CREATE USER. To add a password to an existing user, use the statement. Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible. Password creation is supported only in secure clusters.
.

Examples

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

$ cockroach sql --certs-dir=certs

> SHOW USERS;

username | options | member_of
---------+---------+------------
admin    |         | {}
root     |         | {admin}
(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 user

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

root@:26257/defaultdb> CREATE USER no_options;

root@:26257/defaultdb> SHOW USERS;

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

After creating users, you must:

.
For secure clusters, you must also .

Create a user with a password

root@:26257/defaultdb> CREATE USER with_password WITH LOGIN PASSWORD '$tr0nGpassW0rD' VALID UNTIL '2021-10-10';

root@:26257/defaultdb> SHOW USERS;

  username    |                options                | member_of
--------------+---------------------------------------+------------
admin         |                                       | {}
no_options    |                                       | {}
root          |                                       | {admin}
with_password | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(4 rows)

Prevent a user from using password authentication

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

root@:26257/defaultdb> CREATE USER no_password WITH PASSWORD NULL;

root@:26257/defaultdb> SHOW USERS;

  username    |                options                | member_of
--------------+---------------------------------------+------------
admin         |                                       | {}
no_options    |                                       | {}
no_password   |                                       | {}
root          |                                       | {admin}
with_password | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(5 rows)

Create a user that can create other users and manage authentication methods for the new users

The following example allows the user to and for them:

root@:26257/defaultdb> CREATE USER can_create_users WITH CREATEROLE CREATELOGIN;

root@:26257/defaultdb> SHOW USERS;

    username     |                options                | member_of
-----------------+---------------------------------------+------------
admin            |                                       | {}
can_create_users | CREATELOGIN, CREATEROLE               | {}
no_options       |                                       | {}
no_password      |                                       | {}
root             |                                       | {admin}
with_password    | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(6 rows)

Create a user that can create and rename databases

The following example allows the user to or databases:

root@:26257/defaultdb> CREATE USER can_create_db WITH CREATEDB;

root@:26257/defaultdb> SHOW USERS;

      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(7 rows)

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

The following example allows the user to cancel and for other non-admin roles: The following example allows the user to , , and jobs:

root@:26257/defaultdb> CREATE USER can_control_job WITH CONTROLJOB;

root@:26257/defaultdb> SHOW USERS;

      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB                            | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(8 rows)

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

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

root@:26257/defaultdb> CREATE USER can_manage_queries WITH CANCELQUERY VIEWACTIVITY;

root@:26257/defaultdb> SHOW USERS;

      username        |                options                | member_of
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB                            | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
can_manage_queries    | CANCELQUERY, VIEWACTIVITY             | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(9 rows)

Create a user that can control changefeeds

The following example allows the user to run :

root@:26257/defaultdb> CREATE USER can_control_changefeed WITH CONTROLCHANGEFEED;

root@:26257/defaultdb> SHOW USERS;

       username        |                options                | member_of
-----------------------+---------------------------------------+------------
admin                  |                                       | {}
can_control_changefeed | CONTROLCHANGEFEED                     | {}
can_control_job        | CONTROLJOB                            | {}
can_create_db          | CREATEDB                              | {}
can_create_users       | CREATELOGIN, CREATEROLE               | {}
can_manage_queries     | CANCELQUERY, VIEWACTIVITY             | {}
no_options             |                                       | {}
no_password            |                                       | {}
root                   |                                       | {admin}
with_password          | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(10 rows)

Create a user that can modify cluster settings

The following example allows the user to modify :

root@:26257/defaultdb> CREATE USER can_modify_cluster_setting WITH MODIFYCLUSTERSETTING;

root@:26257/defaultdb> SHOW USERS;

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

​Considerations

​Required privileges

​Synopsis

​Parameters

​User names

​Role options

​User authentication

​Examples

​Create a user

​Create a user with a password

​Prevent a user from using password authentication

​Create a user that can create other users and manage authentication methods for the new users

​Create a user that can create and rename databases

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

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

​Create a user that can control changefeeds

​Create a user that can modify cluster settings

​See also

Considerations

Required privileges

Synopsis

Parameters

User names

Role options

User authentication

Examples

Create a user

Create a user with a password

Prevent a user from using password authentication

Create a user that can create other users and manage authentication methods for the new users

Create a user that can create and rename databases

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

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

Create a user that can control changefeeds

Create a user that can modify cluster settings

See also