Keycloak 18 + PostgreSQL database

Hi everyone,

I was using a docker image for my local dev setting with keycloak 10. This is the docker-compose.yml that I was using:

version: '3'

volumes:
  postgres_data:
      driver: local

services:
  postgres:
    image: postgres
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: keycloak
      POSTGRES_USER: keycloak
      POSTGRES_PASSWORD: password
    ports:
      - "5434:5432"
    container_name: postgresql_keycloak
  keycloak:
    image: quay.io/keycloak/keycloak:10.0.1
    environment:
      DB_VENDOR: POSTGRES
      DB_ADDR: postgres
      DB_DATABASE: keycloak
      DB_USER: keycloak
      DB_SCHEMA: public
      DB_PASSWORD: password
      KEYCLOAK_USER: admin
      KEYCLOAK_PASSWORD: Pa55w0rd
    ports:
      - 9090:8080
    depends_on:
      - postgres
    container_name: keycloak_server

Last week I started a new project that uses keycloak as well and this time I sent with the latest version (18). I tried to run Keyclaok with docker using the following files:

  • Dockerfile from the official documentation
ROM quay.io/keycloak/keycloak:18.0.0 as builder

ENV KC_HEALTH_ENABLED=true
ENV KC_METRICS_ENABLED=true
ENV KC_FEATURES=token-exchange
ENV KC_DB=postgres

# Install custom providers
RUN curl -sL https://github.com/aerogear/keycloak-metrics-spi/releases/download/2.5.3/keycloak-metrics-spi-2.5.3.jar -o /opt/keycloak/providers/keycloak-metrics-spi-2.5.3.jar
RUN /opt/keycloak/bin/kc.sh build

FROM quay.io/keycloak/keycloak:18.0.0
COPY --from=builder /opt/keycloak/ /opt/keycloak/
WORKDIR /opt/keycloak

# change these values to point to a running postgres instance
ENV KC_DB_URL=jdbc:postgresql://db:5434/keycloak?ssl=allow
ENV KC_DB_USERNAME=keycloak
ENV KC_DB_PASSWORD=keycloak
ENV KC_HOSTNAME=localhost
ENTRYPOINT ["/opt/keycloak/bin/kc.sh", "start"]
  • docker-compose.yml
version: '3.8'

services:
  db:
    image: postgres:14.1-alpine
    restart: always
    environment:
      - POSTGRES_USER=keycloak
      - POSTGRES_PASSWORD=keycloak
    ports:
      - '5434:5432'
    volumes:
      - db:/var/lib/postgresql/data
  keycloak:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - db
    ports:
      - 9090:8080
    container_name: keycloak_server_18
volumes:
  db:
    driver: local

When I run docker-compose up command, docker is able to pull PostgreSQL images and starts the container but starting Keycloak is not able to connect to PostgreSQL instance and fails:

keycloak_server_18  | 2022-04-30 01:52:06,596 WARN  [io.agroal.pool] (agroal-11) Datasource '<default>': Connection to db:5434 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
keycloak_server_18  | 2022-04-30 01:52:06,597 WARN  [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator] (JPA Startup Thread: keycloak-default) HHH000342: Could not obtain connection to query metadata: org.postgresql.util.PSQLException: Connection to db:5434 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
keycloak_server_18  | 	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:319)
keycloak_server_18  | 	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
keycloak_server_18  | 	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
keycloak_server_18  | 	at org.postgresql.Driver.makeConnection(Driver.java:400)
keycloak_server_18  | 	at org.postgresql.Driver.connect(Driver.java:259)
keycloak_server_18  | 	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
keycloak_server_18  | 	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
keycloak_server_18  | 	at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:103)
keycloak_server_18  | 	at org.postgresql.xa.PGXADataSource.getXAConnection(PGXADataSource.java:49)
keycloak_server_18  | 	at org.postgresql.xa.PGXADataSource.getXAConnection(PGXADataSource.java:35)
keycloak_server_18  | 	at io.agroal.pool.ConnectionFactory.createConnection(ConnectionFactory.java:216)
keycloak_server_18  | 	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:513)
keycloak_server_18  | 	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:494)
keycloak_server_18  | 	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
keycloak_server_18  | 	at io.agroal.pool.util.PriorityScheduledExecutor.beforeExecute(PriorityScheduledExecutor.java:75)
keycloak_server_18  | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1126)
keycloak_server_18  | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
keycloak_server_18  | 	at java.base/java.lang.Thread.run(Thread.java:829)
keycloak_server_18  | Caused by: java.net.ConnectException: Connection refused (Connection refused)
keycloak_server_18  | 	at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
keycloak_server_18  | 	at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:412)
keycloak_server_18  | 	at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:255)
keycloak_server_18  | 	at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:237)
keycloak_server_18  | 	at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
keycloak_server_18  | 	at java.base/java.net.Socket.connect(Socket.java:609)
keycloak_server_18  | 	at org.postgresql.core.PGStream.createSocket(PGStream.java:241)
keycloak_server_18  | 	at org.postgresql.core.PGStream.<init>(PGStream.java:98)
keycloak_server_18  | 	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:109)
keycloak_server_18  | 	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235)
keycloak_server_18  | 	... 17 more

This is the output of docker ps command, we can see that PostgreSQL is up and running on port 5434:

CONTAINER ID   IMAGE                  COMMAND                  CREATED         STATUS         PORTS                                       NAMES
3a36a9fe496b   postgres:14.1-alpine   "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes   0.0.0.0:5434->5432/tcp, :::5434->5432/tcp   18-db-1

What am I missing here?

Thank you so much for your help.

Bests,

I guess your DB is exposed on db:5432 in this case.

I’m kind of confused because I’m mapping the 5432 port to 5434
se.yml that I was using:

version: '3'

volumes:
  postgres_data:
      driver: local

services:
  postgres:
    image: postgres
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: keycloak
      POSTGRES_USER: keycloak
      POSTGRES_PASSWORD: password
    ports:
      - "5434:5432"
    container_name: postgresql_keycloak
  keycloak:
    image: quay.io/keycloak/keycloak:10.0.1
    environment:
      DB_VENDOR: POSTGRES
      DB_ADDR: postgres
      DB_DATABASE: keycloak
      DB_USER: keycloak
      DB_SCHEMA: public
      DB_PASSWORD: password
      KEYCLOAK_USER: admin
      KEYCLOAK_PASSWORD: Pa55w0rd
    ports:
      - 9090:8080
    depends_on:
      - postgres
    container_name: keycloak_serve

Why is the keycloak is trying to connect to 5432 port instead of 5434?

Thank you

Postgres is exposed on the port 5434 only on your host (machine where is docker running). But keycloak container is not connecting to the host, but to the the db service, where postgresql is exposed on the standard port 5432.

It’s not a Keycloak problem. You need to understand how container networking is working. Or you don’t need that, but then don’t use port remapping.

2 Likes

Got it thank you @jangaraj

Thanks for this great explanation.
I had a similar situation, but in my case I also had multiple postgres instances running under the same service name (postgres), inside one shared docker network.
Renaming my postgres service and corresponding links from keycloak in docker-compose.yml solved the issue.