Authentication Process

How the system handles user authentication from initial login through permission resolution. The DB provides identity validation, group resolution, and permission caching -- but never sees raw passwords or TOTP secrets. The application layer handles credential verification and calls DB functions to record results.

---

Two Authentication Paths

The system supports two authentication paths, both converging at group/permission resolution:

flowchart LR
    A["External Provider<br/>(OAuth / SAML / LDAP)"] --> P["Permission Resolution<br/>ensure_groups_and_permissions()"]
    B["Email / Password"] --> MFA["MFA Check<br/>(email/password only)"]
    MFA --> P
    P --> R["Return: tenant,<br/>groups, permissions"]

Path A -- external providers (AzureAD, Google, LDAP, etc.) go straight to permission resolution. The provider handles its own MFA.

Path B -- email/password goes through status validation, password verification, MFA check, then permission resolution. Two integration options: single-call (verify_user_by_email -- DB compares hashes) or two-step (get_user_by_email_for_authentication + record_login_failure -- app compares hashes).

---

Service Accounts

Authentication functions are called by dedicated service accounts, not by the system superuser (user_id 1). Each service account has only the permissions its role requires:

Service Account	user_id	Role in Auth Flow
svc_registrator	2	User registration, default group assignment, token creation
svc_authenticator	3	Login data retrieval, permission resolution, failure recording
svc_token_manager	4	Token create / validate / set-as-used
svc_api_gateway	5	API key validation at gateway level

-- Example: app calls login function as svc_authenticator (not system user)
select * from auth.get_user_by_email_for_authentication(
    'backend', 3, 'corr-login-001', 'user@example.com');
--            ^ svc_authenticator's user_id

See Service Accounts & Admin Roles for the full list and permission sets.

---

Path A: External Provider Login (OAuth / SAML / LDAP)

Used for AzureAD, Google, Windows Auth, KeyCloak, LDAP, and any other external identity provider.

flowchart TB
    A1["App receives OAuth/SAML token"] --> A2["auth.ensure_user_from_provider()"]
    A2 --> A3{"New user?"}
    A3 -->|Yes| A4["Create user + identity<br/>(blacklist checks)"] --> P1
    A3 -->|No| A5["Validate status<br/>can_login → is_active → identity_active"] --> P1
    P1["auth.ensure_groups_and_permissions()"] --> P2["Return: tenant, groups, permissions"]

Step 1: Ensure User from Provider

The application receives authentication claims from the provider (UID, OID, display name, groups, roles) and passes them to the DB:

select * from auth.ensure_user_from_provider(
    'backend', 3, 'corr-login-001',
    'azuread',                    -- provider_code
    'aad-uid-12345',              -- provider_uid (stable user ID)
    'aad-oid-67890',              -- provider_oid (object ID)
    'john.doe',                   -- username
    'John Doe',                   -- display_name
    'john@example.com',           -- email
    null,                         -- user_data
    '{"ip": "192.168.1.1"}'::jsonb  -- request_context
);

What happens inside:

1. Guard: email provider is rejected -- use auth.register_user instead
2. Provider active check -- raises 52107 if the provider is disabled
3. Lookup existing identity by provider_uid or provider_oid

If new user (not found):

• Blacklist check by provider identity (raises 33019 if blocked)
• Creates auth.user_info (with blacklist-by-username check)
• Creates auth.user_identity
• Logs user_registered event
• Returns immediately (new users pass through -- they were just created)

If existing user:

• Updates UID/OID if the provider rotated them
• Updates user data if username/display_name/email changed
• Validates status (in this order):
  • can_login = false → logs user_login_failed (reason: login_disabled) → raises 52112
  • is_active = false → logs user_login_failed (reason: user_disabled) → raises 52105
  • identity.is_active = false → logs user_login_failed (reason: identity_disabled) → raises 52110
• Updates last_used_provider_code (drives permission calculations)
• Logs user_logged_in event

Returns: user_id, code, uuid, username, email, display_name

Step 2: Resolve Groups & Permissions

select * from auth.ensure_groups_and_permissions(
    'backend', 3, 'corr-login-001',
    __user_id,                    -- from step 1
    'azuread',                    -- provider_code
    array['AzureAD-Group-A'],     -- provider_groups (from OAuth claims)
    array['Admin']                -- provider_roles (from OAuth claims)
);

This function:

1. Stores provider_groups and provider_roles on auth.user_identity
2. Recalculates external group memberships via group mappings
3. Rebuilds the permission cache
4. Returns per-tenant: tenant_id, tenant_uuid, groups[], permissions[], short_code_permissions[]

