Skip to content

Keycloak‐migraatio 2025: kuntalaiset

Jump to bottom
Joonas Javanainen edited this page Jan 29, 2025 · 2 revisions

Vaihe 1: datan valmistelu

Tässä vaiheessa kerätään tarvittava data Keycloakin kannasta, siirretään data eVakan kantaan valmisteltavaksi, ja etsitään vastaavat käyttäjät eVakasta. Mikään oikea data ei muutu vielä tässä vaiheessa, joten tämän voi tehdä turvallisesti ja lopputulosta voi tutkia rauhassa ennen lopullista migraatiota.

1. Aja SQL-kysely 1 Keycloakin kannassa

Tässä kyselyssä on kaksi parametria jotka voivat vaihdella eri kunnissa: hetu-attribuutin nimi, ja Keycloak-realmin nimi. Nämä kannattaa tarkistaa, jos myöhemmässä vaiheessa selviää että yksikään käyttäjä ei migratoidu onnistuneesti.

2. Siirrä syntynyt keycloak_citizen_migration taulu jollain tavalla eVakan tietokantaan

Datassa on mukana henkilötunnuksia joten siirtotapa täytyy toteuttaa tietoturvallisesti, esim. ajamalla psql-komentorivityökalua bastionilla, jossa CSV-tiedoston laittaminen levylle on ok.

Esimerkki bastion-dumppauksesta:

-- taulun dumppaus citizens.csv -tiedostoon
\copy keycloak_citizen_migration TO 'citizens.csv' (FORMAT CSV, HEADER TRUE)

-- taulun pitää olla olemassa, joten kopioi tämä migraatio-SQL numero 1:stä
CREATE TABLE keycloak_citizen_migration (...)
-- taulun luku citizens.csv -tiedostosta
\copy keycloak_citizen_migration FROM 'citizens.csv' (FORMAT CSV, HEADER TRUE)

3. Aja SQL-kysely 2 eVakan kannassa

4. Tutki keycloak_citizen_migration-taulua

Taulun keycloak_problem ja evaka_problem -sarakkeista voi lukea syitä miksi käyttäjää ei voi migratoida. Näistä saa hyvän yleiskäsityksen esim. ryhmittelemällä ongelmien määrien mukaan:

SELECT coalesce(keycloak_problem, evaka_problem) AS problem, count(*)
FROM keycloak_citizen_migration
GROUP BY 1
ORDER BY 2 DESC

Ongelmia voi korjata tekemällä tarvittavia muutoksia Keycloakin / eVakan tietokantoihin, tai korjaamalla SQL-skriptiä 1 jos siellä on väärä hetu-attribuutin tai realmin nimi.

Vaihe 2: varsinainen migraatio eVakaan

Jos keycloak_citizen_migration -taulu näyttää järkevältä, ja mahdolliset ongelmarivit voidaan jättää siirtämättä, aja SQL-kysely 3, joka siirtää kyseisestä taulusta ongelmattomat datat eVakaan.

Liite: migraatio-SQL:t

Migraatio-SQL osa 1

Tämä ajetaan Keycloakin kannassa, ja tästä syntyvä keycloak_citizen_migration -taulu pitää saada siirrettyä eVakan kantaan jollain tavalla.

DROP TABLE IF EXISTS keycloak_citizen_migration;
CREATE TABLE keycloak_citizen_migration (
    id uuid NOT NULL,
    username text NOT NULL,
    email text NOT NULL,
    ssn text,
    password jsonb,
    keycloak_problem text,
    evaka_problem text
);

CREATE OR REPLACE FUNCTION pg_temp.migrate_argon2id(secret_data jsonb, credential_data jsonb) RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
AS $$
DECLARE
    value text;
    salt text;
    hash_iterations int;
    hash_length int;
    memory int;
    type text;
    version text;
    parallelism int;
BEGIN
    -- Extract and check fields from secret_data
    value := secret_data->>'value';
    IF value IS NULL THEN
        RAISE EXCEPTION 'Missing required field: secret_data.value';
    END IF;

    salt := secret_data->>'salt';
    IF salt IS NULL THEN
        RAISE EXCEPTION 'Missing required field: secret_data.salt';
    END IF;

    -- Extract and check fields from credential_data
    hash_iterations := (credential_data->>'hashIterations')::int;
    IF hash_iterations IS NULL THEN
        RAISE EXCEPTION 'Missing required field: credential_data.hashIterations';
    END IF;

    -- Extract and validate fields from additionalParameters in credential_data
    IF jsonb_array_length(credential_data->'additionalParameters'->'hashLength') <> 1 THEN
        RAISE EXCEPTION 'credential_data.additionalParameters.hashLength must be an array with exactly one element';
    END IF;
    hash_length := (credential_data->'additionalParameters'->'hashLength'->>0)::int;

    IF jsonb_array_length(credential_data->'additionalParameters'->'memory') <> 1 THEN
        RAISE EXCEPTION 'credential_data.additionalParameters.memory must be an array with exactly one element';
    END IF;
    memory := (credential_data->'additionalParameters'->'memory'->>0)::int;

    IF jsonb_array_length(credential_data->'additionalParameters'->'type') <> 1 THEN
        RAISE EXCEPTION 'credential_data.additionalParameters.type must be an array with exactly one element';
    END IF;
    type := credential_data->'additionalParameters'->'type'->>0;
    IF type IS DISTINCT FROM 'id' THEN
        RAISE EXCEPTION 'Only "id" type is supported. Found: %', type;
    END IF;

    IF jsonb_array_length(credential_data->'additionalParameters'->'version') <> 1 THEN
        RAISE EXCEPTION 'credential_data.additionalParameters.version must be an array with exactly one element';
    END IF;
    version := credential_data->'additionalParameters'->'version'->>0;
    IF version IS DISTINCT FROM '1.3' THEN
        RAISE EXCEPTION 'Only version "1.3" is supported. Found: %', version;
    END IF;

    IF jsonb_array_length(credential_data->'additionalParameters'->'parallelism') <> 1 THEN
        RAISE EXCEPTION 'credential_data.additionalParameters.parallelism must be an array with exactly one element';
    END IF;
    parallelism := (credential_data->'additionalParameters'->'parallelism'->>0)::int;

    RETURN jsonb_build_object(
      'algorithm', jsonb_build_object(
        'type', 'Argon2id',
        'hashLength', hash_length,
        'version', 'VERSION_13',
        'memoryKbytes', memory,
        'iterations', hash_iterations,
        'parallelism', parallelism
      ),
      'salt', salt,
      'hash', value
    );
END;
$$;

CREATE OR REPLACE FUNCTION pg_temp.migrate_pbkdf2(hash_type text, secret_data jsonb, credential_data jsonb) RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
AS $$
DECLARE
    value text;
    salt text;
    hash_iterations int;
    decoded_value bytea;
    bit_length int;
BEGIN
    value := secret_data->>'value';
    IF value IS NULL THEN
        RAISE EXCEPTION 'Missing required field: secret_data.value';
    END IF;

    salt := secret_data->>'salt';
    IF salt IS NULL THEN
        RAISE EXCEPTION 'Missing required field: secret_data.salt';
    END IF;

    -- Extract fields from credential_data with NULL checks
    hash_iterations := (credential_data->>'hashIterations')::int;
    IF hash_iterations IS NULL THEN
        RAISE EXCEPTION 'Missing required field: credential_data.hashIterations';
    END IF;

    -- Decode the base64-encoded value to calculate its length in bits
    decoded_value := decode(value, 'base64');
    bit_length := length(decoded_value) * 8; -- Length in bytes * 8 = length in bits

    -- Check if bit_length is a power of 2
    IF bit_length = 0 OR (bit_length & (bit_length - 1)) <> 0 THEN
        RAISE EXCEPTION 'Decoded value bit length (%s) is not a power of 2', bit_length;
    END IF;

    RETURN jsonb_build_object(
      'algorithm', jsonb_build_object(
        'type', 'Pbkdf2',
        'hashType', hash_type,
        'keySize', bit_length,
        'iterationCount', hash_iterations
      ),
      'salt', salt,
      'hash', value
    );
END;
$$;

CREATE OR REPLACE FUNCTION pg_temp.migrate_keycloak_password_hash(secret_data jsonb, credential_data jsonb) RETURNS text
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
RETURN CASE credential_data ->> 'algorithm'
    WHEN 'argon2' THEN pg_temp.migrate_argon2id(secret_data, credential_data)
    WHEN 'pbkdf2-sha512' THEN pg_temp.migrate_pbkdf2('SHA512', secret_data, credential_data)
    WHEN 'pbkdf2-sha256' THEN pg_temp.migrate_pbkdf2('SHA256', secret_data, credential_data)
END;

