The CREATE USER
statement creates SQL users, which let you control privileges on your databases and tables.
New in v20.1: Since the keywords ROLE
and USER
can now be used interchangeably in SQL statements for enhanced Postgres compatibility, CREATE USER
is now an alias for CREATE ROLE
.
Considerations
- Usernames:
- Are case-insensitive
- Must start with a letter, number, or underscore
- Must contain only letters, numbers, periods, or underscores
- 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.
Required privileges
New in v20.1: To create other users, the user must have the CREATEROLE
parameter set.
Synopsis
Parameters
Parameter | Description |
---|---|
user_name |
The name of the user you want to create. Usernames are case-insensitive; must start with a letter, number, 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 should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier. To prevent a user from using password authentication and to mandate certificate-based client authentication, set the password as NULL . |
VALID UNTIL |
New in v20.1: The date and time (in the timestamp format) after which the password is not valid. |
LOGIN /NOLOGIN |
New in v20.1: The LOGIN parameter allows a user to login with one of the user authentication methods. Setting the parameter to NOLOGIN prevents the user from logging in using any authentication method. By default, the parameter is set to LOGIN for the CREATE USER statement. |
CREATEROLE /NOCREATEROLE |
New in v20.1: Allow or disallow the new user to create, alter, and drop other users. By default, the parameter is set to NOCREATEROLE for all non-admin and non-root users. |
User authentication
Secure clusters require users to authenticate their access to databases and tables. CockroachDB offers three 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 users and roles who you've created passwords for. To create a user with a password, use the
WITH PASSWORD
clause ofCREATE USER
. To add a password to an existing user, use theALTER USER
statement.Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible.
Password creation is supported only in secure clusters.
GSSAPI authentication, which is available to Enterprise users.
Examples
Create a user
Usernames are case-insensitive; must start with a letter, number, or underscore; must contain only letters, numbers, periods, or underscores; and must be between 1 and 63 characters.
> CREATE USER carl;
After creating users, you must:
- Grant them privileges to databases.
- For secure clusters, you must also create their client certificates.
Allow the user to create other users
> CREATE USER carl with CREATEROLE;
Create a user with a password
> CREATE USER carl WITH PASSWORD 'ilov3beefjerky';
CREATE USER 1
Create a user with a password using an identifier
The following statement changes the password to ilov3beefjerky
, as above:
> CREATE USER carl WITH PASSWORD ilov3beefjerky;
This is equivalent to the example in the previous section because the password contains only lowercase characters.
In contrast, the following statement changes the password to thereisnotomorrow
, even though the password in the syntax contains capitals, because identifiers are normalized automatically:
> CREATE USER carl WITH PASSWORD ThereIsNoTomorrow;
To preserve case in a password specified using identifier syntax, use double quotes:
> CREATE USER carl WITH PASSWORD "ThereIsNoTomorrow";
Prevent a user from using password authentication
The following statement prevents the user from using password authentication and mandates certificate-based client authentication:
> CREATE USER carl WITH PASSWORD NULL;
Set password validity
The following statement sets the date and time after which the password is not valid:
> CREATE USER carl VALID UNTIL '2021-01-01';
Manage users
After creating a user, you can use the ALTER USER
statement to add or change the user's password, update role options, and the DROP USER
statement to the remove users.
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.
$ cockroach sql --user=carl
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 --user=carl
$ cockroach sql --insecure --user=carl
Set login privileges for a user
The following statement prevents the user from logging in using any user authentication method:
> CREATE USER carl NOLOGIN;
> SHOW USERS;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | NOLOGIN | {}
root | CREATEROLE | {admin}
(3 rows)
To allow the user to log in using one of the user authentication methods, use the ALTER USER
statement:
> ALTER USER carl LOGIN;
> SHOW USERS;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | | {}
root | CREATEROLE | {admin}
(3 rows)