Setting up Keycloak 10 with MySQL 8 on your Windows machine

Keycloak is a very good Identity and Access Management solution from Red Hat. It is open-source and works straight out of the box on most operating systems, including Windows. Keycloak allows you to secure your applications and services with the best in class security measures like OpenID, OAuth2, et-al. Please refer to the official site for more details.

I have started using it as the standard for all application security and user management needs. I will share my experiences of using Keycloak to secure Spring Boot traditional MVC apps as well as microservices in a future series of posts.

Purpose of this blog post

There are many good resources on setting up Keycloak on your local machine and in the cloud. The official documentation is a good place to start.

This blog post is NOT about setting up Keycloak to serve as a full fledged Identity and Access Management solution but about setting up an external database for Keycloak to store its data and the realms, users, roles, credentials, etc created by you.

Keycloak comes with a built-in in-memory H2 database out of the box. This is good for getting started with Keycloak on your development machine, but not recommended for using in a production environment. This blog post is about using a database like MySQL (version 8) as the Keycloak database instead of the default in-memory H2 database. Once you understand how it is done, then you can do the same on your production server.

One small note - I am sharing my experiences and success on Windows and so this blog post will show how it is done on Windows. I am sure the same steps can be replicated after adjusting for other operating systems to achieve the same.

Problems when setting up on Windows 10

I use Windows as my development machine and I came across many errors and failed to set up KeyCloak with MySQL on my Windows machine at first go, despite following the official documentation and some blog posts. This is my attempt to record this here so that it might be useful to some of you who face problems setting up on Windows.

Here is a small subset of the issues I faced -

• Issue 1 - Failure to initialize database with MySQL ...
• Issue 2 - Character set utf8mb4 is not supported for MySQL/MariaDB ...
• Issue 3 - Mysql::Error: Specified key was too long; max key length is 1000 bytes
• Issue 4 - Challenge with setting up MySQL

Basically Keycloak was able to connect with a local Postgresql database but not with a MySQL database. So after lot of searching and trying things on my own, borrowing from my experience, I was able to make it all work. I noted down the steps I performed. Now I am sharing them so that you can save time if you face similar issues.

Versions used in this blog post

• Keycloak 10.0.2 - Download
  • Follow the official guide to setup the server. I have installed it to D:\keycloak on my Windows machine.
• MySQL Community Server 8.0.20 - Download
  • Follow the official guide to setup the database server. I have used WampServer to install MySQL versions 5 and 8 on my Windows machine. Details in the next section.
• MySQL Connector for Java (Connector/J) - Download
  • This is needed for Keycloak to talk to the MySQL database. I have downloaded the latest Platform Independent version mysql-connector-java-8.0.20.jar
  • Extract and place this jar file in a convenient location for adding it to the module as explained in later sections. I have placed it in D:\ on my Windows machine.

Useful tools

Though I prefer the command line for many tasks, sometimes a GUI tool helps getting things done quicker. I use the following tools. You don't have to use the same or use such tools at all.

• HeidiSQL - Database Management GUI tool - Download
  • This is a free open-source tool, actively maintained and released with new features and bug fixes, which I find very useful.
• WampServer - Download from an aletrnative site which has more related options and tools
  • This is a free open-source tool which installs the MySQL, Apache HTTP server and PHP stack - LAMP stack on your Windows machine. It also allows you to have multiple versions of MySQL on your machine as some of your projects might still be on legacy MySQL version 5.7.x

Assumptions

I am assuming that you have installed Keycloak and MySQL. You have configured them enough to be operational as intended. Please go through the official documentation of each to setup and configure just enough. I will show you how the Connector/J comes into the picture further down.

Basic Steps

Keycloak uses the JBoss Wildfly server under the hood. If you know how to use the JBoss/Wildfly CLI and add a datasource to Wildfly, then you might not need to follow all the steps shown here.

Here are the basic steps for configuring your MySQL database as the Keycloak database.

• Create a MySQL database, say, keycloakdb with a user, say, keycloakadmin in your MySQL and grant all privileges to this user
• Add a module to the Keycloak (Wildfly) server defining your MySQL database and define a datasource along with the database driver (Connector/J), database user credentials, etc.
• Restart your Keycloak server

I will detail every step next.

Using JBoss/Wildfly CLI

The official documentation shows the files in Keycloak that you need to edit to configure your database. I have found that manually editing the files can sometimes lead to a problem. I could not pin point the exact reason when I tried, but being a newbie with Wildfly, I realized that using the CLI is a much better way to achieve these steps. Using the CLI works; and you don't have to troubleshoot if your configuration did not work as intended.

Step 1: Create a database in MySQL

Using the MySQL shell (or a GUI tool) create a database in MySQL with the utf8 character set and the collation as utf8_unicode_ci. This is important as Keycloak requires UTF8 (for a few columns) and the official documentation states so here.

CREATE DATABASE keycloakdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Step 2: Create a database user for Keycloak

Now you need to create a user and grant privileges. Follow these commands if you are using the MySQL command line (and not a GUI tool). Note that I have used a simple password which is not secure. On production systems, the passwords need to be very secure with features like upper and lower case characters, special characters, numbers, etc. For purpose of this blog post, a simple password is good enough.

CREATE USER 'keycloakadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'keycloak123';

Why use mysql_native_password when creating a user?

From MySQL 8.0.x, caching_sha2_password is the default authentication plugin. Upto MySQL 5.x the default authentication plugin was mysql_native_password. Since a development environment, like mine, has two different versions of MySQL to support some legacy projects, I recommend using the older mysql_native_password authentication plugin. If you are just using the latest MySQL 8 for all your development, then you need not use the mysql_native_password keyword in the SQL command shown above.

Step 3: Grant all privileges

You can fine tune the privileges later by reading the Keycloak documentation in detail or talking to your DBA, but for setting up the Keycloak database user, just grant all privileges initially. As far as I know, I am sure that the database user requires all privileges.

GRANT ALL PRIVILEGES ON keycloakdb.* TO 'keycloakadmin'@'%';
--- Tell the server to reload the grant tables
--- by performing a flush privileges operation
FLUSH PRIVILEGES;

Step 4: Ensure that your databse engine is InnoDB

MySQL has several database storage engines with MyISAM and InnoDB being the most prominent. There are many reasons to use one over the other, but that discussion is beyond the scope of this blog post. After reading a lot about this topic, like the documentation for Keycloak and MySQL 8, I found that for the Keycloak database, MySQL 8 storage engine should be set to InnoDB, if it is not set already.

You can check the storage engine by following the MySQL documentation or by using MySQL commands like the following -

--- Should output: default_storage_engine InnoDB
SHOW GLOBAL variables like "default_storage%"

--- If storage engine is not InnoDB, use this to set it
SET GLOBAL default_storage_engine = 'InnoDB';

Step 5: Adding a module with the MySQL driver (Connector/J) to Keycloak

This is about telling Keycloak the database connector/driver you will be using to connect Keycloak to your MySQL database. As I said earlier, I have realized that using the JBoss/Wildfly CLI is the optimal way to configure Keycloak. So I will show you how to add a module using the CLI.

You need to run the Keycloak server so that the JBoss/Wildfly CLI tool can connect to the running server and further actions can be performed.

From the /bin directory of your keycloak installation, run the standalone.bat batch file.

Your command/console window should now show you the regular Keycloak messages and indicate that the server is running.

Start another command prompt.

From the /bin directory of your keycloak installation, run the jboss-cli.bat batch file.

Now enter the connect command to connect to the Keycloak server.

Now enter the following command on the console. Note that the command is all in one line. If it is showing up in multiple lines, then it is because of the responsive design and layout of this blog post.

module add --name=com.mysql --dependencies=javax.api,javax.transaction.api --resources=D:/mysql-connector-java-8.0.20.jar --module-root-dir=../modules/system/layers/keycloak/

What did this module add command do?

The above command created a directory structure inside your {Keycloak-Directory}\modules\system\layers\keycloak/ directory and added a module.xml file. After executing the module add command you should see a module file at D:\keycloak\keycloak-10.0.2\modules\system\layers\keycloak\com\mysql\main\module.xml

The module.xml file looks like the XML snippet below. Notice that the connector jar has also been copied inside this newly created directory by this command. So now Keycloak knows the definition of your database connector and the location of the jar file too.

<?xml version='1.0' encoding='UTF-8'?>

<module xmlns="urn:jboss:module:1.1" name="com.mysql">

    <resources>
        <resource-root path="mysql-connector-java-8.0.20.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Step 6: Defining a data source

Now that Keycloak knows about the database connector through its modules, its time to actually define a data source. This will allow Keycloak to connect to your MySQL database instead of the built-in H2 database.

• First, go to your {Keycloak-Directory}\standalone\configuration\ and open the standalone.xml file in your text editor.
• Search for the word KeycloakDS and you should see a section defining a data source and database driver for the default H2 database the XML snippet below.
• You need to do two things - replace the KeycloakDS data source and the database connection driver defined for H2 with your MySQL database.

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" 
    use-java-context="true" 
    statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
  <connection-url>jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE</connection-url>
  <driver>h2</driver>
  <security>
    <user-name>sa</user-name>
    <password>sa</password>
  </security>
</datasource>
<drivers>
  <driver name="h2" module="com.h2database.h2">
    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
  </driver>
</drivers>

The above two tasks can be achieved using the CLI (and I recommend this versus manually editing the configuration in standalone.xml)

Remove the existing datasource

• Enter the following command in the console to first remove the KeycloakDS

/subsystem=datasources/data-source=KeycloakDS:remove

• You should see console messages indicating success, like in the screen shot below.

Define your MySQL based driver and data source

• Enter the following command in the console to define the MySQL driver

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.cj.jdbc.Driver)

• You should see console messages indicating success, like in the screen shot below.

• Enter the following command in the console to define the MySQL data source

