Key accounts on issuer and subject across two providers

Setup

This lab also sets up the shell helpers that the rest of the OpenID Connect labs on accounts, authentication control, logout and implementation use. Your terminal and a small SQLite file play the collage app, the relying party.

1. In Lab Photos, open Users and set an administrator password for Ava Archer ([email protected]) and Ben Okafor ([email protected]). The Lab Photos preset creates them without passwords. You type these passwords only on the tenant's sign-in page.

2. In Lab Photos, open OAuth > Clients. If lab-collage does not exist yet, create it from the Web preset: confidential, PKCE required, grants authorization_code and refresh_token, scopes openid profile email, consent mode Remember, redirect URI http://127.0.0.1:8765/callback. Copy the client ID. The secret is shown once.

3. In Lab Mail, create lab-mail-collage the same way if it does not exist. Then open Users and create a user with the email [email protected], display name Ava Lin, and a password. Same email, different person, different provider.

4. Store both providers in your shell. Paste each secret at the silent prompt, so it never appears in history or a URL.

export ISSUER="https://<Lab Photos host>" CLIENT_ID="<lab-collage client ID>"
export ISSUER2="https://<Lab Mail host>" MAIL_CLIENT_ID="<lab-mail-collage client ID>"
read -rs CLIENT_SECRET; read -rs MAIL_CLIENT_SECRET; export CLIENT_SECRET MAIL_CLIENT_SECRET
export REDIRECT_URI="http://127.0.0.1:8765/callback" SCOPE="openid profile email"
btl-lab env

5. Save the track's sign-in helpers. They hold no secrets. signin prints an authorization URL with fresh STATE, NONCE and PKCE values, and takes extra parameters such as prompt=none. redeem exchanges a code. part prints a JWT's header (part 1) or payload (part 2) for reading only.

cat > ~/btl-oidc.sh <<'EOF'
enc() { jq -rn --arg v "$1" '$v|@uri'; }
signin() {
  eval "$(btl-lab pkce)"; eval "$(btl-lab state)"
  local url="$ISSUER/oauth/authorize?response_type=$(enc "${RT:-code}")&client_id=$CLIENT_ID&redirect_uri=$(enc "$REDIRECT_URI")"
  url="$url&scope=$(enc "$SCOPE")&state=$STATE&nonce=$NONCE&code_challenge=$CHALLENGE&code_challenge_method=S256"
  for extra in "$@"; do url="$url&$extra"; done
  echo "$url"
}
redeem() {
  local auth=(-d "client_id=$CLIENT_ID"); [ -n "$CLIENT_SECRET" ] && auth=(-u "$CLIENT_ID:$CLIENT_SECRET")
  RESP=$(curl -s "${auth[@]}" "$ISSUER/oauth/token" -d grant_type=authorization_code --data-urlencode "code=$1" \
    --data-urlencode "redirect_uri=$REDIRECT_URI" --data-urlencode "code_verifier=$VERIFIER")
  ID_TOKEN=$(jq -r '.id_token // empty' <<<"$RESP"); TOKEN=$(jq -r '.access_token // empty' <<<"$RESP")
  REFRESH=$(jq -r '.refresh_token // empty' <<<"$RESP")
  jq '{token_type, expires_in, scope, id_token: (.id_token != null), refresh_token: (.refresh_token != null), error, error_description}' <<<"$RESP"
}
part() { local p; p=$(cut -d. -f"${2:-2}" <<<"$1" | tr '_-' '/+'); while [ $(( ${#p} % 4 )) -ne 0 ]; do p="$p="; done; printf %s "$p" | base64 -d | jq -c .; }
EOF
source ~/btl-oidc.sh

6. Before each sign-in, run btl-lab callback in a second terminal. It listens on the loopback redirect URI and prints code, state, iss and any error. Without a terminal for it, you can register the hosted callback page https://beyondthelogin.dev/lab/callback/ on the client and set REDIRECT_URI to it instead.

7. Create the relying party's account store with the lesson's table:

sqlite3 accounts.db "CREATE TABLE accounts(id TEXT PRIMARY KEY, display_name TEXT, contact_email TEXT);
  CREATE TABLE sign_in_identities(account_id TEXT NOT NULL REFERENCES accounts(id), issuer TEXT NOT NULL,
    subject TEXT NOT NULL, linked_at TEXT NOT NULL, UNIQUE (issuer, subject));"

Walkthrough

1. Press Start on this page, then sign in at Lab Photos. Open the printed URL in a private window and sign in as Ava. Check that the listener's state equals $STATE and its iss equals $ISSUER, then redeem the code.

signin
redeem '<code from the listener>'

The response has "id_token": true and "scope": "openid profile email".

Why it matters: the relying party may act only on a pair it took from a validated ID token, so the callback checks and the validation come before any account lookup.

2. Validate the ID token, then keep only the pair.

btl-lab verify "$ID_TOKEN" --issuer "$ISSUER" --audience "$CLIENT_ID" --type id --nonce "$NONCE" && \
  { ISS=$(part "$ID_TOKEN" | jq -r .iss); SUB=$(part "$ID_TOKEN" | jq -r .sub); ID_TOKEN_AVA=$ID_TOKEN; }
part "$ID_TOKEN" | jq '{iss, sub, email}'

sub is an opaque value, not the email address. Keep ID_TOKEN_AVA: later labs use it as an old ID token.

Why it matters: of everything the provider sends, only iss and sub together identify the person.

3. The first sign-in. Look up the pair, find nothing, and register an account. The account and its identity row are written in one transaction.

sqlite3 accounts.db "SELECT account_id FROM sign_in_identities WHERE issuer='$ISS' AND subject='$SUB';"
sqlite3 accounts.db "BEGIN; INSERT INTO accounts VALUES('acct-8812','Ava Archer','[email protected]');
  INSERT INTO sign_in_identities VALUES('acct-8812','$ISS','$SUB',datetime('now')); COMMIT;"

Why it matters: a missing row says only that this provider account never signed in here. The account gets its own identifier, and neither record can exist without the other.

4. A returning customer. Run signin again in the same private window. No password page appears, because the tenant's session answers. Redeem, validate, and repeat the SELECT: it returns acct-8812.

Why it matters: every returning sign-in is a lookup on the pair and nothing else.

5. Sign in at Lab Mail as Ava Lin. Point the helpers at the second provider, sign in, validate and keep its pair.

ISSUER_A=$ISSUER CLIENT_A=$CLIENT_ID SECRET_A=$CLIENT_SECRET
ISSUER=$ISSUER2 CLIENT_ID=$MAIL_CLIENT_ID CLIENT_SECRET=$MAIL_CLIENT_SECRET
signin
redeem '<code>'
btl-lab verify "$ID_TOKEN" --issuer "$ISSUER" --audience "$CLIENT_ID" --type id --nonce "$NONCE" && \
  { ISS_B=$(part "$ID_TOKEN" | jq -r .iss); SUB_B=$(part "$ID_TOKEN" | jq -r .sub); }
part "$ID_TOKEN" | jq '{iss, sub, email}'

The email is [email protected] again. iss and sub differ.

Why it matters: the same string in email does not make the same person. The provider and subject say this is someone else.

6. Compare the two lookups.

sqlite3 accounts.db "SELECT account_id FROM sign_in_identities WHERE issuer='$ISS_B' AND subject='$SUB_B';"
sqlite3 accounts.db "SELECT id FROM accounts WHERE contact_email='[email protected]';"

The pair lookup finds nothing: register or link. The email lookup returns acct-8812, which is Ava Archer's.

Why it matters: an email lookup would hand Ava Archer's orders and addresses to Ava Lin.

7. Let the table enforce the rule. Register Lab Mail's pair for a new account acct-9001, then try to add Lab Photos' pair to acct-9001 as well.

sqlite3 accounts.db "BEGIN; INSERT INTO accounts VALUES('acct-9001','Ava Lin','[email protected]');
  INSERT INTO sign_in_identities VALUES('acct-9001','$ISS_B','$SUB_B',datetime('now')); COMMIT;"
sqlite3 accounts.db "INSERT INTO sign_in_identities VALUES('acct-9001','$ISS','$SUB',datetime('now'));"

The second command fails with UNIQUE constraint failed.

Why it matters: one provider account can never open two relying-party accounts, and the constraint is on the pair, not on the subject alone.

8. Change the email, keep the key. Switch back to Lab Photos (ISSUER=$ISSUER_A CLIENT_ID=$CLIENT_A CLIENT_SECRET=$SECRET_A). In Users, change Ava's email to [email protected]. The change ends her tenant session, so the next signin asks for her password. Sign in with the new address, validate, and look up the pair: still acct-8812. Update the copied profile, not the key.

sqlite3 accounts.db "UPDATE accounts SET contact_email='[email protected]' WHERE id='acct-8812';"

Why it matters: the account record keeps a labeled copy of the profile. Email changes break email-keyed accounts but not pair-keyed ones.

Break it

1. A reassigned address. In Users, create [email protected] with display name Reuse One and a password. Sign in as that user, register the pair as acct-reuse, then delete the user. Create [email protected] again as Reuse Two and sign in. The sub is new: the tenant never gives a subject to another user, even after a deletion. The pair lookup finds nothing, which is correct. An email lookup would hand Reuse Two the old account.

2. A provider-side subject change. In OAuth > ID token managers, open the manager assigned to lab-collage and set the sub standard claim to the subject identifier with prefix lab-. Sign in as Ava: sub is now lab-<id> and the pair lookup misses. Falling back to the email here is the mistake the lesson names. A provider that changes subjects needs a planned migration.

Restore: set the sub claim back to its default and save. The next sign-in returns the original sub.

Check your work

Press Check my progress. The checks look for Ava's password sign-in to lab-collage, the code exchange, a single sign-on answer with no password, the email change in Users, and the sign-in that followed it.

In Audit, filter on oauth.authorize for lab-collage to see request_started, user_signed_in and code_issued. In Lab Mail's Audit you see the same events for lab-mail-collage.

sqlite3 accounts.db "SELECT account_id, issuer, subject FROM sign_in_identities;"

Two rows, two issuers, and no duplicated pair.

Cleanup

1. Make sure the sub override is removed.

2. Change Ava's email back to [email protected]. Her sub, and so acct-8812, is unaffected.

3. Delete the user [email protected].

4. Keep accounts.db, ~/btl-oidc.sh, ID_TOKEN_AVA, Ava Lin and both collage clients. The next labs use them.

Missing infrastructure

• G66 Second lab tenant: this lab uses Lab Mail, a second tenant. Additional tenants currently need a paid subscription or a BTL grant, so an ordinary learner can do only the Lab Photos steps until every learner can have a second lab tenant.