User management

User account information is stored in the cluster metatdata of CrateDB and supports the following statements to create, alter and drop users:

These statements are database management statements that can be invoked by superusers that already exist in the CrateDB cluster. The CREATE USER and DROP USER statements can also be invoked by users with the AL privilege.

When CrateDB is started, the cluster contains one predefined superuser. This user is called crate. It is not possible to create any other superusers

Users cannot be backed up or restored.

CREATE USER

To create a new user for the CrateDB database cluster use the CREATE USER SQL statement:

  1. cr> CREATE USER user_a;
  2. CREATE OK, 1 row affected (... sec)

Tip

Newly created users do not have any privileges. After creating a user, you should configure user privileges.

For example, to grant all privileges to the user_a user, run:

  1. cr> GRANT ALL PRIVILEGES TO user_a;
  2. GRANT OK, 4 rows affected (... sec)

It can be used to connect to the database cluster using available authentication methods. You can specify the user’s password in the WITH clause of the CREATE statement. This is required if you want to use the Password authentication method:

  1. cr> CREATE USER user_b WITH (password = 'a_secret_password');
  2. CREATE OK, 1 row affected (... sec)

The username parameter of the statement follows the principles of an identifier which means that it must be double-quoted if it contains special characters (e.g. whitespace) or if the case needs to be maintained:

  1. cr> CREATE USER "Custom User";
  2. CREATE OK, 1 row affected (... sec)

If a user with the username specified in the SQL statement already exists the statement returns an error:

  1. cr> CREATE USER "Custom User";
  2. UserAlreadyExistsException[User 'Custom User' already exists]

ALTER USER

To alter the password for an existing user from the CrateDB database cluster use the ALTER USER SQL statement:

  1. cr> ALTER USER user_a SET (password = 'pass');
  2. ALTER OK, 1 row affected (... sec)

The password can be reset (cleared) if specified as NULL:

  1. cr> ALTER USER user_a SET (password = NULL);
  2. ALTER OK, 1 row affected (... sec)

Note

The built-in superuser crate has no password and it is not possible to set a new password for this user.

DROP USER

To remove an existing user from the CrateDB database cluster use the DROP USER SQL statement:

  1. cr> DROP USER user_c;
  2. DROP OK, 1 row affected (... sec)

If a user with the username specified in the SQL statement does not exist the statement returns an error:

  1. cr> DROP USER user_c;
  2. UserUnknownException[User 'user_c' does not exist]

Note

It is not possible to drop the built-in superuser crate.

List users

CrateDB exposes database users via the read-only sys.users system table. The sys.users table shows all users in the cluster which can be used for authentication. The initial superuser crate which is available for all CrateDB clusters is also part of that list.

To list all existing users query that table:

  1. cr> SELECT * FROM sys.users order by name;
  2. +-------------+----------+-----------+
  3. | name        | password | superuser |
  4. +-------------+----------+-----------+
  5. | Custom User |     NULL | FALSE     |
  6. | crate       |     NULL | TRUE      |
  7. | user_a      |     NULL | FALSE     |
  8. | user_b      | ******** | FALSE     |
  9. +-------------+----------+-----------+
  10. SELECT 4 rows in set (... sec)

The column name shows the unique name of the user, the column superuser shows whether the user has superuser privileges or not.

Note

CrateDB also supports retrieving the current connected user using the system information functions: CURRENT_USER, USER and SESSION_USER.