Role-based Access Control (RBAC) is a common request from teams with fast growing datasets. QuestDB Enterprise and QuestDB Cloud deployments now provide a robust, SQL-based access control syntax to keep permissions organized and your team free from the looming stress of accidental destruction.
This tutorial demonstrates RBAC basics with both Postgres Wire (PGWire) and InfluxDB Line Protocol (ILP) ingest examples. After reading, you will be ready to administer an instance of any size.
Full administrative access to all tables and operations is useful for moving fast during implementation. But eventually, projects demonstrate value and datasets grow, and more team members request access. These people often span many types of role and possess a wide range of technical comfort. We want to accommodate their needs, and make it easy to grant or revoke access as required.
For example, analysts, support agents and similar may just want to
read wherever appropriate and work with the peace-of-mind that they will not
blow something up by mistake. While an overall administrator will want to
alter permissions, see everything, create tables and
If someone no longer needs access? One command and they are revoked.
How does it work? Proceed!
Role-based Access Control is the concept and an Access Control List (ACL) is how the concept is put into practice. We start with the assumption that you have a local QuestDB Enterprise binary running. If you can already access your QuestDB Enterprise deployment via QuestDB Cloud, skip ahead to the next section.
In our QuestDB
server.conf, set the following to
Access control automatically enables authentication. If you are after authentication without authorization, use this config option instead:
Using the default credential of
quest, the user can now login with
full permissions. We are experimenting and this data is not sensitive, but
obligatory warning: do not proceed with default passwords! Luckily, that is
a key reason why we are interested in a flexible ACL: security.
Next we need to put data in, and then we can unpack how the ACL functions.
The handy Create my first dataset document guides the creation of a small two-table data set. While QuestDB is most adept at ingesting vast amounts of time-series data, our demo case will use static data. We will work with temperature, and begin with basic SQL commands.
First, create a table with sensors of various make and some cities:
Another table will do, to demonstrate temperature readings across time:
Right now we are an admin, the
We can configure all available options and blow stuff up as much as we please.
But now we have company coming, and the house must be in order.
A new analyst joins the team.
Alex is new to SQL, but has a keen eye for data analysis.
They request access to the dataset to create charts and reports.
As administrators, we agree that Alex should:
- have access to both tables in the dataset
- be able to
- have no destructive capabilities
We also assume there may be more analysts like Alex in the near future.
For now, create a user for Alex and provide a password:
Before we provide them their credentials, we should think more about how to provide permissions.
Right now, Alex is a fully restricted user by default. But we want to open things up. An ACL is most helpful when we can be granular with our permissions. With granularity, we choose what someone can and cannot access to a fine and careful degree.
At this point, we could setup permissions for just Alex. But once other analysts see the marvelous new reports, we expect that more access requests will follow. It would therefore be ideal to setup a group for all present and future analysts.
To do so, create a group and assign permissions to it:
Next, determine what the group can and cannot do.
To start, analysts can read everything in the
Great -- that is
readings, and an analyst can access the table and all
columns. By adding explicit permissions to the analyst group, we have implicitly
restricted everything else. For all future analysts, the
sensors table and any
reading columns are to remain a secret.
To demonstrate, add Alex's user to the new group:
Once a user is assigned to a group and that group has permissions, any further access must be explicit. We have said what they can see, and everything else is now restricted. Users in an access controlled group can see what is granted and nothing more.
Consider that Alex logged in and tried a broad
As expected, all data are present (table truncated for clarity):
* query worked because the group can see all of the columns. If they
lacked permission to just one of the columns, any
SELECT statements would need
to include only the permitted columns, or else they would be rejected. This
applies to tables, too.
If Alex tries to
SELECT from the
If we were to adjust the group and remove access to a column, the change takes
effect the moment the permission is altered. For example, if we decide analysts
do not need to see the
sensorId column, OK - we can adjust access using
This is true across all contexts. If a user were to try to
JOIN into an area
where they lack permissions, then a denial will be received. If they cannot see
the column, table or database, then they cannot access it via any aggregate.
While the group concept is valuable for overall organization, direct changes to
a specific user within a group may be needed. In that case, no problem. It is
GRANT access to specific users, and that access will form a union
with the group permission.
Alex already has
readings access from the analysts group.
But now we can be direct and grant Alex
Alex can now do interesting
JOIN statements across the entire access plane:
As above however, what if analysts lose access to
On Alex's next
SELECT * FROM readings; attempt, they will receive an error:
On Alex's broad
JOIN query? The same thing, rejected.
Alex has explicit full access to
sensors and limited group access to
readings. This demonstrates inherited permissions. New users have no
permissions. They are locked down by default. But once placed within a group or
granted an explicit individual permission, their scope is then limited.
When using InfluxDB Line Protocol for ingest, if
table_name is set and does
not yet exist in QuestDB, the table is created. What if, in the future, we
wanted a specific role to create tables only over InfluxDB Line Protocol?
Consider we have a specialized data entry role we call
individuals setup new data streams via InfluxDB Line Protocol. No one else
requires InfluxDB Line Protocol, so we need only apply the permission to their
There is a specific permission to allow this:
A person who creates a table gets ALL table and column level permissions for the entire table.
A person who adds a new column to a table will get ALL column level permissions for the newly created column.
This is very helpful for our
streamers, who need to setup and tune new streams
and will therefore automatically receive permissions to do whatever is needed
within their creations. If an analyst were to want to view these new tables?
They cannot, they now require access to do so.
Our walkthrough so far demonstrates basic password authentication. Above, we
sam_streamer but did not provide Sam a password. That is because we
expect Sam to use JSON Web Key authentication instead of basic password
authentication. Why? The InfluxDB Line Protocol does not support the option.
To do so, we
ALTER the user and create a JWK access token:
This return the needed key values. The client can now use these values to
authenticate into the database, with no server restart required. With their keys
sam_streamer creates an authenticated stream which can create new
tables in the database.
RBAC is essential for a secure and scalable deployment. Deep coverage across databases, tables and columns and the ability to flexibly group and to provide roles that span across groups, means that granular control over access is at your fingertips.
Want to learn more about how we engineered the QuestDB access control list? Checkout the article: Navigating Access Control Design: Pursuing Clarity and Simplicity.