# DuckDB authentication providers: Keycloak, Google, Azure AD, Cognito > Enable and configure each authentication provider for DuckDB clients: built-in passwords, external JWT, Keycloak, Google, Azure AD, Cognito, UI SSO. Quack-on-Demand supports multiple authentication providers that can be enabled independently. Any combination may be active at the same time: the edge tries each enabled provider in order and accepts the first success. The full chain logic, session TTL, and how tenant identity is derived from claims are covered in the Authentication page. This page focuses on enabling and configuring each provider. See [/reference/configuration](/qod/reference/configuration) for the complete list of tunables. --- ## Database (built-in) Validates username and password against the `qodstate_user` table in the control-plane Postgres using bcrypt. Enabled by default so the bootstrap admin works out of the box. The authenticator runs one of two queries, picked by the caller-declared auth realm: - `systemQuery` - used when the caller asked for the **system** realm (manager UI login with empty tenant; FlightSQL handshake with `?superuser=true`). Matches the row with `tenant IS NULL` - the bootstrap admin / system superuser. - `tenantQuery` - used when the caller asked for the **tenant** realm. Matches the row with `tenant = ?`. There is no fallback between the two: a system credential cannot authenticate a tenant-scoped login and vice versa. ```bash QOD_AUTH_DB_ENABLED=true # default true; set false to disable entirely # Override if the auth DB differs from the control-plane Postgres QOD_AUTH_DB_JDBC_URL=jdbc:postgresql://host:5432/qod QOD_AUTH_DB_USER=postgres QOD_AUTH_DB_PASSWORD=secret # Custom lookup queries # - systemQuery placeholders in order: username # - tenantQuery placeholders in order: tenant, username # Both MUST return four columns, in order: # password_hash, kind, enabled, must_change_password # The last two are mandatory: a shorter projection is refused at boot and # fails every login (no tolerant default-to-enabled / default-to-unflagged). QOD_AUTH_DB_SYSTEM_QUERY="SELECT password_hash, kind, enabled, must_change_password \ FROM qodstate_user WHERE tenant IS NULL AND username = ? LIMIT 1" QOD_AUTH_DB_TENANT_QUERY="SELECT password_hash, kind, enabled, must_change_password \ FROM qodstate_user WHERE tenant = ? AND username = ? LIMIT 1" ``` The second column is the account kind (`admin` or `user`). The column was named `role` before the [built-in RBAC release](/qod/operating/rbac-model#upgrading-to-the-built-in-rbac-release): a custom query that still selects `role` from `qodstate_user` fails at boot. `QOD_ADMIN_PASSWORD` is used on the first boot only, to create each admin row that is missing; changing it and restarting rotates nothing. Rotate the password with `qod auth change-password`, and recover a lost one with `qod admin reset-password` (works with the manager down). The seeded admin's account kind is `QOD_ADMIN_KIND` (default `admin`; formerly `QOD_ADMIN_ROLE`, now ignored). On the management plane (REST/UI), DB credentials are accepted when `auth.management.identitySource=db` (the default). Setting it to `oidc` skips the DB authenticator on the management login even with this provider enabled; the edge keeps using it. > **Upgrading from a single-query deployment**: the old `QOD_AUTH_DB_QUERY` setting is gone, along with the `(tenant IS NULL OR tenant = ?)` wildcard fallback. Pre-existing JDBC URLs that used the bootstrap admin (`admin@localhost.local`) against tenants must now add `?superuser=true` to the URL. There is no migration on the `qodstate_user` table itself. ### Account lockout and password reset `qodstate_user` has an optional `email` column, set with `qod user create --email` / `qod user update --email` (or the REST `user/create` / `user/update` body). A row with an email can use the self-service password reset flow described below and is eligible for lockout; a row with a non-email username and no email cannot reset by email and is never locked out. An email-format username is auto-assigned `email = username`, so it is lockable and reset-eligible - and this includes the default seeded admin `admin@localhost.local`, whose username is email-format. A locked superuser is still recoverable without the email flow: restarting the manager re-seeds the admin (resetting the password to `QOD_ADMIN_PASSWORD` and clearing `failed_attempts` / `locked_at` in the same statement), and the static `X-API-Key` bypasses login lockout entirely. Because `admin@localhost.local` is not a routable mailbox, the seeded admin's self-service email reset will not deliver by default - set `QOD_ADMIN_USERNAME` to a real deliverable address for the admin to self-recover by email, otherwise use restart or the API key. When the username itself is in email format, `email` is derived from it automatically and cannot be set to anything else: `user/create` / `user/update` (CLI and REST) and manifest import reject a conflicting value with `400 invalid_email`. Existing rows whose username is email-format were backfilled to match on upgrade. **SMTP.** Reset links are delivered by mail. With `QOD_SMTP_HOST` unset (the default) the manager logs the mail instead of sending it - fine for local dev, useless for real users. ```bash QOD_SMTP_HOST=smtp.example.com QOD_SMTP_PORT=587 # default QOD_SMTP_USER=apikey QOD_SMTP_PASSWORD=secret QOD_SMTP_FROM="no-reply@quack-on-demand.local" # default QOD_SMTP_STARTTLS=true # default # Externally visible origin used to build the mailed link # ($QOD_PUBLIC_BASE_URL/ui/reset-password?token=...). Left empty, the link is # host-relative and the manager logs a boot warning. QOD_PUBLIC_BASE_URL=https://qod.example.com ``` **Self-service reset flow.** ```bash # Public endpoint - no API key. Always returns 200, whether or not the # account exists or has an email, so the response can't be used to enumerate # users. Only sends mail when the account exists, has an email, and the # per-account rate limiter admits. curl -sS -X POST http://localhost:20900/api/auth/forgot-password \ -H 'Content-Type: application/json' \ -d '{"username":"alice","tenant":"acme"}' # Redeem the token from the emailed link. Also public. curl -sS -X POST http://localhost:20900/api/auth/reset-password \ -H 'Content-Type: application/json' \ -d '{"token":"","newPassword":"a-new-password"}' ``` The token is a stateless, single-use, 1-hour HS256 JWT (`ResetTokenStore`), signed with the same secret as `QOD_SESSION_JWT_SECRET`. Single-use is enforced by embedding a fingerprint of the password hash at mint time rather than a server-side redemption ledger, so any password change (including the reset itself) invalidates every other outstanding link for that user. CLI: `qod auth forgot-password --username --tenant `, `qod auth reset-password` (prompts for the token and new password). **Account lockout** is opt-in (`QOD_AUTH_LOCKOUT_ENABLED`, default `false`) and locks a row after `QOD_AUTH_LOCKOUT_MAX_FAILURES` (default `10`) consecutive failed passwords - but only rows with an email; a row with a non-email username and no email is never locked. An email-format username carries `email = username`, so the seeded admin `admin@localhost.local` is lockable too. Turning lockout on without a configured SMTP relay fails boot with an error naming `QOD_SMTP_HOST`, since a locked-out user would otherwise have no way back in. A locked login gets `401 account_locked` with a message pointing at the forgot-password flow. Recovery is either the self-service reset above or an admin password reset (`user/update` with a new `password`); both clear the failure count and the lock as part of writing the new password. A locked seeded superuser additionally recovers on a manager restart (re-seeding rewrites the password and clears the lock) or via the static `X-API-Key`, which bypasses login lockout. --- ## External JWT Validates Bearer tokens signed with a shared HMAC secret (HS256/HS512) or an RSA/EC public key. This provider is for custom JWT issuers that are not OIDC-compliant. It is activated automatically when either `JWT_SECRET_KEY` or `JWT_PUBLIC_KEY_PATH` is set; there is no separate enable flag. ```bash # Option A: shared HMAC secret JWT_SECRET_KEY=your-shared-secret # Option B: RSA or EC public key (PEM file, PKCS#8 format) JWT_PUBLIC_KEY_PATH=/etc/quack/jwt-public.pem # Optional validation JWT_ISSUER=https://your-issuer.example.com JWT_AUDIENCE=quack-on-demand # leave empty to skip audience check ``` If both `JWT_SECRET_KEY` and `JWT_PUBLIC_KEY_PATH` are set, either a valid HMAC or a valid RSA/EC signature is accepted. Token username is resolved from `preferred_username`, then `email`, then `sub`. --- ## Keycloak Validates OIDC Bearer tokens via the Keycloak JWKS endpoint. Also supports the OAuth2 Resource Owner Password Credentials (ROPC) grant for clients that send Basic `username:password` credentials directly, such as JDBC drivers and BI tools. ```bash QOD_AUTH_KEYCLOAK_ENABLED=true QOD_AUTH_KEYCLOAK_BASE_URL=https://keycloak.example.com QOD_AUTH_KEYCLOAK_REALM=your-realm QOD_AUTH_KEYCLOAK_CLIENT_ID=quack-client QOD_AUTH_KEYCLOAK_CLIENT_SECRET=client-secret ``` The JWKS URL is derived automatically as `{baseUrl}/realms/{realm}/protocol/openid-connect/certs`. For ROPC, the token endpoint is `{baseUrl}/realms/{realm}/protocol/openid-connect/token`. --- ## Google Validates OIDC Bearer tokens issued by Google (`https://accounts.google.com`). Google does not support the ROPC grant; users must supply a Bearer token obtained via the [browser token page](#browser-token-page-authorization-code-flow) or a service account flow. Optionally, a Google Workspace service account with domain-wide delegation can enrich group membership from the Directory API. ```bash QOD_AUTH_GOOGLE_ENABLED=true QOD_AUTH_GOOGLE_CLIENT_ID=123456789.apps.googleusercontent.com QOD_AUTH_GOOGLE_CLIENT_SECRET=GOCSPX-... # Optional: Google Workspace groups lookup via Directory API QOD_AUTH_GOOGLE_GROUPS_LOOKUP=true QOD_AUTH_GOOGLE_SVC_ACCT_KEY_PATH=/etc/quack/google-svc-acct.json QOD_AUTH_GOOGLE_GROUPS_CACHE_TTL_SEC=300 # default 300 s ``` The service account key JSON must contain `client_email`, `private_key`, and `token_uri`. The account needs domain-wide delegation with the `https://www.googleapis.com/auth/admin.directory.group.readonly` scope. Group results are cached per user for `QOD_AUTH_GOOGLE_GROUPS_CACHE_TTL_SEC` seconds. ### Per-tenant Google OAuth clients The HOCON block above defines a **single** Google OAuth client used by every Google tenant by default. To give a tenant its own client (separate consent screen, separate `clientId`, independent revocation blast radius), set per-tenant fields on the tenant's auth provider in the admin UI or via `setTenantAuth`: | Field | Value | |---|---| | `clientId` | The tenant's Google OAuth client ID, e.g. `.apps.googleusercontent.com` | | `clientSecretRef` | A reference to the secret, NOT the literal value. Today only `env:NAME` is supported (e.g. `env:GOOGLE_CS_TPCH`) | When BOTH are set, the edge builds a per-tenant `OidcBearerAuthenticator` and substitutes it into the bearer chain for that tenant's handshakes. Tenants that leave the fields blank keep using the global block. The `clientSecret` is never stored in the tenant row. Only the reference (`env:NAME`) is persisted, and the env var must be set on the manager process at boot. Mutations via `setTenantAuth` automatically invalidate the cached per-tenant authenticator. This mechanism is currently Google-only; Keycloak / Azure / AWS still share a single per-manager OAuth client. --- ## Azure AD Validates OIDC Bearer tokens issued by Azure Active Directory. Also supports ROPC for Basic `username:password` authentication via the Azure OAuth2 token endpoint. ```bash QOD_AUTH_AZURE_ENABLED=true QOD_AUTH_AZURE_TENANT_ID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx QOD_AUTH_AZURE_CLIENT_ID=your-app-client-id QOD_AUTH_AZURE_CLIENT_SECRET=your-app-client-secret ``` The JWKS URL is derived as `https://login.microsoftonline.com/{tenantId}/discovery/v2.0/keys`. For ROPC the token endpoint is `https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token`. --- ## AWS Cognito Validates OIDC Bearer tokens from an AWS Cognito User Pool. ROPC is not supported; clients must obtain a token from Cognito before connecting. ```bash QOD_AUTH_AWS_ENABLED=true QOD_AUTH_AWS_REGION=us-east-1 QOD_AUTH_AWS_USER_POOL_ID=us-east-1_AbCdEfGhI QOD_AUTH_AWS_CLIENT_ID=your-app-client-id ``` The JWKS URL is derived as `https://cognito-idp.{region}.amazonaws.com/{userPoolId}/.well-known/jwks.json`. --- ## Browser token page (authorization-code flow) A JDBC client (DBeaver, Spark) can't run an interactive OAuth flow in the Arrow Flight SQL driver, so the manager serves a browser login page that returns a bearer to paste into the driver's `token` property. It runs on the **manager port** (no separate port or server) and reuses the first enabled OIDC provider (Keycloak, Google, or Azure AD, in that order) to resolve the authorization and token endpoints. - A user opens `https://:20900/api/auth/sql-token/start`, logs in at the IdP, and the callback page renders the access token (and the ready-to-paste `token=` form). - The IdP must allow `/api/auth/sql-token/callback` as a redirect URI (`publicBaseUrl` = `auth.management.publicBaseUrl`). - The only knob is the requested scopes: ```bash QOD_AUTH_OAUTH_SCOPES="openid profile email" # default "openid profile email" ``` At least one OIDC provider (Keycloak, Google, or Azure AD) must be enabled for the page to function; AWS Cognito is not supported as an interactive backend. Because the page reuses the data-plane confidential client, the token already carries the audience the edge expects (no separate client or audience mapper needed). See [Connecting clients](/qod/connecting/clients#oauth-with-dbeaver) for the DBeaver recipe. --- ## Admin UI single sign-on (OIDC) The providers above govern the **FlightSQL data plane** (how a SQL client authenticates). This section is separate: it controls how operators log in to the **admin UI** at `/ui/`. The admin-UI login mode is resolved **per scope**, not globally: - `/ui/?tenant=` reads **that tenant's** `authProvider`: `db` shows the password form, an OIDC provider (`keycloak` / `google` / `azure` / `aws`) redirects to that tenant's IdP. A tenant is therefore pure-password or pure-SSO; there is no password fallback inside an OIDC tenant. - The bare `/ui/` (system / superuser scope) uses the manager-wide `auth.management.identitySource`: `db` (default) shows the password form, `oidc` makes it a **pure SSO client** against the manager-wide issuer below. A tenant-scoped page URL counts as that tenant too: opening `/ui/t//...` without a session (a bookmark, or a reload after the session expired) resolves the login mode, and for SSO starts the sign-in, against tenant ``, as `/ui/?tenant=` does; an explicit `?tenant=` wins. The SPA resolves the mode for the tenant in the URL via the unauthenticated `GET /api/auth/mode?tenant=` (omit `tenant` for the system scope) before deciding what to render. It is **provider-agnostic**: it uses OIDC Discovery, so it works with any compliant IdP (Keycloak, Google, Azure AD, Okta, Auth0, Cognito, ...). You configure an **issuer URL** and a client id/secret; the manager resolves the authorize / token / end-session / JWKS endpoints from `${issuerUrl}/.well-known/openid-configuration`. `qodstate_user` remains authoritative for role and tenant scope: the IdP verifies identity, and the matching `qodstate_user` grant decides what the operator may manage. IdP role/tenant claims are discarded. ```bash QOD_MGMT_IDENTITY_SOURCE=oidc # system-scope (bare /ui/) SSO (default: db) # System / superuser scope (the bare /ui/ login uses this issuer): QOD_MGMT_OIDC_ISSUER_URL=https://idp.example.com/realms/qod QOD_MGMT_OIDC_CLIENT_ID=qod-admin QOD_MGMT_OIDC_CLIENT_SECRET=... # confidential client secret QOD_MGMT_OIDC_SCOPES="openid email profile" # default "openid email profile" # Externally visible manager URL, used to build the redirect_uri. MUST match the # redirect URI registered on the IdP client. When unset, derived from the request. QOD_MGMT_PUBLIC_BASE_URL=https://qod.example.com ``` Register this **redirect URI** on each IdP client: ``` ${QOD_MGMT_PUBLIC_BASE_URL}/api/auth/oidc/callback ``` ### Login URLs and scope - `/ui/` (no tenant) is the **system / superuser** login. Its mode follows `QOD_MGMT_IDENTITY_SOURCE`; in `oidc` it authenticates against the manager-wide issuer above. Only a superuser (`qodstate_user.tenant IS NULL`, kind admin) may complete it; a non-superuser is rejected and must sign in through their tenant. - `/ui/?tenant=` follows **that tenant's** `authProvider`: a `db` tenant gets the password form, an OIDC tenant authenticates against that tenant's OIDC client. Either way it requires an admin grant for that tenant. ### Per-tenant OIDC A tenant authenticates against its own issuer, configured in `qodstate_tenant.authConfig` with these generic keys (set them via the tenant auth API / admin UI): | Key | Meaning | |---|---| | `issuerUrl` | the tenant's OIDC issuer (discovery base) | | `clientId` | the tenant's OIDC client id | | `clientSecretRef` | a secret reference (e.g. `env:ACME_CLIENT_SECRET`) resolved at runtime | | `scopes` | optional; defaults to `openid email profile` | ### Logout Logout is **RP-initiated**: it clears the `qod_session` cookie and redirects to the IdP end-session endpoint so the IdP session is terminated as well. --- For the full reference of all authentication tunables, including `QOD_AUTH_ROLE_CLAIM` and `QOD_SESSION_TTL_SEC`, see [/reference/configuration](/qod/reference/configuration).