OpenID Connect (OIDC) integration

Enterprise:

OpenID Connect (OIDC) enables SSO authentication with external Identity Providers.

Learn moreFree trial

OpenID Connect (OIDC) integrates with Identity Providers (IdP) external to QuestDB.

It is a convenient way to integrate QuestDB into your enterprise environment, and it provides SSO (Single Sign-On) for the Web Console. Client applications can use device-flow sign-in, and services can use rotating bearer-token providers to keep authenticating as credentials expire.

Microsoft Active Directory and Azure AD, for example, can be turned into an Identity Provider.

Specific installation steps depend on the type of the provider.

QuestDB Enterprise 4.1.0

QuestDB Enterprise 4.1.0 checks tokens and HTTP sessions more strictly. With acl.oidc.groups.encoded.in.token=true, it rejects a token that has expired or whose issuer is not the expected one, and refuses to start with acl.oidc.host unless acl.oidc.issuer is set. A request whose token QuestDB rejects fails with 401 Unauthorized, even with a valid session cookie. See Authentication changes in 4.1.0 before you upgrade.

Architecture overview

Altogether, the architecture appears as such:

Architecture diagram

We can break it down into core components.

Web Console

QuestDB's interactive UI. Users must authenticate before accessing the database via the interface.

The Web Console uses PKCE (Proof Key for Code Exchange) to secure the authentication and authorization flow.

In OAuth2/OIDC terms, the Web Console is referred to as the client, and it is assigned an identifier: the Client Id.

Each application which integrates via OIDC should be given a different Client Id.

OIDC Provider

Typically consists of a number of modules.

We are interested in two of them only.

1. The Identity Provider holds user identities and user information, capable of authenticating users, and to issue an ID Token which uniquely identify them.

2. The Authorization Server grants access to resources, such as a database, in the form of access tokens.

The OIDC Provider usually integrates with a number of applications which require different access to a number of resources.

These clients communicate with the OIDC Provider via its endpoints.

It exposes a number of APIs, including the Authorization, Token and User Info endpoints.

QuestDB

The database, in OAuth2/OIDC terms the protected resource or resource server.

Only processes requests which contain a valid access token.

Authentication and Authorization Flow

The OAuth2/OIDC standard defines different ways of obtaining access and ID tokens from the OIDC Provider, referred to as the "flow".

The goal of this flow is to get the user, who is sitting in front of the Web Console, authenticated.

Then, it allows QuestDB to determine the user's permissions based on user information provided by the Identity Providers.

Specifically, the QuestDB Web Console uses the Authorization Code Flow with PKCE option.

It consists of ten steps...

1. Secret generation

First the Web Console generates a cryptographically strong random secret called the code verifier.

The secret is hashed using the SHA256 algorithm. The result is the code challenge.

After PKCE initialization the Web Console requests an authorization code from the OIDC Provider.

It calls the Authorization endpoint with a few parameters, including the:

• Client Id
• requested scopes (the list of scopes are configurable, default is openid only)
• code challenge
• algorithm used to generate the code challenge from the code verifier (SHA256)

When the Authorization Server receives the request, it checks if the user has been authenticated already:

• If the user has a valid session, it can be provided with an authorization code straight away, so we jump to step 4.

• If the user does not have a valid session yet, it will be redirected to the Identity Provider for authentication.

Authorization code request example

https://oidc.provider:443/as/authorization.oauth2?client_id=questdb&response_type=code&scope=openid&redirect_uri=https%3A%2F%2Fquestdb.host%3A9000&code_challenge=IwZ-WuypAY3fMtvismbj1MQUe5CzMgrBa87nYcgFoLQ&code_challenge_method=S256

2. Prove identity

Next, the user must prove its identity.

This could be a username with:

• a password,
• an OTP
• facial recognition via a mobile app
• or anything else supported by the Identity Provider.

Prove identity

3. Scope consent

After successful authentication, the user provides consent for the requested scopes.

The list of scopes are configurable.

By default the Web Console requests only the openid scope which is mandatory for OIDC.

No ID Token is issued without it.

The OIDC provider can be configured to provide the consent automatically, without presenting the user with an additional screen in the browser.

Scope consent

4. Redirection

Consent is granted!

The Authorization Server redirects the user back to the Web Console with the authorization code:

Authorization code response example

https://questdb.host:9000/?code=1L344XEY5XRka1j4ySNa8bVQSLf71as9uGLEuv_A

5. Credential request

Now, the QuestDB Web Console requests the ID and access tokens from the Token endpoint of the OIDC Provider with the authorization code.

It includes the Client ID and the PKCE code verifier together with the authorization code in the request.

The endpoint then hashes the code verifier using the method specified previously in step 1.

The result must match the code challenge, also provided in step 1.

The matching code challenge proves that the token is requested by the client which requested the authorization code, and it was not stolen:

Token request example

POST https://oidc.provider:443/as/token.oauth2 HTTP/1.1
Content-Type: application/x-www-form-urlencoded
grant_type=authorization_code&code=1L344XEY5XRka1j4ySNa8bVQSLf71as9uGLEuv_A&client_id=questdb&&redirect_uri=https%3A%2F%2Fquestdb.host%3A9000&code_verifier=uGZh4sQffXLgRna7D-jtEAkuXzp7Lm_okZXBljzP38coAD44kEheIaz7Pdh98KxYtYLZHNiQPCczQYeF

6. Credentials received

If the PKCE check is passed, the Web Console receives the ID and access tokens.

There is a third token in the response too, the refresh token.

The refresh token is used by the Web Console to refresh the access token before it expires.

Without the refresh token mechanism, the user would be forced to re-authenticate when the access token expires.

The validity of the tokens are configurable inside the OIDC Provider.

Token response example

{
  "access_token": "gslpJtzmmi6RwaPSx0dYGD4tEkom",
  "refresh_token": "FUuAAqMp6LSTKmkUd5uZuodhiE4Kr6M7Eyv.eg83ge",
  "id_token": "eyJhbGciOiJSUzI1NiIsImtpZCI6I...",
  "token_type": "Bearer",
  "expires_in": 300 // In seconds, thus 5 minutes
}

7. Database access

With the tokens, the Web Console can interact with the database.

The access token is in the header of every request sent to QuestDB.

Worried about exposing the token? It is rather opaque and does not contain user details.

To carry out permission checks, the database has to know more about the user.

For this, QuestDB has a User Info Cache.

If it finds a valid entry with the access token in the cache, steps 8 and 9 are skipped:

Query request example

https://questdb.host:9999/api/v1/sql/execute?query=select%20current_user()
Authorization: Bearer gslpJtzmmi6RwaPSx0dYGD4tEkom

8. Find user information

No user information in the cache, or stale information?

QuestDB uses the access token to request user information from the OIDC Provider's User Info endpoint.

This call also serves as token validation.

If the token is not real or has been expired, the User Info endpoint replies with an error:

User info request example

https://oidc.provider:443/idp/userinfo.openid
Authorization: Bearer gslpJtzmmi6RwaPSx0dYGD4tEkom

9. Receive user information

If the access token is valid, QuestDB receives the required user information from the endpoint, then updates its cache.

The cache improves performance, as QuestDB does not have to turn to the OIDC Provider on every single request.

Do note that cache expiry is configurable:

User info response example with Active Directory groups

{
  "sub": "externalUser",
  "name": "External User",
  "groups": [
    "CN=TestGroup1,OU=DC Users,DC=ad,DC=quest,DC=dev",
    "CN=TestGroup2,OU=DC Users,DC=ad,DC=quest,DC=dev"
  ]
}

10. Permission check

With the help of the user information, QuestDB can carry out permission checks.

If the permission check is successful, the database will process the request, and then sends the results back:

Query response example

{
  "query": "select current_user()",
  "columns": [
    {
      "name": "current_user",
      "type": "STRING"
    }
  ],
  "dataset": [["External User"]],
  "count": 1,
  "timestamp": -1
}

Interactive clients

Any interactive client - a UI, Jupyter notebook, CLI - can integrate with an OIDC provider. However, the level of support will vary between these tools.

Interactive clients usually fall into one of the following categories:

• Browser-based clients with support for HTTP redirects; this includes the Web Console or any javascript UI
• Applications running in a browser without support for redirects, such as Jupyter notebooks
• Non-browser based clients, usually some kind of command line interface (CLI) or a standalone application, such as Microsoft Access

Device authorization flow

The C and C++, Rust, and Python clients implement the OAuth 2.0 Device Authorization Grant (RFC 8628). Use it for a CLI, desktop application, or notebook where a person can sign in through a browser, even if the client runs on a remote machine. It requires native client 7.3.0 or Python client 6.1.0 or later, QuestDB Enterprise configured for OIDC, and an IdP application that allows the device grant.

1. The application calls sign_in() explicitly. The client requests a device code and displays the verification URL and user code.
2. The user opens the URL on any device and completes the IdP's sign-in and MFA steps. Passwords stay in the browser; the application does not collect them or need an inbound redirect listener.
3. The client polls the token endpoint until authorization succeeds, is denied, or the device code expires.
4. Attach the authentication object to the QuestDB pool or sender. It supplies cached or silently refreshed bearer credentials on later requests and reconnects without rebuilding clients.

Only sign_in() can prompt. Token reads, ingestion, query failover, and pool connection setup never launch a device flow. When no cached or silently refreshable credential is available, they report InteractionRequired; Python exposes this as OidcInteractionRequired. Arrange a new explicit sign-in on your UI thread rather than repeatedly retrying the data operation.

Discovery, scopes, and token selection

from_questdb reads the client ID, scope, endpoints, and groups mode from QuestDB's public /settings endpoint. Pass the IdP's issuer explicitly to pin the provider and discover its device-authorization endpoint from /.well-known/openid-configuration when QuestDB does not advertise one. Use HTTPS for QuestDB discovery and non-loopback IdP endpoints.

Explicit client ID, endpoint, scope, audience, and groups-mode arguments override discovered values. You can also skip QuestDB discovery and supply the client ID, device-authorization endpoint, token endpoint, and scope explicitly. The two credential endpoints must share an origin (scheme, host, and port); explicit endpoints configured together with an issuer must also match the issuer's origin.

The device-flow helper chooses the token to send based on QuestDB's mode:

• User Info mode uses the access token.
• acl.oidc.groups.encoded.in.token=true selects the ID token. With explicit client configuration, set groups_in_token=true and include openid in the scope so the IdP issues an ID token.

Silent refresh requires an IdP-issued refresh token. Most providers require offline_access and may require additional consent or IdP configuration. The client examples leave scope unset to preserve the scopes discovered from acl.oidc.scope. Add offline_access to that server setting, or pass a complete scope override that retains required openid, profile, groups, and API scopes. For example, a deployment using openid profile needs scope="openid profile offline_access", not just openid offline_access. An override replaces the whole scope string; the client does not add scopes automatically. Without a refresh token, sign in again after the token expires.

A retryable refresh failure uses bounded backoff. If a dispatched refresh loses its response, the IdP may already have rotated the refresh token; the client can discard that credential and require sign-in rather than risk reusing a rotated refresh token.

TLS trust and timeouts

The native and Python OIDC helpers use their own HTTPS client for QuestDB /settings discovery and all IdP requests. Configure its trust separately from the pool or sender: a pool's tls_roots does not configure the auth helper, and the helper's ca_bundle does not configure database connections.

The C/C++ and Python distributions trust the bundled Mozilla roots together with the OS trust store by default. Rust follows its enabled Cargo features: the default features, including when oidc is added, trust only the bundled Mozilla roots. Enable tls-native-certs as well to include the OS store, for example features = ["oidc", "tls-native-certs"] with default features enabled.

For private CAs, supply a PEM bundle before building the authentication object. A supplied bundle replaces the default roots, so include the CAs needed for both QuestDB discovery and the IdP. Use an expanded absolute path; the native builders do not expand ~.

Language	OIDC CA bundle	Per-request timeout example
Python	ca_bundle="/etc/questdb/oidc-ca.pem" on the constructor or from_questdb	timeout=10 (seconds)
Rust	.ca_bundle("/etc/questdb/oidc-ca.pem") on the builder	.timeout(std::time::Duration::from_secs(10))
C++	builder.ca_bundle("/etc/questdb/oidc-ca.pem")	builder.timeout_ms(10000)
C	questdb_oidc_builder_ca_bundle	questdb_oidc_builder_timeout_ms (milliseconds)

The OIDC request timeout defaults to 30 seconds, must be positive, and has a 120-second maximum. It bounds each discovery or IdP HTTP request, not the whole interactive sign-in, which can last until the device code expires.

