Keycloak MySQL Setup#

Prepare Keycloak schema and user#

$ mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'keycloak'@'%' IDENTIFIED BY 'keycloak';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'%';
Query OK, 0 rows affected (0.00 sec)

created keycloak database
created keycloak user with the password keycloak (please use a strong password for production)
granted all privileges to the keycloak on the keycloak database

RDBMS Setup Checklist#

Locate and download a JDBC driver for your database
Package the driver JAR into a module and install this module into the server
Declare the JDBC driver in the configuration profile of the server
Modify the datasource configuration to use your database’s JDBC driver
Modify the datasource configuration to define the connection parameters to your database

JDBC Setup#

$ wget -P ~/tmp/  https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.42.zip
$ unzip ~/tmp/mysql-connector-java-5.1.42.zip -d ~/tmp
$ sudo mkdir -p /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
$ sudo cd /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
$ sudo cp ~/tmp/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar .
$ sudo touch module.xml

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-5.1.42-bin.jar" />
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Declare and Load JDBC Driver#

vim /opt/keycloak/standalone/configuration/standalone.xml

<subsystem xmlns="urn:jboss:domain:datasources:4.0">
    <datasources>
      <drivers>
          <driver name="mysql" module="com.mysql">
              <driver-class>com.mysql.jdbc.Driver</driver-class>
          </driver>      
          <driver name="h2" module="com.h2database.h2">
              <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
          </driver>
      </drivers>
  </datasources>
</subsystem>

name is set to mysql, but can be everything we want
we speciy the module attribute which points to the module package we created earlier for the driver JAR
finally, we specify the driver’s Java class, which in case of MySQL is com.mysql.jdbc.Driver

Datasource setup#

<subsystem xmlns="urn:jboss:domain:datasources:4.0">
    <datasources>
        <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
            <driver>h2</driver>
            <security>
                <user-name>sa</user-name>
                <password>sa</password>
            </security>
        </datasource>
        <datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
            <connection-url>jdbc:mysql://localhost:3306/keycloak?useSSL=false&amp;characterEncoding=UTF-8</connection-url>
            <driver>mysql</driver>
            <pool>
                <min-pool-size>5</min-pool-size>
                <max-pool-size>15</max-pool-size>
            </pool>
            <security>
                <user-name>keycloak</user-name>
                <password>keycloak</password>
            </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>
        <drivers>
            <driver name="h2" module="com.h2database.h2">
                <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
            </driver>
            <driver name="mysql" module="com.mysql">
                <driver-class>com.mysql.jdbc.Driver</driver-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>

What we modified:

we’ve searched for datasource definition for KeycloakDS.
we modified the connection-url to point to the MySQL server
we defined the driver we use for the connection; this is the logical name of the JDBC driver we declared in the previous section (mysql)
it is expensive to open a new connection to a database every time you want to perform a transaction. To compensate, the datasource implementation maintains a pool of open connections. The max-pool-size specifies the maximum number of connections it will pool. We may want to change the value of this depending on the load of the system.
finally we need to define the database user-name and password, that is needed to connect to the database.

Database configuration#

<subsystem xmlns="urn:jboss:domain:keycloak-server:1.1">
    <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>
</subsystem>

dataSource- JNDI name of the dataSource
initializeEmpty - initialize database if empty. If set to false the database has to be manually initialized. If you want to manually initialize the database set migrationStrategy to manual which will create a file with SQL commands to initialize the database. Defaults to true.
migrationStrategy - strategy to use to migrate database. Valid values are update, manual and validate.
- Update will automatically migrate the database schema.
- Manual will export the required changes to a file with SQL commands that you can manually execute on the database.
- Validate will simply check if the database is up-to-date.
migrationExport - path for where to write manual database initialization/migration file.

Unicode Considerations for MySQL#

Please note that utf8mb4 character set does not work due to different storage requirements to utf8 character set footnote:[Tracked as https://issues.jboss.org/browse/KEYCLOAK-3873]).

References#

Keycloak MySQL Setup

Keycloak MySQL Setup

Contents