Skip to main content

​
Authentication

SQLPage provides multiple approaches to authenticate users in your web applications. Choose the method that best fits your security requirements and infrastructure. The most common authentication pattern uses cookies to maintain user sessions after login.

​
Database Schema

First, create tables to store user accounts and sessions: 
CREATE TABLE IF NOT EXISTS accounts (
    username TEXT COLLATE NOCASE PRIMARY KEY,
    password_hash TEXT COLLATE BINARY NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    id TEXT COLLATE NOCASE PRIMARY KEY,
    username TEXT COLLATE NOCASE NOT NULL
        REFERENCES accounts(username),
    created_at TEXT COLLATE NOCASE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

​
User Registration

Use sqlpage.hash_password() to securely hash passwords: 
-- signup_form.sql
SELECT 'form' AS component,
    'Create Account' AS title,
    'create_user.sql' AS action;

SELECT 'username' AS name, TRUE AS required;
SELECT 'password' AS name, 'password' AS type,
    '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' AS pattern,
    'Password must be at least 8 characters with a letter and number' AS description;

-- create_user.sql
INSERT INTO accounts (username, password_hash)
VALUES (
    :username,
    sqlpage.hash_password(:password)
)
RETURNING 'redirect' AS component, 'login.sql' AS link;

​
Login Flow

Use the authentication component to verify credentials: 
-- login.sql
SELECT 'authentication' AS component,
    'signin.sql?error' AS link,
    (SELECT password_hash FROM accounts WHERE username = :username) AS password_hash,
    :password AS password;

-- Generate a random session ID and save it as a cookie
INSERT INTO sessions (id, username)
VALUES (sqlpage.random_string(32), :username)
RETURNING
    'cookie' AS component,
    'session' AS name,
    id AS value;

-- Redirect to protected page
SELECT 'redirect' AS component, 'dashboard.sql' AS link;

​
Protecting Pages

Check for valid sessions at the top of protected pages: 
-- Protected page
SELECT 'redirect' AS component, 'login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions
    WHERE
        sqlpage.cookie('session') = id AND
        created_at > datetime('now', '-1 day')
);

-- Rest of your page content
SELECT 'text' AS component,
    'Welcome back!' AS title;

​
Logout

Clear the session cookie and database record: 
-- logout.sql
DELETE FROM sessions WHERE id = sqlpage.cookie('session');

SELECT 'cookie' AS component,
    'session' AS name,
    '' AS value;

SELECT 'redirect' AS component, '/' AS link;

​
HTTP Basic Authentication

For simple applications or API endpoints, use HTTP Basic Auth with the authentication component: 
SELECT 'authentication' AS component,
    (SELECT password_hash FROM accounts WHERE username = sqlpage.basic_auth_username()) AS password_hash,
    sqlpage.basic_auth_password() AS password;

-- User is authenticated, display content
SELECT 'text' AS component,
    'Hello ' || sqlpage.basic_auth_username() || '!' AS contents;

​
OpenID Connect (OIDC) Single Sign-On

SQLPage has built-in support for OIDC authentication, enabling “Login with Google”, enterprise SSO, or self-hosted identity providers like Keycloak.

​
Configuration

Add OIDC settings to your sqlpage/sqlpage.json: 
{
  "oidc_issuer_url": "https://accounts.google.com",
  "oidc_client_id": "your-client-id",
  "oidc_client_secret": "your-client-secret",
  "host": "myapp.example.com",
  "oidc_protected_paths": ["/protected"]
}

​
Configuration Parameters

  • oidc_issuer_url: Your OIDC provider’s base URL
  • oidc_client_id: Application ID from your OIDC provider
  • oidc_client_secret: Secret key for authentication (keep confidential)
  • host: Your application’s web address
  • oidc_protected_paths: URL paths requiring authentication (default: ["/"] protects everything)
  • oidc_public_paths: URL paths accessible without authentication (default: [])

​
Public and Protected Pages

By default, OIDC protects your entire site. To mix public and protected pages: 
{
  "oidc_protected_paths": ["/admin", "/dashboard"],
  "oidc_public_paths": ["/", "/about"]
}

​
Accessing User Information

Retrieve authenticated user data in your SQL files: 
-- Get user's email
SET email = sqlpage.user_info('email');

-- Show different content based on authentication
SELECT 'hero' AS component,
    'Welcome' AS title,
    '/protected' AS link,
    'Log in' AS link_text
WHERE $email IS NULL;

SELECT 'text' AS component,
    'Welcome back, ' || sqlpage.user_info('name') || '!' AS title,
    'You are logged in as ' || sqlpage.user_info('email') AS contents
WHERE $email IS NOT NULL;

​
Available User Claims

Common claims available via sqlpage.user_info():
  • name - Full name
  • email - Email address
  • sub - Unique user identifier
  • preferred_username - Username
  • given_name, family_name - First and last names
Get the full token as JSON: 
SET user_token = sqlpage.user_info_token();

​
Logout

Create a logout link using the secure OIDC logout flow: 
SELECT 'button' AS component;
SELECT
    'Log out' AS title,
    sqlpage.oidc_logout_url() AS link,
    'red' AS color;
This URL:
  1. Verifies the CSRF token
  2. Removes authentication cookies
  3. Redirects to the OIDC provider’s logout endpoint
  4. Returns to your homepage

​
Security Best Practices

​
Password Storage

  • Always use sqlpage.hash_password() - never store plain text passwords
  • The function uses Argon2id, a secure password hashing algorithm
  • Each password gets a unique salt automatically

​
Session Security

  • Use strong random session IDs: sqlpage.random_string(32)
  • Set session expiration times (e.g., 1 day)
  • Delete expired sessions regularly:
DELETE FROM sessions
WHERE created_at < datetime('now', '-1 day');

​
HTTPS

Enable HTTPS in production to protect credentials in transit: 
{
  "https_domain": "myapp.example.com",
  "https_certificate_email": "[email protected]"
}
SQLPage will automatically request and manage Let’s Encrypt certificates. For production HTTPS sites, set secure cookie flags: 
SELECT 'cookie' AS component,
    'session' AS name,
    session_id AS value,
    TRUE AS secure,        -- Only send over HTTPS
    TRUE AS http_only;     -- Not accessible via JavaScript

​
Examples

See complete working examples in the SQLPage repository:
  • Cookie-based authentication: examples/user-authentication/
  • Image gallery with auth: examples/image gallery with user uploads/
  • OIDC single sign-on: examples/single sign on/
  • CRUD with authentication: examples/CRUD - Authentication/

Build docs developers (and LLMs) love

Get started for freeTalk to us