Configuring the database

This guide explains how to configure the NQRust-Identity server to store data in a relational database.

Supported databases

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.

Installing a database driver

Database drivers are shipped as part of NQRust-Identity except for the Oracle Database driver.

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

Installing the Oracle Database driver

To install the Oracle Database driver for NQRust-Identity:

1. Download the ojdbc17 and orai18n JAR files from one of the following sources:

   1. Zipped JDBC driver and Companion Jars version 23.26.0.0.0 from the Oracle driver download page (opens in a new tab).
   2. Maven Central via ojdbc17 and orai18n.
   3. Installation media recommended by the database vendor for the specific database in use.

2. When running the unzipped distribution: Place the ojdbc17 and orai18n JAR files in NQRust-Identity’s providers folder

3. When running containers: Build a custom NQRust-Identity image and add the JARs in the providers folder. When building a custom image for the Operator, those images need to be optimized images with all build-time options of NQRust-Identity set.

   A minimal Containerfile to build an image which can be used with the NQRust-Identity Operator and includes Oracle Database JDBC drivers downloaded from Maven Central looks like the following:

   FROM quay.io/keycloak/keycloak:latest
   ADD --chown=keycloak:keycloak --chmod=644 https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc17/23.26.0.0.0/ojdbc17-23.26.0.0.0.jar /opt/keycloak/providers/ojdbc17.jar
   ADD --chown=keycloak:keycloak --chmod=644 https://repo1.maven.org/maven2/com/oracle/database/nls/orai18n/23.26.0.0.0/orai18n-23.26.0.0.0.jar /opt/keycloak/providers/orai18n.jar
   # Setting the build parameter for the database:
   ENV KC_DB=oracle
   # Add all other build parameters needed, for example enable health and metrics:
   ENV KC_HEALTH_ENABLED=true
   ENV KC_METRICS_ENABLED=true
   # To be able to use the image with the Keycloak Operator, it needs to be optimized, which requires Keycloak's build step:
   RUN /opt/keycloak/bin/kc.sh build

Then continue configuring the database as described in the next section.

Configuring a database

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:

1. Using a build command followed by an optimized start command (recommended)

   First, the minimum settings needed to connect to the database can be specified in conf/keycloak.conf:

   # The database vendor.
   db=postgres
   
   # The username of the database user.
   db-username=keycloak
   
   # The password of the database user.
   db-password=change_me
   
   # Sets the hostname of the default JDBC URL of the chosen vendor
   db-url-host=keycloak-postgres

   Then, the following commands create a new and optimized server image based on the configuration options and start the server.

   bin/kc.[sh|bat] build
   bin/kc.[sh|bat] start --optimized

2. Using only a start command (without --optimized)

   bin/kc.[sh|bat] start --db postgres --db-url-host keycloak-postgres --db-username keycloak --db-password change_me

By default, no schema is explicitly set and NQRust-Identity uses the default schema of the chosen database. You can override this 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

For more information, see Configuring NQRust-Identity.

Overriding default connection settings

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

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.

Configuring Unicode support for the database

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

• If these fields can be set, Unicode is likely to work, usually at the expense of field length.
• If the database only supports Unicode in the NVARCHAR and NCHAR fields, Unicode support for all text fields is unlikely to work because the server schema uses VARCHAR and CHAR fields extensively.

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

• Realms: display name, HTML display name, localization texts (keys and values)
• Federation Providers: display name
• Users: username, given name, last name, attribute names and values
• Groups: name, attribute names and values
• Roles: name
• Descriptions of objects

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.

The recommended character set to use is summarized in the table below, as most recent versions of these databases already use it by default.

Database default charset and collation considerations for MySQL and MariaDB

Different MySQL and MariaDB versions may have different default settings for character sets and collations. NQRust-Identity uses the database default charset and collation when creating new tables and columns. This design allows you to manage your own data lifecycle by defining the charset and collation that best fits your requirements.

Configuring Unicode support for a Microsoft SQL Server database

Unicode characters are supported only for the special fields for a Microsoft SQL Server database. The database requires no special settings.

By default, NQRust-Identity sets the sendStringParametersAsUnicode JDBC driver property to false when using Microsoft SQL Server. This can improve performance and query plan efficiency, as Microsoft SQL Server may be unable to use indexes when string parameters are sent as Unicode.

If you need to override this setting, set sendStringParametersAsUnicode explicitly in the JDBC URL (using db-url) or via db-url-properties. For example:

bin/kc.[sh|bat] start --db mssql --db-url-properties=';sendStringParametersAsUnicode=true'

Database connection and login timeout

When NQRust-Identity connects to the database, network problems can occur, especially during failovers or switchovers. To improve failover behavior and startup resilience during network issues, NQRust-Identity sets a default connection timeout of 10s for all supported database vendors when using the standard JDBC driver.

db-connect-timeout

Sets the JDBC driver connection timeout and login timeout. Default: 10s.

Preparing for PostgreSQL

Writer and reader instances

When running PostgreSQL reader and writer instances, NQRust-Identity needs to always connect to the writer instance to do its work. When using the original PostgreSQL driver, NQRust-Identity 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.

