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.