Connect to an external PostgreSQL database using SSL/TLS for Red Hat's single sign-on technology

August 31, 2021
Olivier Rivat
Related topics:
DevSecOpsLinuxSecurity
Related products:
Red Hat OpenShiftRed Hat Enterprise Linux

Note: The connection URL shown in this example is the URL used by Red Hat's single-sign on technology to connect the PostgreSQL database in a lab environment, using SSL/TLS without certificate validation. It should not be used in a production environment.

To deploy your system in a production environment, you should perform an SSL/TLS connection using certificate validation. Refer to Using SSL in the PostgresSQL JDBC driver documentation.

This article shows you how to connect securely to applications and data sources using Red Hat's single sign-on technology. The example connects to an external PostgreSQL database in Secure Sockets Layer (SSL) or secure Transport Layer Security (TLS) mode, first locally and then on Red Hat OpenShift. As you will see, it is usually much easier to carry out the integration first on a standalone instance of Red Hat's single-sign on tool and then deploy it on OpenShift.

At a high level, we will do the following:

  1. Configure SSO to connect to the PostgreSQL server in SSL/TLS mode.
  2. Deploy SSO on OpenShift and connect to the PostgreSQL database using SSL/TLS.

Prerequisites

First, you need to configure a PostgreSQL database that you can access remotely over SSL/TLS. (Describing how to configure the PostgreSQL database over SSL/TLS is beyond the scope of this article.) Create a database called keycloak. 

Note: The name of the database created will appear in the data source's connection URL.

Step 1: Connect SSO to the PostgreSQL server using SSL/TLS

Once PostgreSQL is running and able to communicate over SSL/TLS, you can set up Red Hat's single sign-on technology to connect to the PostgreSQL database using a Red Hat JBoss Enterprise Application Platform script.

Download the PostgreSQL driver

Download the postgresql driver from the PostgreSQL JDBC driver page. The driver is in a file named postgresql-42.2.23.jar.

Use a JBoss script to connect to the external database

A JBoss script named sso-extensions.cli follows. It contains commands to make Keycloak use PostgreSQL instead of the H2 database that Keycloak uses by default. You need to run the script to allow Keycloak to connect to PostgreSQL in SSL/TLS mode.

Before you run the following script, replace the string <postgresql-server-hostname> with the fully qualified domain name of the PostgreSQL server:

batch


set DB_USERNAME=postgres
set DB_PASSWORD=postgres
set DRIVER_NAME=postgres
set DRIVER_MODULE_NAME=org.postgres
set XA_DATABASESOURCE_CLASS="org.postgresql.xa.PGXADataSource"
set CONNECTION_URL="jdbc:postgresql://<postgresql-server-hostname>:5432/keycloak?ssl=true;sslfactory=org.postgresql.ssl.NonValidatingFactory"
set FILE=/tmp/postgresql-42.2.23.jar


module add --name=$DRIVER_MODULE_NAME --resources=$FILE --dependencies=javax.api,javax.resource.api

/subsystem=datasources/jdbc-driver=$DRIVER_NAME:add( \
  driver-name=$DRIVER_NAME, \
  driver-module-name=$DRIVER_MODULE_NAME, \
  xa-datasource-class=$XA_DATABASESOURCE_CLASS \
)

/subsystem=datasources/data-source=KeycloakDS:remove()
/subsystem=datasources/data-source=KeycloakDS:add( \
  jndi-name=java:jboss/datasources/KeycloakDS, \
  enabled=true, \
  use-java-context=true, \
  connection-url=$CONNECTION_URL, \
  driver-name=$DRIVER_NAME, \
  user-name=$DB_USERNAME, \
  password=$DB_PASSWORD \
)

run-batch

Note: The connection URL shown in this example is the URL used by Red Hat's single-sign on technology to connect the PostgreSQL database in a lab environment, using SSL/TLS without certificate validation. It should not be used in a production environment.

To deploy your system in a production environment, you should perform a SSL/TLS connection using certificate validation. Refer to Using SSL in the PostgresSQL JDBC driver documentation.

set CONNECTION_URL="jdbc:postgresql://<postgresql-server-hostname>:5432/keycloak?ssl=true;sslfactory=org.postgresql.ssl.NonValidatingFactory"

Test the PostgreSQL database connection

Now, check whether the previous steps let you connect safely to the PostgreSQL database in SSL/TLS mode:

$ unzip rh-sso-7.4.0.zip

$ cd <rhsso-install-dir>

$ bin/sh standalone.sh

# Run the PostgreSQL CLI script

$ bin/jboss-cli.sh --connect --file=sso-extensions.cli

Finally, visit the URL https://localhost:8443/auth to see that the connection is successful.

Step 2: Deploy SSO on OpenShift and connect to the PostgreSQL database using SSL/TLS

Now, we'll move to OpenShift. This section deploys Red Hat's SSO on an OpenShift cluster and connects from there to the external PostgreSQL database in SSL/TLS mode. The steps are:

  1. Build a new SSO Docker image using the JBoss command file shown previously.
  2. Deploy Red Hat's SSO on OpenShift using the standard sso74-x509-https template.
  3. Update the SSO deployment configuration to use the new SSO image.

Build an SSO Docker image

We'll build a new SSO Docker image to allow connections to the external PostgreSQL driver using SSL/TLS. This process is described in detail in the Red Hat documentation for using a custom JDBC driver.

Create a new directory and install the following files there:

  • Dockerfile
  • postgresql-42.2.23.jar
  • sso-extensions.cli

Update the sso-extensions.cli JBoss script to install the driver from the location in /opt/eap/extensions/jdbc-driver.jar:

batch



set DB_USERNAME=postgres
set DB_PASSWORD=postgres
set DRIVER_NAME=postgres
set DRIVER_MODULE_NAME=org.postgres
set XA_DATABASESOURCE_CLASS="org.postgresql.xa.PGXADataSource"
set CONNECTION_URL="jdbc:postgresql://<postgresl-server-hostname>:5432/keycloak?ssl=true;sslfactory=org.postgresql.ssl.NonValidatingFactory"
set FILE=/opt/eap/extensions/jdbc-driver.jar



module add --name=$DRIVER_MODULE_NAME --resources=$FILE --dependencies=javax.api,javax.resource.api
/subsystem=datasources/jdbc-driver=$DRIVER_NAME:add( \
  driver-name=$DRIVER_NAME, \
  driver-module-name=$DRIVER_MODULE_NAME, \
  xa-datasource-class=$XA_DATABASESOURCE_CLASS \
)

/subsystem=datasources/data-source=KeycloakDS:remove()
/subsystem=datasources/data-source=KeycloakDS:add( \
  jndi-name=java:jboss/datasources/KeycloakDS, \
  enabled=true, \
  use-java-context=true, \
  connection-url=$CONNECTION_URL, \
  driver-name=$DRIVER_NAME, \
  user-name=$DB_USERNAME, \
  password=$DB_PASSWORD \
)

run-batch

The Dockerfile contains:

FROM rh-sso-7/sso74-openshift-rhel8:latest

COPY sso-extensions.cli /opt/eap/extensions/

COPY postgresql-42.2.23.jar /opt/eap/extensions/jdbc-driver.jar

Now, build a new SSO image using Podman:

$ podman build -t localhost/docker-registry-default/project/sso74-external-db-postgres-ssl:1.0 .

STEP 1: FROM rh-sso-7/sso74-openshift-rhel8:latest
STEP 2: COPY sso-extensions.cli /opt/eap/extensions/
--> 9f79713bfc3
STEP 3: COPY postgresql-42.2.23.jar /opt/eap/extensions/jdbc-driver.jar
STEP 4: COMMIT localhost/docker-registry-default/project/sso74-external-db-postgres-ssl:1.0
--> af34362aeab
af34362aeabbdaeb4c3319e42ff8f20c7e3a9cbf6031b6f60301a7ba83d4e558

