The CREATE USER statement creates SQL users, which let you control privileges on your databases and tables.

When creating users, it’s important to note:

You can also create and manage users through the cockroach user command. Notably, this is the most efficient way to remove users.

Required Privileges

The user must have the INSERT and UPDATE privileges on the system.users table.

Synopsis

CREATE USER name WITH PASSWORD password

Parameters

Parameter Description
name The name of the user you want to create.

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.
password Let the user authenticate their access to a secure cluster using this password. Passwords must be entered as string values surrounded by single quotes (').

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 create a user with a password, use the WITH PASSWORD clause of CREATE 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.

    To add a password to an existing user, use the cockroach user command.
Insecure clusters do not support user authentication, but you can still create passwords for users (besides root) through the WITH PASSWORD clause.

Examples

Create a User

> CREATE USER jpointsman;

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.

For users on secure clusters, you also need to generate client certificates and keys to authenticate the user’s access to the cluster.

Create a User with Password Authentication

> CREATE USER jpointsman WITH PASSWORD 'Q7gc8rEdS';
We strongly recommend also creating client certificates and keys and using them to authenticate the user's access to the cluster.

Manage Users

After creating users, you can manage them using the cockroach user command.

Authenticate as a Specific User

Insecure Clusters

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

$ cockroach sql --user=jpointsman

After issuing this command, you must enter the password for jpointsman twice.

See Also



Yes No