sso *(working name

Note: This code is almost entirely AI-authored (Claude, Anthropic), albeit under close human supervision, and is for research and experimentation purposes. Successful experiments may be re-implemented in a more coordinated and curated manner.

A DuckDB C++ extension that turns an enterprise identity — a JWT, or a Kerberos ticket via SPNEGO — into temporary, auto-rotating S3 credentials, so DuckDB httpfs reads/writes an S3-compatible store (AWS, MinIO, …) with no static access keys. It registers an sso provider for the s3 secret type:

-- Kerberos / SPNEGO: no password, no static keys — the OS ticket becomes S3 creds
CREATE SECRET lake (
  TYPE s3, PROVIDER sso,
  oidc_issuer  'https://keycloak.example/realms/lake',
  client_id    'minio',
  sts_endpoint 'https://minio.example:9000/',
  endpoint     'minio.example:9000', region 'us-east-1'
);

-- …or hand it a JWT you already hold (file / env / inline):
CREATE SECRET lake (TYPE s3, PROVIDER sso, token '<jwt>', sts_endpoint '…', endpoint '…');

Flow: acquire a JWT (inline token / web_identity_token_file / AWS_WEB_IDENTITY_TOKEN_FILE env / or Kerberos SPNEGO against an OIDC issuer) → STS AssumeRoleWithWebIdentity (AWS STS or MinIO STS) → temporary credentials in a KeyValueSecret → httpfs consumes them exactly like static keys, and they auto-rotate on expiry.

flowchart LR
  subgraph ACQ["1 · acquire JWT"]
    direction TB
    T["inline token /<br/>web_identity_token_file / env"]
    K["oidc_issuer:<br/>kinit → SPNEGO → Keycloak/OIDC"]
  end
  ACQ -->|JWT| STS["2 · STS<br/>AssumeRoleWithWebIdentity<br/>(AWS / MinIO)"]
  STS -->|"temporary creds<br/>(key_id · secret · session_token · exp)"| SEC["3 · KeyValueSecret<br/>+ refresh_info"]
  SEC --> HF["4 · httpfs · S3 read / write"]
  HF -. "creds expired (403):<br/>httpfs re-invokes the provider" .-> ACQ

Proof by construction: DuckDB-native SSO

sso is a working demonstration that enterprise SSO → temporary S3 credentials can live entirely inside DuckDB — no client-side SDK, no static keys, no separate broker. The whole identity → STS → S3 flow is a SQL-loadable secret provider, driven by ordinary SQL:

CREATE SECRET lake (
  TYPE s3, PROVIDER sso,
  oidc_issuer 'https://keycloak/realms/lake', client_id 'minio',
  sts_endpoint 'https://minio:9000/', endpoint 'minio:9000'
);
SELECT * FROM read_parquet('s3://lake/sales/*.parquet');

Because it's registered at the SQL layer, any DuckDB client gets SSO'd access to on-prem MinIO for free — the CLI, Python, JDBC, and crucially ODBC → Tableau / Power BI / Excel:

flowchart TD
  C1["CLI · Python · JDBC"] --> DDB
  C2["ODBC → Tableau / BI"] --> DDB
  DDB["DuckDB + sso<br/>CREATE SECRET … PROVIDER sso"]
  DDB -. "① pre-flight SPNEGO<br/>ambient kinit ticket — no browser" .-> KC["Keycloak / OIDC"]
  KC -. "JWT" .-> DDB
  DDB -. "② AssumeRoleWithWebIdentity" .-> STS["MinIO STS"]
  STS -. "temp creds" .-> DDB
  DDB ==>|"③ httpfs reads s3:// with temp creds"| M[("on-prem MinIO")]

The linchpin is non-interactive auth. A browser-based OIDC redirect is impossible from Tableau or a raw ODBC connection. sso's pre-flight SPNEGO uses the caller's ambient Kerberos ticket — no prompt, no redirect, no device code — so the whole SSO completes silently inside CREATE SECRET. That is what turns "surface Kerberos-gated on-prem MinIO to Tableau, keylessly" from impossible into a two-line SQL preamble.

What it proves, concretely:

• No static keys — credentials are short-lived STS tokens, auto-rotated (see Refresh).
• No client SDK or broker — the AWS-style web-identity → STS exchange lives in the extension, reachable from any language that can issue SQL.
• No interactive step — it runs headless, the hard requirement for BI tools and service accounts.

Lightweight by design — no heavyweight dependencies

The goal is enterprise SSO-style auth with the smallest possible footprint. The extension is a thin shim over the DuckDB host; it links no large libraries:

• No HTTP library. Every request — the STS POST, OIDC discovery, the SPNEGO auth-code GET, the token exchange — rides DuckDB's core HTTPUtil (the same HTTP abstraction httpfs implements, obtained via HTTPUtil::Get(db)). We deliberately reuse the host's HTTP stack instead of linking libcurl/cpr.
• No JSON library. OIDC discovery and token responses are read with a few lines of string scanning — the fields we need (authorization_endpoint, token_endpoint, access_token) are simple, well-formed strings.
• No XML library. STS responses are parsed by pulling a handful of <AccessKeyId> / <SecretAccessKey> / <SessionToken> / <Expiration> tags directly.
• No Kerberos/GSSAPI at link time. The SPNEGO path dlopens GSS-API (libgssapi_krb5 on Linux, GSS.framework on macOS, SSPI on Windows) at runtime, and only when you use the oidc_issuer flow. The token/file/env paths touch no security library at all. The only link-time additions are the dl loader (${CMAKE_DL_LIBS}) and, on Windows, secur32 for SSPI.

Net effect: the "real" work is one STS exchange plus an optional SPNEGO handshake — everything else (HTTP, TLS, the S3 client) is borrowed from the DuckDB host.

Why a C++ extension (the security API is not in the C interface)

sso must be C++. The DuckDB secret-provider surface — CreateSecretFunction, KeyValueSecret, RegisterSecretType, ExtensionLoader::RegisterFunction, and HTTPUtil — lives only in the C++ core. It is not exposed through the stable C extension API: duckdb.h and duckdb_extension.h contain zero secret symbols (verified at the header level), whereas ordinary scalar-function registration is in the C API. So a stable-ABI, version-independent C extension cannot register a secret provider — anything security-related has to go through the C++ interface, which is why this is a C++ extension built against a pinned duckdb version.

Token acquisition (the sso provider)

Source	Parameters
Inline JWT	token
Token file	web_identity_token_file (or AWS_WEB_IDENTITY_TOKEN_FILE env)
Kerberos / SPNEGO	oidc_issuer, client_id, client_secret, redirect_uri, allow_http_negotiate

Kerberos/SPNEGO (oidc_issuer set): kinit ticket → spnego::GenerateTokenForUrl (the dlopen'd GSS-API, from the shared spnego-token submodule) → OIDC discovery → proactive Authorization: Negotiate auth-code GET → token exchange → JWT. The client's krb5.conf should set dns_canonicalize_hostname = false so the requested SPN matches the issuer host (HTTP/<issuer-host>) rather than a DNS-canonicalized name.

By default SPNEGO requires HTTPS (replay protection); allow_http_negotiate true opts into plain HTTP when the transport is already encrypted by an overlay (e.g. WireGuard/Tailscale).

Scalar functions (preemptive Negotiate tokens)

The same spnego-token atom is also exposed as UDFs, so you can mint a token and drop it into any request — an EXTRA_HTTP_HEADERS entry on a TYPE http secret, ad-hoc SQL, etc. All are VOLATILE (a fresh token per call).

Function	Returns
negotiate_token(url)	base64 SPNEGO token for HTTP/<host> (raises on failure)
negotiate_token(url, service)	token for <service>/<host> — LDAP, cifs, host, …
negotiate_token_describe(url)	a JSON diagnostics blob (SPN, provider, token-or-error); never raises
negotiate_token_from_json(config)	strict JSON property-bag {"url"|"host", "service", "allow_insecure"} → token

CREATE SECRET kerb (TYPE http, EXTRA_HTTP_HEADERS MAP {
    'Authorization': 'Negotiate ' || negotiate_token('https://intranet.example/')
});

Refresh / auto-rotation

STS credentials are short-lived (minutes to ~an hour), so the provider is built to be re-runnable, and the secret carries everything needed to re-run it:

• On creation, sso stores all the original CREATE SECRET options as a refresh_info struct inside the secret (alongside the temp creds and their expiration).
• When an S3 request fails because the credentials have expired, httpfs (CreateS3SecretFunctions::TryRefreshS3Secret) reconstructs the CreateSecretInput from refresh_info, calls CreateSecret again — which re-invokes this provider — and retries the S3 request with the fresh secret. No user action, no re-issuing CREATE SECRET.

What "re-invoke" actually does depends on the token source:

Source	On refresh
oidc_issuer (Kerberos/SPNEGO)	Full re-acquire — new SPNEGO handshake → new JWT → new STS creds. Requires a still-valid Kerberos ticket (TGT) at refresh time.
web_identity_token_file	Re-reads the file → new STS creds. Works with an external token-rotation agent (e.g. an EKS/IRSA-style projected token).
inline token	Re-runs the STS call with the same JWT; only useful while that JWT is still valid — a static token cannot be renewed from inside the secret.

Caveats, stated plainly:

• Refresh is reactive, not a proactive timer: it fires on an expired-credential failure and retries, so the first call after expiry pays one failed-then-retried round-trip. (The expiration is stored in the secret but sso does not itself run a background refresher.)
• For the SPNEGO path, refresh needs a valid Kerberos ticket when it runs. If the TGT has also expired, the refresh fails and you re-kinit — the long-lived secret of record is the Kerberos credential, not an S3 key.
• Verified end-to-end: re-invoking the provider yields a different key_id each time, confirming genuinely fresh STS credentials.

Build, test, CI

• make builds against pinned duckdb v1.5.4 + extension-ci-tools + the spnego-token submodule (clone --recursive to also get its nested nlohmann/json); make test runs the suite. (httpfs is INSTALL/LOADed at test time, not compiled in — it supplies the s3 secret type and the HTTPUtil used for the STS call.)
• GitHub Actions builds the full distribution matrix (linux amd64/arm64, macOS amd64/arm64, windows mingw+MSVC, wasm). A Forgejo workflow additionally builds on self-hosted runners.

Performance

In an enterprise setting the costs that matter are the auth handshake and the per-object overhead of object storage — not the SQL engine. See docs/sso Performance.md for measured numbers (one-time SSO ~230ms; ~8–12ms per S3 object open) and the layered mitigations that retire them (DuckLake catalog for metadata, cache_httpfs for repeated data) — all driven by one keyless CREATE SECRET, with BLOBSSO_TIMING=1 for the per-step breakdown.

For the broader architecture this enables — out-of-band catalog writes and zero-copy facets over on-prem object storage (cloud→on-prem data repatriation, governed by SSO reads) — see docs/Disaggregated Lakehouse.md.

Layout

CMakeLists.txt                     # links only ${CMAKE_DL_LIBS} (+ secur32 on Windows)
src/sso_extension.cpp          # the 'sso' provider: token acquisition + STS + KeyValueSecret
src/include/sso_extension.hpp
third_party/spnego-token/          # submodule: shared SPNEGO atom (+ nested nlohmann/json)
test/sql/sso.test              # sqllogictest: registration + input validation
test/mock_sts_test.py              # mock-STS round-trip integration test
.github/workflows/                 # multi-arch distribution pipeline
.forgejo/workflows/                # self-hosted build (dc1 + Mac runners)
duckdb/  extension-ci-tools/       # submodules, pinned to v1.5.4

See CONTEXT.md for the full design/decision history.