Skip to content

Latest commit

 

History

History
139 lines (119 loc) · 5.2 KB

12. Upgrade Details.md

File metadata and controls

139 lines (119 loc) · 5.2 KB

Summary

This document describes the Keycloak database changes and its impact on transactional data. Depending on the impact, possible risks/impediments on upgrades as well as mitigation plans are described. Each section includes the respective change details, impact on existing data and the respective release with which the change is getting active.

v2.0.0

Role Concept changes - UPDATE

In the next Version the Roles 'App Tech User' & 'Connector User' in the client 'technical_user_management' will be removed. To assure that all Users which currently have one or both roles assigned have the correct roles in the future, the following script must be executed on the central idp database:

WITH connector_users AS (
    SELECT user_id
    FROM public.user_role_mapping AS urm
        JOIN public.keycloak_role AS kr 
        ON urm.role_id = kr.id
    WHERE kr.name = 'Connector User'
),
new_connector_roles_to_insert AS (
    SELECT DISTINCT atu.user_id, kr.id AS role_id
    FROM connector_users AS atu
    CROSS JOIN (
        SELECT id
        FROM public.keycloak_role
        WHERE name IN ('Semantic Model Management', 'Identity Wallet Management', 'Dataspace Discovery')
    ) kr
)
INSERT INTO public.user_role_mapping (user_id, role_id)
SELECT rt.user_id, rt.role_id
FROM new_connector_roles_to_insert rt
    LEFT JOIN public.user_role_mapping urm
    ON rt.user_id = urm.user_id AND rt.role_id = urm.role_id
WHERE urm.user_id IS NULL;

WITH app_tech_users AS (
    SELECT user_id
    FROM public.user_role_mapping AS urm
        JOIN public.keycloak_role AS kr
        ON urm.role_id = kr.id
    WHERE kr.name = 'App Tech User'
),
roles_to_insert AS (
    SELECT DISTINCT atu.user_id, kr.id AS role_id
    FROM app_tech_users AS atu
    CROSS JOIN (
        SELECT id
        FROM public.keycloak_role
        WHERE name IN ('Semantic Model Management', 'Dataspace Discovery', 'CX Membership Info')
    ) kr
)
INSERT INTO public.user_role_mapping (user_id, role_id)
SELECT rt.user_id, rt.role_id
FROM roles_to_insert rt
    LEFT JOIN public.user_role_mapping urm
    ON rt.user_id = urm.user_id AND rt.role_id = urm.role_id
WHERE urm.user_id IS NULL;

WITH service_management_users AS (
    SELECT user_id
    FROM public.user_role_mapping AS urm
        JOIN public.keycloak_role AS kr
        ON urm.role_id = kr.id
    WHERE kr.name = 'Service Management'
),
new_offer_management_roles_to_insert AS (
    SELECT DISTINCT atu.user_id, kr.id AS role_id
    FROM service_management_users AS atu
    CROSS JOIN (
        SELECT id
        FROM public.keycloak_role
        WHERE name = 'Offer Management'
    ) kr
)
INSERT INTO public.user_role_mapping (user_id, role_id)
SELECT rt.user_id, rt.role_id
FROM new_offer_management_roles_to_insert rt
    LEFT JOIN public.user_role_mapping urm
    ON rt.user_id = urm.user_id AND rt.role_id = urm.role_id
WHERE urm.user_id IS NULL;

To test if all user got the expected roles assigned please execute the following scripts after each other. All the scripts shouldn't return anything. If you see an userId returned from one of the scripts the above script needs to be executed again.

SELECT DISTINCT u.user_id
FROM public.user_role_mapping u
JOIN public.keycloak_role r ON u.role_id = r.id
WHERE r.name = 'Connector User'
AND u.user_id NOT IN (
    SELECT user_id
    FROM public.user_role_mapping
    JOIN public.keycloak_role ON user_role_mapping.role_id = keycloak_role.id
    WHERE keycloak_role.name IN ('Semantic Model Management', 'Identity Wallet Management', 'Dataspace Discovery')
);

SELECT DISTINCT u.user_id
FROM public.user_role_mapping u
JOIN public.keycloak_role r ON u.role_id = r.id
WHERE r.name = 'App Tech User'
AND u.user_id NOT IN (
    SELECT user_id
    FROM public.user_role_mapping
    JOIN public.keycloak_role ON user_role_mapping.role_id = keycloak_role.id
    WHERE keycloak_role.name IN ('Dataspace Discovery', 'Semantic Model Management', 'CX Membership Info')
);

SELECT DISTINCT u.user_id
FROM public.user_role_mapping u
JOIN public.keycloak_role r ON u.role_id = r.id
WHERE r.name = 'Service Management'
AND u.user_id NOT IN (
    SELECT user_id
    FROM public.user_role_mapping
    JOIN public.keycloak_role ON user_role_mapping.role_id = keycloak_role.id
    WHERE keycloak_role.name = 'Offer Management'
);

Since Keycloak uses a caching mechanism its likely that you'll experience a strange behavior in admin console where you see only the old roles assigned to a user instead of the newly assigned ones.

To fix that our recommendation is to restart the stateful set of the central idp. If you don't want to restart the stateful set you could disable the Central-CX realm and enable it directly afterwards.

! Important ! the new roles: 'Semantic Model Management', 'Dataspace Discovery', 'CX Membership Info' and 'Offer Management' must be existing in the central idp database

NOTICE

This work is licensed under the Apache-2.0.