Manage Users

To create, manage, and remove your cluster's users (which lets you control SQL-level privileges, use the cockroach user command with appropriate flags.

{{site.data.alerts.callout_success}}You can also use the CREATE USER and DROP USER statements to create and remove users.{{site.data.alerts.end}}

Considerations

Usernames are case-insensitive; must start with either a letter or underscore; must contain only letters, numbers, or underscores; and must be between 1 and 63 characters.
After creating users, you must grant them privileges to databases and tables.
All users belong to the public role, to which you can grant and revoke privileges.
On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.

Subcommands

Subcommand	Usage
`get`	Retrieve a table containing a user and their hashed password.
`ls`	List all users.
`rm`	Remove a user.
`set`	Create or update a user.

Synopsis

# Create a user:
$ cockroach user set <username> <flags>

# List all users:
$ cockroach user ls <flags>

# Display a specific user:
$ cockroach user get <username> <flags>

# View help:
$ cockroach user --help
$ cockroach user get --help
$ cockroach user ls --help
$ cockroach user rm --help
$ cockroach user set --help

Flags

The user command and subcommands support the following general-use and logging flags.

General

Flag	Description
`--password`	Enable password authentication for the user; you will be prompted to enter the password on the command line. Password creation is supported only in secure clusters for non-`root` users. The `root` user must authenticate with a client certificate and key.
`--echo-sql`	Reveal the SQL statements sent implicitly by the command-line utility. For a demonstration, see the example below.
`--format`	How to display table rows printed to the standard output. Possible values: `tsv`, `csv`, `table`, `raw`, `records`, `sql`, `html`. Default: `table` for sessions that output on a terminal; `tsv` otherwise.

Client connection

Flag	Description
`--host`	The server host and port number to connect to. This can be the address of any node in the cluster. Env Variable: `COCKROACH_HOST` Default: `localhost:26257`
`--port` `-p`	The server port to connect to. Note: The port number can also be specified via `--host`. Env Variable: `COCKROACH_PORT` Default: `26257`
`--user` `-u`	The SQL user that will own the client session. Env Variable: `COCKROACH_USER` Default: `root`
`--insecure`	Use an insecure connection. Env Variable: `COCKROACH_INSECURE` Default: `false`
`--certs-dir`	The path to the certificate directory containing the CA and client certificates and client key. Env Variable: `COCKROACH_CERTS_DIR` Default: `${HOME}/.cockroach-certs/`
`--url`	A connection URL to use instead of the other arguments. Env Variable: `COCKROACH_URL` Default: no URL

See Client Connection Parameters for more details.

Currently, only members of the admin role can create users. By default, the root user belongs to the admin role.

{{site.data.alerts.callout_info}} Password creation is supported only in secure clusters for non-root users. The root user must authenticate with a client certificate and key. {{site.data.alerts.end}}

Logging

By default, the user command logs errors to stderr.

If you need to troubleshoot this command's behavior, you can change its logging behavior.

Examples

Create a user

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

{% include copy-clipboard.html %} ~~~ shell $ cockroach user set jpointsman --certs-dir=certs ~~~ {{site.data.alerts.callout_success}}If you want to allow password authentication for the user, include the --password flag and then enter and confirm the password at the command prompt.{{site.data.alerts.end}} After creating users, you must: - [Create their client certificates](create-security-certificates.html#create-the-certificate-and-key-pair-for-a-client). - [Grant them privileges to databases](grant.html).

{% include copy-clipboard.html %} ~~~ shell $ cockroach user set jpointsman --insecure ~~~ After creating users, you must [grant them privileges to databases](grant.html).

Log in as a specific user

#### Secure clusters with client certificates All users can authenticate their access to a secure cluster using [a client certificate](create-security-certificates.html#create-the-certificate-and-key-pair-for-a-client) issued to their username. {% include copy-clipboard.html %} ~~~ shell $ cockroach sql --certs-dir=certs --user=jpointsman ~~~ #### Secure clusters with passwords Users with passwords can authenticate their access by entering their password at the command prompt instead of using their client certificate and key. If we cannot find client certificate and key files matching the user, we fall back on password authentication. {% include copy-clipboard.html %} ~~~ shell $ cockroach sql --certs-dir=certs --user=jpointsman ~~~

{% include copy-clipboard.html %} ~~~ shell $ cockroach sql --insecure --user=jpointsman ~~~

Update a user's password

$ cockroach user set jpointsman --certs-dir=certs --password

After issuing this command, enter and confirm the user's new password at the command prompt.

Password creation is supported only in secure clusters for non-root users. The root user must authenticate with a client certificate and key.

List all users

$ cockroach user ls --insecure

+------------+
|  username  |
+------------+
| jpointsman |
+------------+

Find a specific user

$ cockroach user get jpointsman --insecure

+------------+--------------------------------------------------------------+
|  username  |                        hashedPassword                        |
+------------+--------------------------------------------------------------+
| jpointsman | $2a$108tm5lYjES9RSXSKtQFLhNO.e/ysTXCBIRe7XeTgBrR6ubXfp6dDczS |
+------------+--------------------------------------------------------------+

Remove a user

{{site.data.alerts.callout_danger}}Removing a user does not remove that user's privileges. Therefore, to prevent a future user with an identical username from inheriting an old user's privileges, it's important to revoke a user's privileges before or after removing the user. {{site.data.alerts.end}}

$ cockroach user rm jpointsman --insecure

{{site.data.alerts.callout_success}}You can also use the DROP USER SQL statement to remove users.{{site.data.alerts.end}}

Reveal the SQL statements sent implicitly by the command-line utility

In this example, we use the --echo-sql flag to reveal the SQL statement sent implicitly by the command-line utility:

$ cockroach user rm jpointsman --insecure --echo-sql

> DELETE FROM system.users WHERE username=$1
DELETE 1