Chapter 9. Configuring the database

The server has built-in support for different databases. You can query the available databases by viewing the expected values for the db configuration option. The following table lists the supported databases and their tested versions.

By default, the server uses the dev-file database. This is the default database that the server will use to persist data and only exists for development use-cases. The dev-file database is not suitable for production use-cases, and must be replaced before deploying to production.

Database drivers are shipped as part of Red Hat build of Keycloak except for the Oracle Database and Microsoft SQL Server drivers.

Install the necessary missing driver manually if you want to connect to one of these databases or skip this section if you want to connect to a different database for which the database driver is already included.

For each supported database, the server provides some opinionated defaults to simplify database configuration. You complete the configuration by providing some key settings such as the database host and credentials.

The configuration can be set during a build command OR a start command:

The default schema is keycloak, but you can change it by using the db-schema configuration option.

It is also possible to configure the database when Importing and exporting realms or Bootstrapping and recovering an admin account:

bin/kc.[sh|bat] import --help
bin/kc.[sh|bat] export --help
bin/kc.[sh|bat] bootstrap-admin --help

bin/kc.[sh|bat] import --help
bin/kc.[sh|bat] export --help
bin/kc.[sh|bat] bootstrap-admin --help

For more information, see Configuring Red Hat build of Keycloak.

The server uses JDBC as the underlying technology to communicate with the database. If the default connection settings are insufficient, you can specify a JDBC URL using the db-url configuration option.

The following is a sample command for a PostgreSQL database.

bin/kc.[sh|bat] start --db postgres --db-url jdbc:postgresql://mypostgres/mydatabase

bin/kc.[sh|bat] start --db postgres --db-url jdbc:postgresql://mypostgres/mydatabase

Be aware that you need to escape characters when invoking commands containing special shell characters such as ; using the CLI, so you might want to set it in the configuration file instead.

Unicode support for all fields depends on whether the database allows VARCHAR and CHAR fields to use the Unicode character set.

The database schema provides support for Unicode strings only for the following special fields:

Otherwise, characters are limited to those contained in database encoding, which is often 8-bit. However, for some database systems, you can enable UTF-8 encoding of Unicode characters and use the full Unicode character set in all text fields. For a given database, this choice might result in a shorter maximum string length than the maximum string length supported by 8-bit encodings.

When running PostgreSQL reader and writer instances, Red Hat build of Keycloak needs to always connect to the writer instance to do its work. When using the original PostgreSQL driver, Red Hat build of Keycloak sets the targetServerType property of the PostgreSQL JDBC driver to primary to ensure that it always connects to a writable primary instance and never connects to a secondary reader instance in failover or switchover scenarios.

You can override this behavior by setting your own value for targetServerType in the DB URL or additional properties.

When using Amazon Aurora PostgreSQL, the Amazon Web Services JDBC Driver offers additional features like transfer of database connections when a writer instance changes in a Multi-AZ setup. This driver is not part of the distribution and needs to be installed before it can be used.

To install this driver, apply the following steps:

Beginning with MySQL 8.0.30, MySQL supports generated invisible primary keys for any InnoDB table that is created without an explicit primary key (more information here). If this feature is enabled, the database schema initialization and also migrations will fail with the error message Multiple primary key defined (1068). You then need to disable it by setting the parameter sql_generate_invisible_primary_key to OFF in your MySQL server configuration before installing or upgrading Red Hat build of Keycloak.

Because cluster nodes can boot concurrently, they take extra time for database actions. For example, a booting server instance may perform some database migration, importing, or first time initializations. A database lock prevents start actions from conflicting with each other when cluster nodes boot up concurrently.

The maximum timeout for this lock is 900 seconds. If a node waits on this lock for more than the timeout, the boot fails. The need to change the default value is unlikely, but you can change it by entering this command:

bin/kc.[sh|bat] start --spi-dblock--jpa--lock-wait-timeout 900

bin/kc.[sh|bat] start --spi-dblock--jpa--lock-wait-timeout 900

Red Hat build of Keycloak uses non-XA transactions and the appropriate database drivers by default.

If you wish to use the XA transaction support offered by your driver, enter the following command:

bin/kc.[sh|bat] build --db=<vendor> --transaction-xa-enabled=true

bin/kc.[sh|bat] build --db=<vendor> --transaction-xa-enabled=true

Red Hat build of Keycloak automatically chooses the appropriate JDBC driver for your vendor.

XA recovery defaults to enabled and will use the file system location KEYCLOAK_HOME/data/transaction-logs to store transaction logs.

To setup the JPA migrationStrategy (manual/update/validate) you should setup JPA provider as follows:

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-migration-strategy=manual

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-migration-strategy=manual

If you want to get a SQL file for DB initialization, too, you have to add this additional SPI initializeEmpty (true/false):

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-initialize-empty=false

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-initialize-empty=false

In the same way the migrationExport to point to a specific file and location:

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-migration-export=<path>/<file.sql>

bin/kc.[sh|bat] start --spi-connections--jpa--quarkus-migration-export=<path>/<file.sql>

For more information, check the Migrating the database documentation.

Red Hat build of Keycloak allows you to specify additional datasources in case you need to access another database from your extensions. This is useful when using the main Red Hat build of Keycloak datasource is not a viable option for storing custom data, like users.

You can find more details on how to connect to your own users database in the {developerguide_userstoragespi_name} documentation.

Defining multiple datasources works like defining a single datasource, with one important change - you have to specify a name for each datasource as part of the config option name.

<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
                         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                         xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
                         version="3.0">
    <persistence-unit name="user-store-pu" transaction-type="JTA">
        <class>org.your.extension.UserEntity</class>
        <properties>
            <property name="jakarta.persistence.jtaDataSource" value="user-store" />
        </properties>
    </persistence-unit>
</persistence>

<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
                         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                         xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
                         version="3.0">
    <persistence-unit name="user-store-pu" transaction-type="JTA">
        <class>org.your.extension.UserEntity</class>
        <properties>
            <property name="jakarta.persistence.jtaDataSource" value="user-store" />
        </properties>
    </persistence-unit>
</persistence>

bin/kc.[sh|bat] start --db-kind-user-store=postgres

bin/kc.[sh|bat] start --db-kind-user-store=postgres

export KC_DB_KIND_USER_STORE=postgres
export KC_DB_USERNAME_USER_STORE=my-username

export KC_DB_KIND_USER_STORE=postgres
export KC_DB_USERNAME_USER_STORE=my-username

export KC_USER_STORE_DB_KIND=mariadb
export KCKEY_USER_STORE_DB_KIND=db-kind-user_store$marketing

export KC_USER_STORE_DB_KIND=mariadb
export KCKEY_USER_STORE_DB_KIND=db-kind-user_store$marketing

quarkus.datasource.user-store.db-kind=h2
quarkus.datasource.user-store.username=sa
quarkus.datasource.user-store.jdbc.url=jdbc:h2:mem:user-store;DB_CLOSE_DELAY=-1
quarkus.datasource.user-store.jdbc.transactions=xa

quarkus.datasource.user-store.db-kind=h2
quarkus.datasource.user-store.username=sa
quarkus.datasource.user-store.jdbc.url=jdbc:h2:mem:user-store;DB_CLOSE_DELAY=-1
quarkus.datasource.user-store.jdbc.transactions=xa