Keycloak won't start for failling to create lock table

Configuring the server
1

Hello, it’s the first time I try to configure a Keycloak instance, i use PostgreSQL as the database provider that I run locally on the same machine. It run on a LXC container in a Proxmox VE.
So the problem I face is that the server fail to start because he fail to create lock table. I think it’s about PostgreSQL, but as I am new in Keycloak and PostgreSQL (I’ve never used PostgreSQL but I actually use MySQL) i don’t really understand what the issue is caused by.
So here my own documentation that I written during the installation, so it contain all the step I’ve done : Keycloak Deployment on EL9.md · GitHub
And here the output when I start the keycloak server :

2023-01-11 14:32:29,687 INFO  [org.keycloak.quarkus.runtime.hostname.DefaultHostnameProvider] (main) Hostname settings: Base URL: <unset>, Hostname: myconfigureddomain.com, Strict HTTPS: true, Path: <request>, Strict BackChannel: false, Admin URL: <unset>, Admin: <request>, Port: -1, Proxied: true
2023-01-11 14:32:33,507 WARN  [io.quarkus.agroal.runtime.DataSources] (main) Datasource <default> enables XA but transaction recovery is not enabled. Please enable transaction recovery by setting quarkus.transaction-manager.enable-recovery=true, otherwise data may be lost if the application is terminated abruptly
2023-01-11 14:32:35,853 WARN  [org.infinispan.PERSISTENCE] (keycloak-cache-init) ISPN000554: jboss-marshalling is deprecated and planned for removal
2023-01-11 14:32:35,972 WARN  [org.infinispan.CONFIG] (keycloak-cache-init) ISPN000569: Unable to persist Infinispan internal caches as no global state enabled
2023-01-11 14:32:36,033 INFO  [org.infinispan.CONTAINER] (keycloak-cache-init) ISPN000556: Starting user marshaller 'org.infinispan.jboss.marshalling.core.JBossUserMarshaller'
2023-01-11 14:32:36,720 INFO  [org.infinispan.CONTAINER] (keycloak-cache-init) ISPN000128: Infinispan version: Infinispan 'Triskaidekaphobia' 13.0.10.Final
2023-01-11 14:32:36,999 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000078: Starting JGroups channel `ISPN`
2023-01-11 14:32:36,999 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000088: Unable to use any JGroups configuration mechanisms provided in properties {}. Using default JGroups configuration!
2023-01-11 14:32:37,213 WARN  [org.jgroups.protocols.UDP] (keycloak-cache-init) JGRP000015: the send buffer of socket MulticastSocket was set to 1.00MB, but the OS only allocated 212.99KB
2023-01-11 14:32:37,213 WARN  [org.jgroups.protocols.UDP] (keycloak-cache-init) JGRP000015: the receive buffer of socket MulticastSocket was set to 20.00MB, but the OS only allocated 212.99KB
2023-01-11 14:32:37,215 WARN  [org.jgroups.protocols.UDP] (keycloak-cache-init) JGRP000015: the send buffer of socket MulticastSocket was set to 1.00MB, but the OS only allocated 212.99KB
2023-01-11 14:32:37,216 WARN  [org.jgroups.protocols.UDP] (keycloak-cache-init) JGRP000015: the receive buffer of socket MulticastSocket was set to 25.00MB, but the OS only allocated 212.99KB
2023-01-11 14:32:39,270 INFO  [org.jgroups.protocols.pbcast.GMS] (keycloak-cache-init) sso-48185: no members discovered after 2004 ms: creating cluster as coordinator
2023-01-11 14:32:39,295 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000094: Received new cluster view for channel ISPN: [sso-48185|0] (1) [sso-48185]
2023-01-11 14:32:39,323 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000079: Channel `ISPN` local address is `sso-48185`, physical addresses are `[10.27.0.40:49021]`
2023-01-11 14:32:40,556 INFO  [org.keycloak.connections.infinispan.DefaultInfinispanConnectionProviderFactory] (main) Node name: sso-48185, Site name: null
2023-01-11 14:32:40,559 INFO  [org.keycloak.broker.provider.AbstractIdentityProviderMapper] (main) Registering class org.keycloak.broker.provider.mappersync.ConfigSyncEventListener
2023-01-11 14:32:45,019 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,047 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,089 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,154 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,217 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,305 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:45,616 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:46,426 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:48,129 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:49,012 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction created in the meantime. Retrying...
2023-01-11 14:32:49,058 INFO  [org.infinispan.CLUSTER] (main) ISPN000080: Disconnecting JGroups channel `ISPN`
2023-01-11 14:32:49,167 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2023-01-11 14:32:49,167 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.DatabaseException: ERROR: permission denied for schema public
  Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))]
