Article

Using Red Hat's single sign-on technology with external databases, Part 1: Install and configure SSO with MariaDB

May 12, 2021
LinuxKubernetesSecurity
Issa Gueye
Senior Product Manager, Technical
Using Red Hat’s single sign-on technology with external databases, Part 1

Red Hat's single sign-on (SSO) technology, based on the Keycloak open source project, is Red Hat's solution for securing web applications and RESTful web services. The goal of Red Hat's single sign-on technology is to make security simple, so that it is easy for application developers to secure the apps and services they have deployed in their organization.

Out of the box, single sign-on uses its own Java-based embedded relational database, called H2, to store persistent data. However, this H2 database is not viable in high-concurrency situations and should not be used in a cluster. So, it is highly recommended to replace this H2 database with a more production-ready external database.

This article shows you how to install and configure Red Hat's single sign-on technology to connect to a more mature database. We'll use MariaDB for the example, but the instructions should also work for MySQL.

Note: This article is the first in a series about using SSO with external databases. My next article will cover deploying an SSO image in Red Hat OpenShift. You will learn how to connect the image to an external MariaDB or MySQL server running outside of OpenShift using a custom JDBC driver.

Setting up and configuring the MariaDB or MySQL database

To perform the steps in this section, you must first log in as root on a Red Hat Enterprise Linux (RHEL) 7 system.

Step 1: Install and configure a MariaDB or MySQL database server

This step can be done through the following commands:

[root@testvm ~]# yum install mariadb-server mariadb mysql-connector-java

[root@testvm ~]# systemctl enable mariadb

[root@testvm ~]# systemctl start mariadb

[root@testvm ~]# /usr/bin/mysql_secure_installation
...
Set root password? [Y/n] Y
New password: redhat
Re-enter new password: redhat
...
Remove anonymous users? [Y/n] Y
...
Disallow root login remotely? [Y/n] Y
...
Remove test database and access to it? [Y/n] Y
...
Reload privilege tables now? [Y/n] Y
...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@testvm ~]#

[root@testvm ~]# mysql -h localhost -uroot -predhat
...
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]> quit
Bye
[root@testvm ~]#

Step 2: Create a new user and a database for SSO

Enter the following commands to create the new user:

[root@testvm ~]# cat mariadb_rhsso74_db_setup.sql
CREATE USER 'rhsso74'@'%' IDENTIFIED BY 'redhat';

DROP DATABASE IF EXISTS `rhsso74`;

CREATE DATABASE IF NOT EXISTS `rhsso74`;

GRANT ALL PRIVILEGES ON rhsso74.* TO 'rhsso74'@'%' identified by 'redhat';

[root@testvm ~]#

[root@testvm ~]# mysql -h localhost -uroot -predhat < mariadb_rhsso74_db_setup.sql
[root@testvm ~]#

[root@testvm ~]# mysql -h localhost -uroot -predhat

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rhsso74  |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [rhsso74]> quit
Bye
[root@testvm ~]#

[root@testvm ~]# mysql -h localhost -urhsso74 -predhat
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| rhsso74  |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use `rhsso74`;
Database changed
MariaDB [rhsso74]> show tables;
Empty set (0.00 sec)

MariaDB [rhsso74]> quit
Bye
[root@testvm ~]#

Installing and configuring SSO

To perform the steps in this section, log in as ssoadmin on a RHEL 7 system.

Step 1: Download and install Red Hat's single sign-on 7.4 software

At the time of writing this article, SSO 7.4 is the latest available release. Download and install SSO under a directory path of your choice, for instance:

/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/

After creating the directory, run the following commands to install the software:

[ssoadmin@testvm LATEST]$ id -a
uid=1000(ssoadmin) gid=1000(ssoadmin) groups=1000(ssoadmin),10(wheel) context=system_u:system_r:unconfined_service_t:s0
[ssoadmin@testvm LATEST]$

[ssoadmin@testvm LATEST]$ pwd
/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/
[ssoadmin@testvm LATEST]$

[ssoadmin@testvm LATEST]$ unzip rh-sso-7.4.0.zip
Archive: rh-sso-7.4.0.zip
creating: rh-sso-7.4/
creating: rh-sso-7.4/modules/
...
...
inflating: rh-sso-7.4/docs/licenses-rh-sso/org.keycloak,keycloak-server-spi-private,9.0.3.redhat-00002,Apache Software License 2.0.txt
[ssoadmin@testvm LATEST]$

[ssoadmin@testvm LATEST]$ cd rh-sso-7.4/
[ssoadmin@testvm rh-sso-7.4]$

[ssoadmin@testvm rh-sso-7.4]$ cat version.txt
Red Hat Single Sign-On - Version 7.4.0.GA
[ssoadmin@testvm rh-sso-7.4]$

Step 2: Add a new admin user and start the SSO server instance

This step can be done through the following commands:

[ssoadmin@testvm rh-sso-7.4]$ cd bin
[ssoadmin@testvm bin]$ ./add-user.sh --user admin -p redhat
Updated user 'admin' to file '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/mgmt-users.properties'
Updated user 'admin' to file '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/domain/configuration/mgmt-users.properties'
[ssoadmin@testvm bin]$
[ssoadmin@testvm bin]$ ./standalone.sh

Step 3: Change the SSO binding addresses to listen to all network interfaces

By default, Red Hat's single sign-on technology binds via Red Hat JBoss Enterprise Application Platform (JBoss EAP) to the localhost loopback address 127.0.0.1. However, that’s not a very useful default setting if you want the authentication server available on your network. So, you need to set up your network interfaces to bind to something other than localhost. You could also do this when starting the server instance by setting the -b option to the IP bind address of your choice. But it is better to set it in the configuration just once, where it applies to all the network interfaces on the host.

Open a new terminal tab and run the following JBoss command-line interface (CLI) commands:

[ssoadmin@testvm bin]$ ./jboss-cli.sh --connect
[standalone@localhost:9990 /] /interface=public:write-attribute(name=inet-address,value=0.0.0.0)
[standalone@localhost:9990 /] /interface=management:write-attribute(name=inet-address,value=0.0.0.0)
[standalone@localhost:9990 /] reload
[standalone@localhost:9990 /] exit

Step 4: Add a new admin user for SSO

This user account is specific to the server runtime for SSO, and will allow you to log into the master realm’s administration console to perform administrative tasks in SSO. These tasks include creating realms, creating users, registering clients for applications to be secured by single sign-on, and so on.

When adding the user account, the command parameters differ a little, depending on whether you are using standalone operation mode or domain operation mode. The following works for standalone mode:

[ssoadmin@testvm bin]$ ./add-user-keycloak.sh -r master -u admin -p redhat
Added 'admin' to '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/keycloak-add-user.json', restart server to load user
[ssoadmin@testvm bin]$

Note: If your server is running and accessible from localhost, you can also create this admin user by going to http://localhost:8080/auth.

For domain mode, you have to point the script to one of your server hosts using the -sc option. For example:

[ssoadmin@testvm bin]$ ./add-user-keycloak.sh --sc domain/servers/server-one/configuration -r master -u username -p password

Step 5: Restart the SSO server instance to load the new admin user

Restart the SSO server instance:

[ssoadmin@testvm bin]$ ./standalone.sh
...
22:20:04,239 INFO [org.jboss.as] (MSC service thread 1-1) WFLYSRV0049: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) starting
...
...
22:20:22,469 INFO [org.keycloak.services] (ServerService Thread Pool -- 68) KC-SERVICES0006: Importing users from '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/keycloak-add-user.json'
22:20:23,322 INFO [org.keycloak.services] (ServerService Thread Pool -- 68) KC-SERVICES0009: Added user 'admin' to realm 'master'
...
22:20:23,948 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0060: Http management interface listening on http://0.0.0.0:9990/management
22:20:23,948 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0051: Admin console listening on http://0.0.0.0:9990
22:20:23,949 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) started in 19857ms - Started 598 of 893 services (602 services are lazy, passive or on-demand)
...

