Where are the Postgres Tables?

Configuring the server
1

Hi,

I am trying to understand when and which tables Keycloak 19.0 creates in Postgres.

While Keycloak at http://localhost:8080 runs without any issues (change password, create clients etc), When I get into Postgres to check in keycloak DB, there are no tables!

$ psql -h localhost -p 5432 -d keycloak -U postgres 
Password for user postgres: 
psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1))
Type "help" for help.

keycloak=# \dt
Did not find any relations.
keycloak=#

While in Keycloak container I can see the configuration happened correctly:

$ docker exec -it kc1 /bin/bash
bash-4.4$ ./bin/kc.sh show-config
Current Mode: development
Current Configuration:
        kc.cache =  local (PersistedConfigSource)
        kc.config.built =  true (SysPropConfigSource)
        kc.db =  dev-file (PersistedConfigSource)
        kc.db-password =  ******* (KcEnvVarConfigSource)
        kc.db-url-database =  keycloak (KcEnvVarConfigSource)
        kc.db-url-host =  db (KcEnvVarConfigSource)
        kc.db-url-port =  5432 (KcEnvVarConfigSource)
        kc.db-username =  postgres (KcEnvVarConfigSource)
        kc.db.password =  Abcd1234 (KcEnvVarConfigSource)
        kc.db.url.database =  keycloak (KcEnvVarConfigSource)
        kc.db.url.host =  db (KcEnvVarConfigSource)
        kc.db.url.port =  5432 (KcEnvVarConfigSource)
        kc.db.username =  postgres (KcEnvVarConfigSource)
        kc.health-enabled =  false (PersistedConfigSource)
        kc.hostname =  localhost (KcEnvVarConfigSource)
        kc.hostname-port =  8080 (KcEnvVarConfigSource)
        kc.hostname-strict =  false (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.hostname-strict-https =  false (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.hostname.port =  8080 (KcEnvVarConfigSource)
        kc.http-enabled =  true (KcEnvVarConfigSource)
        kc.http-port =  8080 (KcEnvVarConfigSource)
        kc.http-relative-path =  / (PersistedConfigSource)
        kc.http.enabled =  true (KcEnvVarConfigSource)
        kc.http.port =  8080 (KcEnvVarConfigSource)
        kc.log-console-output =  default (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.log-file =  ${kc.home.dir:default}${file.separator}data${file.separator}log${file.separator}keycloak.log (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.metrics-enabled =  false (PersistedConfigSource)
        kc.spi-map-storage-concurrenthashmap-dir =  ${kc.home.dir:default}${file.separator}data${file.separator}chm (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-map-storage-concurrenthashmap-key-type-authz-resource-servers =  string (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-map-storage-concurrenthashmap-key-type-realms =  string (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-map-storage-concurrenthashmap-key-type-single-use-objects =  string (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-theme-cache-templates =  false (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-theme-cache-themes =  false (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.spi-theme-static-max-age =  -1 (PropertiesConfigSource[source=jar:file:///opt/keycloak/lib/lib/main/org.keycloak.keycloak-quarkus-server-19.0.3.jar!/META-INF/keycloak.conf])
        kc.version =  19.0.3 (SysPropConfigSource)

Can you please help me understand what I might be doing/expecting wrong?

Best,
Bikram

I’m using this setup for experimentation.

Dockerfile

# Stage 1
FROM quay.io/keycloak/keycloak:19.0.3 as builder

ENV KC_DB=postgres

RUN /opt/keycloak/bin/kc.sh build

# Stage 2
FROM quay.io/keycloak/keycloak:19.0.3
COPY --from=builder /opt/keycloak/ /opt/keycloak/
WORKDIR /opt/keycloak

ENV KC_HOSTNAME=localhost       \
    KC_HOSTNAME_PORT=8080       \
    KC_HTTP_ENABLED=true        \
    KC_HTTP_PORT=8080           \
    KEYCLOAK_ADMIN=admin        \
    KEYCLOAK_ADMIN_PASSWORD=admin

EXPOSE 8080

ENTRYPOINT ["/opt/keycloak/bin/kc.sh"]
# CMD ["start", "--optimized"]
CMD ["start-dev"]

docker-compose.yaml

version: '3.3'

volumes:
  postgres_data:
    driver: local

services:
  db:
    container_name: db
    image: postgres:latest
    restart: always
    environment:
      POSTGRES_DB: keycloak
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: Abcd1234
    ports:
      - 5432:5432

  kc1:
    container_name: kc1
    build: .
    restart: always
    depends_on:
      - db
    environment:
      KC_DB_URL_HOST: db
      KC_DB_URL_PORT: 5432
      KC_DB_URL_DATABASE: keycloak
      KC_DB_USERNAME: postgres
      KC_DB_PASSWORD: Abcd1234
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: admin
    ports:
      - 8080:8080
2

Today I found myself in the same position, spent 3h20 finding the solution. The default schema the KC docker image uses is keycloak, so you either have to change the PostgreSQL user’s search_path to match that, or use SET search_path TO keycloak; on the psql client each time (so, do the former).