User management¶
User account information is stored in the cluster metadata 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
The definition of all users, including hashes of their passwords, is backed up
together with the cluster’s metadata when a snapshot is created, and it is
restored when using the ALL, METADATA, or USERS keywords with the
RESTORE SNAPSHOT command.
Table of contents
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";
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:
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.
DROP USER¶
To remove an existing user from the CrateDB database cluster use the DROP USER SQL statement:
cr> DROP USER user_c;
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_c;
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 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 | password | superuser |
+-------------+----------+-----------+
| Custom User | NULL | FALSE |
| crate | NULL | TRUE |
| user_a | NULL | FALSE |
| user_b | ******** | FALSE |
+-------------+----------+-----------+
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.