Uploaded image for project: 'Keycloak'
  1. Keycloak
  2. KEYCLOAK-6095

Not supported schemas for databases

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 3.4.0.CR1, 3.4.1.CR1
    • Fix Version/s: 3.4.3.Final, 4.0.0.Beta1
    • Component/s: Database
    • Labels:
      None
    • Environment:

      keycloak 3.4.1.CR1, PostgreSQL 9.6.2

    • Steps to Reproduce:
      Hide

      1) download keycloak 3.4.1.CR1.
      2) add module:
      modules/system/layers/keycloak/org/postgresql/
      main/
      module.xml
      postgresql-9.4.1212.jar
      3) Fill module.xml as follows:

      <?xml version="1.0" ?>
      <module xmlns="urn:jboss:module:1.3" name="org.postgresql">

      <resources>
      <resource-root path="postgresql-9.4.1212.jar"/>
      </resources>

      <dependencies>
      <module name="javax.api"/>
      <module name="javax.transaction.api"/>
      </dependencies>
      </module>

      4) Next changes in standalone.xml:

      ...
      <subsystem xmlns="urn:jboss:domain:datasources:5.0">
      <datasources>
      <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
      <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
      <driver>postgresql</driver>
      <security>
      <user-name>user-name</user-name>
      <password>password</password>
      </security>
      </datasource>
      <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
      <connection-url>jdbc:postgresql://localhost:5432/database</connection-url>
      <driver>postgresql</driver>
      <security>
      <user-name>user-name</user-name>
      <password>password</password>
      </security>
      </datasource>
      <drivers>
      <driver name="postgresql" module="org.postgresql">
      <driver-class>org.postgresql.Driver</driver-class>
      </driver>
      </drivers>
      </datasources>
      </subsystem>
      ...
      <subsystem xmlns="urn:jboss:domain:keycloak-server:1.1">
      ...
      <spi name="connectionsJpa">
      <provider name="default" enabled="true">
      <properties>
      <property name="dataSource" value="java:jboss/datasources/KeycloakDS"/>
      <property name="initializeEmpty" value="true"/>
      <property name="migrationStrategy" value="update"/>
      <property name="migrationExport" value="$

      {jboss.home.dir}

      /keycloak-database-update.sql"/>
      <property name="schema" value="auth"/>
      </properties>
      </provider>
      </spi>
      ...
      </subsystem>

      5) start standalone.bat(sh)

      Show
      1) download keycloak 3.4.1.CR1. 2) add module: modules/system/layers/keycloak/org/postgresql/ main/ module.xml postgresql-9.4.1212.jar 3) Fill module.xml as follows: <?xml version="1.0" ?> <module xmlns="urn:jboss:module:1.3" name="org.postgresql"> <resources> <resource-root path="postgresql-9.4.1212.jar"/> </resources> <dependencies> <module name="javax.api"/> <module name="javax.transaction.api"/> </dependencies> </module> 4) Next changes in standalone.xml: ... <subsystem xmlns="urn:jboss:domain:datasources:5.0"> <datasources> <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true"> <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url> <driver>postgresql</driver> <security> <user-name>user-name</user-name> <password>password</password> </security> </datasource> <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true"> <connection-url>jdbc:postgresql://localhost:5432/database</connection-url> <driver>postgresql</driver> <security> <user-name>user-name</user-name> <password>password</password> </security> </datasource> <drivers> <driver name="postgresql" module="org.postgresql"> <driver-class>org.postgresql.Driver</driver-class> </driver> </drivers> </datasources> </subsystem> ... <subsystem xmlns="urn:jboss:domain:keycloak-server:1.1"> ... <spi name="connectionsJpa"> <provider name="default" enabled="true"> <properties> <property name="dataSource" value="java:jboss/datasources/KeycloakDS"/> <property name="initializeEmpty" value="true"/> <property name="migrationStrategy" value="update"/> <property name="migrationExport" value="$ {jboss.home.dir} /keycloak-database-update.sql"/> <property name="schema" value="auth"/> </properties> </provider> </spi> ... </subsystem> 5) start standalone.bat(sh)
    • Docs QE Status:
      NEW
    • QE Status:
      ASSIGNED

      Description

      Problem related to schema configuration for database storage. When keycloak server started at first time, it was create corresponding tables in external database in right schema, but at some step it was breaked with error. Problem in some update section. I think, you can found it here, in next section:

      <sql>
                  UPDATE RESOURCE_SERVER_POLICY p SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
                  UPDATE RESOURCE_SERVER_RESOURCE p SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
                  UPDATE RESOURCE_SERVER_SCOPE p SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
      </sql>
      

      Stacktrace in attachements. If just comment line

      <property name="schema" value="auth"/>

      in standalone.xml than all works fine, but all tables will be create in public schema.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                hmlnarik Hynek Mlnařík
                Reporter:
                evgeniy.muravitskiy Evgeniy Muravitskiy
                Tester:
                Vlastislav Ramik
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: