`CREATE USER`¶

Create a new database user.

Synopsis¶

CREATE USER username
[ WITH ( user_parameter = value [, ...]) ] |
[ [ WITH ] user_parameter [value] [ ... ] ]

Description¶

CREATE USER is a management statement to create a new database user in the CrateDB cluster. The newly created user does not have any special privileges, and those must be assigned afterwards, for details see the privileges documentation. The created user can be used to authenticate against CrateDB, see Host-Based Authentication (HBA).

The statement allows to specify a password for this account. This is not necessary if password authentication is disabled.

Note

USER is essentially the same as ROLE with the difference that a USER can login to the database and can also be assigned a password, but cannot be granted to another USER or ROLE. On the contrary, a ROLE cannot login to the database, and therefore cannot be assigned a password, but it can be granted to another USER or ROLE.

For usages of the CREATE USER statement see Users and roles management.

Parameters¶

username:

The unique name of the database user.

The name follows the principles of a SQL identifier (see Key words and identifiers).

Clauses¶

`WITH`¶

The following user_parameter are supported to define a new user account:

password:

The password as cleartext entered as string literal. e.g.:

CREATE USER john WITH (password='foo')

CREATE USER john WITH password='foo'

CREATE USER john WITH password 'foo'

CREATE USER john password 'foo'

jwt:

JWT properties map (‘iss’, ‘username’ and ‘aud’) entered as string literal. e.g.:

CREATE USER john WITH (jwt = {"iss" = 'https://example.com', "username" = 'test@example.com', "aud" = 'test_aud'})

iss is a JWK endpoint, containing public keys. Required field.

username is a user name in a third party app. Required field.

aud is a recipient that the JWT is intended for. Optional field. If not provided, the cluster id is used (default).

Combination of iss and username must be unique.

Warning

If auth.host_based.jwt.iss is set, user specific properties are ignored and JWT Based Authentication are used.

See also

JWT authentication method

session settings:

Any of the modifiable session settings. The value set is used when the user logs in to the database, instead of the default value, thus, there is no need to use SET statements to modify a setting value after the user is created. e.g.:

CREATE USER john WITH (password='foo', search_path='my_schema')

Note

Session settings can only be set for a user and not for a role and are therefore not inherited by other users. They can be changed or reset after a user is created with ALTER ROLE.