Local runner: desktop/native OAuth via loopback redirect (RFC 8252) instead of a registered web redirect URI

[BorisTyshkevich]

Today

npm run local (build/local.py) serves the SPA + config.json on 127.0.0.1:8900 and does no auth — the browser runs PKCE with redirect_uri = http://localhost:8900/sql (src/ui/app.js, src/main.js). So a local user must:

• register the exact http://localhost:8900/sql as a redirect URI on a Web-type IdP client (and re-register whenever PORT changes), and
• for some IdPs (e.g. a Google "Web application" client) ship a client_secret in the browser-served config.json.

Proposal

Give the local runner a desktop/native OAuth flow (RFC 8252 §7.3, loopback interface redirection). The user registers a Desktop/Native IdP client; build/local.py then:

1. generates a PKCE verifier/challenge + state (stdlib: secrets, hashlib, base64),
2. binds a loopback HTTP server on http://127.0.0.1:<port> and opens the system browser (webbrowser) to the authorize URL with that loopback redirect_uri,
3. catches the redirect GET /callback?code&state, validates state, then POSTs the code + PKCE verifier to the IdP token endpoint (urllib, no client_secret),
4. hands the resulting bearer to the SPA (e.g. a localhost-only endpoint the page reads, or injected into the served page), after which the SPA talks to ClickHouse exactly as today.

Stdlib-only (keeps local.py's zero-dependency property); the .well-known discovery, PKCE, and token-exchange shapes mirror the existing JS (src/core/pkce.js, src/net/oauth.js, src/net/oauth-config.js). Scope: authenticate the local app only — queries still run from the browser SPA.

Why this matters vs the current web client

1. Loopback redirects need no per-URL registration. A Web client must register the exact http://localhost:8900/sql; change the port → re-register; every developer repeats the setup. IdPs grant Desktop/Native clients loopback redirects on any http://127.0.0.1:<port> (RFC 8252), so the runner can use an OS-assigned ephemeral port with zero IdP changes. This is the biggest friction removed.
2. Plain-HTTP localhost is first-class only for native clients. "Web application" client types often reject http:// redirects or special-case localhost; the Desktop/Native type is the IdP-sanctioned path for an app catching a loopback redirect — using the right client type stops fighting the provider.
3. Secret-free, and correctly so. A Google "Web application" client can require a client_secret even with PKCE (this repo documents that, and the antalya deploy ships the Google secret inline as "public-by-design"). A Desktop/Native client is a true public client — PKCE only, no secret — which fits the repo's "no secrets in config.json" rule and removes the awkward shipped-secret for local use.
4. Refresh tokens / log-in-once. Native clients are designed to receive refresh tokens (offline access) for installed apps, so the local runner can persist a session across restarts; the pure-browser SPA flow (esp. Google) re-prompts each session and can't safely hold a refresh token. (Follow-on benefit once token storage is added.)
5. The runner owns the callback. Catching the code on a dedicated loopback endpoint (instead of depending on the redirect landing on the exact served SPA URL) is more robust, allows an auto-closing "you can return to the app" tab, and lays the groundwork for a headless Python query runner later.

Implementation notes

• build/local.py: add the loopback OAuth broker — PKCE generation, a /callback handler on the loopback server, urllib-based token POST (no secret), and a path to surface the bearer to the SPA. Keep it stdlib-only. Prefer an ephemeral port (RFC-recommended) with a fixed-port fallback.
• Config: local.py can default to loopback for OAuth IdPs; optionally add a small config.json opt-in (e.g. a redirect/mode hint) so the SPA knows the runner brokered the token. No client_secret required → config.json stays secret-free.
• Reuse vs reimplement: the JS PKCE/discovery/exchange in src/core/pkce.js + src/net/oauth*.js is the reference; Python re-implements the same ~30 lines in stdlib (can't import the JS). Keep the JS browser flow unchanged for deployed clusters.
• Docs: update README "run it locally" + "Configuring OAuth" and docs/local-app.html — register a Desktop client, no fixed redirect URI, no secret.
• Tests: build/local.py isn't under the JS coverage gate, but factor the PKCE/exchange/url-building into testable pure Python functions with unit tests; any src/ change keeps the per-file 100% gate.

Acceptance criteria

• With a Desktop IdP client configured, npm run local opens the browser, the user signs in, and the redirect is caught on a 127.0.0.1 loopback server — no fixed web redirect URI and no client_secret registered.
• Works on a non-default PORT with no IdP re-registration.
• The SPA receives the bearer and queries ClickHouse as today (bearer / basic modes unchanged).
• build/local.py stays stdlib-only (no new Python deps); npm test green at the per-file gate.
• README + docs/local-app.html updated for the Desktop-client setup.

Out of scope / follow-on

• Headless Python query runner (using the token to run SQL from Python) — natural next step, not here.
• Deployed-cluster OAuth and the production SPA flow — unchanged.

Open questions

• Ephemeral loopback port vs a fixed default (ephemeral = RFC-recommended; fixed = simpler docs).
• How the bearer reaches the SPA (a localhost-only endpoint the page fetches vs injection) and where it's stored (in-memory vs OS keyring for "log in once").
• Add a config.json field to opt an IdP into desktop/loopback mode, or infer it in local.py?