Manage Users

On this page Carat arrow pointing down
Warning:
CockroachDB v2.0 is no longer supported as of October 4, 2019. For more details, refer to the Release Support Policy.

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

Tip:
You can also use the CREATE USER and DROP USER statements to create and remove users.

Considerations

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.

Changed in v2.0: 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 New in v1.1: Reveal the SQL statements sent implicitly by the command-line utility. For a demonstration, see the example below.
--pretty Format table rows printed to the standard output using ASCII art and disable escaping of special characters.

When disabled with --pretty=false, or when the standard output is not a terminal, table rows are printed as tab-separated values, and special characters are escaped. This makes the output easy to parse by other programs.

Default: true when output is a terminal, false otherwise

Client Connection

Flag Description
--host The server host to connect to. This can be the address of any node in the cluster.

Env Variable: COCKROACH_HOST
Default:localhost
--port
-p
The server port to connect to.

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 the root user can create users.

Note:

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

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.

User Authentication

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

  • Client certificate and key authentication, 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 non-root users who you've created passwords for. To set a password for a non-root user, include the --password flag in the cockroach user set command.

    Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible.

    Changed in v2.0: Password creation is supported only in secure clusters.

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.

icon/buttons/copy

$ cockroach user set jpointsman --certs-dir=certs
Tip:
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.

After creating users, you must:

icon/buttons/copy

$ cockroach user set jpointsman --insecure

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

Authenticate as a Specific User

Secure Clusters with Client Certificates

All users can authenticate their access to a secure cluster using a client certificate issued to their username.

icon/buttons/copy

$ 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.

icon/buttons/copy
$ cockroach sql --certs-dir=certs --user=jpointsman

icon/buttons/copy

$ cockroach sql --insecure --user=jpointsman

Update a User's Password

icon/buttons/copy
$ cockroach user set jpointsman --certs-dir=certs --password

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

Changed in v2.0: 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

icon/buttons/copy
$ cockroach user ls --insecure
+------------+
|  username  |
+------------+
| jpointsman |
+------------+

Find a Specific User

icon/buttons/copy
$ cockroach user get jpointsman --insecure
+------------+--------------------------------------------------------------+
|  username  |                        hashedPassword                        |
+------------+--------------------------------------------------------------+
| jpointsman | $2a$108tm5lYjES9RSXSKtQFLhNO.e/ysTXCBIRe7XeTgBrR6ubXfp6dDczS |
+------------+--------------------------------------------------------------+

Remove a User

Warning:
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.
icon/buttons/copy
$ cockroach user rm jpointsman --insecure
Tip:
You can also use the DROP USER SQL statement to remove users.

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:

icon/buttons/copy
$ cockroach user rm jpointsman --insecure --echo-sql
> DELETE FROM system.users WHERE username=$1
DELETE 1

See Also


Yes No
On this page

Yes No