Seaching users in LDAP does not scale well due to ignoring local users when processing entries returned from LDAP

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

admin/api

Describe the bug

When calling admin rest api (GET /admin/realms/{realm}/users) sometimes the query takes a very long time to execute.

Parameter max in the query is 100. In the user federation settings cache policy is "no cache" The number of users in DB keylock is 117000

On the database side (postgresql 16) there are 100 connections, but no more than 20 are used in total.

There are no problems with connecting to AD. According to the metrics, the transaction goes into the idle in transaction state.

Version

26.3.3

Regression

• The issue is a regression

Expected behavior

It should be faster

Actual behavior

Long query execution, with an error

org.hibernate.exception.GenericJDBCException: could not prepare statement [Connection is closed] [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=?]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:191)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:153)
	at org.hibernate.sql.exec.internal.StandardStatementCreator.createStatement(StandardStatementCreator.java:49)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:236)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:172)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$1(ConcreteSqmSelectQueryPlan.java:152)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:442)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:362)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:380)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at org.keycloak.models.jpa.JpaUserProvider.getUserByUsername(JpaUserProvider.java:524)
	at org.keycloak.storage.ldap.LDAPStorageProvider.lambda$filterLocalUsers$18(LDAPStorageProvider.java:1179)
	at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:178)
	at java.base/java.util.ArrayList$ArrayListSpliterator.tryAdvance(ArrayList.java:1685)
	at java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:280)
	at java.base/java.util.stream.Stream$2.tryAdvance(Stream.java:1539)
	at java.base/java.util.stream.ReferencePipeline.forEachWithCancel(ReferencePipeline.java:129)
	at java.base/java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:527)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:513)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
	at java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
	at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1708)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEachOrdered(ReferencePipeline.java:601)
	at com.fasterxml.jackson.datatype.jdk8.StreamSerializer.serialize(StreamSerializer.java:71)
	at com.fasterxml.jackson.datatype.jdk8.StreamSerializer.serialize(StreamSerializer.java:15)
	at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize(DefaultSerializerProvider.java:502)
	at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:422)
	at com.fasterxml.jackson.databind.ObjectWriter$Prefetch.serialize(ObjectWriter.java:1579)
	at com.fasterxml.jackson.databind.ObjectWriter._writeValueAndClose(ObjectWriter.java:1285)
	at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:1098)
	at io.quarkus.resteasy.reactive.jackson.runtime.serialisers.FullyFeaturedServerJacksonMessageBodyWriter.writeResponse(FullyFeaturedServerJacksonMessageBodyWriter.java:106)
	at io.quarkus.resteasy.reactive.jackson.runtime.serialisers.FullyFeaturedServerJacksonMessageBodyWriter_ClientProxy.writeResponse(Unknown Source)
	at org.jboss.resteasy.reactive.server.core.ServerSerialisers.invokeWriter(ServerSerialisers.java:218)
	at org.jboss.resteasy.reactive.server.core.ServerSerialisers.invokeWriter(ServerSerialisers.java:186)
	at org.jboss.resteasy.reactive.server.core.serialization.FixedEntityWriter.write(FixedEntityWriter.java:28)
	at org.jboss.resteasy.reactive.server.handlers.ResponseWriterHandler.handle(ResponseWriterHandler.java:34)
	at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:147)
	at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
	at io.quarkus.vertx.core.runtime.VertxCoreRecorder$15.runWith(VertxCoreRecorder.java:638)
	at org.jboss.threads.EnhancedQueueExecutor$Task.doRunWith(EnhancedQueueExecutor.java:2675)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2654)
	at org.jboss.threads.EnhancedQueueExecutor.runThreadBody(EnhancedQueueExecutor.java:1627)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1594)
	at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:11)
	at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:11)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:1583)
	Suppressed: java.lang.RuntimeException: jakarta.transaction.RollbackException: ARJUNA016102: The transaction is not active! Uid is 0:ffff0adc7899:a2f9:68c55f20:66025
		at org.keycloak.transaction.JtaTransactionWrapper.handleException(JtaTransactionWrapper.java:90)
		at org.keycloak.transaction.JtaTransactionWrapper.commit(JtaTransactionWrapper.java:104)
		at org.keycloak.services.DefaultKeycloakTransactionManager.lambda$commitWithTracing$1(DefaultKeycloakTransactionManager.java:170)
		at org.keycloak.tracing.NoopTracingProvider.trace(NoopTracingProvider.java:59)
		at org.keycloak.tracing.NoopTracingProvider.trace(NoopTracingProvider.java:69)
		at org.keycloak.services.DefaultKeycloakTransactionManager.commitWithTracing(DefaultKeycloakTransactionManager.java:169)
		at org.keycloak.services.DefaultKeycloakTransactionManager.lambda$commit$0(DefaultKeycloakTransactionManager.java:136)
		at org.keycloak.tracing.NoopTracingProvider.trace(NoopTracingProvider.java:59)
		at org.keycloak.tracing.NoopTracingProvider.trace(NoopTracingProvider.java:69)
		at org.keycloak.services.DefaultKeycloakTransactionManager.commit(DefaultKeycloakTransactionManager.java:121)
		at org.keycloak.services.DefaultKeycloakSession.closeTransactionManager(DefaultKeycloakSession.java:392)
		at org.keycloak.services.DefaultKeycloakSession.close(DefaultKeycloakSession.java:357)
		at org.keycloak.models.KeycloakBeanProducer_ProducerMethod_getKeycloakSession_XoSEUTXOsE3bpqXlGMAykCiECUM_ClientProxy.close(Unknown Source)
		at org.keycloak.quarkus.runtime.transaction.TransactionalSessionHandler.close(TransactionalSessionHandler.java:60)
		at org.keycloak.quarkus.runtime.integration.jaxrs.CloseSessionFilter.closeSession(CloseSessionFilter.java:67)
		at java.base/java.util.stream.AbstractPipeline.close(AbstractPipeline.java:323)
		at com.fasterxml.jackson.datatype.jdk8.StreamSerializer.serialize(StreamSerializer.java:84)
		... 23 more
	Caused by: jakarta.transaction.RollbackException: ARJUNA016102: The transaction is not active! Uid is 0:ffff0adc7899:a2f9:68c55f20:66025
		at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1297)
		at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:104)
		at io.quarkus.narayana.jta.runtime.NotifyingTransactionManager.commit(NotifyingTransactionManager.java:70)
		at org.keycloak.transaction.JtaTransactionWrapper.commit(JtaTransactionWrapper.java:102)
		... 38 more
Caused by: java.sql.SQLException: Connection is closed
	at io.agroal.pool.wrapper.ConnectionWrapper.lambda$static$0(ConnectionWrapper.java:51)
	at jdk.proxy4/jdk.proxy4.$Proxy69.prepareStatement(Unknown Source)
	at io.agroal.pool.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:661)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$4.doPrepare(StatementPreparerImpl.java:151)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:180)
	... 63 more

How to Reproduce?

Send a request to the API like GET /admin/realms/myrealm/users?first=0&max=100

Anything else?

If the query takes a long time, then the number of spans becomes very large. It seems that repeated requests to the database are being made to get users