sqlpage--sqlpage/examples/single sign on

SQLPage Single Sign-On demo

This project demonstrates how to implement external authentication (Single Sign-On) in a SQLPage application using SQLPage's built-in OIDC support.

It demonstrates the implementation of two external authentication protocols:

• OpenID Connect (OIDC)
• Central Authentication Service (CAS)

Depending on your use case, you can choose the appropriate protocol for your application.

Screenshots

Home Page	Login Page	User Info

Running the Demo

To run the demo, you just need docker and docker-compose installed on your machine. Then, run the following commands:

docker compose up --watch

This will start a Keycloak server and a SQLPage server. You can access the SQLPage application at http://localhost:8080.

The credentials for the demo are:

• Username: demo
• Password: demo

The credentials to the keycloak admin console accessible at http://localhost:8180 are admin/admin.

CAS Client

This example also contains a CAS (Central Authentication Service) client in the cas directory that demonstrates how to authenticate users using the CAS protocol (version 3.0), which is mostly used in academic institutions.

OIDC Client

OIDC is an authentication protocol that allows users to authenticate with a third-party identity provider and then access applications without having to log in again. This is useful for single sign-on (SSO) scenarios where users need to access multiple applications with a single set of credentials. OIDC can be used to implement a "Login with Google" or "Login with Facebook" button in your application, since these providers support the OIDC protocol.

SQLPage has built-in support for OIDC authentication since v0.35. This project demonstrates how to use it with the free and open source Keycloak OIDC provider. You can easily replace Keycloak with another OIDC provider, such as Google, or your enterprise OIDC provider, by following the steps in the Configuration section.

Public and Protected Pages

By default, SQLPage's built-in OIDC support protects the entire website. However, you can configure it to have a mix of public and protected pages using the oidc_protected_paths option in your sqlpage.json file.

This allows you to create a public-facing area (like a homepage with a login button) and a separate protected area for authenticated users.

Configuration

To use OIDC authentication in your own SQLPage application, you need to configure it in your sqlpage.json file:

{
  "oidc_issuer_url": "https://your-keycloak-server/auth/realms/your-realm",
  "oidc_client_id": "your-client-id",
  "oidc_client_secret": "your-client-secret",
  "host": "localhost:8080",
  "oidc_protected_paths": ["/protected"]
}

The configuration parameters are:

• oidc_issuer_url: The base URL of your OIDC provider
• oidc_client_id: The ID that identifies your SQLPage application to the OIDC provider
• oidc_client_secret: The secret key for your SQLPage application
• host: The web address where your application is accessible

Accessing User Information

Once OIDC is configured, you can access information about the authenticated user in your SQL files using these functions:

• sqlpage.user_info(claim_name): Get a specific claim about the user (like name or email)
• sqlpage.user_info_token(): Get the entire identity token as JSON

Example:

select 'text' as component, 'Welcome, ' || sqlpage.user_info('name') || '!' as contents_md;

Implementation Details

The demo includes several SQL files that demonstrate different aspects of OIDC integration:

1. index.sql: A public page that shows a welcome message and a login button. If the user is logged in, it displays their email and a link to the protected page.

2. protected.sql: A page that is only accessible to authenticated users. It displays the user's information.

3. logout.sql: Logs the user out by removing the authentication cookie and redirecting to the OIDC provider's logout page.

Docker Setup

The demo uses Docker Compose to set up both SQLPage and Keycloak. The configuration includes:

• SQLPage service with:

  • Volume mounts for the web root and configuration
  • CAS configuration for optional CAS support
  • Debug logging enabled

• Keycloak service with:

  • Pre-configured realm and users
  • Health checks to ensure it's ready before SQLPage starts
  • Admin credentials for management

References

• SQLPage OIDC Documentation
• OpenID Connect
• Authorization Code Flow