Microsoft EntraID OIDC guide
This document sets up SSO authentication for the QuestDB Web Console in Microsoft EntraID, formerly known as Azure AD.
For a general introduction to OpenID Connect and QuestDB, see the OIDC Operations page.
To enlarge the images, click or tap them.
Set up the client application in Entra ID​
First thing first, let's pick a name for the client!
Then head to Microsoft Entra Admin Center, and register the application under Identity - App registrations - New registration.
The QuestDB Web Console is a SPA (Single Page App).
As a result, it cannot store safely a client secret.
Instead, it can use PKCE (Proof Key for Code Exchange) to secure the flow.
When registering the application, select the SPA platform.
We also have to specify the URL of the Web Console as Redirect URI.
After clicking Register, we have created a client application with the name QuestDB.
Each application is assigned a unique id (known as Client ID in the OAuth2 - OIDC standard). The client will identify itself with this id when sending requests to Entra ID.
We find the platform configurations under Authentication. This is the place where the previously set redirect URI can be viewed and modified. We can also specify additional redirect URIs, if necessary.
The redirect URIs of the application are automatically eligible for the Authorization Code Flow with PKCE, which is a special version of the OAuth2 standard's Authorization Code Flow. It is specifically designed for applications where a client secret (e.g. a password) could not be kept safely. As single page applications run in the browser, they fall into this category.
The redirect URIs are also added to the CORS (Cross-Origin Resource Sharing) policy of EntraID. CORS is a mechanism to allow a web page, such as the Web Console, to access resources from a different domain than the one that served the page. In this context this means that we let the Web Console to access Entra ID, while its origin is the HTTP endpoint of QuestDB.
If we scroll down to the bottom of this page, we can also find a section where we can enable the Resource Owner Password Credential Flow.
This OAuth2 flow is legacy, and should be enabled only if there is a requirement of connecting to QuestDB using SSO (Single Sign-On) via clients not supporting redirect based web flows. This could mean a Postgres client without OAuth2 integration, such as psql, or a standalone in-house client application, or could be just a jupyter notebook.
The main issue with this flow is that the client application has to be trusted with the user's login details. The user's credentials are passed to the application, in this case to QuestDB, and the client application uses these credentials to authenticate the user by forwarding them to the identity provider, in this case to Entra ID.
It is guaranteed that QuestDB does not store the user's credentials in any way. They are not persisted into the database, not even in encrypted form. The login details are treated as passthrough information. Only exception is that server logs can contain the username, logged for audit purposes.
Our next stop is the Token configuration, where the OAuth2/OIDC access and ID tokens can be customized.
Note that users can be authenticated without customized tokens, but authorization would prove to be challenging. The user's security groups are not included in the tokens by default.
QuestDB can be configured to request the user's groups from the UserInfo endpoint of the OAuth2 server, but Entra ID cannot be configured to provide this information via the UserInfo endpoint. Therefore, we choose to customize the tokens, QuestDB will decode and validate the ID token, and take the group information from there.
QuestDB authorization relies on receiving the group memberships of the user. Entra ID groups should be mapped to QuestDB groups, and permissions can be granted to the QuestDB groups. Detailed information about group mappings can be found in the OIDC integration documentation.
The customized tokens contain user information which cannot be accessed without permission. User information is provided by Microsoft Graph, so the client application needs specific permissions to access Microsoft Graph APIs.
These permissions can be configured under API permissions. It is important to note that we will be setting Delegated permissions here, meaning we are not granting actual permissions to access user data. Instead, each user logging into QuestDB will have to consent to accessing their user profile.
By default, the User.Read permission is added to the list, but what we really need is:
- openid: to be able to issue ID tokens
- profile: to access user information
- offline_access: to be able to issue refresh tokens
By clicking on Microsoft Graph we can select and add these permissions.
The User.Read permission is not needed. It can be removed by clicking
on the ... at the end of the row, and selecting Remove permission from
the popup menu.
With this we have finished setting up the QuestDB client application in Entra ID, and now we can wire QuestDB and Entra ID together by adding OIDC configuration to QuestDB.
QuestDB configuration​
The below should be set in QuestDB's server.conf:
# enable OIDC
acl.oidc.enabled=true
# the claim contains the username or user id
acl.oidc.sub.claim=name
# the claim contains the user's group memberships
acl.oidc.groups.claim=groups
# groups are encoded in the token
acl.oidc.groups.encoded.in.token=true
# OIDC configuration endpoint of Entra ID
acl.oidc.configuration.url=https://login.microsoftonline.com/12345678-1234-1234-1234-123456789abc/v2.0/.well-known/openid-configuration
# application ID taken from Entra ID
acl.oidc.client.id=8de84b90-1ea5-4e41-9e84-dba860aa01a6
# redirect URI, QuestDB's HTTP endpoint
acl.oidc.redirect.uri=http://localhost:9000
# OAuth scopes the user has to consent to
acl.oidc.scope=openid profile offline_access
# enable ROPC flow
# optional, required only if ROPC is enabled in Entra ID
acl.oidc.ropc.flow.enabled=true
The application ID and the OIDC configuration endpoint's URL can be found in the Overview of the application in Entra ID.
The application ID is displayed right under the application's name, the OIDC configuration endpoint is displayed on the panel which opens up when the Endpoints button is clicked.
Map groups and grant permissions​
Now we can start QuestDB, and login with the built-in admin to create group mappings.
As mentioned earlier, authorization works by mapping Entra ID groups to QuestDB groups. When the user logs in, QuestDB decodes Entra ID group memberships from the token, then finds the QuestDB groups mapped to them, and the user gets the permissions based on the mapped groups.
CREATE GROUP extUsers WITH EXTERNAL ALIAS '87654321-1234-1234-1234-123456789abc';
The above command maps the Entra ID group identified by object
id 87654321-1234-1234-1234-123456789abc to a QuestDB group called extUsers.
We should grant the necessary QuestDB endpoint permissions first to make sure users can access the Web Console, Postgres and ILP interfaces as required. Read more about endpoint permissions.
GRANT HTTP, PGWIRE TO groupName;
Now we can grant the rest of the permissions as required. We can grant access to tables, for example.
GRANT SELECT ON table1, table2 to groupName;
Confirm group mappings and login​
To test, head to the Web Console and login.
If all has been wired up well, then login will succeed, and the user will have the access granted to them.