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