Hi, we are trying to setup a periodical review of the Keycloak users and their roles via database queries. From Internet we found several possible queries. Some did not work and we had as a “final” candidate this:
SELECT kr_role.REALM_ID ‘Realm’, cl.CLIENT_ID ‘Realm Client’,
kr_role.NAME ‘Role Name’,
kr_role.DESCRIPTION ‘Role Description’,
user_ent.USERNAME ‘Domain ID’, user_ent.EMAIL ‘Email’
FROM keycloak_role kr_role, user_role_mapping role_map,
user_entity user_ent, client cl
WHERE role_map.USER_ID = user_ent.ID
AND kr_role.ID = role_map.ROLE_ID
AND kr_role.CLIENT = cl.ID
AND cl.REALM_ID = ‘<realm_name>’
AND cl.CLIENT_ID = ‘<client_name>’
ORDER BY 1, 2, 3;
This works fine to list users which are given permissions directly, but does not list users given permissions via groups.
For that, we looked further, we got this query which gets group-role relations:
SELECT grm.group_id
, kg.name “GROUP_NAME”
, kr.id “ROLE_ID”
, kr.name “ROLE_NAME”
, kr.description
FROM keycloak_role kr
, group_role_mapping grm
, keycloak_group kg
WHERE kr.id = grm.role_id
AND kg.id = grm.group_id
;
The table user_group_membership which we would think is needed to get user-group relations is empty.
Can not find any other table which can hold this information.
Can you please help?