Moving Keycloak Users Between Realms Directly in Postgres

Keycloak doesn’t offer a built-in way to move a user from one realm to another - the closest thing the admin console gives you is exporting a user and re-importing them elsewhere, which loses role mappings and other relations unless you rebuild them by hand. When I needed to move a handful of users (selected by email domain) from one realm to another, I ended up writing a small PL/pgSQL procedure that does it directly against Keycloak’s Postgres database.

Why not use the admin API or export/import #

Keycloak stores everything relational: a user in user_entity belongs to exactly one realm_id, and their roles live in user_role_mapping, pointing at keycloak_role rows that are themselves realm-scoped. Simply changing the user’s realm_id isn’t enough - their existing role mappings would then point at roles belonging to the old realm, which is inconsistent and will confuse Keycloak (or just silently drop the roles).

So the migration needs two steps:

1. Move the user row itself to the destination realm.
2. For every role the user had in the source realm, find the role with the same name in the destination realm and re-point the mapping there.

The procedure #

CREATE OR REPLACE PROCEDURE migrate_users_by_email(
    source_realm text,
    destination_realm text,
    email_query text
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 1) move users into the new realm
    UPDATE public.user_entity
    SET realm_id = destination_realm
    WHERE realm_id = source_realm
      AND email LIKE email_query;

    -- 2) remap roles to the equivalently-named roles in the destination realm
    UPDATE public.user_role_mapping urm
    SET role_id = kr_new.id
    FROM public.keycloak_role kr_old
    JOIN public.keycloak_role kr_new
        ON kr_new.name = kr_old.name
        AND kr_new.realm_id = destination_realm,
        public.user_entity ue
    WHERE urm.role_id = kr_old.id
      AND urm.user_id = ue.id
      AND kr_old.realm_id = source_realm
      AND ue.email LIKE email_query
      AND ue.realm_id = destination_realm;
END;
$$;

The role remapping join looks convoluted, but it’s just: for each affected user, find the role mapping they had, look up what that role was called, find a role with the same name in the destination realm, and swap the mapping over.

Usage #

Call it with the source realm ID, destination realm ID, and a LIKE pattern to select which users to move (e.g. by email domain):

CALL migrate_users_by_email(
    '11111111-1111-1111-1111-111111111111',  -- source realm ID
    '22222222-2222-2222-2222-222222222222',  -- destination realm ID
    '%@example.com'
);

Realm IDs are UUIDs found in the realm table (SELECT id, name FROM public.realm;), not the human-readable realm name.

Caveats #

• This only remaps roles that already exist under the same name in both realms - roles that don’t have a matching name in the destination realm will silently keep pointing at nothing useful, so it’s worth diffing the role sets beforehand.
• It doesn’t touch group memberships, client-level role mappings’ consumers, or federated/identity-provider links - check whether those matter for your setup before relying on this for a full migration.
• Always take a database backup (or at least a pg_dump of the affected tables) before running this against production, and restart/clear the Keycloak realm cache afterwards so it doesn’t keep serving stale user data from memory.
• Test the LIKE pattern with a plain SELECT first to make sure it matches exactly the users you intend to move.