To create, manage, and remove your cluster's users (which lets you control SQL-level privileges), use the cockroach user
command with appropriate flags.
CREATE USER
statement to create users.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.
- On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.
- 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.
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 |
---|---|
--certs-dir |
The path to the certificate directory. The directory must contain valid certificates if running in secure mode. Env Variable: COCKROACH_CERTS_DIR Default: ${HOME}/.cockroach-certs/ |
-d , --database |
Deprecated: Users are created for the entire cluster. However, you can control a user's privileges per database when granting them privileges. Env Variable: COCKROACH_DATABASE |
--host |
The server host to connect to. This can be the address of any node in the cluster. Env Variable: COCKROACH_HOST Default: localhost |
--insecure |
Run in insecure mode. If this flag is not set, the --certs-dir flag must point to valid certificates.Env Variable: COCKROACH_INSECURE Default: false |
--password |
Enable password authentication for the user; you will be prompted to enter the password on the command line. You cannot set a password for the root user.Find more detail about how CockroachDB handles passwords. |
-p , --port |
Connect to the cluster on the specified port. Env Variable: COCKROACH_PORT Default: 26257 |
--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 |
--url |
Connect to the cluster on the provided URL, e.g., postgresql://myuser@localhost:26257/mydb . If left blank, the connection flags are used (host , port , user , database , insecure , certs ). Env Variable: COCKROACH_URL |
-u , --user |
Deprecated: Only the root user can create users, so you cannot pass any other usernames into this flag. Env Variable: COCKROACH_USER Default: root |
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 only to users who you've created passwords for. To set a password for a user, include the
--password
flag in thecockroach user set
command. However, you cannot add password authentication to theroot
user.
You can use this password to authenticate users without supplying their client certificate and key; however, we recommend instead using client certificate and key authentication whenever possible.
root
) through the --password
flag.Examples
Create a User
Insecure Cluster
$ cockroach user set jpointsman --insecure
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.
Secure Cluster
$ cockroach user set jpointsman --certs-dir=certs
--password
flag and then enter and confirm the password at the command prompt.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:
Authenticate as a Specific User
Insecure Clusters
$ cockroach sql --insecure --user=jpointsman
Secure Clusters with Client Certificates
All users can authenticate their access to a secure cluster using a client certificate issued to their username.
$ 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.
$ cockroach sql --certs-dir=certs --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.
root
user.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
$ cockroach user rm jpointsman --insecure