User Authorization

CockroachDB Cloud requires you to create SQL users to access the cluster.

By default, a new SQL user created using a Console Admin is assigned to the admin role. An admin SQL user has full privileges for all databases and tables in your cluster. This user can also create additional users and grant them appropriate privileges.

Create a SQL user

Note:

Only Console Admins can create SQL users. If you are a Developer, you need to ask your Console Admin for the credentials of a SQL user to access the cluster. To find out who your Console Admin is, log in and navigate to Cluster Overview > Access.

Once you are logged in, you can use the Console to create a new user:

  1. Navigate to your cluster's SQL Users page.
  2. Click the Add User button in the top right corner.

    The Add User modal displays.

  3. Enter a Username.

  4. Enter and confirm the Password.

  5. Click Create.

    Currently, all new users are created with full privileges. For more information and to change the default settings, see Grant privileges and Use roles.

Once you have connected to the cluster's SQL client, you can create a new user.

To create a new user, use the CREATE USER ... WITH PASSWORD statement:

icon/buttons/copy
> CREATE USER <username> WITH PASSWORD '<password>';
Note:

Be sure to create a password for each new user. Without a password, a user cannot connect to the cluster or access the DB Console. To add or change a password for a user, use the ALTER USER statement.

Manage SQL users

View all users in your cluster

To view a list of all of the users in your cluster, navigate to the SQL Users page.

SQL Users page

On the SQL Users page, you can do the following:

To list all the users in your cluster, use the SHOW USERS statement:

icon/buttons/copy
> SHOW USERS;

Change a user's password

Note:

Only Console Admins can change a user's password. If you are a Developer, you need to ask your Console Admin to change the password. To find out who your Console Admin is, log in and navigate to Cluster Overview > Access.

To change a user's password:

  1. Navigate to the SQL Users page.
  2. In the row of the user whose password needs to be changed, click the ... button.

    Change SQL password

  3. From the dropdown, select Change Password.

  4. In the New Password field, enter the new password.

    The password must be at least 12 characters long.

  5. Click Save.

To change a user's password, use the ALTER USER statement:

icon/buttons/copy
> ALTER USER <user> WITH PASSWORD '<new password>';

Remove a user

To remove a user:

  1. Navigate to the SQL Users page.
  2. In the row of the user you want to remove, click the ... button.

    Remove a SQL users

  3. From the dropdown, select Delete SQL User.

  4. Click Delete.

To remove a user, use the DROP USER statement:

icon/buttons/copy
> DROP USER <user>;
Note:

All of a user's privileges must be revoked before the user can be dropped.

Grant privileges

Access to the data in your cluster is controlled by privileges. When a user connects to a database, either via the CockroachDB SQL client or a Postgres driver or ORM, CockroachDB checks the user's privileges for each statement executed. If the user does not have sufficient privileges for a statement, CockroachDB returns an error.

To grant a user privileges for specific databases and tables in your cluster, use the GRANT statement. For example, to assign a user all privileges for all tables in a database:

icon/buttons/copy
> GRANT ALL ON DATABASE <database> TO <user>;

To assign a user more limited privileges for one table in a database:

icon/buttons/copy
> GRANT SELECT, INSERT ON TABLE <database>.<table> TO <user>;

For more details, see Privileges and GRANT.

Manage privileges

View a user's privileges

To show privileges granted to a user, use the SHOW GRANTS statement:

icon/buttons/copy
> SHOW GRANTS ON DATABASE <database> FOR <user>;

Revoke a user's privileges

To revoke privileges from a user, use the REVOKE statement:

icon/buttons/copy
> REVOKE INSERT ON TABLE <database>.<table> FROM <user>;

Use roles

Role-based access control lets you simplify how you manage privileges. In essence, a role is a group containing any number of other roles and users as members. You can assign privileges to a role, and all direct and indirect members of the role will inherit the privileges.

Once you have connected to the cluster, you can set up roles:

  • To create a role, use the CREATE ROLE statement:

    icon/buttons/copy
    > CREATE ROLE <role>;
    
  • To grant privileges to a role, use the GRANT <privilege> statement:

    icon/buttons/copy
    > GRANT <privilege> ON DATABASE <database> TO <role>;
    
  • To add a user (or another role) to a role, use the GRANT <role> statement:

    icon/buttons/copy
    > GRANT <role> TO <user or role>;
    
  • To revoke privileges from a role, use the REVOKE <privilege> statement:

    icon/buttons/copy
    > REVOKE INSERT ON TABLE <database>.<table> FROM <role>;
    
  • To remove a user (or another role) from a role, use the REVOKE <role> statement:

    icon/buttons/copy
    > REVOKE <role> FROM <user or role>;
    
  • To list all roles in your cluster, use the SHOW ROLES statement:

    icon/buttons/copy
    > SHOW ROLES;
    
  • To remove a role, use the DROP ROLE statement:

    icon/buttons/copy
    > DROP ROLE <role>;
    
    Note:

    All of a role's privileges must be revoked before the role can be dropped.

See also

YesYes NoNo