SSO JIT and Profile Sync Developer Guide

SSO auto-create and profile sync - Developer Guide

Shipped in 2.10.0. Contributed by Santhosh Srinivasan (Sandy, @srinivasansanthosh) in discussion #155.

This page explains how the feature works underneath, file by file, with the real code. For what it does from an admin's point of view, see Single Sign-On (SSO). For how the contribution was reviewed, see the review notes.

---

Thank you, Sandy

This is FreeITSM's first sizeable feature from an outside contributor, and it came from a real deployment: one company sign-in (Entra ID / Okta) used by both IT staff and customers. Sandy spotted four gaps, built all four, and then worked through a ten-point review in a single follow-up commit. The design is his - the two switches, the three fallback modes, the three sync modes, the claim mapping across Entra, Okta and Keycloak, the confirm screen with its one-time CSRF token, and the "Analyst console" link that fills the gap left by #81.

What I added when merging is listed in What changed at merge time, so it's clear which parts are whose.

---

The four things it does, in plain English

#	Before	After
1	One switch, Auto-create users, meant "create an analyst" on the analyst login and "create a requester" on the portal. You couldn't say "customers yes, staff no".	Two switches: Auto-create portal requesters and Auto-create IT analysts.
2	A customer who used the company sign-in on the analyst login got an error.	A per-provider choice: warn and confirm (offer the portal), automatically switch, or block.
3	Name, title, department and phone were typed in by hand, and drifted from the directory.	Profile sync: never, on account creation, or always. "Always" makes those fields read-only in FreeITSM.
4	The analyst side had a link to the portal (#81), but nothing came back.	The portal's account menu shows Analyst console to anyone who is also an active analyst.

---

Files

🗄️ schema · 🔐 sign-in · 📖 read · ✏️ write · 🖥️ UI · ❓ help · 🧪 diagnostics

🎨	File	What it does for this feature
🗄️	database/freeitsm.sql	Three new auth_providers columns for fresh installs
🗄️	includes/db_verify_schema.php	The same three columns for Database Verification
🗄️	api/system/db_verify.php	One-time copy of the old switch into the new one on upgrade
🔐	api/auth/oidc_callback.php	The heart of it: JIT split, fallback, confirm POST, userinfo, oidcSyncProfile(), oidcExtractProfileAttributes()
🔐	includes/oidc.php	oidcFetchUserInfo() - calls the userinfo endpoint with the access token
🔐	includes/ldap.php	ldapResolveAnalyst() reads the analyst switch instead of the portal one
🔐	includes/sso_identity.php	ssoJitColumnsReady(), ssoProfileSyncLocks(), ssoAnalystProfileLocked() - shared by every reader
🖥️	auth/sso_confirm_portal.php	New page: "No analyst account - Continue / Cancel"
🔐	auth/login.php	?cancel_sso=1 clears a parked portal sign-in
📖	api/system/get_sso_providers.php	Returns the new columns (or what the old switch meant, before verification)
✏️	api/system/save_sso_provider.php	Saves them; validates the two ENUMs; leaves them out until the columns exist
📖	includes/users.php	portalProfileAccess() adds the five synced fields to the portal's locked list
📖	api/myaccount/get_signatures.php	Tells My details whether it is locked (sso_synced)
✏️	api/myaccount/save_profile.php	Refuses the save when locked - the real guard
🖥️	system/sso/index.php	Provider dialog fields, "Profile sync" column, auto-create pill
🖥️	system/sso/provider.php	LDAP page: the analyst switch (not sync or fallback - LDAP ignores both)
🖥️	system/preferences/index.php	My details: greyed fields, note, no Save button when locked
🖥️	self-service/includes/user-menu.php	The Analyst console menu item
❓	system/help/sso.php, system/help/_registry.php, system/help/portal-profile.php	Two new help sections: Auto-create rules and Profile sync
🧪	api/system/debug-tools/D010_signin_methods.php	The sign-in diagnostic shows both switches, the fallback and sync mode
🧪	includes/feature_bingo/cards/system-core.php	Three new Feature Bingo cards, one reworded
🌐	lang/en/auth.php, lang/en/system.php, lang/en/self-service.php	29 new English strings (existing wording left alone)

What was deliberately not touched: the portal's own login flow (self-service/login.php), the CardDAV write-back rules, users.is_managed, and anything to do with passwords or MFA.

---

1. The schema, and the upgrade

Three columns on auth_providers:

`auto_create_analysts`   TINYINT(1) NOT NULL DEFAULT 0,
`analyst_fallback_mode`  ENUM('confirm','redirect','block') NOT NULL DEFAULT 'confirm',
`profile_sync_mode`      ENUM('always','initial','never') NOT NULL DEFAULT 'never',

auto_create_users stays, and now means portal requesters only.

The defaults are for a new provider. An existing provider must behave exactly as before, so Database Verification does a one-time fix-up. It first asks "was the column missing before this run?" - the same pattern as $analystIsAdminColWasMissing:

// api/system/db_verify.php - before the schema loop
$providerAutoAnalystsColWasMissing = false;
try {
    $aaProbe = $conn->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = ? AND table_name = 'auth_providers' AND column_name = 'auto_create_analysts'");
    $aaProbe->execute([$dbName]);
    $providerAutoAnalystsColWasMissing = ((int)$aaProbe->fetchColumn() === 0);
} catch (Exception $e) {}

and, after the columns have been added:

if ($providerAutoAnalystsColWasMissing) {
    $copied = $conn->exec("UPDATE auth_providers SET auto_create_analysts = auto_create_users");
    $conn->exec("UPDATE auth_providers SET analyst_fallback_mode = 'block'");
    ...
}

In plain English: if you had auto-create on, you still get new analysts; if someone has no analyst account, they still get an error rather than a new screen. It only happens on the run that adds the column, so an admin's later choices are never overwritten.

2. Working before Database Verification has run

Every upgraded install spends some time with the new code and the old schema, until someone runs System → Database Verification. A query that names a missing column fails, and several screens treat a failed query as "nothing there" - the Authentication page would say "No providers yet". (That exact thing happened in 2.1.0, which is why this is a hard rule now.)

So there is one probe, cached per request:

// includes/sso_identity.php
function ssoJitColumnsReady(PDO $conn): bool {
    static $ready = null;
    if ($ready === null) {
        try {
            $ready = (bool)$conn->query("SHOW COLUMNS FROM auth_providers LIKE 'profile_sync_mode'")->fetch();
        } catch (PDOException $e) {
            $ready = false;
        }
    }
    return $ready;
}

and every reader asks it first. The provider list reports what the old switch meant:

// api/system/get_sso_providers.php
$jitCols = ssoJitColumnsReady($conn)
    ? 'p.auto_create_analysts, p.analyst_fallback_mode, p.profile_sync_mode'
    : "p.auto_create_users AS auto_create_analysts, 'block' AS analyst_fallback_mode, 'never' AS profile_sync_mode";

The sign-in paths read SELECT * rows, so they check for the key instead:

// api/auth/oidc_callback.php (and the same in includes/ldap.php)
$autoAnalysts = array_key_exists('auto_create_analysts', $provider)
    ? (int)$provider['auto_create_analysts'] === 1
    : (int)($provider['auto_create_users'] ?? 0) === 1;

The save leaves the three columns out of the INSERT/UPDATE until they exist, the same way it already treats carddav_allow_create.

3. The analyst sign-in: create, or fall back

After the existing link and the email match have both found nobody, the callback decides:

if ($autoAnalysts) {
    $analystId = oidcCreateAnalyst($conn, $providerId, $preferredUser, $name, $email,
                                   $provider['default_modules'], $claims, $provider['profile_sync_mode'] ?? 'never');
    $analyst   = oidcLoadAnalyst($conn, $analystId);
} else {
    $fallbackMode = $provider['analyst_fallback_mode'] ?? 'block';
    if ($fallbackMode === 'block') {
        ssoBail('No analyst account exists for ' . ($email ?: 'this user') . '. Ask an administrator to create one.');
    } elseif ($fallbackMode === 'redirect') {
        completeSelfServiceSso($conn, $provider, $providerId, $sub, $email, $emailVerified, $name, $tokens, $claims);
    } else {
        // 'confirm' - park the verified result and ask
        $_SESSION['sso_portal_csrf'] = bin2hex(random_bytes(16));
        $_SESSION['sso_pending_portal'] = [
            'provider_id' => $providerId, 'sub' => $sub, 'email' => $email,
            'email_verified' => $emailVerified, 'name' => $name,
            'tokens'  => ['id_token' => $tokens['id_token']],
            'claims'  => $claims,
            'created' => time(),
        ];
        header('Location: ' . BASE_URL . 'auth/sso_confirm_portal.php');
        exit;
    }
}

redirect simply hands over to the existing portal function, completeSelfServiceSso(), which runs the portal's own rules: link → verified email → portal JIT (only if Auto-create portal requesters is on). That function now sets $_SESSION['oidc_portal'] = 'self-service' first, so if the portal refuses the person, the error appears on the portal login, not the analyst one.

confirm is the interesting one. By this point the identity provider has already proved who the person is - the ID token's signature, issuer, audience and nonce have all been checked. So the session holds the verified result, not a way to skip verification. The confirm page shows a form that posts back to the callback:

// top of api/auth/oidc_callback.php
if (isset($_POST['action']) && $_POST['action'] === 'confirm_portal_proceed') {
    $csrf = $_POST['csrf'] ?? '';
    if (empty($csrf) || empty($_SESSION['sso_portal_csrf']) || !hash_equals($_SESSION['sso_portal_csrf'], $csrf)) {
        ssoBail('Security check failed (CSRF mismatch). Please try signing in again.');
    }
    $pending = $_SESSION['sso_pending_portal'] ?? null;
    unset($_SESSION['sso_pending_portal'], $_SESSION['sso_portal_csrf']);   // one use only
    if (!$pending || empty($pending['provider_id'])
        || (time() - (int)($pending['created'] ?? 0)) > 600) {
        ssoBail('Session expired. Please try signing in again.');
    }
    // re-load the provider (it may have been disabled meanwhile), then:
    completeSelfServiceSso($conn, $prov, ...$pending...);
}

Three protections, each worth knowing about:

• CSRF token, one use. Taken out of the session before it's acted on, so pressing Continue twice, or replaying the POST, gets "Session expired".
• Ten minutes. A confirm screen left open on a shared PC shouldn't work the next morning.
• Only the ID token is kept, because logout uses it as a hint. The access and refresh tokens are never parked.

Cancel goes to auth/login.php?cancel_sso=1, which removes both session keys.

LDAP only uses the switch, not the fallback. A directory sign-in is a password form on the analyst login - there is no redirect to fall back from.

4. Profile sync

Where the values come from

oidcExtractProfileAttributes() turns claims into the five fields. Each identity provider spells them differently, so each field tries the known names in order:

Field	Claims tried, in order
job title	job_title, jobTitle (Entra), title (Okta), jobtitle
department	department, departmentName, dept
office	office, officeLocation (Entra), physicalDeliveryOfficeName, location, then address.locality
phone	phone_number (standard), telephoneNumber, phone, businessPhones[0] (Entra), telephonenumber
mobile	mobile, mobilePhone (Entra), mobile_phone, mobilePhones[0], mobilephone

Values are trimmed, arrays are joined, and each is cut to its column length (100, or 50 for phones).

Topping up from userinfo

Some identity providers leave these out of the ID token. So when sync is on, the callback also asks the provider's userinfo endpoint:

if (($provider['profile_sync_mode'] ?? 'never') !== 'never'
    && !empty($disco['userinfo_endpoint']) && !empty($tokens['access_token'])) {
    $userInfo = oidcFetchUserInfo($disco['userinfo_endpoint'], $tokens['access_token']);
    if (!empty($userInfo) && isset($userInfo['sub'], $claims['sub'])
        && hash_equals((string)$claims['sub'], (string)$userInfo['sub'])) {
        $claims = array_merge($userInfo, $claims);
    }
}

• Only when sync is on. Otherwise it's a wasted round trip on every sign-in.
• The sub must match. The OpenID Connect spec (Core 1.0, §5.3.2) requires it: the userinfo response must be about the same person as the ID token, or it's thrown away.
• The ID token wins. array_merge() lets later arrays overwrite earlier ones, and the ID token is last - so a signed claim is never replaced by an unsigned one. (Sandy had this the right way round from the start.)
• oidcFetchUserInfo() does not follow redirects, because it is carrying a bearer token.

Note for Entra ID: its userinfo endpoint only returns the basic profile, so for Entra the fields have to be configured as optional claims on the ID token. The help page says so.

When it is written

initial is handled when an account is created: oidcCreateAnalyst() and the portal's JIT INSERT include the five fields.

always is handled by one helper, called once per side, after every access check has passed:

function oidcSyncProfile(PDO $conn, array $provider, string $table, array $record, array $claims, string $name): void {
    if (($provider['profile_sync_mode'] ?? 'never') !== 'always') return;

    if ($table === 'analysts') {
        $nameCol = 'full_name';
        $cols    = ['job_title', 'department', 'phone', 'mobile'];   // analysts has no office column
        $stamp   = ', last_modified_datetime = UTC_TIMESTAMP()';
    } elseif ($table === 'users') {
        $nameCol = 'display_name';
        $cols    = ['job_title', 'department', 'office', 'phone', 'mobile'];
        $stamp   = '';
    } else {
        return;
    }

    $attrs = oidcExtractProfileAttributes($claims);
    // A requester's own preferred_name wins over the directory's display name.
    $nameLocked = ($table === 'users' && !empty($record['preferred_name']));
    // ... build "col = ?" for every value that is present and different ...
    $conn->prepare("UPDATE $table SET " . implode(', ', $updates) . "$stamp WHERE id = ?")->execute($params);
}

// Analyst side, just before the session is set:
oidcSyncProfile($conn, $provider, 'analysts', $analyst, $claims, $name);
$analyst = oidcLoadAnalyst($conn, $analystId);   // pick up any new full_name for the session

// Portal side, inside completeSelfServiceSso(), likewise:
oidcSyncProfile($conn, $provider, 'users', $user, $claims, $name);
$user = ssLoadUser($conn, $userId);

Why "after every check" matters. The first version of the branch updated the record as soon as it was matched by email, and then checked whether it was active and assigned to this provider. On a multi-company install, that let a provider pinned to Company A assert the email address of someone at Company B and rewrite their name and job title - even though the sign-in itself was then refused. With one call at the end, the rule is simple: nothing about an account is written until the account has passed every check. The tests below prove it for all three refusal cases.

An empty claim never blanks a field: only values that are present and different are written.

Locking the fields

With always, anything typed into the profile would be undone at the next sign-in, so the five fields become read-only. One rule decides:

// includes/sso_identity.php
function ssoProfileSyncLocks(?string $protocol, ?string $syncMode): bool {
    return strtolower((string)$protocol) === 'oidc' && $syncMode === 'always';
}

OIDC only, on purpose. LDAP and CardDAV keep records up to date their own way, with their own rules about what's editable. In particular, the CardDAV write-back rules in portalProfileAccess() depend on $managed meaning "a directory import owns this record", so the OIDC lock is added beside it, never folded into it:

// includes/users.php - portalProfileAccess()
if (ssoProfileSyncLocks($row['protocol'] ?? null, $row['profile_sync_mode'] ?? null)) {
    $locked = array_values(array_unique(array_merge(
        $locked,
        array_intersect($fields, USER_CARDDAV_OWNED)   // job_title, department, office, phone, mobile
    )));
}

Only those five - never employee_id or manager_id, which sync doesn't write.

On the analyst side, api/myaccount/get_signatures.php returns sso_synced so the page can grey the fields out, and api/myaccount/save_profile.php refuses the save - the page only stops people typing; the endpoint is the guard.

5. The Analyst console link

// self-service/includes/user-menu.php
$__hasAnalystAccount = !empty($_SESSION['analyst_id']);
if (!$__hasAnalystAccount && !empty($ss_user_email)) {
    $__aa = connectToDatabase()->prepare(
        "SELECT 1 FROM analysts WHERE LOWER(email) = LOWER(?) AND is_active = 1 LIMIT 1"
    );
    $__aa->execute([$ss_user_email]);
    $__hasAnalystAccount = (bool)$__aa->fetchColumn();
}

<?php if ($__hasAnalystAccount): ?>
<button class="ss-menu-item" onclick="window.open('<?php echo BASE_URL; ?>', '_blank', 'noopener');">
    ... <span><?php echo htmlspecialchars(t('self-service.menu.analyst_console')); ?></span>
</button>
<?php endif; ?>

Matching on email is fine here: it only shows a link. Signing in on the analyst side still goes through the analyst login. The URL is built from BASE_URL, like the analyst side's link to the portal, because pages can be served at "pretty" URLs deeper than their file.

6. The provider dialog

One function decides which fields show, and it re-runs when either the protocol or the analyst switch changes:

const autoAnalysts = $('fAutoCreateAnalysts').checked;
$('analystFallbackField').style.display = (isOidc && !autoAnalysts) ? '' : 'none';
$('profileSyncModeField').style.display = isOidc ? '' : 'none';
$('defaultModulesField').style.display  = (!isCardDav && autoAnalysts) ? '' : 'none';

Protocol	Portal switch	Analyst switch	Default modules	Non-analyst action	Profile sync
OIDC	✅	✅	when analyst switch on	when analyst switch off	✅
LDAP	✅	✅	when analyst switch on	-	-
CardDAV	-	-	-	-	-

The providers table gets a Profile sync column (Never / Initial / Always; Not applicable for LDAP and CardDAV), and the auto-create pill says Portal, Analysts, Portal + analysts or Off.

---

What changed at merge time

The review asked for ten changes; Sandy made them in one commit (f8d57cf0). When merging I found two review items only half done, a few new issues, and one thing neither of us had thought about. For the record:

Area	What I changed	Why
userinfo (review 5a, 5b)	Skip it when sync is never; discard it unless the sub matches	The reply listed these as done, but the code still called userinfo on every sign-in and merged without checking sub. Easy to miss when a fix commit touches 20 files.
Before Database Verification	New ssoJitColumnsReady(); every reader and writer of the new columns now works on an old schema	Not in the review at all - an upgraded install would have shown "No providers yet" and broken both profile pages until Verification ran
Lock rule	One ssoProfileSyncLocks() used by the portal, the analyst page and the analyst save	The same rule was written three slightly different ways
Confirm screen	10-minute expiry; park only the ID token; sentence case and one-word buttons; branded background	Hygiene and house style
Provider dialog (review 7)	Fallback and sync OIDC-only, in one visibility function	The first function showed them for LDAP; a second one then fought it
LDAP page	Removed the sync and fallback selects; relabelled the portal switch	LDAP ignores both; the old label was ambiguous beside the new analyst switch
Portal menu	Removed CSS !important rules	They turned the red Logout text plain
Help	Rewrote the two sections	They were nested inside each other, one was missing from the page index, and one sentence promised IdP-group restrictions that don't exist
api/system/db_verify.php	Moved the backfill out of the middle of the is_admin comment	The comment ended up describing the wrong code

None of that takes anything away from the contribution. The review was written against a moving target, and the "before Database Verification" rule is a FreeITSM-specific trap that only someone who has been bitten by it would know about.

---

How it was tested

Docker wasn't running, so I built a clean room on WAMP instead:

• a git worktree of the merged code, served at its own URL, with its config.php pointed at
• a throwaway database loaded from the 2.9.0 schema - which is exactly an upgraded install that hasn't run Database Verification - and
• a small mock OpenID Connect provider in PHP that signs real RS256 ID tokens and serves discovery, JWKS, token and userinfo endpoints. So the real callback ran every real check: signature, issuer, audience, nonce, expiry. The mock can also return a userinfo response for a different sub, to test that it gets discarded.

Each test drove a real sign-in with curl (following the redirects through the mock provider and back), then checked the database.

Stage	What was checked	Result
A - upgrade, before Verification	Save providers; the list isn't empty; analyst JIT and the refusal behave exactly as 2.9.0; portal JIT; both profile pages load and save	16/16
B - Database Verification	Old switch copied into the new one; existing providers set to block and never; a hand-made change survives a second run; a new provider gets never / confirm / off	10/10
C - the features	Confirm: page, wrong CSRF, Continue, replay, Cancel, 10-minute expiry. Redirect. Block. Portal JIT off. Analyst JIT with default modules. Sync never (userinfo not called), initial, always. Entra-style claims. userinfo fills gaps; ID token wins; mismatched sub ignored. Preferred name kept. A refused provider writes nothing (wrong provider, inactive analyst, wrong-provider requester). Locking on and off, LDAP never locked. Analyst console link shown and hidden.	41/41
D - LDAP	Analyst switch decides; before Verification the old switch decides	4/4
Browser (headless Chrome)	Dialog field visibility for OIDC / LDAP / CardDAV and when toggling the analyst switch; table columns and pills; LDAP page; help sections numbered and not nested; locked My details; no JS errors	all as expected
Existing suites	sso-dangling-link 12/12, oidc-discovery 27/27, web-exposure-guard 12/12, config-not-load-bearing 13/13, db-verify-indexes 32/32, security-findings 189/190*, Feature Bingo check 588 cards / 0 errors, i18n gate OK	pass

* The one failure (the api/tickets/get_users.php requester list) was already failing on main before this work and is being looked at separately.

Not tested live: a real CardDAV server with write-back. The CardDAV code path wasn't changed - the OIDC lock sits beside it rather than inside it - and the LDAP-with-sync-set-to-always case confirms that non-OIDC providers never lock.

---

Traps for the next person

• A new column must not break an install that hasn't run Database Verification. Probe first (ssoJitColumnsReady()), or read SELECT * rows with array_key_exists. The Authentication page reads a failed query as "No providers yet".
• Write nothing until every check has passed. If you add another thing that updates an account at sign-in, put it next to oidcSyncProfile(), not in one of the three lookup branches.
• users.is_managed / $managed means "a directory import owns this record". Don't fold the OIDC lock into it - the CardDAV write-back rules depend on the old meaning.
• completeSelfServiceSso() can now be reached from the analyst login. Anything it does must make sense for someone who started on the analyst side - that's why it sets oidc_portal to self-service first.
• The fallback reads analyst_fallback_mode only when the analyst switch is off. The dialog hides it when the switch is on for the same reason.
• Changing existing English wording silently leaves 20+ locales showing the old meaning. New meaning = new key. That's why cb_autocreate and cb_autocreate_desc are left unused and cb_autocreate_users exists.

Related

• Single Sign-On (SSO) - the admin-facing reference
• SSO - JIT roles and profile sync: review notes - the review this page follows on from
• Sign-in method per team - Developer Guide - how analysts get assigned to a provider in the first place
• LDAP & Active Directory