data-source add --name=KeycloakDS --jndi-name=java:jboss/datasources/KeycloakDS --enabled=true --password=keycloak123 --user-name=keycloakadmin --driver-name=com.mysql --use-java-context=true --connection-url=jdbc:mysql://localhost:3306/keycloakdb?useSSL=false&characterEncoding=UTF-8

• You should see console messages indicating success, like in the screen shot below.

• If you take a quick look at your standalone.xml file, your data source should now look like the XML snippet below

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:mysql://localhost:3306/keycloakdb?useSSL=false&amp;characterEncoding=UTF-8</connection-url>
    <driver>com.mysql</driver>
    <security>
        <user-name>keycloakadmin</user-name>
        <password>keycloak123</password>
    </security>
</datasource>
<drivers>
    <driver name="h2" module="com.h2database.h2">
        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
    </driver>
    <driver name="com.mysql" module="com.mysql">
        <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
    </driver>
</drivers>

• There is much more you can do to the datasource configuration like add connection pooling, connection validators, exception handlers, etc. I have not covered all these options as the purpose is to get MySQL connected as the Keycloak database for a development environment. Detailed official documentation about Wildfly server and data sources is available here.
• For example, you can manually add connection pooling to the data source configuration (or use the JBoss/Wildfly CLI options) like the XML snippet below.

...
<driver>com.mysql</driver>
    <pool>
        <min-pool-size>4</min-pool-size>
        <initial-pool-size>4</initial-pool-size>
        <max-pool-size>64</max-pool-size>
    </pool>
    <security>
...

• You can add some validators to your data source like the XML snippet below.

...
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
  </validation> 
</datasource>
...

About the character encoding parameter in the jdbc connection url

Please note that Keycloak requires that your database be capable of storing data in UTF8 format. Similarly, the connection url also needs to specify that the connection can handle UTF8 data. More details in the official guide here.

Test your database connection

After the above two steps you should test if Keycloak can reach your database.

• Enter the following command in the console to test database connectivity

/subsystem=datasources/data-source=KeycloakDS:test-connection-in-pool

• You should see console messages indicating success, like in the screen shot below.

Step 7: Database configuration

You are near the very end of your configuration, but please understand the following. Keycloak allows you to customize code and configuration via their SPI or Service Provider Interface facility. For the database configuration it means ensuring that some of the parameters are set properly for Keycloak to create the required tables and apply required constraints in the keycloakdb database in your MySQL.

• Open the standalone.xml file once again in your text editor and search for connectionsJpa
• You should see configuration like the XML snippet below

<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"/>
    </properties>
  </provider>
</spi>

• This section is about telling Keycloak if you want your MySQL database to be initialized for you. Remember that Keycloak uses JPA too for connecting to your database. So the underlying provider is Hibernate. If you have ever worked with Hibernate then you know about configurations like ddl-auto. These configuration parameters are something similar. Please refer to the official documentation here for more details.

Step 8: Restarting Keycloak server

• Note that Keycloak was started for configuring MySQL but its administrative UI was not accesssed nor was an administrator user added to it. It is not mandatory, but I prefer adding an intial admin user to Keycloak in the master realm. You can add a admin user via the Keycloak Admin Console UI or via the Keycloak CLI.
• Enter the following command from the /bin directory to add a Keycloak user to the master realm.

add-user-keycloak.bat -u superadmin -p superadmin -r master

• You are almost done! You need to restart Keycloak so that when it boots up, it will now use the KeycloakDS data source and connect with your MySQL database. This will also allow you to check the tables and constraints added by Keycloak to your database.

Checking Keycloak log messages to see that MySQL connectivity is working as expected

Once you restart the Keycloak server, you can check the log messages. You should see messages like the following, indicating success.

...
[org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 32) WFLYJCA0005: Deploying non-JDBC-compliant driver class com.mysql.cj.jdbc.Driver (version 8.0)
...
org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) WFLYJCA0018: Started Driver service with driver-name = com.mysql
...
[org.hibernate.jpa.internal.util.LogHelper] (ServerService Thread Pool -- 59) HHH000204: Processing PersistenceUnitInfo [
        name: keycloak-default
        ...]
...

About the log message - Deploying non-JDBC-compliant driver class com.mysql.cj.jdbc.Driver

• Do not worry about that message. Your configuration will work despite this message. It is a known bug. Please see this.
• The simple explanation for this message is that the JDBC driver must have SQL support conforming to the entry level of the SQL92 standard. Unfortunately, MySQL does not support all features that are required by that standard. So JBoss/Wildfly just reports that the driver is not fully compliant. It does not affect the working of your MySQL as the Keycloak database.

Wrapping up

You are now ready to use your MySQL database as the Keycloak data store. I have shown how to configure this on a Windows machine for development purpose. In a real life production scenario, you will probably not run a standalone instance of Keycloak; but most likely clustered mode. Please refer to the official documentation about operating modes.These techniques I have shared will be applicable to those configurations too.

Happy Keycloak-ing! :)