Keycloak and postgres docker compose

Question

I built this compose stack to run keycloak and associate a postgres DB:

volumes:
  postgres_data:
    driver: local
services:
  postgres:
    image: postgres:14.1-alpine
    volumes:
      - path
    environment:
      POSTGRES_DB: test1
      POSTGRES_USER: ex
      POSTGRES_PASSWORD: ex
    ports:
      - '5433:5432'

  keycloak:
    image: quay.io/keycloak/keycloak
    volumes:
      - path
    environment:
      DB_VENDOR: POSTGRES
      DB_ADDR: postgres
      DB_DATABASE: test1
      DB_USER: ex
      DB_PASSWORD: ex
      DB_SCHEMA: public
      KEYCLOAK_USER: 'something'
      KEYCLOAK_PASSWORD: 'something'
    command:
      - start-dev
      - --import-realm
    ports:
      - "8080:8080"
    depends_on:
      - postgres

To run this stack:

docker compose up

note: the values used in here are examples

I was trying to see the tables in the DB and I didn't find any. The docker is running without any error logs... So I'm a bit lost...

Answer 1

This demo will see Keycloak's database table.

I am using Keycloak version 23.0.7 and postgres version 15.6

And git bash termina in Windows

In docker-compose.yml

version: '3.8'

services:
  postgres:
    image: postgres:15.6
    container_name: postgres_db
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: keycloak
      POSTGRES_USER: keycloak
      POSTGRES_PASSWORD: password

  keycloak_web:
    image: quay.io/keycloak/keycloak:23.0.7
    container_name: keycloak_web
    environment:
      KC_DB: postgres
      KC_DB_URL: jdbc:postgresql://postgres:5432/keycloak
      KC_DB_USERNAME: keycloak
      KC_DB_PASSWORD: password

      KC_HOSTNAME: localhost
      KC_HOSTNAME_STRICT: false
      KC_HOSTNAME_STRICT_HTTPS: false

      KC_LOG_LEVEL: info
      KC_METRICS_ENABLED: true
      KC_HEALTH_ENABLED: true
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: admin
    command: start-dev
    depends_on:
      - postgres
    ports:
      - 8180:8080

volumes:
  postgres_data:

Launching it

docker compose up

Step #1 Check containers

docker ps -a

CONTAINER ID   IMAGE                              COMMAND                  CREATED         STATUS         PORTS                              NAMES
ff2a5f7e4386   quay.io/keycloak/keycloak:23.0.7   "/opt/keycloak/bin/k…"   4 minutes ago   Up 4 minutes   8443/tcp, 0.0.0.0:8180->8080/tcp   keycloak_web
bcb8cf4e2f2e   postgres:15.6                      "docker-entrypoint.s…"   4 minutes ago   Up 4 minutes   5432/tcp                           postgres_db

Open browser

http://localhost:8180/admin/master/console/

Step # 2, go inside postgres container

winpty docker exec -it postgres_db bin/bash

(* note: I am using Windows git bash, winpty is not need in Linux)

Step #3, check Linux version

cat /etc/os-release

Output

PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Who Am I

whoami

Output

root

Step #4, add user 'keycloakand addsudo` user

adduser keycloak

(note password is password)

Output

Step #5, switch user keycloak and connect database

su keycloak

psql -U keycloak -w

Output

psql (15.6 (Debian 15.6-1.pgdg120+2))
Type "help" for help.

Step #6, get connected information

 \conninfo

Output

Step #7, list tables and schemas

\dt

Output

Step #8, clients list and detail information

SELECT name, client_id  FROM client;

Output

It should be matched UI's clients

User data for specific realm

You need to check realm ID

SELECT name, id FROM realm;

Output

Then filter by realm_id for my-realm's user list

SELECT username, email FROM  user_entity WHERE realm_id='c4e827a1-823a-4304-8905-5daa89563cbe';

Output

It will return UI's user list for my-realm