Hello,
I am trying to figure out the DB connection usage in keycloak during token generation/refresh flows. I would have thought that once a token is generated for a Resource Owner Password Credentials (ROPC) flow or client credentials flow for a particular realm, then the realm and user data would be cached and hence there would be no need for Keycloak to reach out to the DB when the same user/client is used for these flows subsequently.
But what I observe (by enabling hibernate logs) is that in case of the ROPC flow, SQL queries are fired on the following tables:
CREDENTIAL, USER_ENTITY and CLIENT_SCOPE.
In case of the client_credentials flow, query is fired on the CLIENT_SCOPE table every time.
For the ROPC flow, if the user authentication is local (and not federated), why can’t the credential be stored in the cache and used from there ? Why is there a need to query the ‘credential’ and ‘user_entity’ tables ?
As far as the ‘client_scope’ table is concerned, from a little debugging that I performed, it looks like the ‘client id’ itself is being used as a client scope and RealmCacheSession.getClientScopeById never finds it in cache and always tries to check with the JPA store (which again does not find it in the client_scope table).
Why is client_id added to the list of client scopes ?
If it is needed then do we need to fix something in the code to ensure this unneeded SQL query is not fired every time ?
I have tested with Keycloak version 17.0.1 but I see that the code in this area is mostly the same in Keycloak version 21.1.1 as well.
Any thoughts ?