DuplicateEmailValidator causes two DB queries on every login if a user has an email address

[ahus1]

Before reporting an issue

• I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.

Area

core

Describe the bug

When a user logs in that has an email address, the default configuration runs the DuplicateEmailValidator. This triggers two DB query if the user has an email address set.

This is a regression, as in previous versions this didn't happen before declarative user was there in KC24.

Version

main

Regression

• The issue is a regression

Expected behavior

On log in, the data of the user is assumed to be unchanged, and it would be about invalid and possibly missing data.

So IMHO there shouldn't be a DB query for this, and the test should be skipped. It should only trigger on changed data in the profile.

Actual behavior

The email address is used to query the database twice from the validator.

2024-04-29 10:26:25,085 DEBUG [org.hibernate.SQL] (executor-thread-1) select ue1_0.ID,ue1_0.CREATED_TIMESTAMP,ue1_0.EMAIL,ue1_0.EMAIL_CONSTRAINT,ue1_0.EMAIL_VERIFIED,ue1_0.ENABLED,ue1_0.FEDERATION_LINK,ue1_0.FIRST_NAME,ue1_0.LAST_NAME,ue1_0.NOT_BEFORE,ue1_0.REALM_ID,ue1_0.SERVICE_ACCOUNT_CLIENT_LINK,ue1_0.USERNAME from USER_ENTITY ue1_0 where ue1_0.EMAIL=? and ue1_0.REALM_ID=?
2024-04-29 10:26:25,086 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-1) binding parameter (1:VARCHAR) <- [me@keycloak.org]
2024-04-29 10:26:25,086 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-1) binding parameter (2:VARCHAR) <- [13ee8738-68f5-4a26-8117-60abd6cae4b0]
2024-04-29 10:26:25,089 DEBUG [org.hibernate.SQL] (executor-thread-1) select ue1_0.ID,ue1_0.CREATED_TIMESTAMP,ue1_0.EMAIL,ue1_0.EMAIL_CONSTRAINT,ue1_0.EMAIL_VERIFIED,ue1_0.ENABLED,ue1_0.FEDERATION_LINK,ue1_0.FIRST_NAME,ue1_0.LAST_NAME,ue1_0.NOT_BEFORE,ue1_0.REALM_ID,ue1_0.SERVICE_ACCOUNT_CLIENT_LINK,ue1_0.USERNAME from USER_ENTITY ue1_0 where ue1_0.USERNAME=? and ue1_0.REALM_ID=?
2024-04-29 10:26:25,089 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-1) binding parameter (1:VARCHAR) <- [me@keycloak.org]
2024-04-29 10:26:25,090 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-1) binding parameter (2:VARCHAR) <- [13ee8738-68f5-4a26-8117-60abd6cae4b0]

How to Reproduce?

• Spin up a new Keycloak instance
• Ensure that the "Verify profile" required action is enabled
• For the admin user, set an email address via the Admin ui
• To analyze, enable SQL logging, see: --log-level="INFO,org.hibernate.SQL:debug,org.hibernate.orm.jdbc.bind:trace"
• Log in as the admin user -> you see the two SQL queries above

Anything else?

I'll try to suggest a solution in a PR

[ahus1]

@tkyjovsk - this might show up as a performance regression in your RHBK24.

cc: @kami619