The application uses the returned permissions for client-side access control and caches them in the session.

---

Path B: Email/Password Login

The DB never sees the raw password. The application hashes the password on its side. Two integration options are available:

Option	Functions	DB Calls	Password Hash Comparison
Single-call (recommended)	auth.verify_user_by_email	1	DB compares hashes
Two-step (legacy)	get_user_by_email_for_authentication + record_login_failure	1–2	App compares hashes

Both options are fully supported. The single-call option is symmetric with auth.verify_mfa_challenge which also handles verification + failure recording + auto-lockout in one call.

Option A: Single-Call Verification (Recommended)

flowchart TB
    B1["App receives email + password"] --> B1a["App hashes password"]
    B1a --> B2["auth.verify_user_by_email(email, hash)"]
    B2 --> B3{"Hash match?"}
    B3 -->|Match| M1{"MFA enrolled?"}
    B3 -->|Mismatch| B5["Log failure + auto-lock check"]
    B5 --> B6{"Auto-lock<br/>threshold?"}
    B6 -->|Yes| B7["Lock account + raise 33004"]
    B6 -->|No| ERR["Raise 52103: invalid credentials"]
    M1 -->|Yes| M2["auth.create_mfa_challenge()"]
    M2 --> M3{"App verifies<br/>TOTP code"}
    M3 -->|Valid| M4["auth.verify_mfa_challenge()"] --> P1
    M3 -->|Invalid| MF["Token failed + log event"]
    MF --> B6
    M1 -->|No| M5{"MFA policy<br/>requires setup?"}
    M5 -->|Yes| M6["Redirect to MFA enrollment"]
    M5 -->|No| P1["auth.ensure_groups_and_permissions()"]
    P1 --> P2["Return: tenant, groups, permissions"]

select * from auth.verify_user_by_email(
    3, 'corr-login-002', 'user@example.com', __app_computed_hash,
    '{"ip": "192.168.1.1"}'::jsonb);

What happens inside:

1. Permission check (authentication.get_data)
2. Provider active check (email provider must be enabled)
3. User lookup by email via user_identity (provider_code = email, uid = email)
4. Status validation (same order as the two-step flow):
   • user not found → logs user_login_failed (reason: user_not_found) → raises 52103
   • can_login = false → raises 52112
   • is_active = false → raises 52105
   • identity.is_active = false → raises 52110
   • is_locked = true → raises 52106
5. Hash comparison: stored_hash vs _password_hash
   • Mismatch → logs user_login_failed (reason: wrong_password) → checks auto-lockout → raises 33004 if locked, 52103 otherwise
   • Match → updates last_used_provider_code → logs user_logged_in → returns user data

Returns: user_id, code, uuid, username, email, display_name

Cleaner audit trail

Unlike the two-step flow, the user_logged_in event is only logged after a confirmed hash match. In the two-step flow, the event is logged before the app verifies the password.

Option B: Two-Step Flow

flowchart TB
    B1["App receives email + password"] --> B2["auth.get_user_by_email_for_authentication()"]
    B2 --> B3["Validate status<br/>can_login → is_active → identity_active → is_locked"]
    B3 --> B4{"App verifies<br/>password hash"}
    B4 -->|Match| M1{"MFA enrolled?"}
    B4 -->|Mismatch| B5["auth.record_login_failure()"]
    B5 --> B6{"Auto-lock<br/>threshold?"}
    B6 -->|Yes| B7["Lock account + clear cache"]
    B6 -->|No| ERR["Raise: invalid credentials"]
    M1 -->|Yes| M2["auth.create_mfa_challenge()"]
    M2 --> M3{"App verifies<br/>TOTP code"}
    M3 -->|Valid| M4["auth.verify_mfa_challenge()"] --> P1
    M3 -->|Invalid| MF["Token failed + log event"]
    MF --> B6
    M1 -->|No| M5{"MFA policy<br/>requires setup?"}
    M5 -->|Yes| M6["Redirect to MFA enrollment"]
    M5 -->|No| P1["auth.ensure_groups_and_permissions()"]
    P1 --> P2["Return: tenant, groups, permissions"]

Step 1: Get User Data

select * from auth.get_user_by_email_for_authentication(
    3, 'corr-login-002', 'user@example.com');

Validation order:

1. Permission check (authentication.get_data)
2. Provider active check (email provider must be enabled)
3. User lookup by email via user_identity (provider_code = email, uid = email)
4. user not found → logs user_login_failed (reason: user_not_found) → raises 52103
5. can_login = false → raises 52112
6. is_active = false → raises 52105
7. identity.is_active = false → raises 52110
8. is_locked = true → raises 52106
9. All checks pass → logs user_logged_in

