Keycloak/Liquibase: ERROR: column "salt" is of type oid but expression is of type bytea

Question

I'm using https://github.com/thomasdarimont/spring-boot-keycloak-server-example to run a keycloak instance. I tried to switch from h2 to PostgreSQL, the admin account creation screen appears, but creating the initial admin account fails with:

Hibernate:
insert
into
    CREDENTIAL
    (ALGORITHM, COUNTER, CREATED_DATE, DEVICE, DIGITS, HASH_ITERATIONS, PERIOD, SALT, TYPE, USER_ID, VALUE, ID)
values
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [pbkdf2-sha256]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [INTEGER] - [0]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [1504025461373]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [null]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [INTEGER] - [0]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [INTEGER] - [27500]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [7] as [INTEGER] - [0]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [8] as [VARBINARY] - [[B@708e0a84]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [9] as [VARCHAR] - [password]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [10] as [VARCHAR] - [32e0eb33-091b-4791-a923-4cc9fc976371]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [11] as [VARCHAR] - [CBJ4e+h56g1I0uxyexae7p5xJ2xLILGh8Hkx4t/jGSZ74XHbqDmGLW2vfPyIUl17puB+hihu3OpwNJSjT+LRgw==]
2017-08-29 18:51:01.482 TRACE 7020 --- [io-20909-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [12] as [VARCHAR] - [738017b1-ff7b-47cf-a2e9-7c9f6055f0aa]
2017-08-29 18:51:01.498  WARN 7020 --- [io-20909-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42804
2017-08-29 18:51:01.498 ERROR 7020 --- [io-20909-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column "salt" is of type oid but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 168

I've adapted the connectionsJpa settings in keycloak-server.json as follows:

"connectionsJpa": {
    "provider": "default",
    "default": {
        "url": "${env.KEYCLOAK_DATABASE_URL:jdbc:postgresql://server/testdb}",
        "driver": "${keycloak.connectionsJpa.driver:org.postgresql.Driver}",
        "driverDialect": "${keycloak.connectionsJpa.driverDialect:org.hibernate.dialect.PostgreSQLDialect}",
        "user": "${keycloak.connectionsJpa.user:user}",
        "password": "${keycloak.connectionsJpa.password:password}",
        "initializeEmpty": true,
        "migrationStrategy": "update",
        "showSql": "${keycloak.connectionsJpa.showSql:true}",
        "formatSql": "${keycloak.connectionsJpa.formatSql:true}",
        "globalStatsInterval": "${keycloak.connectionsJpa.globalStatsInterval:-1}"
    }
},

It seems that, since Liquibase 3.5.2, the blob type generates the PostgreSQL type oid, not bytea anymore. See CORE-1863

Anybody with a solution?

Answer 1

The problem seems to be that Liquibase 3.5.2 or later will cause the public.credential.salt column to be created with type oid whereas Keycloak expects it to be bytea.

The solution is to revert back to an earlier version of Liquibase (I recommend 3.4.1 as that's the one Keycloak ordinarily is using). You can do this by adding the liquibase version to the version properties in your project's main pom.xml

<properties>
    ......
    <liquibase.version>3.4.1</liquibase.version>
</properties>

After this don't forget to delete your old Keycloak database so Keycloak can create it with the proper salt type when you restart.