CODEWITHSUNDEEP
keycloakkeycloak-services
Ankur Singhal
Ankur Singhal

Reputation: 26057

Keycloak - Get all Users mapped to roles

I know keycloak has exposed below api,

<dependency>
    <groupId>org.keycloak</groupId>
    <artifactId>keycloak-services</artifactId>
    <version>2.0.0.Final</version>
</dependency>

With complete documentation here. I cannot find the required api here to fetch all users with specific role mapped to them.

Problem Statement - I need to pick all users from keycloak server who have a specific role. I need to send email to all users with role mapped to them.

Upvotes: 13

Views: 27018

Answers (6)

MarkusF
MarkusF

Reputation: 56

If anyone is still searching for a Postgres Query to find information regarding users/roles/groups in keycloak database, I came up with this one lately.

It uses two CTEs to have the groups and roles straight (recursing for groups in groups, because they can nest in arbitrary depth and fetching composite roles with their parents) and a simple UNION for group and direct assignments.

Please note the WHERE clause, where you can limit the realm and different aspects. You can search for

  • all roles from a specific user (just matching username)
  • all users, that have a particular role assigned (matching role_name)
  • everything coming from a specific group (I sometimes use it without the username column in the projection to just see, what roles a group has. Please note the prefix in the group column)
-- flat out GROUPS in GROUPS
WITH RECURSIVE groups AS (
    SELECT
        g.id,
        g.id AS parent_group,
        g.name,
        g.name AS parent_name,
        g.realm_id,
        1 AS iter
    FROM
        keycloak_group g
    UNION
    SELECT
        groups.id,
        parents.parent_group,
        groups.name,
        parents.name,
        groups.realm_id,
        groups.iter + 1
    FROM
        groups
        INNER JOIN keycloak_group parents ON groups.parent_group = parents.id
),
-- Collect roles and composite roles
roles AS (
    SELECT
        r.id,
        r.name AS role_name,
        null AS base_role,
        c.client_id
    FROM
        keycloak_role r
        LEFT JOIN client c ON r.client = c.id
    UNION
    SELECT
        r.id,
        r2.name,
        r.name,
        c.client_id
    FROM
        keycloak_role r
        JOIN composite_role cr ON r.id = cr.composite
        JOIN keycloak_role r2 ON r2.id = cr.child_role
        LEFT JOIN client c ON r.client = c.id
)
SELECT DISTINCT
    username,
    role_name,
    base_role,  -- for composite roles
    client_id,
    source,
    realm_name
FROM
    (
        -- Roles from Groups
        SELECT
            ue.username,
            roles.role_name,
            roles.base_role,
            roles.client_id,
            ue.realm_id,
            'group ' || g.name AS source,
            realm.name AS realm_name
        FROM
            user_entity ue
            JOIN realm ON ue.realm_id = realm.id
            JOIN user_group_membership ugm ON ue.id = ugm.user_id
            JOIN groups g ON g.id = ugm.group_id
            JOIN group_role_mapping grm ON g.parent_group = grm.group_id
            JOIN roles roles ON roles.id = grm.role_id
        UNION
        -- direct role assignments on User
        SELECT
            ue.username,
            roles.role_name,
            roles.base_role,
            roles.client_id,
            ue.realm_id,
            'direct',
           realm.name
        FROM
            user_entity ue
            JOIN realm ON ue.realm_id = realm.id
            JOIN user_role_mapping urm ON ue.id = urm.user_id
            JOIN roles roles ON roles.id = urm.role_id
    ) AS a
WHERE
    realm_name = 'realm_name'
    AND (
        -- username = 'username'
        role_name IN ('roleName')
        -- source = 'group GROUPNAME'
    )
ORDER BY
    username,
    role_name
;

This query works from keycloak 9 to 16.1.1 (the last jboss/keycloak version I got from docker hub).

Upvotes: 1

David Hladky
David Hladky

Reputation: 525

Based on the documentation it appears to be this API:

GET /{realm}/clients/{id}/roles/{role-name}/users

It is there for a while. In this older version however it was not possible to get more than 100 users this way. It was fixed later and pagination possibility was added.

Upvotes: 14

m0z4rt
m0z4rt

Reputation: 1225

SELECT username,
       kr.NAME,
       kr.REALM_ID
FROM KEYCLOAK_ROLE kr
JOIN USER_ROLE_MAPPING rm ON kr.id = rm.role_id
JOIN USER_ENTITY ue ON rm.user_id = ue.id
ORDER BY USERNAME,
         NAME,
         REALM_ID;

Upvotes: 0

Nirojan Selvanathan
Nirojan Selvanathan

Reputation: 11164

This should be now possible with the updated rest endpoint.

Set<UserRepresentation> usersOfRole = realmResource.roles().get(roleName).getRoleUserMembers();

Upvotes: 6

Lester
Lester

Reputation: 1488

Here is another interesting query, which would also display other useful fields.

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;

Upvotes: 3

shonky linux user
shonky linux user

Reputation: 6428

There is an outstanding feature request asking for this function via the API.

In the meantime if your requirement is once-off you could obtain the user names (or email addresses) by interrogating the database joining KEYCLOAK_ROLE to USER_ROLE_MAPPING to USER_ENTITY

Something like:

SELECT username
FROM keycloak_role kr 
   JOIN user_role_mapping rm ON kr.id = rm.role_id
   JOIN user_entity ue ON rm.user_id = ue.id
WHERE kr.name = 'your_role_name';

Upvotes: 11

Related Questions