Users and roles management

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

These statements are database management statements that can be invoked by superusers that already exist in the CrateDB cluster. The CREATE USER, CREATE ROLE, DROP USER and DROP ROLE statements can also be invoked by users with the AL privilege. ALTER USER or ALTER ROLE can be invoked by users to change their own password, without requiring any 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.

The definition of all users and roles, including hashes of their passwords, together with their privileges is backed up together with the cluster’s metadata when a snapshot is created, and it is restored when using the ALL, METADATA, or USERMANAGEMENT keywords with the RESTORE SNAPSHOT command.

Table of contents

ROLES

Roles are entities that are not allowed to login, but can be assigned privileges and they can be granted to other roles, thus creating a role hierarchy, or directly to users. For example, a role myschema_dql_role can be granted with DQL privileges on schema myschema and afterwards the role can be granted to a user, which will automatically inherit those privileges from the myschema_dql_role. A role myschema_dml_role can be granted with DML privileges on schema myschema and can also be granted the role myschema_dql_role, thus gaining also DQL privileges. When myschema_dml_role is granted to a user, this user will automatically have both DQL and DML privileges on myschema.

CREATE ROLE

To create a new role for the CrateDB database cluster use the CREATE ROLE SQL statement:

cr> CREATE ROLE role_a;
CREATE OK, 1 row affected (... sec)

Tip

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

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

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

The name 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 ROLE "Custom Role";
CREATE OK, 1 row affected (... sec)

If a role or user with the name specified in the SQL statement already exists the statement returns an error:

cr> CREATE ROLE "Custom Role";
RoleAlreadyExistsException[Role 'Custom Role' already exists]

ALTER ROLE

ALTER ROLE and ALTER USER SQL statements are not supported for roles, only for users.

DROP ROLE

To remove an existing role from the CrateDB database cluster use the DROP ROLE or DROP USER SQL statement:

cr> DROP ROLE role_c;
DROP OK, 1 row affected (... sec)
cr> DROP USER role_d;
DROP OK, 1 row affected (... sec)

If a role with the name specified in the SQL statement does not exist, the statement returns an error:

cr> DROP ROLE role_d;
RoleUnknownException[Role 'role_d' does not exist]

List roles

CrateDB exposes database roles via the read-only Roles system table. The sys.roles table shows all roles in the cluster which can be used to group privileges.

To list all existing roles query the table:

cr> SELECT name, granted_roles FROM sys.roles order by name;
+--------+------------------------------------------+
| name   | granted_roles                            |
+--------+------------------------------------------+
| role_a | []                                       |
| role_b | [{"grantor": "crate", "role": "role_c"}] |
| role_c | []                                       |
+--------+------------------------------------------+
SELECT 3 rows in set (... sec)

USERS

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)

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:

cr> GRANT ALL PRIVILEGES TO user_a;
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:

cr> CREATE USER user_b WITH (password = 'a_secret_password');
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:

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";
RoleAlreadyExistsException[Role 'Custom User' already exists]

ALTER USER

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

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

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

cr> ALTER USER user_a SET (password = NULL);
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.

To add or alter session settings use the following SQL statement:

cr> ALTER USER user_b SET (search_path = 'myschema', statement_timeout = '10m');
ALTER OK, 1 row affected (... sec)

To reset a session setting to its default value use the following SQL statement:

cr> ALTER USER user_b RESET statement_timeout;
ALTER OK, 1 row affected (... sec)

To reset all modified session setting for a user to their default values, use the following SQL statement:

cr> ALTER USER user_a RESET ALL;
ALTER OK, 1 row affected (... sec)

DROP USER

To remove an existing user from the CrateDB database cluster use the DROP ROLE or DROP USER SQL statements:

cr> DROP USER user_c;
DROP OK, 1 row affected (... sec)
cr> DROP ROLE user_d;
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_d;
RoleUnknownException[Role 'user_d' 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 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 the table:

cr> SELECT name, granted_roles, password, session_settings, superuser FROM sys.users order by name;
+--------+----------------------------------------------------------------------------------+----------+-----------------------------+-----------+
| name   | granted_roles                                                                    | password | session_settings            | superuser |
+--------+----------------------------------------------------------------------------------+----------+-----------------------------+-----------+
| crate  | []                                                                               | NULL     | {}                          | TRUE      |
| user_a | [{"grantor": "crate", "role": "role_a"}, {"grantor": "crate", "role": "role_b"}] | NULL     | {}                          | FALSE     |
| user_b | []                                                                               | ******** | {"search_path": "myschema"} | FALSE     |
+--------+----------------------------------------------------------------------------------+----------+-----------------------------+-----------+
SELECT 3 rows in set (... sec)

Note

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