A token lookup uses a valid cached token immediately. Otherwise it can make a silent refresh request or wait behind another thread already refreshing the shared provider. That wait is bounded by six times the OIDC timeout: three minutes by default, twelve minutes at the maximum. The sender's request_timeout, retry_timeout, connect_timeout, and reconnect budget do not cap this acquisition wait, which happens before the database request. Choose the OIDC timeout to fit the stalls your application can accept, and call sign_in() before connecting to establish the initial credentials. Transport operations never prompt for sign-in.

Credential storage and shutdown

Credentials remain in memory unless you opt into FileTokenStore (the default_file_token_store builder option in C/C++). Its default directory is ~/.questdb/oidc-tokens/; QUESTDB_CLIENT_OIDC_TOKEN_STORE_DIR overrides it with an absolute path. The store coordinates refresh across processes and uses atomic replacement for rotated credentials.

Plaintext token storage

The file store persists access, ID, and long-lived refresh tokens as plaintext JSON. On Unix, directories use 0700 and files use 0600; dedicate the directory to this store. Mutations currently require Unix/POSIX storage. Use memory-only auth on other platforms, or a custom Rust TokenStore backed by a keychain or secrets manager. Custom Python token stores are not supported. Never log tokens, authorization headers, or PGWire connection passwords.

Cancellation, credential removal, and shutdown are different operations:

• cancel_sign_in() cancels only the current attempt and leaves the shared auth usable. Python Ctrl-C has the same effect.
• clear() removes cached and persisted local credentials. It does not revoke them at the IdP or close an established database session.
• close() permanently disables the shared provider. Attached clients stop acquiring tokens on their next lookup; recovery requires a new provider and new clients. Close clients before closing their provider.
• Releasing a native handle or dropping a Rust reference only releases that reference; other attached clients retain shared ownership. Python auth context-manager exit calls close().

Providers and clients must not be inherited across fork(). Construct them in fresh worker processes, or fork before constructing any providers or clients.

Browser-based clients

If the tool is browser based and can handle HTTP redirects, it can implement two possible flows to request an access token.:

1. Authorization Code flow (Recommended, more secure)

2. Implicit flow

The Web Console implements the Authorization Code Flow with PKCE, which is a special version of the Authorization Code flow designed for mobile apps and single page applications.

Regardless of which flow is used by the web or mobile application, the requested access token can be used for authentication and authorization when communicating with QuestDB as explained in the above 7th step.

Jupyter notebook

For Python applications, prefer the client's device flow. It displays a clickable notebook prompt and works when the kernel and browser run on different machines, without collecting the user's password.

JupyterHub can integrate with OAuth2 providers using OAuthenticator, as described in its documentation. The OAuthenticator documentation also contains examples using different identity providers.

If Jupyter notebooks are used without JupyterHub, one option for OAuth2 integration is to use the Resource Owner Password Credentials (ROPC) flow. It is likely that enabling this flow in your OAuth2 provider will require additional setup.

caution

The Resource Owner Password Credentials flow is legacy, and should be used as a last resort.

We can use the code below to acquire an access token in our notebook:

from urllib import request, parse
import json

url = "https://oidc.provider:443/as/token.oauth2"
data = parse.urlencode( {
    "grant_type": "password",
    "username": "testuser",
    "password": "testpwd",
    "scope": "openid",
    "client_id": "testclient"
} ).encode()
req = request.Request(url=url, data=data)
req.add_header("Content-Type", "application/x-www-form-urlencoded")
with request.urlopen(req) as f:
    body = f.read().decode(f.headers.get_content_charset())
    resp = json.loads(body)
    access_token = resp["access_token"]

This token can be used to authenticate with QuestDB:

query = parse.urlencode({
    "query": "select current_user()"
})
req = request.Request(f"http://localhost:9000/api/v1/sql/execute?{query}")
req.add_header("Authorization", f"Bearer {access_token}")
with request.urlopen(req) as f:
    body = f.read().decode(f.headers.get_content_charset())
    resp = json.loads(body)
    print(resp)

Externalizing credentials

The above example saves the user's credentials into the notebook, potentially exposing them to others. One way to improve this is to use environment variables or files to externalize the username and password.

Here is an example using the dotenv library.

First we need to create a file named .env with the settings:

username=testuser
password=testpwd

Then load it in our notebook, and use it to request tokens:

from dotenv import load_dotenv
import os
from urllib import request, parse
import json

load_dotenv()
user = os.environ.get("username")
pwd = os.environ.get("password")

url = "https://oidc.provider:443/as/token.oauth2"
data = parse.urlencode( {
    "grant_type": "password",
    "username": user,
    "password": pwd,
    "scope": "openid",
    "client_id": "testclient"
} ).encode()
req = request.Request(url=url, data=data)
req.add_header("Content-Type", "application/x-www-form-urlencoded")
with request.urlopen(req) as f:
    body = f.read().decode(f.headers.get_content_charset())
    resp = json.loads(body)
    access_token = resp["access_token"]

Enable ROPC

The Resource Owner Password Credentials flow can be enabled in QuestDB within server.conf:

acl.oidc.ropc.flow.enabled = true

Note that the flow also has to be configured in the OAuth2/OIDC provider!

Now we can use Basic Authentication to simplify our code. We send the credentials to QuestDB, and the database will validate the credentials against the OAuth2 provider.

from dotenv import load_dotenv
import os
from urllib import request
import base64

load_dotenv()
user = os.environ.get("username")
pwd = os.environ.get("password")

query = parse.urlencode({
    "query": "select current_user()"
})
req = request.Request(f"http://localhost:9000/api/v1/sql/execute?{query}")
b64credentials = base64.standard_b64encode(f"{user}:{pwd}".encode()).decode()
req.add_header("Authorization", f"Basic {b64credentials}")
with request.urlopen(req) as f:
    body = f.read().decode(f.headers.get_content_charset())
    resp = json.loads(body)
    print(resp)

We can also use a postgres client to connect to the database:

note

QuestDB never persists the user's credentials.

import psycopg as pg
from dotenv import load_dotenv
import os

load_dotenv()
user = os.environ.get("username")
pwd = os.environ.get("password")

conn_str = f"user={user} password={pwd} host=localhost port=8812 dbname=qdb"
with pg.connect(conn_str, autocommit=True) as connection:
    with connection.cursor() as cur:
        cur.execute("select current_user()")
        records = cur.fetchall()
        for row in records:
            print(row)

CLI, standalone applications

Applications built with the C, C++, Rust, or Python clients can use the device flow. For other tools such as psql or Microsoft Access, token-as-password authentication is available through PGWire. The legacy Resource Owner Password Credentials flow is another option when the tool cannot acquire a token.

The user logs in with their SSO credentials, and the server validates the details with the OAuth2 provider:

% psql -h localhost -p 8812 -U testuser
Password for user testuser:
psql (14.2, server 11.3)
Type "help" for help.

testldap=>
testldap=>

Non-interactive clients

For services without a user present, use a managed identity, service principal, OAuth2 client-credentials flow, or a QuestDB service-account token. Interactive device sign-in is not a substitute for unattended credentials.

Rotating bearer tokens

A fixed token= is captured once and cannot follow credential rotation. A token provider supplies a current bearer token without recreating the pool, sender, or reader. Client examples:

• C and C++ 7.3.0 or later.
• Rust 7.3.0 or later.
• Python 6.1.0 or later.
• Java token providers.

The C, C++, Rust, and Python refreshing providers wrap a token source that your application supplies. Fetch a token and its expiry using your identity SDK, OAuth2 flow, or secrets manager; the provider handles caching, background refresh, concurrent acquisitions, expiry safeguards, and backoff. No Azure or other identity SDK is bundled with these clients. If your application already maintains a cache, attach an application provider instead.

Refreshing providers start fetching immediately. They normally refresh around half the token lifetime with jitter, honor earlier refresh hints, and retry failures with bounded backoff. By default, a token is handed out only while more than 60 seconds remain, and a caller without a usable token waits up to 30 seconds. Lower the handout floor for tokens whose full lifetime is at most 60 seconds. These proactive refresh rules apply to RefreshingTokenProvider, not the device-flow helper, which silently refreshes on demand.

Attach providers in code, not connection strings. In these four clients, token_provider, azure_resource, azure_client_id, and azure_credential are rejected connection-string keys. Choose one credential source: OIDC, a bearer-token provider, a fixed token, or username/password.

Token refresh and reconnects

Providers work with ILP/HTTP senders and QWP/WebSocket senders, readers, and pools, not TCP or UDP. Use https:: or wss::; plaintext transports warn but do not prevent bearer-token exposure.

• ILP/HTTP normally resolves a token once per flush and reuses it for ordinary HTTP retries. A transient acquisition failure or eligible HTTP 401 can cause another lookup.
• QWP/WebSocket resolves a token for each connect/reconnect round, including query failover, not for each batch on an established connection. Tokens are not replaced in-band on an open WebSocket.
• An HTTP 401 with no Bearer error or error="invalid_token" notifies the provider and allows one retry of the request or handshake if the token changed. A 403 or a challenge such as insufficient_scope does not trigger this refresh retry.
• A caller-awaited initial connection still fails on rejected credentials. An established QWP sender keeps reconnecting with capped backoff and retains unacknowledged data, including when fixed credentials are rejected. A failure to obtain credentials is reported as credential_unavailable, separately from the server rejecting them (auth_failed).

Retryability governs foreground retry decisions; it does not by itself stop background reconnects. To give up, close a standalone sender, or release all leases and close the pool. Returning a native or Python sender lease (including PooledSender.close()) does not stop its underlying connection's background delivery. Explicitly closing a shared OIDC or refreshing provider stops later token pulls for all attached clients; application-defined providers must implement their own shutdown policy. In particular, native token_provider::close() / questdb_token_provider_close is a no-op for application-defined providers. Shut down outside callbacks, normally closing clients before their provider. Provider shutdown does not delete queued disk-backed store-and-forward data, which a later process can replay. A query has its own failure and replay policy; see Authentication failures and token providers.

For Microsoft Entra ID server configuration and permissions, see Managed identities and service principals.

Legacy password-grant clients

Where the legacy Resource Owner Password Credentials flow is already enabled, a client can request a token itself and then use it for ingestion. The following examples capture fixed credentials; use a provider above for a long-running application:

import json
import os
import requests
import pandas as pd
from dotenv import load_dotenv
from questdb.ingress import Sender

load_dotenv()
user = os.environ.get("username")
pwd = os.environ.get("password")

token_endpoint = "https://oidc.provider:443/as/token.oauth2"
response = requests.post(token_endpoint,
                         data={"grant_type": "password",
                               "client_id": "testclient",
                               "username": user,
                               "password": pwd,
                               "scope": "openid"},
                         headers={"Content-Type": "application/x-www-form-urlencoded"})

response_body = response.content.decode("utf-8")
tokens = json.loads(response_body)
access_token = tokens["access_token"]

conf = f"http::addr=localhost:9000;token={access_token};"
with Sender.from_conf(conf) as sender:
    df = pd.read_csv("data.csv")
    df["ts"] = pd.to_datetime(df["ts"])
    sender.dataframe(df, table_name="foo", at="ts")

Alternatively, a user may rely on QuestDB to authenticate them via the OAuth2 provider when the Resource Owner Password Credentials flow is enabled on the server side:

import os
import pandas as pd
from dotenv import load_dotenv
from questdb.ingress import Sender

load_dotenv()
user = os.environ.get("username")
pwd = os.environ.get("password")

conf = f"http::addr=localhost:9000;username={user};password={pwd};"
with Sender.from_conf(conf) as sender:
    df = pd.read_csv("data.csv")
    df["ts"] = pd.to_datetime(df["ts"])
    sender.dataframe(df, table_name="foo", at="ts")

A token that a client requests itself expires, and the client keeps sending it unchanged. A client that runs longer than its token lives must switch to a new token before QuestDB stops accepting the old one, as described in Sessions and token expiry. Use a rotating bearer-token provider instead of copying one token into a long-lived client's configuration.

OIDC for the PGWire endpoint

If the Resource Owner Password Credentials (ROPC) flow is not an option, we can still authenticate via OIDC on the PGWire endpoint. However, in this case the client's responsibility to source the token required for authentication. This method works wherever a Postgres client library is available, including jupyter notebooks.

Token authentication for the PGWire endpoint should be enabled by adding the acl.oidc.pg.token.as.password.enabled=true setting to the server configuration.

The Python client's PGWire adapters pass a current token to psycopg or to each new SQLAlchemy physical connection. They accept both the device-flow helper and application token providers; connection setup never prompts. With other drivers, resolve the token in your pool's connection factory rather than capturing it when the pool is created.

The token should be sent in the password field, while the username field should contain the string _sso, or left empty if that is an option:

import psycopg as pg

token = "token_requested_from_the_oauth2_provider"

conn_str = f"user=_sso password={token} host=localhost port=8812 dbname=qdb"
with pg.connect(conn_str, autocommit=True) as connection:
    with connection.cursor() as cur:
        cur.execute('select current_user()')
        records = cur.fetchall()
        for row in records:
            print(row)

User permissions

QuestDB requires additional user information to be able to construct the user's access list.

As a reminder, the access list is the list of permissions that determines what the user can and cannot do.

QuestDB itself does not store external users, nor their passwords or any other authentication related detail.

External users and their authentication methods are managed by the Identity Provider.

Since external users are not managed by QuestDB, permissions cannot be granted to them directly.

Instead, the database expects a list of groups, called the groups claim to be present in the user information.

These external group names are mapped to QuestDB's own groups.

The access list of the external user consists of the permissions granted to those groups:

User permissions

Mapping user permissions

The mappings between external and QuestDB groups are managed with the following SQL commands:

Create a group which is mapped to an Active Directory group

CREATE GROUP groupName WITH EXTERNAL ALIAS 'CN=TestGroup1,OU=DC Users,DC=ad,DC=quest,DC=dev';

Map an Active Directory group to an already existing QuestDB group

ALTER GROUP groupName WITH EXTERNAL ALIAS 'CN=TestGroup1,OU=DC Users,DC=ad,DC=quest,DC=dev';

Remove an Active Directory mapping without deleting the QuestDB group

ALTER GROUP groupName DROP EXTERNAL ALIAS 'CN=TestGroup1,OU=DC Users,DC=ad,DC=quest,DC=dev';

See CREATE GROUP and ALTER GROUP for the full syntax.

External users cannot be given a query memory limit directly; ALTER USER ... SET MEMORY LIMIT is rejected for them. Set the limit on the mapped QuestDB group instead; see memory limits.

QuestDB works the list of external groups out from the User Info response message.

If we take the example used earlier, we will see that the message contains a claim called groups. This name is configurable in QuestDB.

If the groups claim is missing or holds no group, QuestDB rejects the login. To read the principal and the groups from different claims for different kinds of tokens, see User and group claims.

The user has to have at least the HTTP permission to be able to successfully login via the Web Console.

User info response example with Active Directory groups

{
  "sub": "externalUser",
  "name": "External User",
  "groups": [
    "CN=TestGroup1,OU=DC Users,DC=ad,DC=quest,DC=dev",
    "CN=TestGroup2,OU=DC Users,DC=ad,DC=quest,DC=dev"
  ]
}

Any change made to the user's group membership in the Identity Provider, QuestDB will adjust the user's access list.

note

There may be a slight delay due to the User Info Cache.

QuestDB will use the cached information until it becomes stale, and gets updated.

With acl.oidc.groups.encoded.in.token=true, QuestDB reads the groups from the token itself, so a change takes effect when the client presents a new token.

The same stands for changes made to the user's status within the Identity Provider.

For example, a disabled user will not be kicked out of QuestDB immediately.

The acl.oidc.cache.ttl config option drives how often user information should be synchronized with the Identity Providers.

It should be set accordingly to your organization's policies.

Other parts of the user information, such as the sub and the name also used by QuestDB.

They could be displayed in the Web Console, or appear in the logs, for example.

User and group claims

QuestDB takes the principal of an external user from the claim that acl.oidc.sub.claim names, sub by default, and the groups of the user from the claim that acl.oidc.groups.claim names. It reads both from the User Info response or, with acl.oidc.groups.encoded.in.token=true, from the token.

The principal identifies the external user: current_user() returns it, the Web Console displays it, and the server log records it. QuestDB keeps the groups of the latest login of each principal, so users who share a principal share their permissions. Pick a claim whose value the Identity Provider gives to one user or service only, such as a username or an object ID, and avoid display names, such as the name claim.

Fallback claim lists

In QuestDB Enterprise 4.1.0 and later, both settings accept a comma-separated list of claim names in priority order, so that one configuration accepts tokens of different shapes. With Microsoft Entra ID, for example, the ID tokens of users carry preferred_username and groups, while the app-only tokens of managed identities and service principals carry only oid and roles:

server.conf

# the ID tokens of users carry preferred_username, app-only tokens only oid
acl.oidc.sub.claim=preferred_username,oid
# app-only tokens carry roles, the ID tokens of users carry groups
acl.oidc.groups.claim=roles,groups

QuestDB picks the claims as follows:

• The list order decides. QuestDB takes the first claim on the list that carries a value, wherever the claims appear in the token or the User Info response.
• Missing, null, and empty claims fall through. A claim that is missing, null, or an empty string falls through to the next claim on the list, and so does a groups claim that holds no group name, such as an empty array. QuestDB skips null and empty group names, and reads the string "null" as null.
• Groups are not combined. QuestDB takes every group from one claim. With roles,groups, a user whose token carries a roles claim gets only the QuestDB groups that the roles map to.
• Claim shapes. Use a string for the principal, and an array of strings or a single string for the groups. QuestDB also accepts numbers and booleans as their text values. Unsupported containers, such as an array in a principal claim or an object in a listed claim, fail the login even when a claim earlier on the list carries a value.
• Only top-level claims count. QuestDB ignores nested claims, and the claims that the lists do not name, whatever their shape.

When none of the listed claims carries a value, QuestDB rejects the login, as described in Missing principal or groups claims.

Replicated clusters

Versions before 4.1.0 read the whole value of each setting as one claim name, and reject every OIDC login when it holds a list. Upgrade every node of a replicated cluster before you set a list.

Startup validation

With OIDC enabled, QuestDB refuses to start when either setting is empty, when a setting lists a claim twice, or when both settings list the same claim. An empty acl.oidc.sub.claim does not fall back to the default sub. The error names the setting and the claim:

required property is not set: acl.oidc.groups.claim
claim is listed more than once in acl.oidc.sub.claim [claim=oid]
claim cannot be listed in both acl.oidc.sub.claim and acl.oidc.groups.claim [claim=roles]

QuestDB trims the spaces around claim names, and skips empty entries.

Token validation

With acl.oidc.groups.encoded.in.token=true, QuestDB validates tokens itself, instead of asking the User Info endpoint of the provider. This applies to every JWT that QuestDB accepts in this mode: the ID tokens of Web Console users, and the tokens of other clients, such as the app-only access tokens of Microsoft Entra ID. QuestDB Enterprise 4.1.0 and later accept a token only when:

• its signature matches a signing key from the JWKS endpoint of the provider;
• it carries the sub, aud, and exp claims, and the iss claim unless issuer validation is disabled;
• it has not expired, with 60 seconds of allowed clock skew;
• its iss claim is a single string that equals the expected issuer;
• its aud claim contains acl.oidc.audience, the client ID by default;
• its sub claim is not empty, whatever acl.oidc.sub.claim names.

QuestDB caches the result for acl.oidc.cache.ttl, 30 seconds by default, so a token can stay accepted for that long after it expires, on top of the clock skew. In User Info mode, the default, QuestDB checks none of this itself: the User Info endpoint of the provider accepts or rejects the token.

The expected issuer

The expected issuer is the issuer field of the discovery document at acl.oidc.configuration.url. QuestDB does not derive it from the URL. To pin it, set acl.oidc.issuer, which takes precedence over the discovery document. QuestDB compares the issuer exactly and case-sensitively, including the path and any trailing slash.

Set acl.oidc.issuer in these cases:

• QuestDB uses acl.oidc.host and the endpoint settings instead of a discovery document. In token mode, QuestDB refuses to start without an expected issuer.
• The discovery document names a template instead of an issuer, as the multi-tenant endpoints of Microsoft Entra ID do. Set the issuer of your tenant, never a template or a wildcard.

The check stops QuestDB from accepting the tokens of another tenant, when a provider signs the tokens of several tenants with the same keys. Earlier versions don't check the iss claim, so they accept a token of any issuer that a trusted key signs for the expected audience.

With Microsoft Entra ID, the issuer depends on the version of the token: https://login.microsoftonline.com/<tenant ID>/v2.0 for v2.0 tokens, which the v2.0 discovery document names, and https://sts.windows.net/<tenant ID>/ for v1.0 tokens. With the v2.0 discovery document, Web Console users sign in through the v2.0 endpoints, so their ID tokens are v2.0 tokens. The access tokens that services request are v1.0 tokens, unless the QuestDB application asks for v2.0 tokens, as described in Managed identities and service principals.

For a provider whose tokens carry no iss claim, disable the check with acl.oidc.issuer.validation.enabled=false. QuestDB then accepts the tokens of any issuer that the provider signs for the expected audience, and logs an advisory at startup.

Sessions and token expiry

QuestDB checks a token when a request or a new connection presents it. An established connection, such as a QWP or PGWire connection, stays open after its token expires. Every new connection and reconnect presents a token again, so a client that runs longer than its tokens live must present new ones. Use device-flow authentication for interactive applications or rotating bearer-token providers for services.

The Web Console opens an HTTP session when the user logs in, identified by the qdb_session cookie, and also sends its token with every request. In QuestDB Enterprise 4.1.0 and later, a request that carries both the cookie and a token is authenticated with the token. When QuestDB rejects the token, the request fails with 401 Unauthorized, and the session ends. Earlier versions serve such a request from the session, so an expired or rejected token keeps working for as long as the cookie does.

QuestDB also rejects a token that it cannot check, because the Identity Provider is unreachable or answers with an error, once acl.oidc.cache.ttl has passed since the provider last checked it. In User Info mode, this affects every token. In token mode, QuestDB keeps the signing keys that it has downloaded, so it affects only a token signed with a key that QuestDB has not downloaded yet. An outage of the provider can therefore stop the queries of Web Console users, or log them out, as described below. A WebSocket connection that a browser opened with the session cookie stays open when the session ends.

For how sessions treat passwords and REST tokens, see HTTP sessions.

Web Console token renewal

The Web Console sends a token with every request: the access token in User Info mode, or the ID token with acl.oidc.groups.encoded.in.token=true. It renews its tokens with the refresh token that the provider issued at login. Without a refresh token, the user is logged out once QuestDB stops accepting the token of the login. Some providers, such as Microsoft Entra ID, issue a refresh token only for the offline_access scope, which acl.oidc.scope must then include.

In QuestDB Enterprise 4.1.0 and later, the Web Console renews its tokens as follows:

• When. Before a request, when the token expires within 30 seconds. The Web Console reads the expiry of an ID token from its exp claim, and the expiry of an access token from the expires_in field of the token response. The request waits up to 10 seconds for the new tokens.
• ID tokens are required. With acl.oidc.groups.encoded.in.token=true, every token response must include an ID token. Without one, the Web Console logs the user out with Missing ID token. Configure the provider to return an ID token for the refresh token grant, and include openid in acl.oidc.scope.
• A rejected token. When QuestDB answers 401 Unauthorized, for example because the clock of the browser is behind, the Web Console renews its tokens and sends the request again, once. It logs the user out when QuestDB rejects the new token too.
• A refused renewal. When the provider refuses to renew the tokens, for example because the user is disabled or the refresh token has expired, the Web Console logs the user out, and the login page shows the reason that the provider gives.
• A failed renewal. When the provider does not answer within 10 seconds, cannot be reached, or answers with 429 or a 5xx status, the Web Console keeps the user logged in, and shows a warning with a Sign in again button. While the current token is valid, requests use it, and later requests retry the renewal, with a delay that grows from 5 to 30 seconds between attempts. Once the token has expired, the Web Console does not send it: a query fails with Could not renew your SSO session; the request was not executed. Run it again after a short wait to retry the renewal, or sign in again.

Troubleshooting

Missing principal or groups claims

When the user information carries none of the listed principal claims, or none of the listed groups claims, QuestDB rejects the login: HTTP clients receive 401 Unauthorized, and PGWire clients invalid username/password. The server log shows both lists and the user information that QuestDB received:

Failed to find required claims [subClaims=[preferred_username,oid], groupsClaims=[roles,groups], userInfo={...}]

Compare the claims in userInfo with the lists. A provider leaves out the claims that no requested scope covers: Microsoft Entra ID, for example, sends preferred_username only for the profile scope. Versions before 4.1.0 log the two claim names instead: Failed to find required claims [claims=sub,groups userInfo=...].

Token validation errors

In token mode, QuestDB rejects a token that fails token validation the same way, and the server log gives the reason:

Request failed [errorMsg=Failed to decode JWT token Error(InvalidIssuer)]
Request failed [errorMsg=Failed to decode JWT token Error(MissingRequiredClaim("iss"))]
Request failed [errorMsg=Failed to decode JWT token Error(InvalidAudience)]
Request failed [errorMsg=Failed to decode JWT token Error(ExpiredSignature)]
Request failed [errorMsg=Failed to decode JWT token Error(MissingRequiredClaim("exp"))]

• InvalidIssuer: the iss claim is not the expected issuer. Compare the iss claim of a token with acl.oidc.issuer, or with the issuer field of the discovery document. With Microsoft Entra ID, this usually means a v1.0 access token, or a token of another tenant.
• MissingRequiredClaim("iss"): the token carries no iss claim. See The expected issuer.
• InvalidAudience: the aud claim does not contain acl.oidc.audience. The token was requested for another application or, with Microsoft Entra ID, it is a v1.0 access token.
• ExpiredSignature: the token has expired. The client must present a new token. For the Web Console, check that it receives a refresh token, as described in Web Console token renewal.

Web Console sign-in errors

When the Web Console cannot complete a sign-in, the login page shows the reason. QuestDB logs nothing, because the browser exchanges the authorization code at the token endpoint of the provider directly:

• The identity provider did not respond within 10 seconds: the token endpoint did not answer in time.
• Could not reach the identity provider to complete the sign-in: the browser could not call the token endpoint. Usually, the provider does not allow the origin of the Web Console, such as https://questdb.example.com:9000, in its CORS settings.
• The identity provider returned an invalid token response, or The identity provider returned an unexpected response (HTTP <status>): the token endpoint answered with something other than a token response, such as the error page of a proxy.
• Missing ID token: with acl.oidc.groups.encoded.in.token=true, the provider returned no ID token. Include openid in acl.oidc.scope.
• Other messages usually come from the provider, such as the error for a redirect URI that the provider does not accept.

The network tab of the developer tools of the browser shows the request to the token endpoint and the response of the provider.

Configuration options

For all OIDC-related configuration options of QuestDB, see Configuration.

Active Directory

The following sections are guides for setting up single sign-on (SSO) with various OAuth2 providers, and for authenticating Azure services with Microsoft Entra ID.

PingFederate

This document helps set up SSO authentication for the Web Console in PingFederate.

It is assumed that the Azure Active Directory serves as the Identity Provider (IdP).

Set up PingFederate client

First thing first, let's pick a name for the client!

Picking a name

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.

As shown above, leave the client authentication disabled.

We also have to white list the URL of the Web Console as a redirection URL:

Whitelist the redirection URL

We can instruct PingFederate to automatically authorize the scopes requested by the Web Console.

The user will not be presented the extra window asking for consent after authentication:

Bypass, please

The Web Console uses the Authorization Code Flow, and refreshes tokens automatically.

Next, enable the grant types required for this flow:

Granted

We've selected:

• Authorization Code
• Refresh Token
• Access Token Validation (Client is a Resource Server)

After that, select the token manager for the client.

The token manager is responsible for issuing access tokens.

All token related settings should be configured in the token manager.

PKCE enabled

Finally, enable PKCE - as shown above - and save the settings.

Access Token Manager settings

QuestDB does not require any special setup regarding the access token.

We recommend that you do not to use shorter tokens than the default 28 characters.

As the QuestDB Web Console refreshes the token automatically, there is no need for long-lived tokens:

Click to zoom

We've selected:

• Token length: 28
• Token lifetime: 5
• Lifetime extension policy: None
• Maximum token lifetime: Null
• Lifetime extension threshold percentage: 30

For the next step, we tune the Authorization Server.

Authorization Server settings

These settings relate to the authorization code, refresh token and CORS.

Authorization server

In this section, we've entered:

• Authorization code timeout: 60
• Authorization code entropy: 30
• Client secret retention period: 0

Next, ensure the ROLL REFRESH TOKEN VALUES option is selected:

Click to zoom

It is also important to whitelist the Web Console's URL on the CORS list:

Port 9000, or your custom port

Set up a Microsoft Entra ID Data Source

PingFederate needs a Data Source setup.

This is a secure LDAP connection to Microsoft Entra ID, formerly known as Azure Active Directory.

The data source needs a:

• name
• hostname
• port
• username and password for the LDAP connection

Configuring our data source

We have given it the name EntraDS and it will be applied later.

Set up a Password Credential Validator

Now that PingFederate has an LDAP connection, we can use it for authentication.

First, create a Password Credential Validator:

Create the PCV

We've entered:

• Instance name: EntraPCV
• Instance ID: EntraPCV
• Selected: LDAP Username Password Credential Validator
• Parent instance: None

Furthermore, we now declare our previously created data source (EntraDS):

Click to zoom

This links our data store (EntraDS) to our PCV (EntraPCV).

Set up an Identity Provider

We can use our PCV once we set up an Identity Provider.

The IdP will be used to authenticate users against Active Directory using the LDAP connection.

We do this in the Type subsection:

Defining an adapter

Next, in the IdP Adapter section...

Click: Add a new row to Credential Validators.

Select the PCV (EntraPCV) we created.

Optionally alter number of retries:

Select the PCV

Add groups to OIDC policy management

QuestDB now needs to know about the user's AD group memberships to find their permissions.

Groups are passed to QuestDB inside the User Info object in a custom claim.

This has to be added in the OpenID Connect Policy Management.

The field is Multi-Valued, because it is a list of group names.

Under the Attribute Contract subsection, see:

Click to zoom

Next, click to the Attribute Scopes subsection.

Ensure groups is among the openid attributes:

Click to zoom

Onwards to the Attribute Sources & User Lookup Section.

From this view, you can add local data stores.

Note item test of type of LDAP:

Click to zoom

We created it via the following choices in Add Attribute Source:

Click to zoom

Note where we specified the Data Store (EntraDS).

This is also where the directory search parameters are defined.

Back at the Attribute Sources & User Lookup Section section, note we have set email.

The source is LDAP (test), while the value is usePrincipalName:

Click to zoom

And finally!

In the same Attribute Sources & User Lookup Section...

Find groups.

Note the definition of Source (LDAP (test)) that bridges our various parts.

The value is memberOf.

Click to zoom

Enable Resource Owner Password Credentials (ROPC) flow

As described in the OIDC operations document tools - such as psql - can be integrated with the OIDC provider using the ROPC flow.

When setting this flow up, enable the Resource Owner Password Credentials flow in the client settings.

Next, create a Resource Owner Credentials Grant Mapping to map values obtained from the Password Credential Validator (PCV) into the persistent grants.

When setting this up, select the previously created LDAP Data Source and IdP Adapter, which links to the existing PCV.

Then select the username attribute of the PCV as USER_KEY.

Confirm QuestDB mappings and login

QuestDB requires a mapping, as laid out in the OIDC operations document.

