[midPoint] Users are "ghost members" of role after unassignment

[Sven Feyerabend]

Hello everyone,

I have a somewhat curious situation:

Some assignments don't seem to be deleted correctly if a user is removed 
from a role.
If this happens, the user is still shown as a role member in the 
"Members" tab of the role details.
However, the role no longer shows up in the "Assignments>Roles" tab of 
the user details view.

Looking at the database I found some assignments in the m_assignment 
table, that don't have a corresponding entry in the 
m_ref_role_membership table.
Maybe this is normal and I'm on the wrong track, as I'm not overly 
familiar with the midPoint database schema, but this seems odd to me.
Here is the SQL query I ran to get the mentioned users and roles:

WITH user_role_mapping AS (
     SELECT
         owneroid AS userid,
         targetreftargetoid AS roleid
     FROM
         m_assignment
     WHERE
         owneroid NOT IN (
             SELECT
                 owneroid
             FROM
                 m_ref_role_membership
             WHERE
                 targetoid = m_assignment.targetreftargetoid
         )
         AND ownertype = 'USER'
         AND targetreftargettype = 'ROLE'
)

SELECT
     m_user.nameorig AS username,
     m_role.nameorig AS groupname
FROM
     user_role_mapping
JOIN
     m_user ON user_role_mapping.userid = m_user.oid
JOIN
     m_role ON user_role_mapping.roleid = m_role.oid;


 From what I can tell after arbitrarily checking some of these results 
in the GUI, these are indeed the affected user - role pairs.

Has someone ever seen something similar?
If so how did this happen, and how do I fix it?
I would be very grateful for any pointers.
I'm running midPoint 4.8.2 in case this is relevant.

Thanks, and kind regards

Sven

-- 
Sven Feyerabend
Referent für IT-Betreuung
stuvus – Studierendenvertretung Universität Stuttgart
Pfaffenwaldring 5c
70569 Stuttgart
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20240325/3cdad249/attachment.htm>