Permissions of the database user

Ensure that the database user has SELECT permissions to the following tables to ensure an efficient upgrade: pg_class, pg_namespace.

This is used during upgrades of NQRust-Identity to determine an estimated number of rows in a table. If NQRust-Identity does not have permissions to access these tables, it will log a warning and proceed with the less efficient SELECT COUNT(*) ... operation during the upgrade to determine the number of rows in tables affected by schema changes.

Secure your connection

To secure your database connection, configure your PostgreSQL server to use TLS and perform full server certificate verification on the client side.

Server-side Configuration (Prerequisites): Before using the properties below, ensure your PostgreSQL server is configured for TLS.

Client-side Configuration: Secure the connection by adding the options:

--db-tls-mode=verify-server --db-tls-trust-store-file=/path/to/certificate

• db-tls-mode=verify-server: Forces TLS and verifies the server’s identity against the trusted certificate.
• db-tls-trust-store-file=/path/to/certificate: The path to the server’s public certificate file on the client machine.

Preparing for Amazon Aurora PostgreSQL

When using Amazon Aurora PostgreSQL, the Amazon Web Services JDBC Driver (opens in a new tab) 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:

1. When running the unzipped distribution: Download the JAR file from the Amazon Web Services JDBC Driver releases page (opens in a new tab) and place it in NQRust-Identity’s providers folder.

2. When running containers: Build a custom NQRust-Identity image and add the JAR in the providers folder.

   A minimal Containerfile to build an image which can be used with the NQRust-Identity Operator looks like the following:

   FROM quay.io/keycloak/keycloak:latest
   ADD --chmod=0666 https://github.com/awslabs/aws-advanced-jdbc-wrapper/releases/download/2.5.6/aws-advanced-jdbc-wrapper-2.5.6.jar /opt/keycloak/providers/aws-advanced-jdbc-wrapper.jar

3. Configure NQRust-Identity to run with the following parameters:

   db-url Insert aws-wrapper to the regular PostgreSQL JDBC URL resulting in a URL like jdbc:aws-wrapper:postgresql://.... db-driver Set to software.amazon.jdbc.Driver to use the AWS JDBC wrapper.

--db-tls-mode=verify-server --db-tls-trust-store-file=/path/to/certificate

Preparing for MySQL server

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 (opens in a new tab)). 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 NQRust-Identity.

Preparing for MS SQL server

On MS SQL Server, the default transaction isolation level is READ_COMMITTED, which can lead to deadlocks during high load. Therefore, the recommended isolation level for NQRust-Identity is READ_COMMITTED_SNAPSHOT. This isolation level is used by default on Azure SQL.

However, on MS SQL Server, the database isolation level needs to be modified by executing the following command on your database:

ALTER DATABASE <your-database-name> SET READ_COMMITTED_SNAPSHOT ON;

Using Database Vendors with XA transaction support

NQRust-Identity 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

NQRust-Identity 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.

Configuring transaction timeouts

NQRust-Identity provides two CLI options to control transaction timeout behavior:

transaction-default-timeout

Controls the transaction timeout for regular transactions. The default value is 5 minutes.

To configure a different timeout, for example 10 minutes:

bin/kc.[sh|bat] start --transaction-default-timeout=10m

transaction-setup-timeout

Controls the timeout for transactions used by database schema migration, import, and export commands. The default value is 30 minutes, as these operations may take a while to complete for large datasets.

To configure a different timeout, for example 1 hour:

bin/kc.[sh|bat] start --transaction-setup-timeout=1h

Both options accept values as an ISO 8601 duration, an integer number of seconds, or an integer followed by one of ms (milliseconds), s (seconds), m (minutes), h (hours), or d (days). These options can be configured via CLI, environment variables, or the conf/keycloak.conf configuration file.

Secure the database connection

Encrypting the traffic between NQRust-Identity and the database is recommended for increased security, as it prevents third parties from examining the network traffic.

It is recommended to go a step further and enable certificate verification to prevent more complex attacks such as DNS poisoning and address hijacking, whereby NQRust-Identity could be directed to a different server than intended. To perform the certificate validation, the database certificate, or the Certificate Authority (CA) certificate, must be added to the NQRust-Identity truststore.

This section provides guidance on how to enable these settings in NQRust-Identity and configure the JDBC driver properly. Configuring the database server with the private keys and certificates is outside the scope of this section. Consult your vendor documentation on how to do it.

NQRust-Identity provides unified CLI options to configure database TLS settings across different database vendors. These options simplify the configuration by abstracting vendor-specific JDBC properties and providing a consistent interface.

The following options are available:

db-tls-mode

Sets the TLS mode for the database connection. Valid values are disabled and verify-server. When set to verify-server, it enables encryption and server identity verification. Default: disabled

db-tls-trust-store-file

The path to the truststore file containing the database server certificates or Certificate Authority (CA) certificates used to verify the database server’s identity.

db-tls-trust-store-password

The password to access the truststore file (if required and supported by the JDBC driver).

db-tls-trust-store-type

