Open tretinha opened 1 month ago
I'm wondering if this has something to do with the KC_PROXY_HEADERS
variable that is currently set to xforward
.
Looks like a dialect issue. Keycloak uses hibernate to handle SQL dialects. In your case it seems that the database in use is not able to resolve the created SQL-query. The SQL queries are resolved by hibernate based on the selected SQL dialect. The question here is which SQL-dialect is currently in use and which dialect is supported by AWS RDS? I have no experience with the AWS system and cannot support on this behalf but you can change the SQL dialect that is used by Keycloak with like this:
db=mysql
db-username=root
db-password=123456
db-url=jdbc:mysql://localhost:3306/keycloak
db-dialect=org.hibernate.dialect.MySQL8Dialect
in that case the MySQL dialect for verson 8 was explicitly selected. I would guess that in your case you will also need to find the correct dialect that should be used. Normally the dialect is selected automatically but on some occasions this auto-detection does not work or cannot work if the dialect is no longer supported for example. I just had such a case recently with Postgres10 that is also no longer supported by the current Keycloak version.
Thanks for taking a look!
Apparently the dialect you selected there should be the same for AWS MySQL 8; I changed the driver I was using after your comment here because I realized I should use the JDBC AWS wrapper, as it provides a few other functions for AWS. With that said, my setup still didn't work. I'll keep searching to see if I find any information regarding what's the correct dialect for MySQL 8 running on AWS RDS.
Pasting here my configuration just in case someone is going through something similar.
Environment variables:
{
"name": "KC_DB_URL",
"value": "jdbc:aws-wrapper:mysql://${aws_db_instance.db-sso.endpoint}/${aws_db_instance.db-sso.db_name}?characterEncoding=UTF-8&serverTimezone=UTC"
},
{
"name": "KC_DB",
"value": "mysql"
},
{
"name": "KC_DB_DRIVER",
"value": "software.amazon.jdbc.Driver"
},
I'm using a custom Docker image so I can add some providers like the SCIM one:
FROM quay.io/keycloak/keycloak:25.0.5 AS builder
COPY SCIM-2.3.2-free.jar /opt/keycloak/providers/scim-2.3.2.jar
COPY kc-email-2fa.jar /opt/keycloak/providers/kc-email-2fa.jar
ADD --chmod=0666 https://github.com/aws/aws-advanced-jdbc-wrapper/releases/download/2.3.9/aws-advanced-jdbc-wrapper-2.3.9.jar /opt/keycloak/providers/aws-advanced-jdbc-wrapper.jar
COPY html-code-email.ftl /opt/keycloak/themes/base/email/html/code-email.ftl
COPY html-email-test.ftl /opt/keycloak/themes/base/email/html/email-test.ftl
COPY text-code-email.ftl /opt/keycloak/themes/base/email/text/code-email.ftl
COPY text-email-test.ftl /opt/keycloak/themes/base/email/text/email-test.ftl
COPY messages_en.properties /opt/keycloak/themes/base/email/messages/messages_en.properties
RUN /opt/keycloak/bin/kc.sh build -Dkc.db-dialect=org.hibernate.dialect.MySQL8Dialect
FROM quay.io/keycloak/keycloak:25.0.5
COPY --from=builder /opt/keycloak /opt/keycloak
ENTRYPOINT [ "/opt/keycloak/bin/kc.sh" ]
The only doubt I have is if I'm setting the dialect in the correct place there in the build, which I'm assuming that I am since the build didn't complain. I'll keep trying to set the correct drivers and dialect. Appreciate any thoughts/pointers!
Edited: saw somewhere that Hibernate 6 uses MySQLDialect instead of MySQL8Dialect, try that too and didn't work. Error message is fairly the same, complaining of some query:
log="2024-09-17 20:54:06,807 ERROR [de.captaingoldfish.scim.sdk.common.response.ErrorResponse] (executor-thread-1) JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]: de.captaingoldfish.scim.sdk.common.exceptions.InternalServerException: JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]" source=stdout
log="Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]" source=stdout
this part is wrong:
-Dkc.db-dialect=org.hibernate.dialect.MySQL8Dialect
this would be
-Ddb-dialect=org.hibernate.dialect.MySQL8Dialect
without kc.
the KC_
is only used when using environment variables.
But I am also not sure if this will work. This depends on the underlying DBMS. Are you sure you are using a mysql 8 in AWS? I saw that AWS supports mysql 5.7 and 8. Make sure that you are really on mysql 8
Yeah, the DB was created with MySQL 8 and the specific version (by running select version();
) is 8.0.39.
Just tried with the correct settings but it didn't work, unfortunately. I tried with both MySQL8Dialect
and MySQLDialect
.
I also tried with both AWS JDBC driver and the default MySQL one that Keycloak has. I'm gonna paste some more comprehensive logs here just in case it tells you something different.
Logs using the AWS driver but when I'm using the default the messages are very similar the only difference is the driver name that changes to mysql.c.j or something like this (bottom up):
log="2024-09-18 18:15:50,917 WARN [io.agroal.pool] (executor-thread-2) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)" source=stdout
log= source=stdout
log="\t... 27 more" source=stdout
log="\tat org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:87)" source=stdout
log="\tat io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:90)" source=stdout
log="\tat software.amazon.jdbc.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:247)" source=stdout
log="\tat software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:244)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:331)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:230)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)" source=stdout
log="\tat software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.execute(AuroraConnectionTrackerPlugin.java:122)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)" source=stdout
log="\tat software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:222)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)" source=stdout
log="\tat software.amazon.jdbc.plugin.efm2.HostMonitoringConnectionPlugin.execute(HostMonitoringConnectionPlugin.java:177)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:263)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:263)" source=stdout
log="\tat software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)" source=stdout
log="\tat software.amazon.jdbc.plugin.DefaultConnectionPlugin.execute(DefaultConnectionPlugin.java:130)" source=stdout
log="\tat software.amazon.jdbc.wrapper.PreparedStatementWrapper.lambda$executeUpdate$17(PreparedStatementWrapper.java:253)" source=stdout
log="\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:988)" source=stdout
log="\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1312)" source=stdout
log="\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1003)" source=stdout
log="\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1054)" source=stdout
log="\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912)" source=stdout
log="\tat com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)" source=stdout
log="\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)" source=stdout
log="Caused by: java.sql.SQLException: You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause" source=stdout
log="\t... 18 more" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.endpoints.ResourceEndpointHandler.deleteResource(ResourceEndpointHandler.java:934)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.interceptor.NoopInterceptor.doAround(NoopInterceptor.java:21)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.endpoints.ResourceEndpointHandler.lambda$deleteResource$22(ResourceEndpointHandler.java:939)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.backend.scim.remotescimprovider.ScimRemoteProviderResourceHandler.deleteResource(ScimRemoteProviderResourceHandler.java:228)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.backend.scim.remotescimprovider.RemoteScimProviderToDatabaseConverter.deleteAssociatedResources(RemoteScimProviderToDatabaseConverter.java:199)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.backend.scim.remotescimprovider.RemoteScimProviderToDatabaseConverter.deleteAssociatedResourceTypes(RemoteScimProviderToDatabaseConverter.java:235)" source=stdout
log="\tat org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:675)" source=stdout
log="\tat org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:704)" source=stdout
log="\tat org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.executeUpdate(SimpleDeleteQueryPlan.java:191)" source=stdout
log="\tat org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:101)" source=stdout
log="\tat org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)" source=stdout
log="\tat org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)" source=stdout
log="\tat org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63)" source=stdout
log="Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]" source=stdout
log="\tat java.base/java.lang.Thread.run(Thread.java:1583)" source=stdout
log="\tat io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)" source=stdout
log="\tat org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)" source=stdout
log="\tat org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)" source=stdout
log="\tat org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)" source=stdout
log="\tat org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)" source=stdout
log="\tat io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:582)" source=stdout
log="\tat org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)" source=stdout
log="\tat io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)" source=stdout
log="\tat org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.AdministrationBackendEndpoint$quarkusrestinvoker$handleScimDeleteRequest_503dd5e2ff097df80dbe24ca4e4822b62eb5dbfa.invoke(Unknown Source)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.AdministrationBackendEndpoint.handleScimDeleteRequest(AdministrationBackendEndpoint.java:124)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.administration.AdministrationBackendEndpoint.handleScimRequest(AdministrationBackendEndpoint.java:143)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.scim.AbstractEndpoint.handleScimRequest(AbstractEndpoint.java:72)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.scim.RetryStrategy.doWithRetries(RetryStrategy.java:58)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.keycloak.scim.AbstractEndpoint.lambda$handleScimRequest$2(AbstractEndpoint.java:75)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.endpoints.ResourceEndpoint.handleRequest(ResourceEndpoint.java:196)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.endpoints.ResourceEndpoint.resolveRequest(ResourceEndpoint.java:308)" source=stdout
log="\tat de.captaingoldfish.scim.sdk.server.endpoints.ResourceEndpointHandler.deleteResource(ResourceEndpointHandler.java:949)" source=stdout
log="2024-09-18 18:15:50,904 ERROR [de.captaingoldfish.scim.sdk.common.response.ErrorResponse] (executor-thread-2) JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]: de.captaingoldfish.scim.sdk.common.exceptions.InternalServerException: JDBC exception executing SQL [delete from SCIM_RESOURCE_SCHEMA_MAPPING where ID in (select srtsm2_0.ID from SCIM_RESOURCE_TYPE srte1_0 join SCIM_RESOURCE_SCHEMA_MAPPING srtsm2_0 on srtsm2_0.RESOURCE_TYPE_ID=srte1_0.ID where srte1_0.SCIM_REMOTE_PROVIDER_ID=?)] [You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause] [n/a]" source=stdout
log="2024-09-18 18:15:50,899 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-2) You can't specify target table 'SCIM_RESOURCE_SCHEMA_MAPPING' for update in FROM clause" source=stdout
log="2024-09-18 18:15:50,898 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-2) SQL Error: 1093, SQLState: HY000" source=stdout
I am very sorry... I was not able to recreate the issue because I configured the wrong keycloak to mysql and therefore I got wrong test-results... I was able to recreate it now and tried to fix it. The SQL-dialect chosen by hibernate seems to be correct. The problem is that MySQL does not seem to support such SQL queries... I certainly did not expect that. And until know it seems you are the first one to test this with MySQL 😅
I just tried to fix it and uploaded a SNAPSHOT version for further testing: kc-25-2.3.3-SNAPSHOT-free
you can download it on the website.
I'm happy to announce that both errors I had are gone with this new SNAPSHOT version. I was able to load the AWS configuration and will now configure it properly, I'll let you know if I stumble across other issues like this one.
Thank you for your assistance and fixes!
Sounds great. Please let me know if you are finished with testing. If everything works fine I will release the fix.
I tested adding users and groups to AWS and it worked correctly so far, had no issues that weren't my own fault with this version.
Thanks again!
Context
I'm trying to add a Remote SCIM Provider such as AWS, when I tried loading the provider configuration (or using the default configuration) after I filled the fields with the AWS SCIM Base URL and Bearer Token, I got the following error showing on the screen: "An internal error has occurred.", inspecting the page and clicking on the error on the browser's console I get the following json:
I'm not really understanding why I get such message when I'm using the admin user and the master realm. The admin user has the "scim-admin" role. Additional logs related to this error from Keycloak (bottom up):
AWS RDS MySQL is the database, for what it's worth. I'm not really sure how to interpret this error message or how to fix it. And, after this happened I tried to delete the Remote SCIM Provider and also wasn't successful. When I tried to delete the configuration I also got a 500 Internal server error, with the same json. The logs related to this attempt are pretty much the same as the ones I sent above.
The URL it tries to access when I try to load the configuration is:
https://sso.mydomain.com/realms/master/scim/admin/backend/scim/v2/RemoteScimProviderConfig/a-medium-sized-token-here
. It's the same URL for the deletion endpoint.So, to summarize, I can't load any provider configuration or the default configuration, and I also can't delete the providers.
Do you know what might be happening here? Keycloak version 25.0.5, plugin version kc-25-2.3.2. Thanks!
Screenshots
Trying to load a provider configuration:
Trying to delete a provider: