For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

OIDC client integration patterns

How a client authenticates depends on what it can do: follow a browser redirect, prompt a person, or neither. This page covers each case, from a browser-based UI running the Authorization Code Flow to an unattended ingestion job holding a token it acquired out of band.

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

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 (deprecated). It cannot use PKCE, so it does not satisfy acl.oidc.pkce.required, which is true by default.

The client can read the authorization and token endpoints and QuestDB's token selection mode from the settings endpoint instead of hard coding them. The client ID and scope published there belong to the Web Console registration. Reuse them only for evaluation; register a production application separately and use its own client ID, redirect URI, scopes, and provider policies.

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 token can be used for authentication and authorization when communicating with QuestDB. Select the access or ID token as described in Which token to send.

Jupyter notebook​

Use the official Python client's OIDC device flow. It works when the browser and the notebook kernel are on different machines, and does not put the user's password in the notebook.

Note that JupyterHub's OAuth integration (OAuthenticator) is a separate concern: it decides who may log in to JupyterHub, and gives the notebook no token to send to QuestDB.

For tooling without device-flow support, a last-resort option is the Resource Owner Password Credentials (ROPC) flow. Enabling it usually requires additional provider configuration.

caution

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

The ROPC snippets below assume acl.oidc.groups.encoded.in.token=false and therefore send the access token. If the setting is true, the client must send the ID token instead, and the provider must issue one for the password grant. The official device-flow client selects the correct token automatically.

As a fallback, the code below acquires an access token with ROPC:

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"https://questdb.example.com: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:

.env
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:

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 parse, request
import base64
import json

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

query = parse.urlencode({
    "query": "select current_user()"
})
req = request.Request(f"https://questdb.example.com: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​

For a CLI or standalone application built with an official Java, Python, Rust, C, or C++ client, use the OIDC device flow. Tools such as psql and Microsoft Access cannot run the flow themselves; for those tools, acquire a token separately and use PGWire token authentication, or enable the legacy Resource Owner Password Credentials flow.

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​

An unattended job cannot complete device flow because that flow always requires a person. Prefer a dedicated QuestDB service account with a REST token. This keeps the job separate from human identities and gives it an explicit, minimal permission set:

Create credentials for an ingestion job
CREATE SERVICE ACCOUNT ingest_job;
GRANT HTTP TO ingest_job;
GRANT INSERT ON trades TO ingest_job;
ALTER SERVICE ACCOUNT ingest_job CREATE TOKEN TYPE REST WITH TTL '30d' REFRESH;

The final statement displays the token once. Save it in a secrets manager and pass it to the client over TLS, for example through a protected environment variable:

Use the service-account token
import os
import questdb

token = os.environ["QUESTDB_TOKEN"]
conf = f"wss::addr=questdb.example.com:9000;token={token};"

with questdb.connect(conf) as db:
    with db.query("SELECT current_user()") as result:
        print(result.to_pandas())

The REFRESH modifier extends the REST token's expiry after successful authentication to the primary. Review its replication behavior and revocation commands in the ALTER SERVICE ACCOUNT reference.

If organizational policy requires provider-managed machine identities, give the job its own confidential application or service principal and use a machine-to-machine grant supported by that provider, commonly the OAuth 2.0 client credentials grant. Do not use QuestDB's published Web Console client ID or a human's username and password. The token must expose the subject and group information required by QuestDB's configured validation mode; there is no provider-independent client-credentials setup in QuestDB. Many providers do not issue ID tokens for client credentials, so verify this provider-specific path before choosing encoded-token mode.

ROPC authenticates a human by handing their password to the client. It is a legacy fallback, not a machine-identity mechanism. Use it only for tooling that cannot accept a separately acquired token, as described in Enable ROPC.

OIDC for the PGWire endpoint​

Clients such as psql that cannot run the device flow can still authenticate via OIDC on the PGWire endpoint by obtaining an OAuth2 token separately and sending it as the password. In this case, it is the client's responsibility to source the token required for authentication. This method works wherever a PostgreSQL client library is available, including Jupyter notebooks.

Prefer this approach to the legacy Resource Owner Password Credentials (ROPC) flow. If sourcing a token separately is not practical and the provider supports ROPC, enable QuestDB to run that flow as the fallback.

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 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. Send the token selected by Which token to send: the access token by default, or the ID token when group memberships are encoded in it.

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)

See also​