7

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?

1
  • I have tried setting currentSchema=keycloak as a JDBC parameter on the connection url and it still uses public. I am not sure if this is possible. According to what I have read currentSchema will only affect unqualified sql statements. So if public.table name is referenced it will use that any way. Commented Feb 28, 2019 at 1:19

3 Answers 3

6

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;
Sign up to request clarification or add additional context in comments.

1 Comment

This should be the accepted answer. This works for both the legacy WildFly release as well as the Quarkus release.
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

Comments

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.

2 Comments

Hi Tom, the database is not a problem but i was wondering if it possible to set a schema within that database. Currently the tables are correctly created but in the public schema of a database.
If the solution of using a customized standalone.xml works for you, you can consider adding it in the docker image directly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.