Returns: user_id, code, uuid, username, email, display_name, provider, password_hash, password_salt

Step 2: App Verifies Password

The application compares the password hash using its own hashing library (bcrypt, argon2, etc.). The DB is not involved.

Step 3a: Password Correct → MFA Check, then Permission Resolution

After password verification, the app checks MFA (see MFA Step below), then resolves permissions with auth.ensure_groups_and_permissions.

Step 3b: Password Wrong → Record Failure

select auth.record_login_failure(3, 'corr-login-002', __user_id, 'user@example.com',
    '{"ip": "192.168.1.1"}'::jsonb);

This function:

1. Logs user_login_failed event (reason: wrong_password)
2. Calls unsecure.check_and_auto_lock_user which:
   • Counts user_login_failed events in the configured time window (default: 15 minutes)
   • If count >= threshold (default: 5): locks the account, clears permission cache, logs user_auto_locked event
3. Raises 33004 (user locked) if auto-locked, otherwise 52103 (invalid credentials)

Configurable lockout

The threshold and window are stored in const.sys_param and can be changed at runtime without code changes:

select auth.update_sys_param(1, 'login_lockout', 'max_failed_attempts', _number_value := 10);
select auth.update_sys_param(1, 'login_lockout', 'window_minutes', _number_value := 30);

---

MFA Step (Email/Password Only)

MFA applies only to email/password authentication. External providers (AzureAD, Google, etc.) handle their own multi-factor authentication -- by the time the app receives an OAuth/SAML token, the provider has already enforced its MFA policy.

After successful password verification, the application checks whether MFA is required and whether the user has it set up. See Multi-Factor Authentication for full function reference.

Check MFA Status

-- Is MFA enrolled and confirmed?
select * from auth.get_mfa_status(3, 'corr-login-003', __user_id);

-- Is MFA required by policy? (no permission check -- used in login flow)
select unsecure.is_mfa_required(__user_id, __tenant_id);

Three possible outcomes:

MFA Enrolled?	MFA Required by Policy?	Action
Yes (confirmed)	--	Challenge the user (TOTP)
No	Yes	Redirect to MFA enrollment setup
No	No	Skip MFA, proceed to permission resolution

Challenge & Verify

-- 1. Create a 5-minute challenge token
select * from auth.create_mfa_challenge('backend', 3, 'corr-login-003', __user_id, 'totp');
-- Returns: token_uid, expires_at

-- 2. App shows TOTP input, user enters code, app verifies against secret

-- 3. Report result to DB
select auth.verify_mfa_challenge('backend', 3, 'corr-login-003', __user_id,
    __token_uid, true);  -- _code_is_valid = true (TOTP matched)

-- OR: user lost authenticator, uses recovery code
select auth.verify_mfa_challenge('backend', 3, 'corr-login-003', __user_id,
    __token_uid, false, 'recovery-code-here');

Recovery codes are single-use -- each used code is removed from the stored hash array.

MFA failures count toward lockout

Failed MFA attempts (wrong TOTP code, invalid recovery code) log a mfa_challenge_failed event and trigger the same auto-lockout check as wrong passwords. Both event types are counted together within the lockout window. If the combined count exceeds the threshold, the account is locked (raises 33004).

---

Blacklist Checks

Blacklist checks prevent deleted or banned users from re-registering or re-authenticating. They happen automatically at three points:

Check Point	What's Checked	Error
ensure_user_from_provider (new user branch)	Provider UID + OID	33019
unsecure.create_user_info (all user creation)	Username	33018
unsecure.create_user_identity (defence-in-depth)	Provider UID + OID	33019

Existing users are not re-checked against the blacklist on login -- only new user creation paths are guarded.

---

Token System

Tokens are used for password reset, email verification, invitations, and MFA challenges. The application generates the token value; the DB manages lifecycle and validation.

Token Types

Type	Default Expiry	Use
password_reset	1 hour	Password reset flow
email_verification	24 hours	Email address verification
invite	7 days	User invitations
mfa	5 minutes	MFA challenge

Custom token types can be registered via auth.create_token_type.

Token Lifecycle

stateDiagram-v2
    [*] --> valid: create_token
    valid --> used: set_token_as_used / validate_token(set_as_used=true)
    valid --> expired: expiry check (automatic)
    valid --> invalid: new token of same type created for same user
    valid --> validation_failed: set_token_as_failed
    valid --> failed: verify_mfa_challenge (code invalid)

Key behaviors:

• Auto-invalidation: Creating a new token of the same type for the same user automatically invalidates previous valid tokens
• Opportunistic expiry: unsecure.expire_tokens is called during token creation and validation, batch-expiring any globally overdue tokens
• User binding: Tokens are bound to a specific user; validate_token can verify the token belongs to the expected user

---

Complete Email/Password Login -- Code Examples

Single-Call (Recommended)

-- Step 1: App hashes password, DB verifies everything in one call
select * from auth.verify_user_by_email(
    3, 'corr-001', 'user@example.com', __app_computed_hash,
    '{"ip": "192.168.1.1"}'::jsonb)
into __user;
-- Raises on: user not found, disabled, locked, can't login, wrong password
-- On wrong password: also records failure + checks auto-lockout

-- Step 2: Check MFA
select * from auth.get_mfa_status(3, 'corr-001', __user.user_id) into __mfa;

if __mfa.is_confirmed and __mfa.is_enabled then
    -- Step 2a: MFA challenge
    select * from auth.create_mfa_challenge(
        'backend', 3, 'corr-001', __user.user_id, 'totp') into __challenge;
    -- App shows TOTP input, verifies code
    select auth.verify_mfa_challenge(
        'backend', 3, 'corr-001', __user.user_id,
        __challenge.token_uid, __code_is_valid);
else
    -- Step 2b: Check if MFA is required by policy
    select unsecure.is_mfa_required(__user.user_id, 1) into __mfa_required;
    -- If true: redirect to MFA enrollment
end if;

-- Step 3: Resolve permissions (as svc_authenticator)
select * from auth.ensure_groups_and_permissions(
    'backend', 3, 'corr-001', __user.user_id, 'email');
-- Returns: tenant_id, tenant_uuid, groups[], permissions[]

Two-Step (Legacy)

-- Step 1: Get user data (as svc_authenticator)
select * from auth.get_user_by_email_for_authentication(
    3, 'corr-001', 'user@example.com')
into __user;
-- Raises on: user not found, disabled, locked, can't login

-- Step 2: App verifies password (NOT in the DB)
-- if bcrypt.verify(input_password, __user.password_hash) fails:
select auth.record_login_failure(3, 'corr-001', __user.user_id, 'user@example.com',
    '{"ip": "192.168.1.1"}'::jsonb);
-- Raises: 33004 (locked) or 52103 (invalid credentials)

-- Step 3: Check MFA
select * from auth.get_mfa_status(3, 'corr-001', __user.user_id) into __mfa;

if __mfa.is_confirmed and __mfa.is_enabled then
    -- Step 3a: MFA challenge
    select * from auth.create_mfa_challenge(
        'backend', 3, 'corr-001', __user.user_id, 'totp') into __challenge;
    -- App shows TOTP input, verifies code
    select auth.verify_mfa_challenge(
        'backend', 3, 'corr-001', __user.user_id,
        __challenge.token_uid, __code_is_valid);
else
    -- Step 3b: Check if MFA is required by policy
    select unsecure.is_mfa_required(__user.user_id, 1) into __mfa_required;
    -- If true: redirect to MFA enrollment
end if;

-- Step 4: Resolve permissions (as svc_authenticator)
select * from auth.ensure_groups_and_permissions(
    'backend', 3, 'corr-001', __user.user_id, 'email');
-- Returns: tenant_id, tenant_uuid, groups[], permissions[]

---

API Key Authentication

For service-to-service communication. Each API key has a linked "technical user" so permission checks work uniformly.

-- Validate an API key (as svc_api_gateway)
select * from auth.validate_api_key(
    'gateway', 5, 'corr-api-001',
    'the-api-key', 'the-api-secret');
-- Returns: api_key_id, user_id (technical user), tenant_id, permissions
-- Raises 30001 if key/secret combination is invalid

See API Keys for the full API key lifecycle.

---

Summary: What the DB Does vs. What the App Does

Responsibility	DB	App
User lookup & status validation	Yes	--
Password hashing	--	Yes
Password hash comparison	Yes (verify_user_by_email) or App (two-step flow)	Optional
TOTP secret generation & verification	--	Yes
Recovery code hashing & matching	Yes	--
Lockout counting & enforcement	Yes	--
Blacklist checking	Yes	--
Group mapping resolution	Yes	--
Permission cache calculation	Yes	--
MFA policy resolution	Yes	--
Token lifecycle management	Yes	--
Session management	--	Yes
OAuth/SAML token exchange	--	Yes