If a given user has the HTTP permission, they will be able to now login via the Web Console.

To test, head to http://localhost:9000 and login.

If all has been wired up well, then login will succeed.

Microsoft EntraId

This document sets up SSO authentication for the QuestDB Web Console in Microsoft EntraID, formerly known as Azure AD.

Azure services that authenticate without a user, as a managed identity or a service principal, build on this setup, as described in Microsoft Entra ID managed identities and service principals.

tip

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.

App 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.

Add SPA platform with the redirection 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.

Application 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.

PKCE and CORS

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.

Enable ROPC

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.

Token customization

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.

API permissions

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.

Add openid 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.

Permissions final list

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 user's sign-in name, which needs the profile scope
acl.oidc.sub.claim=preferred_username

# 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.

Application overview

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 a group which is mapped to an Entra ID group

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 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 database permissions

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. If the login fails, the login page or the server log gives the reason, as described in Troubleshooting.

Microsoft Entra ID managed identities and service principals

Azure services, such as an ingestion job on a virtual machine or in Azure Kubernetes Service, can authenticate to QuestDB without a user. They request an app-only access token from Entra ID, as a managed identity or a service principal. An app-only token carries no preferred_username and no groups claim: it carries the object ID of the service in oid, and the app roles assigned to the service in roles. QuestDB Enterprise 4.1.0 and later accept these tokens next to the ID tokens of users, with fallback claim lists.

This setup builds on the QuestDB application and the QuestDB configuration of the Microsoft Entra ID guide above. QuestDB reads the principal and the roles of a service from its token, and validates the token itself, so OIDC must run in token mode, with acl.oidc.groups.encoded.in.token=true, as described in Token validation.

First, configure the QuestDB application in Entra ID:

1. Under Expose an API, set the Application ID URI, such as api://8de84b90-1ea5-4e41-9e84-dba860aa01a6. Services request their tokens for this URI.
2. Under App roles, create a role for each kind of access, such as a role with the value QuestDB.Ingest, and set its Allowed member types to Applications. Users then never carry the role, so their tokens keep their groups, which roles,groups would otherwise replace.
3. In the Manifest, set requestedAccessTokenVersion to 2, called accessTokenAcceptedVersion in the legacy manifest. The access tokens of services then carry the application ID in their aud claim, and the v2.0 issuer, which QuestDB expects, as described in The expected issuer.

Then assign the role to the service. For a service principal, add the role under API permissions of its own app registration, as an application permission of the QuestDB application, and grant admin consent. A managed identity has no app registration: assign the role through Microsoft Graph, for example with the Azure CLI, using the object IDs of the managed identity and of the enterprise application of QuestDB, and the ID of the app role:

Assign the QuestDB.Ingest role to a managed identity

MI="<managed identity object ID>"
APP="<QuestDB enterprise application object ID>"
ROLE="<app role ID>"
az rest --method POST \
  --uri "https://graph.microsoft.com/v1.0/servicePrincipals/$MI/appRoleAssignments" \
  --body "{\"principalId\": \"$MI\", \"resourceId\": \"$APP\", \"appRoleId\": \"$ROLE\"}"

In server.conf, keep the QuestDB configuration of the Entra ID guide, and replace its two claim settings with fallback lists. These are the settings that the tokens of services depend on:

server.conf

acl.oidc.enabled=true
# QuestDB validates the tokens itself, and reads the claims from them
acl.oidc.groups.encoded.in.token=true
# the v2.0 discovery document of the tenant names the expected issuer
acl.oidc.configuration.url=https://login.microsoftonline.com/12345678-1234-1234-1234-123456789abc/v2.0/.well-known/openid-configuration
# the application ID, also the expected audience of the tokens
acl.oidc.client.id=8de84b90-1ea5-4e41-9e84-dba860aa01a6
# users have preferred_username, services only oid
acl.oidc.sub.claim=preferred_username,oid
# services have roles, users have groups
acl.oidc.groups.claim=roles,groups

Keep the settings that only the Web Console uses, such as acl.oidc.redirect.uri and acl.oidc.scope, as they are.

The principal of a service is then its object ID, which current_user() returns. Map each app role to a QuestDB group, and grant the group the permissions that the service needs. QWP over WebSocket, which the clients use for ingestion, requires the HTTP endpoint permission:

Allow the services with the QuestDB.Ingest role to ingest into fx_trades

CREATE GROUP fxIngest WITH EXTERNAL ALIAS 'QuestDB.Ingest';
GRANT HTTP TO fxIngest;
GRANT INSERT ON fx_trades TO fxIngest;

Ingestion cannot create a table or add a column for an external user, so create the table, with every column that the service writes, in advance, as a database user such as the built-in admin:

Create the table that the service ingests into

CREATE TABLE fx_trades (
    timestamp TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    quantity DOUBLE
) TIMESTAMP(timestamp) PARTITION BY DAY;

The Java client requests the token of the service and renews it by itself, with token_provider=azure and the questdb-client-azure artifact. The token provider requires the wss schema, so QuestDB must serve HTTP over TLS:

Ingest into fx_trades as the managed identity of the host

import java.time.Instant;

import io.questdb.client.QuestDB;
import io.questdb.client.Sender;

try (QuestDB db = QuestDB.connect(
        "wss::addr=questdb.example.com:9000;"
        + "token_provider=azure;"
        + "azure_resource=api://8de84b90-1ea5-4e41-9e84-dba860aa01a6;"
        + "azure_credential=managed_identity;")) {
    try (Sender sender = db.borrowSender()) {
        sender.table("fx_trades")
              .symbol("symbol", "EURUSD")
              .symbol("side", "buy")
              .doubleColumn("price", 1.0842)
              .doubleColumn("quantity", 100000.0)
              .at(Instant.now());
    }
}

Python 6.1.0 or later and the C/C++ and Rust clients from 7.3.0 can also follow token rotation without recreating clients. Supply an application token source that requests the scope api://<application ID>/.default using Azure Identity or your identity SDK, and attach a shared refreshing provider. These clients do not bundle an Azure SDK or accept Java's token_provider=azure connection-string keys.

For clients or versions without provider support, request a fixed token with the Azure Identity library and create a new client with a new token before the old one expires.

QuestDB reads the roles from the token, so a role that you assign to or remove from a service takes effect when the service presents a new token. Azure caches the tokens of managed identities for around 24 hours. To cut off a service at once, revoke the permissions of its QuestDB group.