How to Secure a Connection between Exalate and a PostgreSQL Database in Docker

Introduction

Assume you are bringing up an Exalate on docker, and you have the need to secure the connection between the application and the database using SSL.

With the standard configuration you will encounter the following error:

Caused by: org.postgresql.util.PSQLException: FATAL: SSL connection is required. Please specify SSL options and retry.

Or alternatively, you want to be sure that PostgreSQL is accessed over SSL.

Setting up PostgreSQL using SSL

Configure the docker-compose to bring up PostgreSQL

Adapt the docker-compose.yml such that PostgreSQL comes up in an SSL mode:

version: '2'
 
services:
  database:
    restart: unless-stopped
    volumes:
      - ./persist/db:/var/lib/postgresql/data
      - ./createdb.sh:/docker-entrypoint-initdb.d/init-user-db.sh
            #
            # provide the certificate and the key to the postgres server
           #

      - ./ca/server.crt:/var/lib/postgresql/server.crt
      - ./ca/server.key:/var/lib/postgresql/server.key
    image: postgres:alpine

    #
    # ensure postgres is coming up with ssl mode on
    #
    command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: user
      POSTGRES_PASSWORD: secret
    environment:
      - POSTGRES_PASSWORD=password
      - DB_NAME=snownode
      - DB_USER=idalko
      - DB_PASS=idalko
    networks:
      - database

networks:
  database:
    driver: bridge
  default:
    driver: bridge

Create the certificates

You can create self-signed certificates as follows

# Store the certificates in a specific folder on your host
mkdir ca
cd ca

# use openssl to generate the certificates

openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt

# change ownership and permissions.  It depend on the underlying operating system.  Userid 70 is postgres on the postgres:alpine image

sudo chown 70:70 server.key 
sudo chmod 600 server.key

cd ..
docker-compose up -d database

Validate

We like to validate if it works before moving on

# assuming that the database 'snownode' has been setup. if there is another database - use that
docker exec -it <name of the container running the database> /bin/bash
psql -U idalko -h localhost snownode 

It must confirm that the SSL is enabled

bash-5.0# psql -U idalko -h localhost snownode
psql (12.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

An additional check is to do a PLSQL command

bash-5.0# psql -U idalko -h localhost snownode
psql (12.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

snownode=# show ssl
snownode-# ;
ssl
-----
on

Configure the app to access the database using SSL

The only configuration to be added to the docker-compose is by specifying that the PGSSLMODE is required

The adapted docker-compose looks like

version: '2'
 
services:
  database:

# <snip>
 
  snownode:
    restart: unless-stopped
    ports:
      - 9000:9000
    image: idalko/snownode:5.0.19
    depends_on:
      - database #wait for postgres to be started, not for ready
    volumes:
      - ./persist/home:/opt/snownode/data
    environment:

#ensure that the connection to the database is using SSL
      - PGSSLMODE="require"
      - SNOWNODE_PORT=9000
      - SNOWNODE_PG_HOST=database
      - SNOWNODE_PG_DB=snownode
      - SNOWNODE_PG_USER=idalko
      - SNOWNODE_PG_PWD=idalko
    networks:
      - database
      - default

 
networks:
  database:
    driver: bridge
  default:
    driver: bridge