CREATE OR REPLACE VIEW pg_temp.candidate_citizen_users AS (
    SELECT
        ue.id::uuid AS id,
        trim(ue.username) AS username,
        trim(ue.email) AS email,
        ue.email_verified,
        (
            SELECT ua.value FROM user_attribute ua
            WHERE ua.user_id = ue.id
            AND ua.name = 'suomi_nationalIdentificationNumber' -- HUOM: attribuutin nimi
        ) AS ssn,
        (
            SELECT pg_temp.migrate_keycloak_password_hash(c.secret_data::jsonb, c.credential_data::jsonb)
            FROM credential c
            WHERE c.user_id = ue.id
            AND c.type = 'password'
        )::jsonb AS password,
        enabled
    FROM user_entity ue
    WHERE ue.realm_id = 'evaka-customer' -- HUOM: realmin nimi
);

INSERT INTO keycloak_citizen_migration (id, username, email, ssn, password, keycloak_problem)
SELECT id, username, email, ssn, password,
    CASE
        WHEN NOT enabled THEN 'user disabled'
        WHEN NOT email_verified THEN 'unverified email'
        WHEN ssn IS NULL THEN 'no ssn'
        WHEN password IS NULL THEN 'missing password'
    END
FROM pg_temp.candidate_citizen_users;

UPDATE keycloak_citizen_migration
SET keycloak_problem = 'duplicate username'
WHERE keycloak_problem IS NULL
AND username IN (
    SELECT username
    FROM pg_temp.candidate_citizen_users
    GROUP BY username
    HAVING count(*) > 1
);

Migraatio-SQL osa 2

Tämä ajetaan eVakan kannassa, kun osassa 1 syntynyt taulu on saatu siirrettyä sinne datoineen.

UPDATE keycloak_citizen_migration
SET evaka_problem = NULL;
UPDATE keycloak_citizen_migration kcm
SET evaka_problem = 'no match in evaka'
WHERE NOT EXISTS (
    SELECT
    FROM person p
    WHERE p.social_security_number = kcm.ssn
);
UPDATE keycloak_citizen_migration kcm
SET evaka_problem = 'different username in evaka'
WHERE evaka_problem IS NULL
AND EXISTS (
    SELECT
    FROM person p
    JOIN citizen_user cu USING (id)
    WHERE p.social_security_number = kcm.ssn
    AND cu.username IS NOT NULL
    AND cu.username != kcm.username
);
UPDATE keycloak_citizen_migration kcm
SET evaka_problem = 'different password in evaka'
WHERE evaka_problem IS NULL
AND EXISTS (
    SELECT
    FROM person p
    JOIN citizen_user cu USING (id)
    WHERE p.social_security_number = kcm.ssn
    AND cu.username = kcm.username
    AND cu.password != kcm.password
);
UPDATE keycloak_citizen_migration
SET evaka_problem = 'duplicate ssn'
WHERE evaka_problem IS NULL
AND ssn IN (
    SELECT ssn
    FROM keycloak_citizen_migration
    WHERE keycloak_problem IS NULL
    AND evaka_problem IS NULL
    GROUP BY ssn
    HAVING count(*) > 1
);

Migraatio-SQL osa 3

Tämä ajetaan eVakan kannassa, ja tämä päivittää lopulliset ongelmattomat käyttäjätiedot eVakaan.

INSERT INTO citizen_user AS cu (id, username, username_updated_at, password, password_updated_at)
SELECT p.id, kcm.username, now(), kcm.password, now()
FROM keycloak_citizen_migration kcm
JOIN person p
ON p.social_security_number = kcm.ssn
WHERE kcm.keycloak_problem IS NULL
AND kcm.evaka_problem IS NULL
ON CONFLICT (id) DO UPDATE
SET username = excluded.username,
    username_updated_at = excluded.username_updated_at,
    password = excluded.password,
    password_updated_at = excluded.password_updated_at
WHERE cu.username IS DISTINCT FROM excluded.username
OR cu.password IS DISTINCT FROM excluded.password;

UPDATE person p
SET verified_email = kcm.email
FROM keycloak_citizen_migration kcm
WHERE p.social_security_number = kcm.ssn
AND p.verified_email IS NULL
AND kcm.keycloak_problem IS NULL
AND kcm.evaka_problem IS NULL;

Copyright 2017-2024 City of Espoo
Sisältö on lisensoitu LGPL-2.1-or-later -lisenssillä

Sisällysluettelo

Clone this wiki locally