Keycloak - Get all Users mapped to roles

Question

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.

Shanley

Answer 1

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';

Bannasch

excellent stuff, thanks for sharing, plus they have admin module as well for such kind of queries, right..?? – Shanley

how can we do this with the java client api? – Ingoing

This query is showing only the manually assigned user. For me we have a LDAP server from which roles are synced - And this query is not returning the users who have role access (readed from LDAP) – Unshackle

your link seems to be broken, working link: issues.redhat.com/browse/KEYCLOAK-1902?_sscc=t – Fp

Answer 2

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.

Peddle

Link is broken - use keycloak.org/docs-api/12.0/rest-api/index.html#_roles_resource – Milled

Thank you for modifying the link. It looks like the old version of the documentation was dropped. – Peddle

one thing here: this will only work on client roles and not on realm roles. – Catalpa

What is client here? – Harbourage

"client" is a defined application, that uses Keycloak to authenticate. Some roles may be defined for each "client", which is what Loading described. – Peddle

Answer 3

This should be now possible with the updated rest endpoint.

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

Harker

Answer 4

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;

Thirtyeight

Answer 5

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).

Zigmund

Answer 6

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;

Sweeten