Keycloak with PostgreSQL Database
Keycloak comes with its own embedded H2 database to persist data by default. H2 is a Java SQL relational database. Keycloak uses the H2 database so that it can run instantly. The H2 is an in-memory database and it is suitable only for use in a test environment. In a production environment, it is recommended that a more mature relational database must be used.
To configure latest Keycloak (Quarkus) version with PostgreSQL, see here
In this tutorial, you will learn how to configure Keycloak to use with PostgreSQL database instead of its default H2 database.
What You Need
- About 15 minutes
- JDK 1.8 or later
- Keycloak 16.1.1
- PostgreSQL
Complete the steps below to set up Keycloak with PostgreSQL:
Create New PostgreSQL Database with a New User for Keycloak
Let's starts by creating a new PostgreSQL database for Keycloak:
- Sign-in to your PostgreSQL Server using your root PostgreSQL credentials.
- Next, run the following PostgreSQL commands one by one. The first command creates a new database keycloak, the second command creates a new user keycloak, and the third command grants all privileges to user keycloak:
CREATE DATABASE keycloak;
CREATE USER 'keycloak'@'localhost' IDENTIFIED BY 'Keycloak123$';
GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'localhost';
Add PostgreSQL JDBC driver as a Keycloak module
Download the PostgreSQL JDBC driver. The current JDBC version at the moment of writing this tutorial is 42.2.19.
I assume that you have already downloaded and extracted the Keycloak Server (Distribution powered by WildFly) bundle.
Next, go to the Keycloak folder and navigate to location keycloak > modules > system > layers > keycloak > org. Inside the org folder create a new folder postgresql and inside postgresql folder create another folder main.
Copy the downloaded PostgreSQL driver jar file into the newly created main folder. Also create a new module.xml file in the same main folder and add the configuration as follows:
module.xml
Replace the resource-root path tag value with your JDBC driver JAR filename. The module name to be used in the above file should match with the directory structure of this module. The directory structure "/org/postgresql" maps to "org.postgresql".
Declare JDBC Driver in the Deployment Profile
Next, we need to declare the JDBC Driver in the deployment profile so that the JDBC Driver becomes available when the Keycloak server boots up.
If you are planning to deploy Keycloak in the standard mode, then edit /standalone/configuration/standalone.xml file.
If you want to deploy Keycloak in the domain mode, then edit /domain/configuration/domain.xml file. In domain mode, make sure you edit within the profile that you are about to use which is either auth-server-standalone or auth-server-clustered.
This example is based on standard mode, so I will be editing /standalone/configuration/standalone.xml
In the standalone.xml file, look for drivers XML block. There must be a predefined JDBC driver declared for H2 database as shown below:
standalone.xml
Now, add JDBC driver for PostgreSQL within the drivers XML block. Here is how the updated drivers element will look like after adding PostgreSQL driver to it:
standalone.xml
Add New Datasource in Deployment Profile
Now, within the same standalone.xml file, search for <datasources> element. There you will see the currently used Datasource for H2 database. Comment out the KeycloakDS Datasource and create a new Datasource for PostgreSQL database.
Note: Do not comment out ExampleDS Datasource. Also do not forget to update the JDBC connection-url, the username and password elements to make them relevant with your project.
standalone.xml
Start the Keycloak Server
Go to the bin folder of the Keycloak Server distribution and run the standalone boot script to start the Keycloak Server:
Windows
....\bin\standalone.bat
Linux/Ubuntu/Unix
$ cd bin
$ ./standalone.sh
The configuration for Keycloak to use PostgreSQL database is complete. Now, the Keycloak will use PostgreSQL database instead of it's default H2 database when it is run.
Some List of Errors and their Solutions
Caused by: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag()
Solution: Do not copy the configuration from the Keycloak document page as it may contain invisible white space characters. Duplicate existing configuration and modify it for the MySQL.
ERROR [org.jboss.msc.service.fail] (MSC service thread 1-3) MSC000001: Failed to start service org.wildfly.clustering.infinispan. cache-container-configuration.keycloak: org.jboss.msc.service.StartException in service org.wildfly.clustering.infinispan.cache-container-configuration .keycloak: java.lang.OutOfMemoryError: Metaspace
Solution: Edit the following entry in /bin/standalone.conf file (ubuntu or linux) or /bin/standalone.conf.bat file (Windows). change MaxMetaspaceSize value to 1GB.Change From:
To:
ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0348: Timeout after [300] seconds waiting for service container stability. Operation will roll back. Step that first updated the service container was 'add' at address '[ ("core-service" => "management"), ("management-interface" => "http-interface")
Solution: Increase timeout by adding the following line at the bottom of the file /bin/standalone.conf file (in ubuntu or linux) or /bin/standalone.conf.bat file (Windows).
ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 57) MSC000001: Failed to start service jboss.deployment.unit. "keycloak-server.war".undertow-deployment: org.jboss.msc.service.StartException in service jboss.deployment.unit."keycloak-server.war".undertow-deployment: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication()
Solution: Add default-timeout="5000" to coordinator-environment XML block in the /standalone/configuration/standalone.xml file for standard mode. If you are using in different mode then edit in the mode's respective file.
Before:
After update:
