Duplicate key value violates unique constraint "constraint_40" #42586

betonetotbo · 2025-09-12T12:53:03Z

betonetotbo
Sep 12, 2025

In our environment, we are running Keycloak 26.1.2 with 2 replicas.

Yesterday, we deployed a new feature that includes a custom SPI. This SPI performs an HTTP request to another internal service within our infrastructure. Its implementation is relatively straightforward.

The SPI in question is an IdpAuthenticator, configured within a custom authentication flow. This flow is only executed during the first login attempt through a SAML Identity Provider (IdP).

Following the deployment of this upgrade, during functional testing, we consistently encountered an HTTP 500 error immediately after a successful SAML login. The error is triggered after the execution of our custom SPI.

Below is the captured call stack from the error log:

2025-09-12 01:51:47,124 INFO  [org.hibernate.orm.jdbc.batch] (executor-thread-18) HHH100503: On release of batch it still contained JDBC statements
2025-09-12 01:51:47,125 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-18) SQL Error: 0, SQLState: 23505
2025-09-12 01:51:47,125 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-18) Batch entry 0 insert into keycloak.FEDERATED_IDENTITY (REALM_ID,TOKEN,FEDERATED_USER_ID,FEDERATED_USERNAME,IDENTITY_PROVIDER,USER_ID) values (('my-realm'),(NULL),('G-2cf57e0c-efc0-457d-b497-5d88bae498ab'),('user@saml.com'),('saml-saml.com'),('88266fa6-93b1-4868-aa22-97a9542ace68')) was aborted: ERROR: duplicate key value violates unique constraint "constraint_40"
  Detail: Key (identity_provider, user_id)=(saml-saml.com, 88266fa6-93b1-4868-aa22-97a9542ace68) already exists.  Call getNextException to see other errors in the batch.
2025-09-12 01:51:47,127 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-18) ERROR: duplicate key value violates unique constraint "constraint_40"
  Detail: Key (identity_provider, user_id)=(saml-saml.com, 88266fa6-93b1-4868-aa22-97a9542ace68) already exists.
2025-09-12 01:51:47,135 WARN  [org.keycloak.events] (executor-thread-18) type="IDENTITY_PROVIDER_LOGIN_ERROR", realmId="my-realm", realmName="my-realm", clientId="my-realm", userId="88266fa6-93b1-4868-aa22-97a9542ace68", ipAddress="189.16.40.2", error="identityProviderUnexpectedErrorMessage", identity_provider="saml-saml.com", identity_provider_identity="user@saml.com", code_id="972ea829-f27a-4b94-b2e2-acbbfdfc1cc1", username="user@saml.com"
2025-09-12 01:51:47,135 ERROR [org.keycloak.services.resources.IdentityBrokerService] (executor-thread-18) identityProviderUnexpectedErrorMessage: org.keycloak.models.ModelDuplicateException: Duplicate resource error
  at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert(PersistenceExceptionConverter.java:103)
  at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:68)
  at jdk.proxy2/jdk.proxy2.$Proxy66.flush(Unknown Source)
  at org.keycloak.models.jpa.JpaUserProvider.addFederatedIdentity(JpaUserProvider.java:175)
  at org.keycloak.storage.UserStorageManager.addFederatedIdentity(UserStorageManager.java:648)
  at org.keycloak.models.cache.infinispan.UserCacheSession.addFederatedIdentity(UserCacheSession.java:867)
  at org.keycloak.services.resources.IdentityBrokerService.afterFirstBrokerLogin(IdentityBrokerService.java:720)
  at org.keycloak.services.resources.IdentityBrokerService.afterFirstBrokerLogin(IdentityBrokerService.java:670)
  at org.keycloak.services.resources.IdentityBrokerService$quarkusrestinvoker$afterFirstBrokerLogin_9cf819ed0809b78947dfd5819657c67a0cd1a62d.invoke(Unknown Source)
  at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
  at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
  at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
  at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:635)
  at org.jboss.threads.EnhancedQueueExecutor$Task.doRunWith(EnhancedQueueExecutor.java:2516)
  at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2495)
  at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1521)
  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)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into keycloak.FEDERATED_IDENTITY (REALM_ID,TOKEN,FEDERATED_USER_ID,FEDERATED_USERNAME,IDENTITY_PROVIDER,USER_ID) values (('my-realm'),(NULL),('G-2cf57e0c-efc0-457d-b497-5d88bae498ab'),('user@saml.com'),('saml-saml.com'),('88266fa6-93b1-4868-aa22-97a9542ace68')) was aborted: ERROR: duplicate key value violates unique constraint "constraint_40"
  Detail: Key (identity_provider, user_id)=(saml-saml.com, 88266fa6-93b1-4868-aa22-97a9542ace68) already exists.  Call getNextException to see other errors in the batch.
  at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
  at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:580)
  at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:888)
  at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:912)
  at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1739)
  at io.agroal.pool.wrapper.StatementWrapper.executeBatch(StatementWrapper.java:340)
  at org.hibernate.engine.jdbc.batch.internal.BatchImpl.lambda$performExecution$2(BatchImpl.java:279)
  at org.hibernate.engine.jdbc.mutation.internal.PreparedStatementGroupSingleTable.forEachStatement(PreparedStatementGroupSingleTable.java:67)
  at org.hibernate.engine.jdbc.batch.internal.BatchImpl.performExecution(BatchImpl.java:264)
  at org.hibernate.engine.jdbc.batch.internal.BatchImpl.execute(BatchImpl.java:242)
  at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:188)
  at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:674)
  at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:511)
  at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:414)
  at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41)
  at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
  at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1429)
  at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1415)
  at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
  at java.base/java.lang.reflect.Method.invoke(Method.java:580)
  at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:66)
  ... 18 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "constraint_40"
  Detail: Key (identity_provider, user_id)=(saml-saml.com, 88266fa6-93b1-4868-aa22-97a9542ace68) already exists.
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
  ... 38 more
2025-09-12 01:53:40,520 WARN  [org.keycloak.events] (executor-thread-32) type="IDENTITY_PROVIDER_LOGIN_ERROR", realmId="my-realm", realmName="my-realm", clientId="my-realm", userId="null", ipAddress="189.16.40.2", error="cookie_not_found", identity_provider="saml-saml.com"

This is the first login saml flow:

Due to this error, we were unable to log in with the user user@saml.com.

The only way to resolve the issue was by restarting all Keycloak instances to ensure that the embedded Infinispan cache was fully discarded.

Any idea about what can I review on my authentication flow or my SPI implementation?

bernhof · 2025-09-12T13:50:56Z

bernhof
Sep 12, 2025

The error message indicates that the user's link to this IDP is created twice, thus the unique constraint violation:

(saml-saml.com, 88266fa6-93b1-4868-aa22-97a9542ace68)

Where the latter uuid is Keycloak's internal user id, ie. you cannot link a user to the same IDP twice. Is the custom SPI messing with the federated user context?

0 replies

Navigation Menu

Repository navigation

Duplicate key value violates unique constraint "constraint_40" #42586

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Duplicate key value violates unique constraint "constraint_40" #42586

Uh oh!

betonetotbo Sep 12, 2025

Replies: 1 comment

Uh oh!

bernhof Sep 12, 2025

betonetotbo
Sep 12, 2025

bernhof
Sep 12, 2025