Skip to main content
Returns specific information about the currently logged-in user from the OIDC ID token.

​
Signature

sqlpage.user_info(claim TEXT) -> TEXT

​
Parameters

​
claim
TEXT
required
The name of the claim (piece of information) to retrieve from the ID token

​
Return Value

​
return
TEXT
The value of the requested claim, or NULL if not available or user is not logged in

​
Description

The user_info() function provides access to user information when using OIDC Single Sign-On. It extracts specific claims from the ID token issued by your identity provider. This function only works when OIDC is configured in sqlpage.json. If OIDC is not configured or the user is not logged in, it returns NULL.

​
Common Claims

​
User Identity

​
sub
TEXT
Subject - Unique identifier for the user. Use this for database foreign keys.
​
email
TEXT
Email - User’s email address (may not be verified)
​
preferred_username
TEXT
Username - The username the user prefers

​
Personal Information

​
name
TEXT
Full name - User’s complete name
​
given_name
TEXT
First name - User’s given name
​
family_name
TEXT
Last name - User’s family name
​
picture
TEXT
Profile picture URL - Link to user’s profile image

​
Examples

​
Personalized Welcome Message

Greet the user by name: 
SELECT 'text' as component,
    'Welcome back, **' || sqlpage.user_info('given_name') || '**!' as contents_md;

​
User Profile Card

Display user information: 
SELECT 'card' as component;
SELECT 
    sqlpage.user_info('name') as title,
    sqlpage.user_info('email') as description,
    sqlpage.user_info('picture') as image;

​
Store User in Database

Create or update user record on first login: 
SET user_sub = sqlpage.user_info('sub');
SET user_email = sqlpage.user_info('email');
SET user_name = sqlpage.user_info('name');

-- Insert or update user
INSERT INTO users (sub, email, name, last_login)
VALUES ($user_sub, $user_email, $user_name, CURRENT_TIMESTAMP)
ON CONFLICT (sub) DO UPDATE SET
    email = EXCLUDED.email,
    name = EXCLUDED.name,
    last_login = EXCLUDED.last_login;

​
Role-Based Access Control

Use custom claims for authorization: 
SET user_role = sqlpage.user_info('role');

-- Restrict to admins
SELECT 'redirect' as component,
    '/forbidden.sql' as link
WHERE $user_role != 'admin';

-- Admin-only content
SELECT 'text' as component,
    'Admin Dashboard' as title;

​
User-Specific Content

Show content filtered by user: 
SET user_id = sqlpage.user_info('sub');

SELECT 'list' as component,
    'Your Documents' as title;

SELECT 
    title,
    created_at as description,
    '/view.sql?doc=' || id as link
FROM documents
WHERE owner_id = $user_id
ORDER BY created_at DESC;

​
Conditional UI Elements

Show different navigation for logged-in users: 
SET is_logged_in = sqlpage.user_info('sub') IS NOT NULL;

SELECT 'shell' as component,
    'My App' as title,
    CASE 
        WHEN $is_logged_in THEN json_array(
            json_object('title', 'Dashboard', 'link', '/dashboard.sql'),
            json_object('title', 'Profile', 'link', '/profile.sql'),
            json_object('title', 'Logout', 'link', sqlpage.oidc_logout_url('/'))
        )
        ELSE json_array(
            json_object('title', 'Login', 'link', '/login.sql')
        )
    END as menu_item;

​
Audit Logging

Track user actions: 
SET user_id = sqlpage.user_info('sub');
SET user_email = sqlpage.user_info('email');

INSERT INTO audit_log (user_id, user_email, action, timestamp)
VALUES ($user_id, $user_email, 'deleted_record', CURRENT_TIMESTAMP);

DELETE FROM records WHERE id = :record_id;

​
Available Claims

The exact claims available depend on your identity provider and requested scopes:

​
Core Claims (Always Available)

  • sub - Unique user identifier
  • iss - Issuer (identity provider URL)
  • aud - Audience (your client ID)
  • exp - Token expiration time (Unix timestamp)
  • iat - Token issued time (Unix timestamp)

​
Standard Claims (Depends on Scopes)

Request these scopes in your OIDC configuration to get these claims: Profile scope:
  • name, given_name, family_name, middle_name
  • nickname, preferred_username
  • profile (profile page URL)
  • picture (profile image URL)
  • website
  • gender, birthdate, zoneinfo, locale
  • updated_at
Email scope:
  • email
  • email_verified
Phone scope:
  • phone_number
  • phone_number_verified
Address scope:
  • address (JSON object with street, city, country, etc.)

​
Custom Claims

Some identity providers allow custom claims: 
-- Example custom claims
SET user_role = sqlpage.user_info('role');
SET tenant_id = sqlpage.user_info('tenant_id');
SET department = sqlpage.user_info('department');

​
Security Best Practices

​
Always Use ‘sub’ for User Identification

-- CORRECT - Use sub for foreign keys
INSERT INTO posts (author_id, content)
VALUES (
    sqlpage.user_info('sub'),
    :content
);

-- WRONG - Email can change or be unverified
INSERT INTO posts (author_email, content)
VALUES (
    sqlpage.user_info('email'),  -- Don't do this!
    :content
);
Why sub?
  • Guaranteed unique per user
  • Never changes for a user
  • Always present in ID tokens
  • Not personally identifiable

​
Validate Email Verification

If you need to trust the email address: 
SET email = sqlpage.user_info('email');
SET email_verified = sqlpage.user_info('email_verified');

SELECT 'redirect' as component,
    '/verify-email.sql' as link
WHERE $email_verified != 'true';

​
Check for NULL Values

Always handle missing claims: 
SET user_name = COALESCE(
    sqlpage.user_info('name'),
    sqlpage.user_info('email'),
    'Guest'
);

SELECT 'text' as component,
    'Welcome, ' || $user_name as contents;

​
Troubleshooting

​
Claim Not Available

If a claim returns NULL:
  1. Check OIDC is configured in sqlpage.json
  2. Verify user is logged in
  3. Check you requested the right scopes
  4. Use sqlpage.user_info_token() to see all available claims
  5. Check your identity provider’s documentation

​
Debugging Available Claims

SELECT 'code' as component;
SELECT 
    'Available Claims' as title,
    'json' as language,
    sqlpage.user_info_token() as contents;

​
Provider-Specific Notes

​
Google

  • Requires email and profile scopes for most claims
  • email_verified is always true for Google accounts
  • sub is stable but unique per client ID

​
Azure AD / Microsoft

  • Uses preferred_username for email
  • Provides roles claim for app roles
  • Supports custom claims through app registration

​
Keycloak

  • Highly configurable claims
  • Supports realm and client roles
  • Can add custom user attributes as claims

​
Auth0

  • Supports custom claims with namespacing
  • Provides user_metadata and app_metadata
  • Claims can be customized via rules

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us