User Management

Note

User Management is only part of the CrateDB Enterprise Edition.

A CrateDB cluster contains a set of database users. A database user is a principle at cluster level.

Users can be created using the CREATE USER statement and removed using the analoguous DROP USER statement. These statements are database management statements that can only be invoked by superusers that already exist in the CrateDB cluster. Also the users table sys.users is only readable by superusers.

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:

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

The newly created user does not have any special Privileges. It can be used to connect to the database cluster using available authentication methods.

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:

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

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

cr> CREATE USER "Custom User";
SQLActionException[UserAlreadyExistsException: User 'Custom User' already exists]

DROP USER

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

cr> DROP USER user_b;
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:

cr> DROP USER user_b;
SQLActionException[UserUnknownException: User 'user_b' does not exist]

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:

cr> SELECT * FROM sys.users order by name;
+-------------+-----------+
| name        | superuser |
+-------------+-----------+
| Custom User | FALSE     |
| crate       | TRUE      |
| user_a      | FALSE     |
+-------------+-----------+
SELECT 3 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.

Warning

When the Elasticsearch HTTP REST API is enabled, it is possible to read the users data via the Elasticsearch API. Therefore access to the users table is not restricted.