Uploaded image for project: 'RH-SSO'
  1. RH-SSO
  2. RHSSO-1739

Add Primary Key Constraint into RESOURCE_URIS table

    XMLWordPrintable

Details

    • Enhancement
    • Resolution: Done
    • Major
    • Archive - 21'
    • RH-SSO-7.3.0.GA
    • Server
    • None

    Description

      It appears that a PRIMARY KEY constraint is missing on the "RESOURCE_URIS" table schema, and which table has been added since Keycloak 4.2.0.

      Cf.> https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resources/META-INF/jpa-changelog-authz-4.2.0.Final.xml

      <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.2.xsd">
          <changeSet author="mhajas@redhat.com" id="authz-4.2.0.Final">
              <createTable tableName="RESOURCE_URIS">
                  <column name="RESOURCE_ID" type="VARCHAR(36)">
                      <constraints nullable="false"/>
                  </column>
                  <column name="VALUE" type="VARCHAR(255)">
                      <constraints nullable="false"/>
                  </column>
              </createTable>
      
              <addForeignKeyConstraint baseColumnNames="RESOURCE_ID" baseTableName="RESOURCE_URIS" constraintName="FK_RESOURCE_SERVER_URIS" referencedColumnNames="ID" referencedTableName="RESOURCE_SERVER_RESOURCE"/>
      
              <customChange class="org.keycloak.connections.jpa.updater.liquibase.custom.AuthzResourceUseMoreURIs"/>
      
              <dropColumn columnName="URI" tableName="RESOURCE_SERVER_RESOURCE"/>
          </changeSet>
      </databaseChangeLog>
      

      As per RHSSO-1060 / KEYCLOAK-4928, it looks like that Primary Key Constraints are needed on all tables of the database.

      Not having a PK on this "RESOURCE_URIS" table can lead to following ERROR which has been reported by a customer running with RH-SSO 7.3.0.GA (see support case #02346751 ) :

      2019-03-22 11:26:42.772 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-28) SQL Error: 0, SQLState: 55000
      2019-03-22 11:26:42.773 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-28) ERROR: Cannot run UPDATE or DELETE on table resource_uris because it does not have a PRIMARY KEY.
        Hint: Add a PRIMARY KEY to the table
      2019-03-22 11:26:42.773 INFO  [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (default task-28) HHH000010: On release of batch it still contained JDBC statements
      2019-03-22 11:26:42.773 ERROR [org.keycloak.services.error.KeycloakErrorHandler] (default task-28) Uncaught server error: java.lang.RuntimeException: Error synchronizing authorization data.
      	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$registerSynchronizationListeners$1(AuthorizationStoreFactory.java:62)
      	at org.keycloak.services.DefaultKeycloakSessionFactory.publish(DefaultKeycloakSessionFactory.java:69) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
      	at org.keycloak.models.jpa.JpaRealmProvider.removeClient(JpaRealmProvider.java:572)
      	at org.keycloak.models.jpa.JpaRealmProvider.removeRealm(JpaRealmProvider.java:153)
      	at org.keycloak.models.cache.infinispan.RealmCacheSession.removeRealm(RealmCacheSession.java:486)
      	at org.keycloak.services.managers.RealmManager.removeRealm(RealmManager.java:248) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
      	at org.keycloak.services.resources.admin.RealmAdminResource.deleteRealm(RealmAdminResource.java:453) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_162]
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_162]
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_162]
      	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_162]
      	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140) [resteasy-jaxrs-3.6.1.SP2-redhat-00001.jar:3.6.1.SP2-redhat-00001]
      	...
      	...
      Caused by: org.keycloak.models.ModelException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
      	at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert(PersistenceExceptionConverter.java:61)
      	at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:51)
      	at com.sun.proxy.$Proxy92.flush(Unknown Source)
      	at org.keycloak.authorization.jpa.store.JPAResourceServerStore.delete(JPAResourceServerStore.java:117)
      	at org.keycloak.models.cache.infinispan.authorization.StoreFactoryCacheSession$ResourceServerCache.delete(StoreFactoryCacheSession.java:443)
      	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.removeFromClientPolicies(ClientApplicationSynchronizer.java:56)
      	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.synchronize(ClientApplicationSynchronizer.java:47)
      	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.synchronize(ClientApplicationSynchronizer.java:40)
      	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$null$0(AuthorizationStoreFactory.java:58)
      	at java.util.HashMap.forEach(HashMap.java:1289) [rt.jar:1.8.0_162]
      	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$registerSynchronizationListeners$1(AuthorizationStoreFactory.java:56)
      	... 77 more
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
      	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1460)
      	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1440)
      	at sun.reflect.GeneratedMethodAccessor589.invoke(Unknown Source) [:1.8.0_162]
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_162]
      	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_162]
      	at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:49)
      	... 86 more
      Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
      	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
      	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
      	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
      	at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:1230)
      	at org.hibernate.action.internal.CollectionRemoveAction.execute(CollectionRemoveAction.java:96)
      	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
      	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
      	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
      	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
      	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
      	... 91 more
      Caused by: org.postgresql.util.PSQLException: ERROR: Cannot run UPDATE or DELETE on table resource_uris because it does not have a PRIMARY KEY.
        Hint: Add a PRIMARY KEY to the table
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
      	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
      	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
      	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
      	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
      	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
      	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
      	... 99 more
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            rhn-support-igueye Issa Gueye
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: