Chapter 5. Relational Database Setup

Red Hat Single Sign-On comes with its own embedded Java-based relational database called H2. This is the default database that Red Hat Single Sign-On will use to persist data and really only exists so that you can run the authentication server out of the box. We highly recommend that you replace it with a more production ready external database. The H2 database is not very viable in high concurrency situations and should not be used in a cluster either. The purpose of this chapter is to show you how to connect Red Hat Single Sign-On to a more mature database.

Red Hat Single Sign-On uses two layered technologies to persist its relational data. The bottom layered technology is JDBC. JDBC is a Java API that is used to connect to a RDBMS. There are different JDBC drivers per database type that are provided by your database vendor. This chapter discusses how to configure Red Hat Single Sign-On to use one of these vendor-specific drivers.

The top layered technology for persistence is Hibernate JPA. This is a object to relational mapping API that maps Java Objects to relational data. Most deployments of Red Hat Single Sign-On will never have to touch the configuration aspects of Hibernate, but we will discuss how that is done if you run into that rare circumstance.

Note

Datasource configuration is covered much more thoroughly within the the datasource configuration chapter of the JBoss EAP Configuration Guide.

5.1. RDBMS Setup Checklist

These are the steps you will need to perform to get an RDBMS configured for Red Hat Single Sign-On.

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

This chapter will use PostgresSQL for all its examples. Other databases follow the same steps for installation.

5.2. Package the JDBC Driver

Find and download the JDBC driver JAR for your RDBMS. Before you can use this driver, you must package it up into a module and install it into the server. Modules define JARs that are loaded into the Red Hat Single Sign-On classpath and the dependencies those JARs have on other modules. They are pretty simple to set up.

Within the …​/modules/ directory of your Red Hat Single Sign-On distribution, you need to create a directory structure to hold your module definition. The convention is use the Java package name of the JDBC driver for the name of the directory structure. For PostgreSQL, create the directory org/postgresql/main. Copy your database driver JAR into this directory and create an empty module.xml file within it too.

Module Directory

db module

After you have done this, open up the module.xml file and create the following XML

Module XML

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="org.postgresql">

    <resources>
        <resource-root path="postgresql-9.4.1208.jar"/>
    </resources>

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

The module name should match the directory structure of your module. So, org/postgresql maps to org.postgresql. The resource-root path attribute should specify the JAR filename of the driver. The rest are just the normal dependencies that any JDBC driver JAR would have.

5.3. Declare and Load JDBC Driver

The next thing you have to do is declare your newly packaged JDBC driver into your deployment profile so that it loads and becomes available when the server boots up. Where you perform this action depends on your operating mode. If you’re deploying in standard mode, edit …​/standalone/configuration/standalone.xml. If you’re deploying in standard clustering mode, edit …​/standalone/configuration/standalone-ha.xml. If you’re deploying in domain mode, edit …​/domain/configuration/domain.xml. In domain mode, you’ll need to make sure you edit the profile you are using: either auth-server-standalone or auth-server-clustered

Within the profile, search for the drivers XML block within the datasources subsystem. You should see a pre-defined driver declared for the H2 JDBC driver. This is where you’ll declare the JDBC driver for your external database.

JDBC Drivers

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

Within the drivers XML block you’ll need to declare an additional JDBC driver. It needs to have a name which you can choose to be anything you want. You specify the module attribute which points to the module package you created earlier for the driver JAR. Finally you have to specify the driver’s Java class. Here’s an example of installing PostgreSQL driver that lives in the module example defined earlier in this chapter.

Declare Your JDBC Drivers

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

5.4. Modify the Red Hat Single Sign-On Datasource

After declaring your JDBC driver, you have to modify the existing datasource configuration that Red Hat Single Sign-On uses to connect it to your new external database. You’ll do this within the same configuration file and XML block that you registered your JDBC driver in. Here’s an example that set’s up the connection to your new database:

Declare Your JDBC Drivers

  <subsystem xmlns="urn:jboss:domain:datasources:4.0">
     <datasources>
       ...
       <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
           <connection-url>jdbc:postgresql://localhost/keycloak</connection-url>
           <driver>postgresql</driver>
           <pool>
               <max-pool-size>20</max-pool-size>
           </pool>
           <security>
               <user-name>William</user-name>
               <password>password</password>
           </security>
       </datasource>
        ...
     </datasources>
  </subsystem>

Search for the datasource definition for KeycloakDS. You’ll first need to modify the connection-url. The documentation for your vendor’s JDBC implementation should specify the format for this connection URL value.

Next define the driver you will use. This is the logical name of the JDBC driver you declared in the previous section of this chapter.

It is expensive to open a new connection to a database ever 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. You may want to change the value of this depending on the load of your system.

Finally, with PostgreSQL at least, you need to define the database username and password that is needed to connect to the database. You may be worried that this is in clear text in the example. There are methods to obfuscate this, but this is beyond the scope of this guide.

Note

For more information and details features of datasources, please see the the datasource configuration chapter of the JBoss EAP Configuration Guide.

5.5. Hibernate Configuration

The Hibernate persistence API is already pre-configured out of the box and rarely needs to be changed. The configuration for this component lies in the keycloak-server.json file. If you are running in standalone mode, this file is in the …​/standalone/configuration directory. If you are running in domain mode this file will live in the …​/domain/servers/{server name}/configuration directory.

Hibernate JPA Config

"connectionsJpa": {
    "default": {
        "dataSource": "java:jboss/datasources/KeycloakDS",
        "databaseSchema": "update"
    }
},

Possible configuration options are:

dataSource
JNDI name of the dataSource
jta
boolean property to specify if datasource is JTA capable
driverDialect
Value of database dialect. In most cases you don’t need to specify this property as dialect will be autodetected by Hibernate.
databaseSchema
Specify if schema should be updated or validated. Valid values are update (default) and validate. The update value will set up or update the table definitions that Red Hat Single Sign-On needs. validate just makes sure that the database has the appropriate table definitions set up.
showSql
Specify whether Hibernate should show all SQL commands in the console (false by default). This is very verbose!
formatSql
Specify whether Hibernate should format SQL commands (true by default)
globalStatsInterval
Will log global statistics from Hibernate about executed DB queries and other things. Statistics are always reported to server log at specified interval (in seconds) and are cleared after each report.
schema
Specify the database schema to use
Note

All these configuration switches and more are described in the JBoss EAP Development Guide.