CREATE USER

On this page

The CREATE USER statement creates a SQL role that can be used to log in to a database.

There is no distinct "user" entity in CockroachDB. A role with the LOGIN option enabled can log in to the SQL shell and is often called a user.

You can assign privileges to the user and set it as a member of other roles, inheriting their privileges.

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

After creating roles, you can grant them privileges to databases and tables and later revoke privileges.
Roles can be members of other roles. All privileges of a role are inherited by all of its members.
Role options of a role are NOT inherited by any of its members.
All roles belong to the public role, to which you can likewise grant and revoke privileges.
There is no limit to the number of members in a role.
Membership loops are not allowed (whether 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).
On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.

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 parameter set.
To add the LOGIN capability for other roles so that they may log in as users, a role must also have the CREATELOGIN parameter 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

Parameters

Parameter	Description
`user_name`	The name of the user you want to create. See the Considerations section for important naming guidelines.
`CREATELOGIN`/`NOCREATELOGIN`	Allow or disallow the user to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN/NOLOGIN` parameters. By default, the parameter is set to `NOCREATELOGIN` for all non-admin users.
`LOGIN`/`NOLOGIN`	The `LOGIN` parameter allows a user to login with one of the client authentication methods. Setting the parameter to `NOLOGIN` prevents the user from logging in using any authentication method.
`password`	Let the user 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 user 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 user to create, alter, and drop other non-admin users. By default, the parameter is set to `NOCREATEROLE` for all non-admin users.
`CREATEDB`/`NOCREATEDB`	Allow or disallow the user to create or rename a database. The user is assigned as the owner of the database. By default, the parameter is set to `NOCREATEDB` for all non-admin users.
`CONTROLJOB`/`NOCONTROLJOB`	Allow or disallow the user to pause, resume, and cancel jobs. Non-admin users cannot control jobs created by admins. By default, the parameter is set to `NOCONTROLJOB` for all non-admin users.
`CANCELQUERY`/`NOCANCELQUERY`	Allow or disallow the user to cancel queries and sessions of other users. Without this privilege, users 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 user can view other users' query and session information. By default, the parameter is set to `NOCANCELQUERY` for all non-admin users.
`VIEWACTIVITY`/`NOVIEWACTIVITY`	Allow or disallow a role to see other users' 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 user's own data and the DB Console pages are unavailable. By default, the parameter is set to `NOVIEWACTIVITY` for all non-admin users.
`CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED`	Allow or disallow the user to run `CREATE CHANGEFEED` on tables they have `SELECT` privileges on. By default, the parameter is set to `NOCONTROLCHANGEFEED` for all non-admin users.
`MODIFYCLUSTERSETTING`/`NOMODIFYCLUSTERSETTING`	Allow or disallow the user to modify the cluster settings with the `sql.defaults` prefix. By default, the parameter is set to `NOMODIFYCLUSTERSETTING` for all non-admin users.

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 USERS;

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 user

Note the considerations for role names.

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:

Grant them privileges to databases.
For secure clusters, you must also create their client certificates.

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 create other users and manage authentication methods 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 create or rename 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 queries and sessions for other non-admin roles:

The following example allows the user to pause, resume, and cancel 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 queries and sessions 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 CREATE CHANGEFEED:

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 cluster settings:

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)

Pricing

Contact us

Sign In