Add native support for OIDC (OAuth, SSO, Sign-In with Google, ...)

[lovasoa]

High-Level Desciption : enable site developers to authenticate users against an OIDC IdP (Identity Provider) before granting access to SQL-based pages.

1. Summary of Goals

1. Allow site developers to configure an OIDC provider (e.g., Google, Microsoft, Keycloak, Okta, Auth0, or other).
2. Redirect unauthenticated requests to the OIDC IdP: depending on configuration, either redirect all requests, or let developers redirect requests using the authentication component.
3. Create a special route /_sqlpage/oauth in sqlpage to handle the callback from the Identity Provider to create or verify a user session within the application.
4. Validate the user’s identity and any pertinent claims (e.g., role, email).
5. Create a new cookie signed by sqlpage itself containing relevant claims from the original jwt
6. Create new sqlpage functions to read these claims
7. Offer configuration options that adapt to different IdPs.

---

2. Proposed High-Level Architecture

1. OIDC Configuration
   • Add a top-level configuration section (e.g., in the existing configuration file) allowing site administrators to specify OIDC endpoints (authorization, token), client ID, client secret, allowed scopes, etc.
   • Store a small subset of user session data (e.g., ID token, user claims) in server-side session storage, protecting against tampering.

2. OIDC Middleware
   • When OIDC is configured and a request should be authenticated, If no valid session is found, redirect the user to the OIDC provider

   • intercept the request before the execution of the sql,
   • generate the redirect URL (storing the initial target as state),
   • return a 307 redirect to the identity provider

3. Session Management
   • Use cookies to track the session info
   • Ensure session cleaning or expiration logic when tokens become invalid or time out.

4. Claim Injection
   • Expose user claims (email, groups, roles, etc.) to the request handlers through sqlpage functions, so developers can filter or personalize SQL queries.

---

3. Step-by-Step Implementation Plan

Step 0: Setup

Run a local Keycloak instance using docker and configure a "sqlpage" client on it.
Do the oidc flow manually with curl to get familiar with it.
Add a github action on CI that runs a preconfigured keycloak.

Step 1: Configuration

Extend the application configuration to include optional OIDC configuration

• An OIDC configuration endpoint, such as https://accounts.google.com/.well-known/openid-configuration
• An optional OIDC client ID (defaults to sqlpage)
• An optional list of sopes to request (defaults to openid, profile, email)
• An optional protected area: a path prefix under which all requests will need to be authenticated. Defaults to / when OIDC is configured, to null otherwise.

Step 2: Rust redirection logic

Implement the following logic:

• If OIDC is configured AND no valid signed session cookie is present in the request AND
  • Redirect the user to the identity provider's authorization_endpoint with the client_id from the configuration and a state value that contains the originally requested path

Step 3: Callback Handling

1. Add a new endpoint that handles the OIDC callback.
2. Exchange the authorization code for tokens (ID token, access token, refresh token) via the OIDC token endpoint.
3. Validate the ID token signature and claims.
4. Set a session cookie referencing this new session. The sessions cookie is a separate JWT signed by sqlpage itself with relevant claims included.

Step 4: Session and Claim Exposure

1. Add new sqlpage functions to access claims from the sqlpage jwt in the session cookie. sqlpage.user_id(), sqlpage.user_email(), sqlpage.jwt_claim(claim_name). The functions validate the JWT before returning the info. And if the cookie is missing, they trigger the oidc authentication flow with a redirection to the id provider.

Step 5: Documentation and Examples

1. Provide documentation on the official website for site developers on how to enable OIDC, including:
   • Updating the config file with OIDC credentials.
   • Setting up the callback URL in their IdP, with examples for popular providers
   • Testing the authentication flow.
   • Using sqlpage authentication function in SQL queries.
2. Reference best practices (e.g., always using HTTPS in production).

---

4. Usage by Site Developers

1. Configuration – They add their OIDC settings to the config file, including the authorization endpoints, client ID, and secrets.
2. Callback Setup – They register the callback URL (e.g., “/_sqlpage/oauth”) in their OIDC IdP settings.
3. SQL Access – Once the user is authenticated, the site developer can use user claims in their queries. For example, they can filter data in an index.sql based on _user_email or _user_roles.
4. Role-Based Control – If the user needs advanced access control, they can incorporate role checks in the .sql files, rejecting unauthorized roles or restricting queries.

Documentation: https://learn.microsoft.com/en-us/entra/identity-platform/v2-protocols-oidc
This is a follow-up on #82