Configuring SSO to use the MariaDB or MySQL database

These steps, like those in the preceding section, are performed as ssoadmin on a RHEL 7 system.

Step 1: Add the MySQL JDBC driver module to SSO

Enter the following commands for this step:

[ssoadmin@testvm bin]$ pwd
/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/bin
[ssoadmin@testvm bin]$
[ssoadmin@testvm bin]$ ./jboss-cli.sh
You are disconnected at the moment. Type 'connect' to connect to the server or 'help' for the list of supported commands.
[disconnected /]
[disconnected /] module add --name=com.mysql --resources=/usr/share/java/mysql-connector-java.jar --dependencies=javax.api,javax.transaction.api
[disconnected /] exit

[ssoadmin@testvm bin]$ ls -alrt /home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/modules/com/mysql/main/
total 868
drwxrwxr-x. 3 ssoadmin ssoadmin   18 Oct 17 19:31 ..
-rw-rw-r--. 1 ssoadmin ssoadmin 883899 Oct 17 19:31 mysql-connector-java.jar
drwxrwxr-x. 2 ssoadmin ssoadmin   56 Oct 17 19:31 .
-rw-rw-r--. 1 ssoadmin ssoadmin   317 Oct 17 19:31 module.xml
[ssoadmin@testvm bin]$

Step 2: Register the database driver and add the DataSource

Register the MySQL or MariaDB driver:

[ssoadmin@testvm bin]$ ./jboss-cli.sh --connect
[standalone@localhost:9990 /]
[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource, driver-class-name=com.mysql.jdbc.Driver)
{"outcome" => "success"}

[standalone@localhost:9990 /]
[standalone@localhost:9990 /] /subsystem=datasources/data-source=KeycloakMariaDBDS:add(jndi-name=java:jboss/datasources/KeycloakMariaDBDS, driver-name=mysql, connection-url=jdbc:mysql://localhost:3306/rhsso74, user-name=rhsso74, password=redhat)
{"outcome" => "success"}

[standalone@localhost:9990 /]
[standalone@localhost:9990 /] /subsystem=datasources/data-source=KeycloakMariaDBDS:test-connection-in-pool
{
"outcome" => "success",
"result" => [true]
}

[standalone@localhost:9990 /]
[standalone@localhost:9990 /] reload
[standalone@localhost:9990 /]
[standalone@localhost:9990 /] exit
[ssoadmin@testvm bin]$

Step 3: Update the SSO JPA connection to point to the new DataSource

You have to stop the server instance for SSO before updating the JPA connection:

[ssoadmin@testvm bin]$ cp -p /home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/standalone.xml /home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/standalone.xml.SAVE_Backup
[ssoadmin@testvm bin]$
[ssoadmin@testvm bin]$ vi /home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/standalone.xml
...
   <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="dataSource" value="java:jboss/datasources/KeycloakMariaDBDS"/>
       <property name="initializeEmpty" value="true"/>
       <property name="migrationStrategy" value="update"/>
       <property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
     </properties>
 </provider>
  </spi>
 ...
...
[ssoadmin@testvm bin]$

Step 4: Restart SSO

In this step, you will restart the server instance and check to make sure that its database model is loaded into the database:

[ssoadmin@testvm bin]$ ./standalone.sh
...
21:14:37,090 INFO [org.jboss.as] (MSC service thread 1-2) WFLYSRV0049: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) starting
...
21:14:42,034 INFO [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-6) WFLYJCA0001: Bound data source [java:jboss/datasources/KeycloakMariaDBDS]

...

21:14:49,568 INFO [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 67) Initializing database schema. Using changelog META-INF/jpa-changelog-master.xml

...
21:15:02,735 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) started in 26903ms - Started 598 of 893 services (602 services are lazy, passive or on-demand)

Connect to the MariaDB or MySQL database and check the SSO database model:

[root@testvm ~]# mysql -h localhost -urhsso74 -predhat

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| rhsso74 |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> use `rhsso74`;

MariaDB [rhsso74]> show tables;
+-------------------------------+
| Tables_in_rhsso74 |
+-------------------------------+
| ADMIN_EVENT_ENTITY |
| ASSOCIATED_POLICY |
| AUTHENTICATION_EXECUTION |
| AUTHENTICATION_FLOW |
| AUTHENTICATOR_CONFIG |
| AUTHENTICATOR_CONFIG_ENTRY |
| BROKER_LINK |
| CLIENT |
| CLIENT_ATTRIBUTES |
| CLIENT_AUTH_FLOW_BINDINGS |
| CLIENT_DEFAULT_ROLES |
| CLIENT_INITIAL_ACCESS |
| CLIENT_NODE_REGISTRATIONS |
| CLIENT_SCOPE |
| CLIENT_SCOPE_ATTRIBUTES |
| CLIENT_SCOPE_CLIENT |
| CLIENT_SCOPE_ROLE_MAPPING |
| CLIENT_SESSION |
| CLIENT_SESSION_AUTH_STATUS |
| CLIENT_SESSION_NOTE |
| CLIENT_SESSION_PROT_MAPPER |
| CLIENT_SESSION_ROLE |
| CLIENT_USER_SESSION_NOTE |
| COMPONENT |
| COMPONENT_CONFIG |
| COMPOSITE_ROLE |
| CREDENTIAL |
| DATABASECHANGELOG |
| DATABASECHANGELOGLOCK |
| DEFAULT_CLIENT_SCOPE |
| EVENT_ENTITY |
| FEDERATED_IDENTITY |
| FEDERATED_USER |
| FED_USER_ATTRIBUTE |
| FED_USER_CONSENT |
| FED_USER_CONSENT_CL_SCOPE |
| FED_USER_CREDENTIAL |
| FED_USER_GROUP_MEMBERSHIP |
| FED_USER_REQUIRED_ACTION |
| FED_USER_ROLE_MAPPING |
| GROUP_ATTRIBUTE |
| GROUP_ROLE_MAPPING |
| IDENTITY_PROVIDER |
| IDENTITY_PROVIDER_CONFIG |
| IDENTITY_PROVIDER_MAPPER |
| IDP_MAPPER_CONFIG |
| KEYCLOAK_GROUP |
| KEYCLOAK_ROLE |
| MIGRATION_MODEL |
| OFFLINE_CLIENT_SESSION |
| OFFLINE_USER_SESSION |
| POLICY_CONFIG |
| PROTOCOL_MAPPER |
| PROTOCOL_MAPPER_CONFIG |
| REALM |
| REALM_ATTRIBUTE |
| REALM_DEFAULT_GROUPS |
| REALM_DEFAULT_ROLES |
| REALM_ENABLED_EVENT_TYPES |
| REALM_EVENTS_LISTENERS |
| REALM_REQUIRED_CREDENTIAL |
| REALM_SMTP_CONFIG |
| REALM_SUPPORTED_LOCALES |
| REDIRECT_URIS |
| REQUIRED_ACTION_CONFIG |
| REQUIRED_ACTION_PROVIDER |
| RESOURCE_ATTRIBUTE |
| RESOURCE_POLICY |
| RESOURCE_SCOPE |
| RESOURCE_SERVER |
| RESOURCE_SERVER_PERM_TICKET |
| RESOURCE_SERVER_POLICY |
| RESOURCE_SERVER_RESOURCE |
| RESOURCE_SERVER_SCOPE |
| RESOURCE_URIS |
| ROLE_ATTRIBUTE |
| SCOPE_MAPPING |
| SCOPE_POLICY |
| USERNAME_LOGIN_FAILURE |
| USER_ATTRIBUTE |
| USER_CONSENT |
| USER_CONSENT_CLIENT_SCOPE |
| USER_ENTITY |
| USER_FEDERATION_CONFIG |
| USER_FEDERATION_MAPPER |
| USER_FEDERATION_MAPPER_CONFIG |
| USER_FEDERATION_PROVIDER |
| USER_GROUP_MEMBERSHIP |
| USER_REQUIRED_ACTION |
| USER_ROLE_MAPPING |
| USER_SESSION |
| USER_SESSION_NOTE |
| WEB_ORIGINS |
+-------------------------------+
93 rows in set (0.01 sec)

