Introducing role-based access control
Role-based Access Control (RBAC) is a common request from teams with fast growing datasets. QuestDB Enterprise now provides 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.
Role call
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 SELECT
and
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 INSERT
data.
If someone no longer needs access? One command and they are revoked.
How does it work? Proceed!
Enable ACL in QuestDB Enterprise
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.
In our QuestDB server.conf
, set the following to true
:
# Enable access control and authentication
acl.enabled=true
Access control automatically enables authentication. If you are after authentication without authorization, use this config option instead:
# Enable authentication only, no ACL
auth.enabled=true
Using the default credential of admin
/ 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.
Simple SQL setup
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:
/*
1. Create an initial sensors table
2. Fill it with sample data
*/
CREATE TABLE sensors
AS (
SELECT
x ID,
rnd_symbol('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
rnd_symbol('New York', 'Miami', 'Boston', 'Chicago', 'San Francisco') city
FROM long_sequence(10000) x
);
Another table will do, to demonstrate temperature readings across time:
/*
1. Create an initial temperature readings table
2. Fill it, too, with sample data across time
*/
CREATE TABLE readings
AS(
SELECT
x ID,
timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,0) * 100000L) ts,
rnd_double(0)*8 + 15 temp,
rnd_long(0, 10000, 0) sensorId
FROM long_sequence(10000000) x)
TIMESTAMP(ts)
PARTITION BY MONTH;
Right now we are an admin, the root
user.
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.
Meet Alex the analyst
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
SELECT
(read) only - 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:
CREATE USER alex_analyst WITH PASSWORD s3cr3t$$$$444!#;
Before we provide them their credentials, we should think more about how to provide permissions.
Group well, secure well
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:
CREATE GROUP analysts;
Next, determine what the group can and cannot do.
To start, analysts can read everything in the readings
table:
GRANT SELECT ON readings(ID, temp, sensorId, ts, timestamp) TO analysts;
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
additional reading
columns are to remain a secret.
To demonstrate, add Alex's user to the new group:
ADD user alex_analyst to analysts;
Tuning a 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 SELECT
statement:
SELECT * FROM readings;
As expected, all data are present (table truncated for clarity):
ID | ts | temp | sensorId |
---|---|---|---|
1 | 2019-10-17T00:00:00.000000Z | 17.707507619309 | 875 |
2 | 2019-10-17T00:00:00.700000Z | 19.45109059006 | 7279 |
3 | 2019-10-17T00:00:01.200000Z | 16.971598905071 | 2116 |
4 | 2019-10-17T00:00:01.400000Z | 19.621503912462 | 8183 |
The *
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 sensors
table?
SELECT * FROM sensors;
x Access denied [SELECT on sensors(ID)]
Not allowed!
Practicing "no"
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
REVOKE
.
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
possible to 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 SELECT
on sensors
:
GRANT SELECT ON sensors TO alex_analyst;
Alex can now do interesting JOIN
statements across the entire access plane:
SELECT *
FROM readings
JOIN(
SELECT ID sensId, make, city
FROM sensors)
ON readings.sensorId = sensId;
As above however, what if analysts lose access to sensId
?
REVOKE SELECT ON readings(sensorId) FROM analysts;
On Alex's next SELECT * FROM readings;
attempt, they will receive an error:
Access denied [SELECT on readings(sensorId)]
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.
What about the InfluxDB Line Protocol?
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 streamers
. These
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
group.
There is a specific permission to allow this:
/* Create a new group */
CREATE GROUP streamers;
/* Create a user without a password! Until next section... */
CREATE USER sam_streamer;
/* Add new user to new group */
ADD user sam_streamer to streamers;
/* Grant group permissions for creating tables via ILP */
GRANT CREATE TABLE to streamers;
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.
Auth flavours: password, JSON Web Keys or REST API
Our walkthrough so far demonstrates basic password authentication. Above, we
created 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:
ALTER USER sam_streamer CREATE TOKEN TYPE JWK;
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
in hand, sam_streamer
creates an authenticated stream which can create new
tables in the database.
In addition to password and JWK authentication, QuestDB also supports REST API token authentication. A user may have multiple REST API tokens, which are tested against tokens previously generated in the database. REST API token authentication is supported by the REST endpoint endpoint.
REST API supports two authentication types:
- HTTP Basic Authentication: Supported by web browsers and can be applied
programmatically in an
Authorization: Basic
header:
curl -G --data-urlencode "query=SELECT * FROM cars;" \
-u "my_user:my_password" \
http://localhost:9000/exec
- Token-Based Authentication: Requires a REST API token to be specified in
an
Authorization: Bearer
header:
curl -G --data-urlencode "query=SELECT * FROM planes;" \
-H "Authorization: Bearer qt1cNK6s2t79f76GmTBN9k7XTWm5wwOtF7C0UBxiHGPn44" \
http://localhost:9000/exec
To generate a REST API token, use the ALTER
command:
ALTER USER user1 CREATE TOKEN TYPE REST WITH TTL '30d';
ALTER SERVICE ACCOUNT application1 CREATE TOKEN TYPE REST WITH TTL '1d' REFRESH;
The output of the command is the generated token.
Summary
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.