Deploying PostgreSQL on Openshift with sslmode=verify-full

Recently, we required a PostgreSQL DB service with SSL mode enabled for one of the Packaged Application deployment on Openshift 4.3. An added challenge was to make the PostgreSQL DB running inside the Openshift Cluster accessible publicly. Initially, I thought that it was quite easy as Openshift provides PostgreSQL templates as part of it’s installation. It was only after deploying using the default template, I discovered that it was really difficult to enable SSL on the running pod due to various reasons.

That was the time, I decided to go ahead and create my own Docker image (of course using a Dockerfile) using postgres:10 official image from Dockerhub (Postgres Official Docker Image). What you are going to read next could help either you or many other folks in their Use-cases, where the client application requires to connect to a publicly accessible PostgreSQL running on an Openshift Cluster as a pod with sslmode=verify-full.

Pre-requisites:

1. Access to an Openshift Cluster.
2. Docker Desktop running on Windows 10.
3. Admin access to the Cluster or know the process to reach out to the Cluster Admin.
4. openssl installed on Windows 10.

Note: All the commands were executed on a Windows 10 Powershell unless stated otherwise.

1. First let’s create a basic Dockerfile to create a new image from postgres:10 base image from Docker Hub. We will use this image to deploy Postgresql on Openshift. Also, we will be updating the same image later with other configuration files to be copied and the same could be updated in Openshift image stream for building final version of the Postgresql DB. Put the below content in your Dockerfile:

#Pull the Postgresql Imagefrom Dockerhub
FROM postgres:10

#Database runs on Port 5432
EXPOSE 5432

2. Build an image using the above Dockerfile:

docker build -t your_dockerhub_repository_name/postgresql_ssl .

3. Push the image to your Docker hub repository:

docker push your_dockerhub_repository_name/postgresql_ssl

4. Create a project name postgresql in your Openshift Cluster:

oc new-project postgresql

5. Build a new PostgreSQL pod using the image postgresql_ssl created in Step 2 above. It must be noted here that we are deploying this default postgres image just to expose the service publicly and obtain an External IP. This pod still does not have the required SSL certificates and other configurations.

oc new-app --name postgresql your_dockerhub_repository_name/postgresql_ssl -e PGDATA=/var/lib/postgresql/data/pgdata -e POSTGRES_PASSWORD=anypassword

6. Check if your Postgresql pod is running. Now, let’s expose this service externally/publicly by exposing the deployment config postgresql with type as Load-Balancer. This will provide us an External IP address to access the database publicly:

oc expose dc/postgresql --type=LoadBalancer --name=ingress-postgresql

7. Get the external IP address assigned to the service using:

oc get svc

8. Make a note of the External IP address shown after executing the above command:

9. Let’s connect to the DB service using psql:

Note: The above psql command uses sqlmode=prefer to connect to the DB with ssl if available. It must be noted that no message is displayed about SSL yet. Also, pass your External-IP noted in Step 8 at the masked portion shown above.

10. Let’s create the SSL certificates for the Database pod running. Postgresql uses native openssl certificates. Navigate into the bin directory of your openssl installation (C:\Program Files\OpenSSL-Win64\bin). Edit the file openssl.cfg and add the External IP address under the section [ v3_ca ] against the parameter subjectAltName. Also, do not forget to uncomment the line just updated. The final edit line will look like:

Note: Pass your External-IP noted in Step 8 at the masked portion shown above.

11. Right click on the openssl.exe inside your openssl installation (C:\Program Files\OpenSSL-Win64\bin) and Run as Administrator. Type the following commands to generate the required Root CA keys and certificates to enable SSL on the DB pod. Pass your External-IP noted in Step 8 in place of “External-IP-Address” text used in below command.

req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=External-IP-Address"

x509 -req -in root.csr -text -days 3650 -extfile openssl.cfg -extensions v3_ca -signkey root.key -out root.crt

Note: Please close the openssl command window and re-open it by “Run as Administrator” before executing next set of commands to generate Server keys and certificate files. It seems openssl has a bug in Windows version and does not allow multiple .csr files generation from same session.

12. Type the following commands to generate the required server keys and certificates to enable SSL on the DB pod. Pass your External-IP noted in Step 8 in place of “External-IP-Address” text used in below command.

req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=External-IP-Address"

x509 -req -in server.csr -text -days 365 -extfile openssl.cfg -extensions v3_ca -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

x509 -in root.crt -out ca.pem

13. Step 11 and 12 above will generate many SSL based key and certificate files. Copy the root.crt, server.crt and server.key to a folder “copy_files” under the parent folder where your Dockerfile is located. Also copy the ca.pem file into the parent folder where your Dockerfile is located.

14. Create a file named custom.conf inside the parent folder where your Dockerfile is located with the following content:

ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file = '/etc/postgresql/server.key'
ssl_ca_file = '/etc/postgresql/root.crt'

15. Now, we need to edit the postgresql.conf file which holds all the configuration required for the PostgreSQL DB. I copied this file from the running pod in the Openshift cluster using the below command:

oc cp your_running_pod_name:/var/lib/postgresql/data/pgdata/postgresql.conf postgresql.conf

16. Edit the downloaded file postgresql.conf. Navigate to section named “CONFIG FILE INCLUDES”. Un-comment the parameter “include_if_exists” and add a value against it ‘/opt/app-root/src/postgresql-cfg/custom.conf’. The file will look like:

Note: The configuration will read the values in the custom.conf file available in /opt/app-root/src/postgresql-cfg/ directory inside the running pod and append it’s content to the postgresql.conf file. This will enable SSL mode to ON for the DB pod.

17. Create a file named updateConfig.sh inside the parent folder where your Dockerfile is located with the following content:

#!/usr/bin/env bash

cat /etc/postgresql/postgresql.conf > /var/lib/postgresql/data/pgdata/postgresql.conf

18. Copy the postgresql.conf file edited in Step 16 to the folder “copy_files” under the parent folder where your Dockerfile is located. This folder “copy_files” should have 4 files now:

19. Update the Dockerfile created in Step 1 as shown below:

#Pull the Postgresql Imagefrom Dockerhub
FROM postgres:10

#Database runs on Port 5432
EXPOSE 5432

#Copy postgresql.conf, root.crt, server.crt and server.key files into the container
COPY ./copy_files/* /etc/postgresql/

#Copy updateConfig.sh file into the container
COPY updateConfig.sh /docker-entrypoint-initdb.d/_updateConfig.sh

#Change the Owner and Permission for the server.key file as required by PostgreSQL DB server
RUN chown postgres:postgres /etc/postgresql/server.key /etc/postgresql/server.crt /etc/postgresql/root.crt && chmod 0600 /etc/postgresql/server.key

20. Build the image as done in Step 2:

docker build -t your_dockerhub_repository_name/postgresql_ssl .

21. Push the image again to your Docker hub repository:

docker push your_dockerhub_repository_name/postgresql_ssl

22. When we deployed the Postgresql DB as a pod in Step 5, it also created an image stream named “postgresql” in the same Openshift project we created. We will just update the image stream to tag with the latest image pushed into Docker hub in the above step.

oc import-image postgresql

23. The above step will automatically trigger a new deployment of the PostgreSQL pod. Let’s try to connect to the DB using psql again:

Note: We tried connecting using sslmode=prefer above. This gave us the same result as Step 9. Also, pass your External-IP noted in Step 8 at the masked portion shown above.

24. Let’s connect to the DB using psql with sslmode=verify-ca. This will force ssl connection to the DB:

Note: Please note we are passing the ca.pem file generated in Step 12 as value to the parameter “sslrootcert”. Also, pass your External-IP noted in Step 8 at the masked portion shown above.

25. The above step failed as we have not copied the file custom.conf created in Step 14 and hence, the SSL mode on the DB pod is still not enabled. Let’s do it now. First, we will create a configmap using the file custom.conf inside the Openshift project:

oc create configmap psql-config --from-file=custom.conf

26. Next, let’s add a volume with the configmap created in previous step with a mount-path “/opt/app-root/src/postgresql-cfg” as configured in the postgresql.conf file in Step 16.

oc set volume dc/postgresql --add --name=postgresql-cfg --configmap-name=psql-config --mount-path=/opt/app-root/src/postgresql-cfg

27. The above step will trigger a new deployment of Postgresql pod. But, the deployment will fail. This is related to root permission issue for the container running in Openshift. Let’s fix this quickly. Create a new service account and add it to anyuid Security Context Constraint (SCC). This step is required as the Postgres container requires root permission while running.

oc create serviceaccount postgres

oc adm policy add-scc-to-user anyuid system:serviceaccount:postgresql:postgres

Note: The second command shown above requires Cluster Admin role. Reach out to your Cluster Admin in case you do not have required permission. PostgreSQL requires root access to run the container.

28. Modify the deployment configuration “postgresql” to use the new service account created in Step 27. Use the oc patch command to do this:

$var='{"spec":{"template":{"spec":{"serviceAccountName":"postgres"}}}}'  | ConvertTo-Json

oc patch dc/postgresql -p $var

Note: Windows does not allow to pass JSON path to oc patch commands directly. It shows an invalid JSON error. The above set of 2 commands is a workaround on Windows OS.

29. The above step will trigger an automatic deployment of the Postgresql pod. Let’s connect to the DB using psql with sslmode=verify-ca as tried in Step 24.

Note: This time the message is displayed about the SSL connection made to the DB pod.

30. Let’s connect to the DB using psql with sslmode=verify-full. This will verify the IP address we configured in the SSL certificate in the server.

Note: The login message from psql in Step 29 and 30 looks similar. But, if you try the command in Step 30 with a different ca.pem file, it will fail with a SSL verify error. You can use the ca.pem file for any client which connects to the PostgreSQL DB running as a pod in the Openshift Cluster.

Thanks for reading this long article. Hope this helps many if not all.