2023-01-11 14:32:49,167 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: ERROR: permission denied for schema public
  Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))]
2023-01-11 14:32:49,167 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: ERROR: permission denied for schema public
  Position: 14
2023-01-11 14:32:49,167 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the details about the usage of the particular command.

Thanks in advance for anyone that can help me!

2

This is because keycloak is looking for the default public schema and use it for all its table creation and liquibase-related operations.

3

Hello, thanks for the answer, but how can I fix this? I understand that I need to create a public schema in my PostgreSQL but as far as I remember I already try to create a schema named public in the database. Maybe it’s a permission problem. Do you have a documentation to setup correctly PostgreSQL to work with Keyckoak?

4

I have the same problem and haven’t figured it out yet.

Keycloak does not necessarily use the public schema.
I used a schema called “test”, and have this error:

ERROR: liquibase.exception.DatabaseException: Schema "TEST" not found; SQL statement:

… even though I also have a public schema. It may be that Keycloak defaults to the public schema when you don’t specify a schema: Postgres creates a public schema by default when you create a new database.

We have used different schemas in the past for Keycloak on PostgreSQL and that was never an issue.

FWIW, I have this problem since I’ve been trying to deploy Keycloak to Kubernetes AND when I am not using the start-dev entry point in the Dockerfile.

This works:
start-dev --hostname=localhost

But this doesn’t - and I want to have that working as well because I can’t use the above in production:
ENTRYPOINT ["/opt/keycloak/bin/kc.sh", "start"]

5

I don’t think so, because the user that I specified for Keycloak is the one that owns the database.
I’ll report here if I get it working.

6

I am working with docker and its working fine for me. here is the observations. if you specify schema name then it will looking for it else by default it will look for public schema.

Here is the docker command that works for me.

docker run -p 8080:8080 --name keycloak --net keycloak-network -e KEYCLOAK_ADMIN=admin -e KEYCLOAK_ADMIN_PASSWORD=admin123 -e KC_DB=postgres -e KC_DB_URL=jdbc:postgresql://localhost:50004/postgres -e KC_DB_USERNAME=postgres -e KC_DB_PASSWORD=password123 -e KC_DB_SCHEMA=public quay.io/keycloak/keycloak:latest start-dev

7

Thanks everyone for the help, I sucessfuly configured my Keycloak instance without Docker and with MySQL, i’ve done a simple documentation on how to do that here : Keycloak Deployment on EL9.md · GitHub

8

I have very similar error. Starting keycloak v26.0.2 in docker with poastgre db working on another machine in docker too. Postgresql is v17.
I verified and 100% sure that user has the access to the DB. And I manually executed the script that fails on keycloak start and it works well.
Is it somthing with race conditions in keycloak code that creating the DB?

2024-10-28 15:48:20,418 WARN  [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (main) Failed to create lock table. Maybe other transaction create
d in the meantime. Retrying...: liquibase.exception.DatabaseException: ERROR: permission denied for schema public
  Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKED
BY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))]

Checked with previous versions. It works wit PostgreSQL v14. But starting from Prostgre V15 - it fails with this error.

9

Ok, I have it work.

  • I started the keycloak v26.0.2 with postgresql v14. It creates the DB schema. I verified that admin user works and allow to login. Created the second admin record and stopped the keycloak.
  • I used pg_dumpall to dump the DB from postgre v14.
    commands to use:
    go into container with shell: docker exec -it postgres14 bash
    make dump inside container: pg_dumpall -U > /backup.sql
    copy dump from container to host: docker cp postgres14:/backup.sql ./backup.sql
  • i stopped v14 and run the postgre v15 in new container
  • restore the dump tom v14 into v15
    commands to use:
    copy dump into new container: docker cp backup.sql postgres15:/backup.sql
    sudo docker exec -it postgres15 bash
    run dump in v15 container: psql -U postgres -f /backup.sql

Run the keycloak image, check that everithing works fine.
And repeat the same to update from postgre v15 to v16.

p.s. I stopped on postgre v16 as it is last tested compatible version for keycloak v26. At least for Today.

p.p.s. My experiment shows that issue is in keycloak DB initialization scripts. That scripts incompatible with the latest version of postgresql. It works only with Postgre v14