Hello everybody! I hope a topic regarding this question hasn’t been created before, I certainly was not able to find anything on this topic.
The situation: We are using Keycloak for identification purposes for multiple clients that are not developed by our team, save for one. We have been informed that some clients want to use the offline_access role that is provided by Keycloak. We have therefore set it as a default role for the realm the clients are connected to. I think it has been set up to automatically apply that role to all new users from the start, but as I have been involved with this project for only about half a year, I would need to check with the team. When using a SQL query to check how many users have been given this role (which we have executed multiple times during the last few months), we have noticed that the number of users this role has been applied to is only sinking (due to users being deleted - accounts that belong to our company are deleted as soon as an employee leaves), while the amount of people without the offline_access role is steadily increasing. I therefore wanted to check if I can spot a pattern and adjusted the query to return the user names.
When I did random spot checks for accounts that include the role, I was able to confirm that they do indeed have the role applied to their account (at least that is what the Keycloak Admin Console implied).
However, when I started doing the same checks for accounts that supposedly did not have the role applied to their account, I noticed that all of them did have the role applied to their account, contrary to what the SQL query has stated. I then checked if perhaps it was data from other realms, but this turned out to not be an issue. “Thankfully”, my account was one that was listed as not having the offline_access role applied. I then logged into one of the connected clients and extracted the JWT. In there, under realm_access roles, it seems like my account has the offline_access role as well, confirming the information shown in the Admin Console and disproving what the SQL query has returned.
To be honest, I am confused as to what my/our next steps should be due to the conflicting information, therefore I have assembled the following questions:
- Are SQL queries reliable for gathering bigger amounts of user data from Keycloak? They should be in theory, but this situation is honestly quite baffling and I just want to be on the safe side. If not, what alternatives should we be using?
- Is there any way to reliably gather information about how many and which users do (not) have the offline_access role applied to their account?
- The last question is simply for verification purposes: If it turns out that some users are indeed missing the offline_access role, we would assign them that role through API calls. That should be a sound method, no? I am unfortunately neither involved with the team for a long enough time nor knowledgeable about the inner workings of Keycloak, as I am working part-time in our team besides still being an CS student.
If any further information is required, please let me know. I don’t think that customer-specific information is required because I am not allowed to give that out to the public, but technical information is fair game. I’m also extremely sorry for the odd mistake here and there in my phrasing, I am not a native English speaker.
Thanks in advance for all the assistance! I am currently at a loss and unsure of what to do next. I hope that some members of this community can share some valuable information with me.