Unable to delete realm and client roles

[dileep-ehr]

Tested with docker deployment of versions 23.0.6 & 23.0.7. Used the default docker-compose.yml with postgres

Once a role is created under the realm or any client, we get error "Could not delete role: unknown_error" when we try to delete them. Able to delete the orgRoles without a problem.

---------------------Container logs snippet--------------------------- 2024-02-29 10:49:27,059 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-10) SQL Error: 90079, SQLState: 90079 2024-02-29 10:49:27,059 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-10) Schema "public" not found; SQL statement: delete from "public".COMPOSITE_ROLE where CHILD_ROLE = ? [90079-224] 2024-02-29 10:49:27,059 ERROR [org.keycloak.services.error.KeycloakErrorHandler] (executor-thread-10) Uncaught server error: org.hibernate.exception.SQLGrammarException: could not prepare statement [Schema "public" not found; SQL statement: delete from "public".COMPOSITE_ROLE where CHILD_ROLE = ? [90079-224]] [delete from "public".COMPOSITE_ROLE where CHILD_ROLE = ?] at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:187) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:76) at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.lambda$executeUpdate$0(NativeNonSelectQueryPlanImpl.java:82) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:62) at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:76) at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:839) at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:637) at org.keycloak.models.jpa.JpaRealmProvider.removeRole(JpaRealmProvider.java:430) at org.keycloak.storage.RoleStorageManager.removeRole(RoleStorageManager.java:185) at org.keycloak.models.cache.infinispan.RealmCacheSession.removeRole(RealmCacheSession.java:848) at org.keycloak.models.cache.infinispan.RealmAdapter.removeRole(RealmAdapter.java:1069) at org.keycloak.services.resources.admin.RoleResource.deleteRole(RoleResource.java:56) at org.keycloak.services.resources.admin.RoleByIdResource.deleteRole(RoleByIdResource.java:127) at org.keycloak.services.resources.admin.RoleByIdResource$quarkusrestinvoker$deleteRole_1c62f20557ae16fb1bc4e5c258c6eb429461da49.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:145) at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576) at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538) at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29) at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.base/java.lang.Thread.run(Thread.java:840) Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "public" not found; SQL statement: delete from "public".COMPOSITE_ROLE where CHILD_ROLE = ? [90079-224] at org.h2.message.DbException.getJdbcSQLException(DbException.java:644) at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) at org.h2.message.DbException.get(DbException.java:223) at org.h2.message.DbException.get(DbException.java:199) at org.h2.command.Parser.getSchema(Parser.java:944) at org.h2.command.Parser.getSchema(Parser.java:950) at org.h2.command.Parser.readTableOrView(Parser.java:7999) at org.h2.command.Parser.readTableOrView(Parser.java:7990) at org.h2.command.Parser.readSimpleTableFilter(Parser.java:1094) at org.h2.command.Parser.parseDelete(Parser.java:1108) at org.h2.command.Parser.parsePrepared(Parser.java:677) at org.h2.command.Parser.parse(Parser.java:592) at org.h2.command.Parser.parse(Parser.java:564) at org.h2.command.Parser.prepareCommand(Parser.java:483) at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:639) at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:559) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1166) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:93) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:316) at io.agroal.pool.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:658) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:91) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:177) ... 23 more

[xgp]

Have you tried the same on the standard Keycloak images? Can you confirm those tables are present under the ‘public’ schema in your Postgres instance? Those are just table not found errors, which makes me think something else is going on.

[dileep-ehr]

Thanks @xgp,

The postgres connection configuration in the docker-compose.yml file was not correct. Look like it is following some older version params. I updated it and now it is working.

---------------Updated db connection details in docker-compose.yaml--------------

  KC_DB_URL: jdbc:postgresql://postgres:5432/keycloak
  KC_DB: postgres
  KC_DB_SCHEMA: public
  KC_DB_USERNAME: keycloak
  KC_DB_PASSWORD: password

---