MariaDB [rhsso74]> select ID, NAME, ACCOUNT_THEME, ADMIN_THEME, DEFAULT_LOCALE from REALM;
+----------------+----------------+------------------+-------------+----------------+
| ID             | NAME           | ACCOUNT_THEME    | ADMIN_THEME | DEFAULT_LOCALE |
+----------------+----------------+------------------+-------------+----------------+
| master         | master         | keycloak-preview | NULL        | NULL           |
+----------------+----------------+------------------+-------------+----------------+
1 rows in set (0.00 sec)

MariaDB [rhsso74]>

Step 5: Recreate the admin user for SSO

This step is needed because you have switched the database SSO is using to MariaDB or MySQL. The admin user created earlier is in the default H2 database, which is not used anymore:

[ssoadmin@testvm bin]$ ./add-user-keycloak.sh -r master -u admin -p redhat
Added 'admin' to '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/keycloak-add-user.json', restart server to load user
[ssoadmin@testvm bin]$

Step 6: Final testing and validation

Start the SSO server instance:

[ssoadmin@testvm bin]$ ./standalone.sh
...
22:20:04,239 INFO [org.jboss.as] (MSC service thread 1-1) WFLYSRV0049: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) starting
...
22:20:22,469 INFO [org.keycloak.services] (ServerService Thread Pool -- 68) KC-SERVICES0006: Importing users from '/home/ssoadmin/RH-SSO_SETUP/RH-SSO_7.x/7.4.x/LATEST/rh-sso-7.4/standalone/configuration/keycloak-add-user.json'
22:20:23,322 INFO [org.keycloak.services] (ServerService Thread Pool -- 68) KC-SERVICES0009: Added user 'admin' to realm 'master'

...

22:20:23,949 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: Red Hat Single Sign-On 7.4.0.GA (WildFly Core 10.1.2.Final-redhat-00001) started in 19857ms - Started 598 of 893 services (602 services are lazy, passive or on-demand)

Verify that the admin user has been created in the database:

MariaDB [rhsso74]> select ID, USERNAME, REALM_ID from USER_ENTITY;
+--------------------------------------+------------------+----------------+
| ID | USERNAME | REALM_ID       |
+--------------------------------------+------------------+----------------+
| 075d1cb9-fb4d-4437-80f8-f4397b1b5370 | admin       | master         |
+--------------------------------------+------------------+----------------+
1 row in set (0.00 sec)

MariaDB [rhsso74]>

Access the admin console in the SSO server instance by opening your browser, loading http://localhost:8080/auth, and logging in as admin/redhat.

Next steps with SSO

To keep learning about using Red Hat's single sign-on technology, you can play with the SSO quickstarts and Keycloak quickstarts on GitHub. Also, check out the documentation for Red Hat's single sign-on technology and the SSO 7.4 release.

Coming up next ...

My next article in this series will show you how to deploy the SSO container image in OpenShift. Future articles will cover topics and use cases such as SSO migration and upgrades across different database flavors, and tricks and tips for connecting to an external database using a custom JDBC driver. I'll also share more powerful features of Red Hat's single sign-on technology and other topics related to SSO integration, such as integrating with third-party OpenID Connect and Security Assertion Markup Language (SAML) vendors, clustering while using Nginx as a load balancer or reverse proxy, and using the Proof Key for Code Exchange (PKCE) extension in SSO. Stay tuned!

Last updated: February 11, 2024