Gating mode: map IdP groups to ClickHouse users via HTTP auth callback

[BorisTyshkevich]

Summary

Enable real ClickHouse authentication in gating mode by mapping IdP groups to pre-created ClickHouse users via the HTTP external authenticator. This works with standard ClickHouse builds (no Antalya/token_processors required).

Motivation

Current gating mode validates OAuth tokens at the MCP proxy but connects to ClickHouse with a single static credential. This means:

• No per-user audit trail in system.query_log
• No per-group access control at the ClickHouse layer

Proposed Design

Group-to-user mapping with domain-qualified names

Groups are domain-qualified (group.domain) to support a single IdP serving multiple organisations:

oauth:
  group_claim: "groups"              # JWT claim containing group list
  group_domain_claim: "hd"           # claim to use as domain (e.g. Google hd)
  group_user_mapping:
    engineering.altinity.com: "ch_engineering"
    analytics.partner.com:   "ch_analytics"
    admin.altinity.com:      "ch_admin"
  default_user: ""                   # empty = reject if no group matches

Domain resolution order:

1. group_domain_claim value from token (e.g. hd: "altinity.com")
2. Email domain fallback (e.g. alice@altinity.com -> altinity.com)
3. If neither available -> reject

How it works

1. MCP validates OAuth token and extracts groups from configured group_claim
2. MCP resolves domain, constructs group.domain FQDNs (e.g. engineering.altinity.com)
3. MCP maps first matching FQDN to a ClickHouse user via group_user_mapping
4. MCP generates a single-use nonce, sends query to CH as user=ch_engineering, password=<nonce>
5. ClickHouse calls back to MCP's /auth/callback endpoint with the same credentials
6. MCP verifies the nonce (consume-on-read) and returns 200 or 401
7. Real user identity is injected into system.query_log.log_comment via X-ClickHouse-Setting-log_comment header

ClickHouse configuration

Pre-create users that delegate auth to MCP:

<http_authentication_servers>
    <mcp_auth>
        <uri>http://altinity-mcp:8080/auth/callback</uri>
        <max_tries>1</max_tries>
    </mcp_auth>
</http_authentication_servers>

<users>
    <ch_engineering>
        <http_authentication>
            <server>mcp_auth</server>
            <scheme>basic</scheme>
        </http_authentication>
    </ch_engineering>
</users>

Group claims across IdPs

There is no standard OIDC claim for groups:

IdP	Claim name	In token by default?	Notes
Google	N/A	No	Groups not in tokens -- requires Directory API
Auth0	custom namespaced	No	Requires Action; claim must be namespaced URI
Okta	groups	No	Custom claim in auth server config
Keycloak	groups	No	Group Membership mapper required
Azure AD	groups (GUIDs)	No	Overage at 200 groups; roles claim is simpler

Google limitation: Google does not put groups in tokens. Options: Directory API integration, generic group resolver webhook, or accept that Google deployments use domain-based allowlists (AllowedHostedDomains, AllowedEmailDomains) instead of groups.

Key design decisions

• Nonce is single-use (consume-on-read) -- replay impossible. max_tries=1 on CH side to avoid retry/consume conflict.
• Stateless -- nonces in memory only. MCP restart loses in-flight nonces; clients retry.
• Opt-in -- existing gating mode unchanged when group_user_mapping is not configured.
• Audit trail -- log_comment header injects real email into system.query_log.

Design document

Full design with sequence diagram, replay analysis, IdP research, and comparison with forward mode: docs/proposal_gated_user_mapping.md