When Keycloak and Your Database Disagree About Who Exists

A user can exist in your database and not in your identity provider. Nothing crashes. Everything fails silently.

TL;DR: a half-deleted account breaks four flows at once, without one useful error. Your code swallows the IdP's 404 on purpose, to block account enumeration. The repair ends in direct SQL, because the Keycloak admin API ignores the id you send. Prevention comes down to two things: a deletion order, and a tested invariant.

This article is for developers running Keycloak, or any IdP, next to an application users table.

The setup

An IdP, an identity provider, is the service that authenticates your users. Keycloak is one of the most deployed open source IdPs.

On my personal SaaS, Keycloak owns authentication. My application database owns the business: profiles, tenants, contracts. The same user lives in two stores, linked by a UUID.

I also design multi-region SSO for a healthcare platform with 25M+ users. The scale changes. This class of bug does not.

The drift: a partial cleanup

One morning in May, a test account refuses to sign up. In the database: a users row with its UUID. In Keycloak: nothing.

The cause was a partial cleanup. A test-environment purge script had deleted accounts in one store without touching the other. The safety guard only protected one mail domain, not the second one.

The first repair pass found 539 orphan accounts. That is not an edge case. That is a population.

The diagnosis fits in two queries:

-- Application database side
SELECT id, email FROM users WHERE email = 'someone@example.com';

-- Keycloak side
SELECT id, email FROM user_entity WHERE email = 'someone@example.com';

-- A row on one side, nothing on the other: drift.

Four symptoms, none points at the cause

An account in this state breaks four flows at the same time.

The magic link returns 200 and sends nothing. A magic link is a login link sent by email, no password involved.

Registration returns 500 on the first try. Then 409 on every retry, forever.

Invitation acceptance can no longer resolve the user.

And business rows stay attached to a UUID nobody can load anymore.

Four symptoms, four different tickets. No shared stack trace. It is the worst kind of incident: the kind that does not look like one.

Why it is invisible: your own security

Why does no error surface? Because I wanted it that way.

Account enumeration means guessing which emails have an account, by watching API responses. To block it, the API must answer the same thing whether the account exists or not.

So my service swallows Keycloak's 404, deliberately. The magic link returns 200 in every case. The security I added destroyed my observability.

The rule I keep: hide the error in the HTTP response, never in your logs.

// Same response for the client, account or no account.
// The drift itself must stay visible to you.
user, err := idp.UserByEmail(ctx, email)
if errors.Is(err, ErrUserNotFound) {
    slog.Warn("idp user missing", "flow", "magic_link")
    metrics.IdpUserMissing.Inc()
    respondOK(w) // anti-enumeration: say nothing outside
    return
}

A swallowed 404 with no metric is incident debt. You will pay it back at the worst time.

The repair: the admin API ignores your id

To repair, you must recreate the user in Keycloak with the same UUID. The business rows hang from it.

Then, surprise. The Keycloak admin API ignores the id field you send when creating a user. It generates its own. Your UUID cannot come back through the official door.

That leaves the service door: SQL, straight into Keycloak's tables.

-- Recreate the user, keeping the application database UUID
INSERT INTO user_entity (id, realm_id, username, email, enabled, ...)
VALUES ('same-uuid-as-your-db', ...);

INSERT INTO user_attribute (user_id, name, value)
VALUES ('same-uuid-as-your-db', 'tenant_id', ...);

INSERT INTO user_role_mapping (user_id, role_id)
VALUES ('same-uuid-as-your-db', ...);

Writing into Keycloak's database is a last resort, not a habit. Do it cold. Verify with kcadm afterwards, then replay the full flow. In my case: magic link requested, token generated, email received.

Prevention: an order and an invariant

The repair is worth nothing without prevention. Three changes followed the incident.

Account deletion became a saga. A saga is a sequence of ordered steps, where each step only runs if the previous one succeeded. In my case: Keycloak first, the database second. No script touches a single store anymore.

An admin endpoint lists the drift: UUIDs present on one side and missing on the other. What was invisible becomes a list.

And every purge ends with a count on both sides. If the numbers disagree, the purge lied.

The general rule: consistency between two identity stores is an invariant to test. Not a convention to hope for.

The anti-drift checklist

If you run an IdP and a users table, walk this list. It would have saved me one incident and 539 orphans.

[ ] One deletion path only: the saga, never a direct script on a single store
[ ] Each saga step is gated on the previous step's success
[ ] Anti-enumeration masks the HTTP response, never the logs or metrics
[ ] A metric counts swallowed IdP 404s, with an alert on it
[ ] An endpoint or job lists the drift between the IdP and your database
[ ] An integration test creates, deletes, then counts both sides
[ ] The repair procedure preserves the UUID, and it is written before the incident