OpenID Connect (OIDC) Integration

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.

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.

note

OpenID Connect (OIDC) is only available in QuestDB Enterprise.

Active Directory

See our example using PingFederate with Active Directory.

Architecture overview

Altogether, the architecture appears as such:

Overall architecture — 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.

The Identity Provider holds user identities and user information, capable of authenticating users, and to issue an ID Token which uniquely identify them.
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.

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/exec?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

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

Authorization Code flow (Recommended, more secure)
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

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 flow. It is likely that to enable this flow in your OAuth2 provider will require additional setup. 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/exec?{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. See an example using PingFederate with Active Directory.

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/exec?{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

When using CLI tools, such as psql, or standalone applications like Microsoft Access, the best option may be the 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

Non-interactive clients are usually jobs or standalone applications, such as a client for ingesting data. It is practical to manage their credentials via an OAuth2 provider too.

As seen in the Jupyter notebook examples, the clients can request a token themselves and then use it to authorise data ingestion:

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")

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:

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';

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 it is an empty list, the user cannot access the database.

Although the user is authenticated, they have no permissions at all.

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.

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.

Configuration options

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

Active Directory​

Architecture overview​

Web Console​

OIDC Provider​

QuestDB​

Authentication and Authorization Flow​

1. Secret generation​

2. Prove identity​

3. Scope consent​

4. Redirection​

5. Credential request​

6. Credentials received​

7. Database access​

8. Find user information​

9. Receive user information​

10. Permission check​

Interactive clients​

Browser-based clients​

Jupyter notebook​

Externalizing credentials​

Enable ROPC​

CLI, standalone applications​

Non-interactive clients​

User permissions​

Mapping user permissions​

Configuration options​

Active Directory

Architecture overview

Web Console

OIDC Provider

QuestDB

Authentication and Authorization Flow

1. Secret generation

2. Prove identity

3. Scope consent

4. Redirection

5. Credential request

6. Credentials received

7. Database access

8. Find user information

9. Receive user information

10. Permission check

Interactive clients

Browser-based clients

Jupyter notebook

Externalizing credentials

Enable ROPC

CLI, standalone applications

Non-interactive clients

User permissions

Mapping user permissions

Configuration options