api: support cookie-based auth for read-only queries for sql-over-http

[dhartunian]

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

[knz]

Based on our convo today I think a good next step would be to define a cookie-only read-only alternative to the current endpoint. This could also leverage follower reads for better performance. We can then discuss other options after the initial prototype is out.

@bdarnell @kpatron-cockroachlabs - an extra 👀 from you would be welcome on this.

[sjbarag]

local storage (persists between browser sessions but is insecure)

IIRC browsers provide per-domain isolation of localStorage — can you elaborate on what's insecure about putting a session token in localStorage? Maybe I'm mis-remembering things 😅

[sjbarag]

local storage (persists between browser sessions but is insecure)

IIRC browsers provide per-domain isolation of localStorage — can you elaborate on what's insecure about putting a session token in localStorage? Maybe I'm mis-remembering things 😅

Ah, @xinhaoz just linked me to https://cheatsheetseries.owasp.org/cheatsheets/HTML5_Security_Cheat_Sheet.html#local-storage , so that makes sense.

[kpatron-cockroachlabs]

I'm personally a fan of continuing with HttpOnly cookies so that even if there is an XSS vulnerability the attacker can't exfiltrate the credential, which makes attacks harder. If for some reason that is not feasible I'd personally prefer non-HttpOnly cookies to LocalStorage as I believe LocalStorage doesn't allow expiring values nor the equivalent "session" cookie functionality.

I think preventing CSRF attacks would require less effort than seems to be implied on this issue and the Slack thread. Some ways to solve the problem:

• Set the SameSite property on the cookie. Notably the Lax value is already the default on some modern browsers which allows users to navigate via third party links to our sites but restricts other types of requests (no loading images for example). We'd still want to manually set the SameSite field as Firefox and Safari don't appear to treat Lax as the default.
• Require a content-type header of application/json (or similar) for all cookie authed POST requests. As this doc lays out, you only can do SIMPLE cross origin requests. These requirements are very strict and include the request is only SIMPLE if the HTTP verb is GET HEAD or POST and if the Content-Type is text/plain, multipart/form-data, or application/x-www-form-urlencoded. It is important to note that non-SIMPLE requests do not run the risk of CSRF as the browser will block them unless the proper Access-control-* headers are populated by the server in response to options requests.
• Similar to above, we could guarantee that by a request isn't simple by having the server check that an additional header has a value. This header could be Authorization or could be X-CRL-CUSTOM-STUFF. The contents of these headers wouldn't need to be any in particular. Enforcing that they have any value would be sufficient.

Some notes:

• SameSite=Lax and setting the content type requires that we don't have GET endpoints that have side effects or are excessively expensive. This is already a best practice but this would up the stakes.
• Setting an additional header or setting SameSite=secure would also protect the GET endpoints although the latter might overprotect by preventing even following links (this would be fine for the API but problematic for other uses of this cookie).