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:
- Configure SSO to connect to the PostgreSQL server in SSL/TLS mode.
- 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:
- Build a new SSO Docker image using the JBoss command file shown previously.
- Deploy Red Hat's SSO on OpenShift using the standard
sso74-x509-httpstemplate. - 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:
Dockerfilepostgresql-42.2.23.jarsso-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