| title | summary | toc |
|---|---|---|
Create & Manage Users |
To create and manage your cluster's users (which lets you control SQL-level privileges), use the cockroach user command with appropriate flags. |
true |
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}}
- 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.
- On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.
- {% include {{ page.version.version }}/misc/remove-user-callout.html %}
| 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. |
# 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 --helpThe user command and subcommands support the following general-use and logging flags.
| Flag | Description |
|---|---|
--password |
Enable password authentication for the user; you will be prompted to enter the password on the command line. |
--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 |
{% include {{ page.version.version }}/sql/connection-parameters-with-url.md %}
See Client Connection Parameters for more details.
Currently, only the root user can create users.
By default, the user command logs errors to stderr.
If you need to troubleshoot this command's behavior, you can change its logging behavior.
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-
rootusers who you've created passwords for. To set a password for a non-rootuser, include the--passwordflag in thecockroach user setcommand.Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible.
{{site.data.alerts.callout_info}}Insecure clusters do not support user authentication, but you can still create passwords for users (besides root) through the --password flag.{{site.data.alerts.end}}
$ cockroach user set jpointsman --insecureUsernames 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.
$ 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}}
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:
$ cockroach sql --insecure --user=jpointsmanAll users can authenticate their access to a secure cluster using a client certificate issued to their username.
$ cockroach sql --certs-dir=certs --user=jpointsmanUsers 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.
$ cockroach sql --certs-dir=certs --user=jpointsman$ cockroach user set jpointsman --certs-dir=certs --passwordAfter issuing this command, enter and confirm the user's new password at the command prompt.
{{site.data.alerts.callout_danger}}You cannot add password authentication to the root user.{{site.data.alerts.end}}
$ cockroach user ls --insecure+------------+
| username |
+------------+
| jpointsman |
+------------+
$ cockroach user get jpointsman --insecure+------------+--------------------------------------------------------------+
| username | hashedPassword |
+------------+--------------------------------------------------------------+
| jpointsman | $2a$108tm5lYjES9RSXSKtQFLhNO.e/ysTXCBIRe7XeTgBrR6ubXfp6dDczS |
+------------+--------------------------------------------------------------+
{{site.data.alerts.callout_danger}}{% include {{ page.version.version }}/misc/remove-user-callout.html %}{{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}}
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