ADR: Add LDAP↔OAuth bridge for ephemeral ClickHouse users and dynamic role mapping

Status

Accepted

Context

We want OAuth authentication for Altinity Stable ClickHouse 24.8 without provisioning a persistent ClickHouse user for every OAuth identity and without synchronizing IdP group membership into ClickHouse with CREATE USER, GRANT, and REVOKE operations.

The target clients already know how to obtain and refresh OAuth/OIDC access tokens. The compatibility layer therefore does not need to implement browser login, authorization-code flow, refresh-token storage, or other OAuth-client behavior.

Altinity Stable 24.8 includes the backport of ClickHouse PR #70332, which propagates externally granted roles from the query originator to other ClickHouse nodes. This makes ClickHouse's LDAP external user directory viable for distributed queries: the originator can authenticate an ephemeral external user, derive roles from LDAP, and propagate those externally granted roles to remote nodes.

ClickHouse's LDAP external user directory already provides the lifecycle we want:

• an undefined user can authenticate through LDAP;
• ClickHouse creates an in-memory user representation after successful authentication;
• LDAP role searches are mapped to existing local ClickHouse roles;
• later authentication can refresh the externally assigned role set;
• persistent ClickHouse roles remain the source of database privileges.

The existing altinity-oauth-helper repository already contains reusable JWT/OIDC validation and identity-policy code in ch-jwt-verify / go-mcp-oauth-sdk.

Decision

Add a new LDAP-facing OAuth authentication helper to this repository, provisionally named ch-oauth-ldap.

ch-oauth-ldap will implement the minimal LDAP behavior required by ClickHouse and translate LDAP authentication/search operations into OAuth token validation and virtual group membership.

The helper is an authentication and identity compatibility layer. It is not a ClickHouse user/role provisioning controller and does not execute ClickHouse SQL.

Authentication flow

OAuth-capable client
        |
        | username + OAuth access token
        v
Altinity Stable ClickHouse 24.8
        |
        | LDAP simple bind
        | DN = uid=<username>,...
        | password = <access token>
        v
ch-oauth-ldap
        |
        | verify signature / issuer / audience / exp / nbf
        | validate identity policy
        | bind requested username to token identity
        v
successful LDAP bind

The OAuth access token is treated as the LDAP bind credential. The helper MUST verify that the requested ClickHouse/LDAP identity corresponds to the authenticated token principal; a valid token for Alice MUST NOT be usable to bind as Bob or an administrative ClickHouse username.

The stable identity is (issuer, subject). A deployment MAY derive the visible ClickHouse username from another claim such as verified email, but the mapping must be explicit and validated.

Dynamic role mapping

After successful bind, ClickHouse performs its configured LDAP role search. ch-oauth-ldap exposes virtual LDAP group entries derived from validated token claims and/or an external group source.

Example:

OAuth claims/groups
    engineering-readers
    finance
          |
          v
ch-oauth-ldap virtual LDAP groups
    cn=clickhouse_engineering_ro,...
    cn=clickhouse_finance_user,...
          |
          v
ClickHouse LDAP role_mapping
    prefix = clickhouse_
          |
          v
ClickHouse roles
    engineering_ro
    finance_user

The helper MUST only emit roles/groups allowed by configured mapping policy. It must not allow an arbitrary token claim to become an arbitrary ClickHouse role name.

Actual database privileges remain exclusively in ClickHouse:

IdP                         ClickHouse
---                         ----------
identity                    CREATE ROLE
user/group membership  ->   GRANT privileges TO ROLE
                            row policies
                            settings profiles
                            quotas

There is no normal-user CREATE USER, GRANT role TO user, REVOKE, or user-directory synchronization loop.

Proposed repository structure

Refactor shared validation/identity functionality where useful rather than duplicating it:

pkg/
  verifier/        shared JWT/OIDC validation
  identity/        principal extraction and identity policy
  groups/          claims/provider -> external groups
  rolemap/         allowlisted external-group -> CH-role mapping

cmd/
  ch-jwt-verify/   existing HTTP external authenticator
  ch-oauth-ldap/   new LDAP server

Exact package names are not part of this ADR; the architectural requirement is shared validation semantics across both helpers.

Group freshness

The generic baseline is group membership carried in the OAuth token. In that mode, revocation latency is bounded by token lifetime rather than being instantaneous.

The helper SHOULD allow group sources to evolve independently, for example:

• token: groups/roles are read from validated JWT claims;
• introspection: current information is obtained from an OAuth introspection endpoint when supported;
• provider-specific API: current group membership is queried from an IdP API;
• bounded cache around any of the above.

The security contract for each mode must be explicit. In particular, token-based group mapping must document that a group removal may remain effective until the already-issued access token expires.

Caching

JWKS caching and short bounded token-verification caches are acceptable.

ClickHouse LDAP verification_cooldown should initially be configured as 0 so that ClickHouse does not independently extend authentication/group state beyond the helper's intended freshness model. Longer cooldowns can be evaluated later against token lifetime and revocation requirements.

Local administrative users

Local ClickHouse operators, break-glass users, and other administrative identities should remain in a higher-precedence local ClickHouse user directory and use normal ClickHouse authentication methods.

ch-oauth-ldap SHOULD support an explicit reserved/denied username list so OAuth identities cannot collide with protected local names such as default, admin, operator, or deployment-specific break-glass accounts.

Distributed queries

