Unify JWT/token external auth with HTTP external auth: allow pre-created users with persistent grants instead of readonly on-RAM directory

[BorisTyshkevich]

Summary

Today Antalya offers three external authentication methods with two fundamentally different user-storage models:

Method	User record location	Grants set via SQL?	Per-user ACLs?
<user_directories><ldap>	LDAPAccessStorage → MemoryAccessStorage memory_storage (readonly, in-RAM, rebuilt per auth)	❌	❌ (only <common_roles>)
<user_directories><token> (JWT/OAuth)	TokenAccessStorage → MemoryAccessStorage memory_storage (readonly, in-RAM, rebuilt per auth)	❌	❌ (only <common_roles> + group-mapped roles)
<http_authentication_servers> + regular users.xml / CREATE USER ... IDENTIFIED WITH http_authentication	Regular writable storage (users_xml, local_directory, or replicated)	✅	✅

LDAP and Token/JWT users live in a per-storage MemoryAccessStorage and are rebuilt from config + external claims on every auth. The user can only be granted roles via <common_roles> or group-mapping — GRANT role TO 'alice@example.com' is impossible because the storage is readonly.

HTTP external auth, by contrast, reuses the main writable storage. The user is pre-provisioned (CREATE USER alice IDENTIFIED WITH http_authentication SERVER 'auth0'), credential check is delegated to an HTTP server via ExternalAuthenticators::checkHTTPBasicCredentials, and all standard GRANT/REVOKE flow works.

The Token code itself acknowledges the duplication — see src/Access/TokenAccessStorage.h:28-29 (antalya-26.1):

"Normally, this should be unified with LDAPAccessStorage, but not done to minimize changes to code that is common with upstream."

Problem for operators

Projects using OAuth/JWT + MCP/BI stacks need per-user access control:

• Disable access for a specific user quickly (audit, offboarding)
• Grant different role sets to different users in the same OAuth domain
• Audit who has what grants via standard SHOW GRANTS FOR ...

Today the only way to disable one JWT user in the <token> model is to add group-filter regex or change <common_roles> — both of which are blunt, require a server restart/reload, and are invisible to SQL.

Proposal

Teach the JWT/token path to use the HTTP external auth pattern:

1. Keep <token_processors> config (JWT validation, JWKS, claims) — no change.

2. Deprecate <user_directories><token> as the only way to onboard JWT users.

3. Add AuthenticationType::JWT (or OAUTH2_TOKEN) as a first-class authentication method usable on any regular user:

   <users>
     <alice@example.com>
       <jwt>
         <processor>google</processor>
         <!-- optional: require specific claim values -->
         <claims>{"email":"alice@example.com"}</claims>
       </jwt>
     </alice@example.com>
   </users>

   or via SQL:

   CREATE USER 'alice@example.com' IDENTIFIED WITH jwt BY 'google';
   GRANT analyst_role TO 'alice@example.com';

4. On an incoming Bearer JWT, IAccessStorage::authenticateImpl looks up the user in the writable storage (as HTTP auth does today), then delegates claim verification to ExternalAuthenticators::checkTokenCredentials(...).

5. TokenAccessStorage / <user_directories><token> continue to work for backwards compatibility and for the "accept any valid JWT in this OAuth domain with <common_roles>" use case — same code path as LDAP.

Benefits

• Unified mental model across the three methods: external system validates credentials, CH looks up a persistent user.
• Standard SQL GRANT/REVOKE/SHOW GRANTS for JWT users.
• Per-user deny works by DROP USER — no restart.
• Gating-mode MCP deployments (where ClickHouse impersonates via initial_user=<email> + cluster-secret) stop needing no_password placeholder users; JWT users can be created directly with persistent grants.

References

• src/Access/TokenAccessStorage.h, TokenAccessStorage.cpp (antalya-26.1)
• src/Access/LDAPAccessStorage.h, LDAPAccessStorage.cpp (the pattern being mirrored)
• src/Access/Authentication.cpp:239-250 (HTTP auth delegation pattern to follow)
• src/Access/ExternalAuthenticators::checkTokenCredentials (already exists, already separable from storage)