# OIDC client integration patterns

> **For AI agents:** the complete documentation index is at [llms.txt](https://questdb.com/docs/llms.txt). Every page is available as markdown by appending `.md` to its URL, or by sending an `Accept: text/markdown` request header.

How browser clients, Jupyter notebooks, CLI tools, unattended jobs, and PGWire clients authenticate against QuestDB Enterprise with OIDC.

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](https://openid.net/specs/openid-connect-core-1_0.html#CodeFlowAuth)
   flow **(Recommended, more secure)**

2. [Implicit](https://openid.net/specs/openid-connect-core-1_0.html#ImplicitFlowAuth)
   flow **(deprecated)**. It cannot use PKCE, so it does not satisfy
   [`acl.oidc.pkce.required`](/docs/configuration/oidc/#acloidcpkcerequired),
   which is `true` by default.

The client can read the authorization and token endpoints and QuestDB's token
selection mode from the
[settings endpoint](/docs/security/oidc/client-discovery/#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](https://oauth.net/2/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](/docs/security/oidc/client-discovery/#which-token-to-send).

### Jupyter notebook

Use the official Python client's
[OIDC device flow](/docs/security/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 <a href="https://datatracker.ietf.org/doc/html/rfc6749#section-1.3.3" target="_blank" rel="noopener noreferrer">
Resource Owner Password Credentials (ROPC) </a> 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:

```python
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:

```python
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:

```ini title=".env"
username=testuser
password=testpwd
```

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

```python
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`:

```ini title="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.

```python
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.

:::

```python
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](/docs/security/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](#oidc-for-the-pgwire-endpoint), 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:

```shell
% 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:

```questdb-sql title="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:

```python title="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](/docs/query/sql/acl/alter-service-account/#add-rest-api-token).

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](/docs/security/oidc/client-discovery/#which-token-to-send);
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](#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](#enable-ropc) 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](/docs/security/oidc/client-discovery/#which-token-to-send): the access token by
default, or the ID token when group memberships are encoded in it.

```python
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

- [Client discovery and tokens](/docs/security/oidc/client-discovery/) - find
  the provider endpoints and select the token QuestDB expects
- [OIDC device flow](/docs/security/oidc/device-flow/) - interactive sign-in
  for CLI applications, containers, and remote notebooks
- [Authentication and endpoint access](/docs/security/rbac/authentication/) -
  grant the endpoint permission each client protocol requires