Hi guys, in this article I'll be sharing how to set up a PostgreSQL database that'll accept SSL connections only, inside a Docker container.

What's Docker?

Docker is a containerization engine, it allows you to bundle your app and its dependencies into a template file called an image, a running image is called a container. Learn more about Docker here.

What's PostgreSQL?

PostgreSQL is one of the most popular databases out there, it's a relational database. Learn more about PostgreSQL here.

Heads Up

Because OpenSSL is quite complex to use, we'll use certstrap for generation of certificates, install certstrap from here.

Follow the instructions here to install docker.

Structure

Create a folder postgres_ssl with a structure like so:

postgres_ssl
|   Dockerfile
│   certs
│   │   
|   └───out
|   ssl-conf.sh

Let's generate the SSL certificates

We're going to use a custom Certificate Authority(CA), open a terminal in the certs directory, and generate the CA using:

$ certstrap init --common-name myCA

This will create three files in the certs/out directory:

myCA.crt which is the CA certificate,
myCA.key which is the CA certificate key that will sign certificate requests,
myCA.crl which is the Certificate Revocation List(a list of revoked certificates).

Learn more about a Certificate Authority here.

Next, we'll request key pairs from our custom CA.

$ certstrap request-cert --common-name postgresdb  --domain localhost
$ certstrap sign postgresdb --CA myCA

The --domain option adds a list of domains(called Subject Alternative Names) that the generated certificate will be valid for. We set ours to localhost because the database will run on localhost, if yours is running remotely you can add the URL instead.

ssl-conf.sh is a simple bash script that'll clear all default network connection settings in /var/lib/postgresql/data/pg_hba.conf and set it to require SSL for each connection. Add the following content to the ssl-conf.sh:

# echo ssl setting into pg_hba.conf configuration file
echo 'hostssl all all all cert clientcert=verify-ca' > /var/lib/postgresql/data/pg_hba.conf

If you wish to keep the previous settings in the file change > to >>.
This script will be run inside the container.

Now we have our own CA and our server key pair & ssl-conf.sh. Let's write our Dockerfile.

Add the following content to the Dockerfile:

# This Dockerfile contains the image specification of our database
FROM postgres:13-alpine

COPY ./certs/out/postgresdb.key /var/lib/postgresql
COPY ./certs/out/postgresdb.crt /var/lib/postgresql

COPY ./certs/out/myCA.crt /var/lib/postgresql
COPY ./certs/out/myCA.crl /var/lib/postgresql

COPY ./ssl-conf.sh /usr/local/bin

RUN chown 0:70 /var/lib/postgresql/postgresdb.key && chmod 640 /var/lib/postgresql/postgresdb.key
RUN chown 0:70 /var/lib/postgresql/postgresdb.crt && chmod 640 /var/lib/postgresql/postgresdb.crt

RUN chown 0:70 /var/lib/postgresql/myCA.crt && chmod 640 /var/lib/postgresql/myCA.crt
RUN chown 0:70 /var/lib/postgresql/myCA.crl && chmod 640 /var/lib/postgresql/myCA.crl

ENTRYPOINT ["docker-entrypoint.sh"] 

CMD [ "-c", "ssl=on" , "-c", "ssl_cert_file=/var/lib/postgresql/postgresdb.crt", "-c",\
    "ssl_key_file=/var/lib/postgresql/postgresdb.key", "-c",\
    "ssl_ca_file=/var/lib/postgresql/myCA.crt", "-c", "ssl_crl_file=/var/lib/postgresql/myCA.crl" ]

What the Dockerfile entails

• We're using postgres:13-alpine as our base image, this image will be pulled automatically if it isn't available locally.
• Copies our SSL certificate files into the /var/lib/postgresql directory of the image.
• Copy ssl-conf.sh into the /usr/local/bin directory of the image.
• Change file permissions of the certificate files, so as to prevent malicious changes.
• Specify Container Entrypoint, which is what to run on startup.
• The CMD command adds a list of arguments to pass to the Entrypoint command. Learn more about Dockerfile syntax here.

Open a terminal in the postgres_ssl directory and build the Dockerfile:

$ docker build --rm -f "Dockerfile" -t postgres:ssl "."

Then run the image using:

$ docker run -d -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=postgres postgres:ssl

The container should be running now, remember in the Dockerfile we copied ssl-conf.sh into the image's /usr/local/bin directory. We can run ssl-conf.sh like so:

$ docker exec -it postgres_db bash /usr/local/bin/ssl-conf.sh

This will run the bash script inside the container. Of course, you can avoid always typing these using an automation tool like Make.

What next?

To connect to the database in the container you'll need to connect with an SSL connection. To do this, you need to request another key-pair from the custom CA, using the same command we used above. Then use the generated key to connect, the method of doing this can vary with different tools/languages you use.