The type of the truststore file. Common values include JKS (Java KeyStore) and PKCS12. If not specified, the driver’s default truststore type is used.

The following example demonstrates how to configure database TLS using these options for a PostgreSQL database.

bin/kc.[sh|bat] start --db=postgres --db-tls-mode=verify-server --db-tls-trust-store-file=/path/to/certificate.pem

Alternatively, you can use --truststore-paths instead of the db-tls-trust-store-* options to add your certificate to the Java truststore.

bin/kc.[sh|bat] start --db=postgres --db-tls-mode=verify-server --truststore-paths=/path/to/certificate.pem

Setting JPA provider configuration option for migrationStrategy

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

Setting the migration-strategy for the quarkus provider of the connections-jpa SPI

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):

Setting the initialize-empty for the quarkus provider of the connections-jpa SPI

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:

Setting the migration-export for the quarkus provider of the connections-jpa SPI

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

Configuring the connection pool

MySQL and MariaDB

In order to prevent 'No operations allowed after connection closed' exceptions from being thrown, it is necessary to ensure that NQRust-Identity’s connection pool has a connection maximum lifetime that is less than the server’s configured wait_timeout. When using the MySQL and MariaDB database, NQRust-Identity configures a default max lifetime of 7 hours and 50 minutes, as this is less than the default server value of 8 hours.

If you are explicitly configuring the wait_timeout in your database, it is necessary to ensure that you configure a db-pool-max-lifetime value that is less than the wait_timeout. The recommended best practice, is to define this value to be your wait_timeout minus a few minutes. Failure to correctly configure the db-pool-max-lifetime will result in NQRust-Identity logging a warning on startup.

Configure multiple datasources

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

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.

Required configuration

In order to enable an additional datasource, you need to set up 2 things - the JPA persistence.xml file and NQRust-Identity configuration. The persistence.xml file serves to specify persistence units as part of the Jakarta Persistence API standard, and is required for proper configuration propagation to the Hibernate ORM framework. When you complete the part with the persistence.xml file, you need to set up NQRust-Identity configuration accordingly.

The additional datasource properties might be specified via the standard config sources like CLI, keycloak.conf, or environment variables.

The additional datasources can be configured in a similar way as the main datasource. This is achieved by using analogous names for config options, which additionally include the name of the additional datasource. For example, when the main datasource uses the db-username, the additional one would be db-username-<datasource>. See the Relevant options chapter for the complete list of them.

1. JPA persistence.xml file

The persistence.xml provides configuration for Jakarta Persistence API (JPA) such as what entities it should manage, the datasource name, JDBC settings, JPA/Hibernate custom settings, and more. The file needs to be placed in the META-INF/persistence.xml folder of your custom NQRust-Identity extension.

In NQRust-Identity, most of the configuration is automatic, and you just need to provide fundamental configuration details - the datasource name and transaction type.

NQRust-Identity requires setting the transaction type for the additional datasource to JTA. You can set the transaction type and datasource name as follows for this minimal persistence.xml file:

<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>

In order to use your own JPA entities, you need to provide the <class> properties that mark JPA entities that will be managed by this persistence unit, directed to a specific datasource. In the example above, the org.your.extension.UserEntity JPA entity will be managed by the persistence unit user-store-pu, directed to the user-store datasource.

2. Required properties

Once you have set up your persistence.xml, the minimal configuration on the NQRust-Identity side is the setup of the DB kind/vendor for the specified datasource. You need to specify the build time option db-kind-<name>, where the <name> is the name of your datasource and must be the same as specified in the persistence.xml file.

Therefore, you can enable the additional datasource user-store as follows (postgres as an example):

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

After specifying the db-kind for the datasource, all database-kind–specific defaults (such as the driver and dialect) are automatically applied, just like for the main datasource.

Configuration via environment variables

If you do not want to configure the datasource via CLI or keycloak.conf properties, you can use the environment variables.

You can set the DB kind via environment variables (for the user-store datasource) as follows:

export KC_DB_KIND_USER_STORE=postgres
export KC_DB_USERNAME_USER_STORE=my-username

It maps to the db-kind-user-store and db-username-user-store NQRust-Identity properties due to the default mapping of the _ (underscore) to the - (dash) for environment variables. However, sometimes, the name of the datasource might contain some special characters like _, $ or .

In order to have it properly configured via the NQRust-Identity environment variables, you need to explicitly say what the key for the datasource should look like. You can use a pair of unique NQRust-Identity environment variables with a special case of the KCKEY_.

For instance, for a datasource with the name user_store$marketing, you can set environment variables as follows:

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

You can find more information in the guide Configuring NQRust-Identity, in subsection Formats for environment variable keys with special characters.

Backward compatibility for the quarkus.properties

In the past, we instructed users to use raw Quarkus properties to configure additional datasources in some places. However, as using Quarkus properties in the conf/quarkus.properties file is considered unsupported, it is strongly recommended to use the dedicated additional datasources options as described above.

Before you are able to migrate to the dedicated options, you can still specify the datasource settings via the Quarkus properties as follows:

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

Relevant options

Additional datasources options