How to set PostgreSQL schema for Keycloak when using the Docker Image?

Question

i am trying to use the Docker Image for Keycloak but I seem to be unable to set a schema for the tables that are created in PostgreSQL.

Currently all tables end up in the public schema. Is there a way that i can instruct Keycloak to create the tables inside a schema?

Answer 1

You should be able to specify the POSTGRES_DATABASE env variable for the data source:

docker ... -e POSTGRES_DATABASE=<your_database_name> ...

By default, it should be using the database name 'keycloak', so it's weird you don't see that:

/subsystem=datasources/data-source=KeycloakDS: add(jndi-name=java:jboss/datasources/KeycloakDS,enabled=true,use-java-context=true,use-ccm=true, connection-url=jdbc:postgresql://${env.POSTGRES_ADDR:postgres}:${env.POSTGRES_PORT:5432}/${env.POSTGRES_DATABASE:keycloak}, driver-name=postgresql)

source code: https://github.com/jboss-dockerfiles/keycloak/blob/cd866b905d026eb69dab5176b352064252d92aff/server/cli/databases/postgres/change-database.cli#L2

Update

Sorry, I see what you mean. In that case, I think you'll have to manually create the schema in the database, then update the standalone.xml to use your schema:

<spi name="connectionsJpa">
  <provider name="default" enabled="true">
    <properties>
      <property name="dataSource" value="java:jboss/datasources/KeycloakDS"/>
      <property name="initializeEmpty" value="true"/>
      <property name="migrationStrategy" value="update"/>
      <property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
      <property name="schema" value="your_schema"/>
    </properties>
  </provider>
</spi>

It looks like they don't support automatic schema creation, so you'll probably have to submit a feature request.

Answer 2

TL;DR

Use DB_SCHEMA env variable taking care of creating the schema before running keycloak.

More details

The docker image of keycloak supports the DB_SCHEMA environment variable. However the schema must created before you run keycloak.

Here's an example of a docker compose that would first create the schema in the postgres container and then run keycloak.

version: "3.8"
services:
  db:
    image: postgres:12
    restart: always 
    ports:
      - 5432:5432
    volumes:
      - /c/db:/var/lib/postgresql/data
      # This will bind the files inside the pgscripts to docker-entrypoint-initdb.d
      # The scripts will be run on startup
      - $PWD/postgres:/docker-entrypoint-initdb.d
    environment:
      # This is required otherwise the container will fail to start
      POSTGRES_PASSWORD: password
  keycloak:
    image: jboss/keycloak
    ports:
      - 8080:8080
    depends_on:
      - db
    environment:
      KEYCLOAK_USER: admin
      KEYCLOAK_PASSWORD: password
      DB_VENDOR: postgres
      DB_ADDR: db
      DB_PORT: 5432
      DB_SCHEMA: keycloak_schema
      DB_DATABASE: postgres
      DB_USER: keycloak_user
      DB_PASSWORD: keycloak_password

Notice that I'm using volume binding to bind the files under the host postgres directory to the docker-entrypoint-initdb.d (more info here: How to create User/Database in script for Docker Postgres)

Here's the init.sql script

CREATE USER keycloak_user WITH PASSWORD 'keycloak_password';
CREATE SCHEMA IF NOT EXISTS keycloak_schema AUTHORIZATION keycloak_user;

Answer 3

Find all possible config parameters at
https://www.keycloak.org/server/all-config

Direct link to the ones for the database connection
https://www.keycloak.org/server/all-config#_database

For example the current schema param is

• CLI: --db-schema
• Env: KC_DB_SCHEMA