Push the new SSO image to quay.io:

podman login quay.io -u <username> -p <password>



$ podman push localhost/docker-registry-default/project/sso74-external-db-postgres-ssl:1.0 quay.io/<username>/sso74-external-db-postgres-ssl
Getting image source signatures
Copying blob fa592e808c80 done  
Copying blob 329b07dcfb80 done  
Copying blob 69fa687f24b7 skipped: already exists  
Copying blob 870b2c4dba9d skipped: already exists  
Copying blob 1e3f73167579 skipped: already exists  
Copying config af34362aea done  
Writing manifest to image destination
Copying config af34362aea [--------------------------------------] 0.0b / 4.4KiB
Writing manifest to image destination
Storing signatures

Configure SSO on OpenShift

Create a new project in OpenShift and deploy the sso74-x509-https template there. This template initially connects to the default H2 database:

$ oc new-project sso-74-external-db-ssl

Now using project "sso-74-external-db-ssl" on server "https://openshift.example.com:443"


$ oc process sso74-x509-https SSO_ADMIN_USERNAME=admin SSO_ADMIN_PASSWORD=password -n openshift -o yaml > sso74-x509-https.yaml


$ oc create -f sso74-x509-https.yaml

You can now check the status of the SSO server:

$ oc get pods

NAME          READY     STATUS    RESTARTS   AGE
sso-1-x625p   1/1       Running   0          42s



$ oc status

In project sso-74-external-db-ssl on server https://openshift.example.com:443

svc/sso-ping (headless):8888
https://sso-sso-74-external-db-ssl.apps.example.com (reencrypt) (svc/sso)
  dc/sso deploys openshift/sso74-openshift-rhel8:7.4 
    deployment #1 deployed about a minute ago - 1 pod

View details with 'oc describe <resource>/<name>' or list everything with 'oc get all'.

You can also connect to the SSO admin console at the URL https://sso-sso-74-external-db-ssl.apps.example.com/auth.

Update the deployment with the new SSO image

Update the deployment configuration as follows:

$ oc edit dc/sso

Replace the SSO image with:


...
...

 image: quay.io/<username>/sso74-external-db-postgres-ssl:latest
        imagePullPolicy: Always
        livenessProbe:

....
....

Also remove the triggers section so that it pulls new SSO images from quay.io instead of from Red Hat's SSO registry:

....
....

triggers:
    - imageChangeParams:
        automatic: true
        containerNames:
        - sso
        from:
          kind: ImageStreamTag
          name: sso74-openshift-rhel8:7.4
          namespace: openshift
      type: ImageChange
    - type: ConfigChange

....
....

Test the SSO server status

You can see the status of the SSO server as follows:



oc status

In project sso-74-external-db-ssl on server https://openshift.example.com:443

svc/sso-ping (headless):8888

https://sso-sso-74-external-db-ssl.apps.example.com (reencrypt) (svc/sso)
  dc/sso deploys quay.io/<username>/sso74-external-db-postgres-ssl:latest 
    deployment #2 failed 34 minutes ago: config change
    deployment #1 deployed about an hour ago - 1 pod

The output indicates that it has pulled the image from quay.io.

It is now possible to connect safely from the SSO admin console to the external PostgreSQL database in SSL/TLS mode at the following URL:

https://sso-sso-74-external-db-ssl.apps.example.com/auth

Conclusion

This article showed you how to use Red Hat's single sign-on technology to connect from OpenShift to an external PostgreSQL database over SSL/TLS. We used a new custom SSO Docker image containing the PostgreSQL driver and a JBoss configuration script to connect to the external database in SSL/TLS mode. You can generalize the approach we've followed here to any OpenShift project using Red Hat's single sign-on tool.

Last updated: September 19, 2023