Is your feature request related to a problem? Please describe.
Currently, the JS code in DB Console uses cookie-based auth for all requests to CRDB. This works smoothly because the browser handles all the cookie storage and security and the cookie stored with the httponly tag that prevents any JS access.

This model presents some struggles with attempting to use endpoints under the newer /api/v2 prefix since these endpoints weren't built with a UI as a primary consumer. They implement header-based authentication, and do not accept cookies.

Attempting to use the header-based auth in DB console in order to use the recently merged SQL-over-HTTP endpoint (#79663) has presented some challenges because we need to add the session auth header but have no programmatic access to it since the browser hides it from us in the list of cookies.

Describe the solution you'd like
Ideally, the /api/v2 should accept cookie-based authentication just like the rest of the endpoints we currently rely on since this is how UIs like the DB Console expect to authenticate with backends and it lets us rely on browser-based security models instead of storing our own auth header...somewhere else.

One proposal from @knz is to allow cookie-based auth for read-only queries only instead of the current read/write ability in the new endpoint. Observability needs are almost entirely read-only and we can find other solutions or custom gRPC endpoints for write endpoints if we have to. This can mitigate the blast radius of security CSRF attacks while allowing for smoother DB Console development.

Describe alternatives you've considered

• The UI could login with the /api/v2 endpoint separately and store the session in redux (does not persist between browser sessions) or local storage (persists between browser sessions but is insecure)
• /api/v2 auth code could be modified to accept cookie-based auth. (reduces security for all API endpoints)
• /api/v2 auth code could accept cookie-based auth but only with a CSRF token which would be retrieved in a separate call (this adds complexity to the UI code).

Slack thread

Jira issue: CRDB-17583