Describes role-based access control (RBAC), authentication and authorization in QuestDB.
Role-based Access Control (RBAC) is available in QuestDB Enterprise & QuestDB Cloud.
QuestDB's access control system applies both user and service accounts. Users and service accounts are the entities that can be authenticated, then authorized to perform operations in the database.
Although users and service accounts look similar, they are fundamentally different:
A user generally belongs to an individual. Individuals are often part of a team or a department of an organization.
A service account generally belongs to an org. A service account is suited for an application accessing the database.
Organizational units, such as teams or departments are represented by groups. Users can be associated with multiple groups at the same time. Members of a team often require the same permissions - a group of analysts, for example. As a consequence, both users and groups can be granted permissions. Users inherit permissions from the groups with which they are associated.
Service accounts, on the other hand, do not inherit permissions from anywhere. All permissions required by the application accessing the database must be granted directly to the service account. This is because applications work with the data programmatically, so their access is usually more restrictive than the permissions of a user or a group. For example, a service account would not usually need to create and drop database tables, while users are often granted this ability.
The below diagram is an example of a QuestDB access control system. Inherited permissions are shown in grey colour:
Based on the abovem service accounts seem to be very isolated entities. But you
might have already noticed the appearance of the above
ASSUME SERVICE ACCOUNT
Applications are usually owned by teams or a group of individuals. Hence, service accounts can be linked to a number of users and/or groups by granting them this permission. Being able to assume a service account means that a user can switch to the access list of the service account from their own account. This concept provides a great way of debugging access control related bugs in an application, and also controls who can see the permissions of a service account.
Ultimately, users are granted a set of permissions, either directly or indirectly via groups. The overall set of permissions granted to them forms their access control list. This list governs their authorization, what they can and cannot do.
The diagram below shows the authentication and authorization flow in QuestDB:
Users must authenticate themselves before they perform any database operations. Authentication is required to determine an identity, and then subsequently to find their access control list.
Authentication must happen via a secure TLS connection, otherwise the user's secrets used for authentication will travel unencrypted.
QuestDB supports three different ways of authentication:
- Password: The user is required to provide their credentials for successful authentication. An initial password can be set when the user is created. This password should be modified by the user after their first successful login. Only a single password can be set for a user; there is no option to have multiple passwords at the same time. Password authentication is supported by the REST and the PostgreSQL Wire endpoints.
- JWK Token: The user is required to provide a JWK token. The private key of the token is tested against their public key which has been recorded previously in the database. Only a single token can be set for a user; there is no option to have multiple tokens at the same time. JWK token authentication is supported by the InfluxDB Line Protocol (ILP) endpoint.
- REST API Token: The user is required to provide a REST API token. The token is tested against other tokens that were previously generated in the database. A user may have multiple REST API tokens. REST API token authentication is supported by the REST endpoint.
Users and service accounts can have both password and token authentication enabled at the same time.
The only exception is QuestDB's built-in admin, which supports only password authentication. The built-in admin should be used to create the users and groups initially. More on the built-in admin later.
User management happens via SQL commands.
CREATE USER /
CREATE SERVICE ACCOUNT commands are used to create new
users and service accounts. These commands also provide the option to specify an
initial password, which should be changed after the first login. The name of the
principal must be unique across all users, service accounts and groups. This
is enforced by QuestDB.
The list of users can be displayed with the help of the
SHOW USERS command.
A similar command exists for service accounts too.
Principals can change their password with the help of the
ALTER USER /
ALTER SERVICE ACCOUNT commands.
JWK tokens can be used for authentication too. The
ALTER command can be used
to create a new JWK token.
The output of the command is the public and private keys of the principal. The keys should be copied and stored safely by the user. QuestDB does not store the private key. If the private key is lost, it cannot be recovered. A new token must be generated. If the above command is repeated, the token gets replaced with a new one.
As mentioned earlier, REST API tokens can be used for authentication as well.
ALTER command can be used to create a new REST API token:
The output of the command is the generated token. The token should be copied and stored safely by the user. QuestDB does not store the token. If the token is lost, it cannot be recovered. A new token must be generated. If the above command is repeated, a new token gets generated, but all generated tokens will remain valid.
Check which authentication mode is enabled for a user with the
A similar command exists for service accounts too:
Passwords and tokens also can be removed with the help of
By removing all secrets, the user is essentially disabled. They cannot access the database anymore.
If we wanted to remove the user entirely, we can use the
DROP USER /
DROP SERVICE ACCOUNT statements:
Related SQL commands:
- CREATE USER
- CREATE SERVICE ACCOUNT
- ALTER USER
- ALTER SERVICE ACCOUNT
- DROP USER
- DROP SERVICE ACCOUNT
- SHOW USERS
- SHOW SERVICE ACCOUNTS
- SHOW USER
- SHOW SERVICE ACCOUNT
Groups are a great tool to reduce the burden of user management. They help group people together when many users require the same or similar permissions.
CREATE GROUP command is used to create new groups.
To avoid confusion, the name of the group must be unique across all users, service accounts and groups.
The list of groups can be displayed with the help of the
SHOW GROUPS command:
Users can be added to or removed from groups:
Users of a group inherit the permissions granted to the group.
Permissions inherited from groups cannot be revoked directly from the user.
We can check the groups of a user with the
SHOW GROUPS command:
Groups can be deleted with
If a group is deleted, all users of the group lose the permissions they previously inherited from the group.
Related SQL commands:
A new QuestDB instance starts with a single user, which is the built-in admin. The default built-in admin settings are:
It is strongly recommended that the built-in admin's password and all other default password are changed via QuestDB's server configuration before the instance is started. Please change both the admin's username and password.
The following property keys are used to configure the built-in admin in
The built-in admin has all permissions granted by default. Its access cannot be modified. It is root.
After startup we can use the built-in admin to create new users, service accounts and groups. We can also grant permissions to them. It is recommended that the built-in admin is disabled in the configuration files after users and groups are setup.
The following property key is used to enable/disable the built-in admin in server.conf:
Permissions are the building blocks of access lists, and determine what a user can or cannot do. They can be granted to users, service accounts and groups, allowing them to access specific functionality.
Users belong to
group1 can insert data into
table1, and can also query the
data from the table.
user1 can create users in the database.
Permissions can be revoked from users and groups, which means losing their access to the given functionality:
In this example, users who belong to
group1 can no longer insert data into
table1. But they can still run queries on the data stored in the table. And
user1 can no longer create new users.
As admins we can grant or revoke access to operations performed on database objects. By database objects we mean tables, columns, users, service accounts, groups or the database itself as a whole. Hence, permissions can be granted to users or groups on different levels.
SELECT as an example, this permission can be granted on three
When granted on database level, the user can run queries involving any data from any table of the database
When granted on table level, the user can query only specific tables on which the permission has been granted to them
When granted on column level, the user can query only specific columns of tables. It could happen that the user can access all data in some tables, but only some columns from other tables.
Depending on related functionality, some permissions do not make sense when granted on the table or column level. The granularity in which permissions can be granted is defined in QuestDB for each permission.
Table level permissions on the other hand can be granted only on database or
table level. The functionality linked to a table level permission does not
work on columns. Take
DETACH PARTITION and
ATTACH PARTITION as an example.
Partitions of a table can be detached and re-attached only as a whole. It would
not make sense to grant these permissions only on specific columns of a table.
Other functionality is performed on the entire database, and is therefore not
applicable to specific tables or columns of a table. A good example is taking a
backup of the database, the permission
BACKUP DATABASE can be granted only on
Functionality related to user management is always granted on the database level. However, users are always able to manage their own passwords and tokens, and view the list of permissions granted to them. Users can also view the permissions of the groups and service accounts they are associated with.
Related SQL commands:
An interesting - and perhaps risky - property of granted high level permissions
is that they implicitly provide access to database objects created in the
future. For example, if a user has been granted
SELECT permission on the
database level, the user can query all tables in the database, and also will
access any tables created in the future. Those tables will be part of the
Let's look at an example:
user1 has access to all four tables in the database.
However, if access is revoked on any of the tables, the permission is moved from the database to individual tables. That is because QuestDB does not maintain an exclude list. It is therefore not possible to grant a permission on the database level, and then exclude specific tables.
Revoking access even on a single table means that the permission level on which the permission has been granted is re-adjusted. We move from the database level to the table level. This results in not only losing access to the table from which access is revoked, but also losing access to all tables created in the future.
user1 has access to
table3 only. The revoke statement
re-adjusted the database level permission to table level, meaning it
SELECT on database level and granted it to
If we want the user to access the newly created
table4, we must grant access
Permission re-adjustment can also happen when a permission is granted on table level, but then gets revoked on one or more columns.
An example of permission re-adjustment from table to column level:
In the above example, revoking access on
col1 triggers permission
re-adjustment. Access is revoked on the table level and granted to
col3 only. When
col4 is added to the table, the user cannot access it.
Access has to be granted explicitly, if needed.
Since QuestDB is a time-series database, the designated timestamp column is treated on a special way. Some functionality, such as SAMPLE BY, LATEST ON or ASOF JOIN, require a designated timestamp. If a user can access only some columns of a table, but not the designated timestamp, then these operations would become unavailable to the user. It is something of a dependency.
As a solution, QuestDB derives permissions for the designated timestamp column
based on the access granted to other columns of the table. If a user is granted
UPDATE permission to any of the columns of a table, the same
permission is then granted to the designated timestamp column implicitly.
Let's look at an example:
The overall permissions on the designated timestamp column will be the union of the permissions granted to the column explicitly and implicitly:
Implicit permissions cannot be revoked directly from the designated timestamp column:
It is possible to grant permission to non-existent users, service accounts or groups. These permissions will get picked up when the entity eventually gets created.
Flexibility is great, but can lead to issues when a typo in the name of the entity occurs.
If we want to avoid problems caused by typos, we can use the
The above will fail, because QuestDB will not find the user. This is especially useful for scripts.
If a user, service account or group is deleted and later re-created, permissions do not get reinstated.
Permissions can be granted not only to non-existent users and groups, but also on non-existent tables and columns.
This is useful in some situations. For example, when it is expected that the table or the column will be created automatically via InfluxDB Line Protocol (ILP):
Permission is picked up after the table is created.
When the table is deleted permissions disappear.
However, if a table with the same name is re-created, the permissions are re-instated:
This can be useful in situations when a table is deleted only temporarily.
For example, when you need to remove some data from a table, and the data to be
deleted is not aligned to partitions, so
DROP PARTITION is not an option:
Reinstating permissions on the re-created table is not always desirable. If we want a clean start, we can delete the old table along with the permissions assigned to it.
This can be done by using the
CASCADE PERMISSIONS option:
CASCADE PERMISSIONS is not implemented yet, but will be available soon!
The same is true for columns.
When a column is deleted and then re-created, permissions are re-instated.
When a user creates a new table or adds a new column to an existing table, it gets owner permissions on the newly created database object.
If the user creates a table, the user automatically gets all table level
permissions with the
GRANT option on it.
If the user adds a new column to an existing table, the user automatically gets
all column level permissions with the
GRANT option on it.
In QuestDB ownership does not persist. This means that the user gets full control over the newly created table or column at the time of creating it, but if the permissions are later revoked, then the user cannot get it back without someone re-granting it to them.
There are a number of access control related SHOW commands in QuestDB.
We can list all users, service accounts and groups, which groups are associated with a user, which service accounts a user or the members of a group can assume; we can list their permissions and also view which authentication mode is enabled for users and service accounts.
However, this functionality is only available to the user if the necessary
permissions are granted to them. When trying to list all users, service accounts
or groups, QuestDB checks for the
LIST USERS permission. When trying to view
the groups, service accounts, permissions or authentication modes of an entity,
QuestDB checks for the
USER DETAILS permission.
The only exception is when the user is querying information related to themselves or to the groups/service accounts with which they are associated. In this case no permission is required. Anyone is allowed to check their own groups, service accounts, permissions, and the list of authentication methods enabled for them.
The built-in admin is special because it is pre-configured in the system, and has all permissions granted on the database level, meaning it can do anything. It is root.
The access list of the built-in admin is hardcoded in the system, and cannot be
changed. Any attempt to issue a
REVOKE statement involving the
built-in admin is rejected by the database.
The SHOW PERMISSIONS command also treats the built-in admin differently. When trying to list the permissions of the built-in admin, nothing will be displayed.
When using QuestDB Cloud, the built-in admin is initially root, but permissions can be granted or revoked, and the user itself can be deleted. If the admin user password is updated from the QuestDB Cloud management interface, the admin user will be reset and it will become root again.
Additionally, all QuestDB Cloud instances have a built-in
cloudadmin, which is
exclusively used by the QuestDB internal automations. The
cloudadmin user cannot be deleted, and its permissions cannot be granted or revoked.
As seen above, the admin user is protected on QuestDB Enterprise, but can be altered — and even deleted — on QuestDB Cloud. If you lose programatic access to your instance because you accidentally removed the admin user before creating new users, you can reset it in either of these ways:
- Log into your QuestDB Cloud management interface.
- Find the instance where you need to reset admin and navigate to
- Click on
Changeand enter a new password for HTTP Basic Auth/PG Wire.
Update, in moments your admin will be reset.
- Log into your QuestDB Cloud management interface.
- Find the instance where you need to reset admin and take a snapshot. Please note all data you ingest from this point onwards will not be part of the snapshot, so you might want to stop data ingestion.
- Restore the snapshot into a new instance. As part of the provisioning, an admin user will be created with root permissions. At this point you can delete your old instance and keep working with the new one.