-
Bug
-
Resolution: Done
-
Undefined
-
None
-
False
-
-
False
-
-
Before reporting an issue
[x] 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
storage
Describe the bug
When using a database that automatically converts unquoted schema names to either Upper or Lower case, realm removal fails if --db-schema is explicitly configured.
This is because we retrieve the default schema from Hibernate, which does not return a case-sensitive value and then we quote the returned schema forcing the used value to be case-sensitive. This leads to a miss-match between the SQL specified and the table which is created, resulting in the following exception being thrown:
[org.keycloak.services.error.KeycloakErrorHandler] (executor-thread-1) Uncaught server error: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete from "KEYCLOAK".SCOPE_MAPPING where ROLE_ID = ?] [ERROR: relation "KEYCLOAK.scope_mapping" does not exist Position: 13] [n/a] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:104) at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:76) at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:973) at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:651) at org.keycloak.models.jpa.JpaRealmProvider.preRemove(JpaRealmProvider.java:864) at org.keycloak.models.jpa.JpaRealmProviderFactory.onEvent(JpaRealmProviderFactory.java:89) at org.keycloak.services.DefaultKeycloakSessionFactory.publish(DefaultKeycloakSessionFactory.java:87) at org.keycloak.models.jpa.JpaRealmProvider.removeRole(JpaRealmProvider.java:472) at org.keycloak.storage.RoleStorageManager.removeRole(RoleStorageManager.java:185) at org.keycloak.models.cache.infinispan.RealmCacheSession.removeRole(RealmCacheSession.java:921) at org.keycloak.models.cache.infinispan.ClientAdapter.removeRole(ClientAdapter.java:555) at org.keycloak.services.resources.admin.RoleResource.deleteRole(RoleResource.java:56) at org.keycloak.services.resources.admin.RoleContainerResource.deleteRole(RoleContainerResource.java:283) at org.keycloak.services.resources.admin.RoleContainerResource$quarkusrestinvoker$deleteRole_bd669db90c99320b3d767fea0f75df9d2cc9d83d.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$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) Caused by: org.postgresql.util.PSQLException: ERROR: relation "KEYCLOAK.scope_mapping" does not exist Position: 13 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155) at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:90) at org.hibernate.
Version
26.3.1
Regression
[ ] The issue is a regression
Expected behavior
All SQL statements should utilise the correct case for the schema.
Actual behavior
See description.
How to Reproduce?
- Provision a database that automatically converts to upper or lower case, e.g. Oracle or Postgres
- Explicitly configure --db-schema
- Delete a role from a realm
Anything else?
No response
- links to