This design assumes Altinity Stable 24.8 contains the backport of ClickHouse PR #70332 for interserver propagation of externally granted roles.

The OAuth helper does not hold the ClickHouse cluster secret and does not directly assert external_roles to ClickHouse peers. The trust path is:

IdP
 |
 v
ch-oauth-ldap
 |
 v
originating ClickHouse
 |
 | authenticated ClickHouse interserver propagation
 v
remote ClickHouse nodes

This keeps cluster-peer trust inside ClickHouse.

Security properties

The intended properties are:

1. No persistent ClickHouse user is required for ordinary OAuth identities.
2. A valid OAuth token is not sufficient to impersonate a different requested ClickHouse username.
3. IdP groups select from an allowlisted set of ClickHouse roles; they do not directly define SQL grants.
4. ClickHouse remains authoritative for database privileges, row policies, settings, and quotas.
5. The helper has no ClickHouse admin credentials and performs no SQL provisioning.
6. Local administrative identities remain independent of OAuth-helper compromise.
7. Distributed role propagation is performed by ClickHouse, not by the OAuth helper acting as a trusted cluster peer.

Alternatives considered

Persistent ClickHouse users plus synchronized role grants

Rejected as the preferred architecture because it requires lifecycle state and synchronization between the IdP and ClickHouse. It remains a fallback for builds where external-role propagation is unavailable.

EXECUTE AS / service-user impersonation

Rejected because it is not transparent ClickHouse identity/RBAC behavior and makes the intermediary part of the authorization execution model.

Proxy directly asserts external roles using cluster-secret authentication

Rejected because it moves a strong ClickHouse cluster trust primitive into a general-purpose edge component and makes that component authoritative for role assertion.

HTTP external authenticator only

The existing ch-jwt-verify remains useful, but by itself it requires pre-created ClickHouse users and does not solve ephemeral users plus dynamic role assignment.

Consequences

Positive

• no user provisioning or user-role synchronization;
• ClickHouse continues to enforce normal RBAC;
• works naturally with OAuth-aware clients that already manage their token lifecycle;
• shared OAuth validation code can be reused from the existing helper;
• local administrators remain isolated from the OAuth identity plane.

Negative / risks

• we must implement a small, security-sensitive LDAP server surface;
• token-carried groups have revocation latency up to token expiry;
• LDAP/ClickHouse behavior must be verified carefully against Altinity Stable 24.8, especially distributed queries and role refresh;
• OAuth tokens are transported through ClickHouse as credentials and must be protected with TLS and redacted from logs/metrics/errors.

Validation criteria

Before accepting this ADR as implemented, an integration test against Altinity Stable 24.8 should demonstrate all of the following:

1. A user not present in ClickHouse can authenticate with an OAuth token through the LDAP external user directory.
2. A token for one principal cannot authenticate as another username.
3. Virtual LDAP groups map to pre-existing ClickHouse roles.
4. A refreshed token with changed groups changes the user's externally assigned roles on the next authentication.
5. currentUser() reports the OAuth-derived ClickHouse identity.
6. currentRoles() / effective privileges match the mapped roles.
7. A Distributed query succeeds and enforces the same identity/role set on remote nodes using the #70332 backport.
8. A locally defined administrative username takes precedence and cannot be authenticated through the OAuth LDAP directory.
9. Expired, wrong-audience, wrong-issuer, malformed, and identity-mismatched tokens fail closed.
10. Tokens are not emitted into normal logs, metrics, or error messages.

Amendments

2026-08-28 — MVP transport exception (owner: Boris Tyshkevich, @BorisTyshkevich).
For the ch-oauth-ldap MVP delivered under #19, the ClickHouse → helper LDAP hop runs in clear text on the trusted in-cluster network (ClusterIP-only Service, default-on NetworkPolicy restricting ingress to ClickHouse pods, never exposed outside the cluster). The OAuth bearer token therefore travels unencrypted as the LDAP simple-bind password inside the cluster. This is an accepted, time-bounded deviation from this ADR's transport requirement, not a reinterpretation of it: the requirement stands for every hop that leaves the trusted cluster network, and the exception is to be revisited when either (a) LDAPS/StartTLS support lands in ch-oauth-ldap, or (b) a per-ClickHouse-pod loopback sidecar topology is adopted. A NetworkPolicy is reachability control, not transport confidentiality. Rationale and alternatives: issue #19 ship log, Phase 4 note; operator-facing statement in docs/ch-oauth-ldap-operator-guide.md and helm/ch-oauth-ldap/README.md.

2026-08-29 — Distributed-query validation criterion qualified. Validation criterion 7 ("A Distributed query succeeds and enforces the same identity/role set on remote nodes using the #70332 backport") holds on ClickHouse ≥ 25.8 for Distributed tables over base tables only: 24.8/25.3 lack ClickHouse #79099 (pushed external roles are filtered against the ephemeral user's empty local grants), and every version through 26.3 drops pushed external roles when the remote table is a VIEW (ClickHouse/ClickHouse#116840). See issue #19 "Compatibility target".

Start by reading the reusable validation and identity code in ch-jwt-verify and go-mcp-oauth-sdk, then review the proposed pkg/ and cmd/ch-oauth-ldap/ structure. Implement the LDAP helper and shared validation semantics described here. Done means the Altinity Stable 24.8 integration checks pass, including identity binding, dynamic role mapping, distributed queries, administrative-user precedence, failure cases, and token redaction.