DEV Community

Cover image for How to Setup PostgreSQL with SSL inside a Docker Container
Daniel Oluojomu
Daniel Oluojomu

Posted on • Edited on

How to Setup PostgreSQL with SSL inside a Docker Container

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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" ]
Enter fullscreen mode Exit fullscreen mode

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 "."
Enter fullscreen mode Exit fullscreen mode

Then run the image using:

$ docker run -d -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=postgres postgres:ssl
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (15)

Collapse
 
neilcamoore profile image
Neil Moore

There are a couple of typos, I think. The certificates generated are called postgresql.{key,crt} but the Dockerfile refers to them as server.{key,crt}

Collapse
 
danvixent profile image
Daniel Oluojomu

Thanks a lot Neil!, fixed.

Collapse
 
audstanley profile image
Richard Stanley • Edited
CMD [ "-c", "ssl=on" , "-c", "ssl_cert_file=/var/lib/postgresql/server.crt",
Enter fullscreen mode Exit fullscreen mode

needs to be:

CMD [ "-c", "ssl=on" , "-c", "ssl_cert_file=/var/lib/postgresql/postgresqldb.crt",
Enter fullscreen mode Exit fullscreen mode

and,

    "ssl_key_file=/var/lib/postgresql/server.key", "-c",\
Enter fullscreen mode Exit fullscreen mode

needs to be:

    "ssl_key_file=/var/lib/postgresql/postgresqldb.key", "-c",\
Enter fullscreen mode Exit fullscreen mode

thank you for putting this together - certstrap helped save a lot of time also - much appreciated. :-)

Thread Thread
 
danvixent profile image
Daniel Oluojomu • Edited

Actually its postgresdb., but thanks a lot Richard. I've fixed that also.

Almost a year i wrote this. Need to put out something on cryptography again!

Thread Thread
 
audstanley profile image
Richard Stanley

lol - my bad - it's so easy to mess up. :-)

Thread Thread
 
audstanley profile image
Richard Stanley

...i wrote it correctly in the Containerfile - so... i don't know what's wrong with me today.

Thread Thread
 
danvixent profile image
Daniel Oluojomu

Haha, i was asking myself the same thing after reading your comment.

Collapse
 
azophy profile image
Abdurrahman Shofy Adianto

Just want to add that debian/ubuntu image already included a self-signed certificate. So instead of generate it on your own, you could just use whats already available:

docker run \
  --rm \
  -e POSTGRES_PASSWORD=password \
  postgres:12 \
  -c ssl=on \
  -c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem \
  -c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
Enter fullscreen mode Exit fullscreen mode

source: gist.github.com/mrw34/c97bb03ea105...

Collapse
 
danvixent profile image
Daniel Oluojomu

Thanks Abdurrahman!

Collapse
 
_naraic profile image
Ciarán

Daniel, first off I want to thank you for taking the time to write this up. I was able to eventually get an SSL PG DG up and running in my Docker environment.

A couple of things to point out especially for anyone else following this guide.

1)

You say...
"If you wish to keep the previous settings in the file change >> to >"

You have this backwards. If you only want to append to a file (keep previous settings) you use ">>" and if you want to clear the file and insert your piece, then you use ">"

2)

There is an extra "-c" in your snippet above. One of them needs to be removed to get this to work.

"-c",\
"-c"

3)

I had to add in some additional steps to the ssl-conf because my certs didn't get mapped correctly. This should of course be fixed in the dockerfile, but it was easier for my troubleshooting to just edit this file and continue to re-execute it.

echo 'ssl_ca_file='\''/var/lib/postgresql/data/CertAuth.crt'\''' >> /var/lib/postgresql/data/postgresql.conf
echo 'ssl_cert_file='\''/var/lib/postgresql/data/postgresdb.crt'\''' >> /var/lib/postgresql/data/postgresql.conf
echo 'ssl_key_file='\''/var/lib/postgresql/data/postgresdb.key'\''' >> /var/lib/postgresql/data/postgresql.conf

Other than that, I learned a tonne off your guide so thanks again! :D

_Naraic

Collapse
 
danvixent profile image
Daniel Oluojomu

Thanks a lot Ciarán!, i fixed all.

Collapse
 
grachev profile image
Alexey • Edited

Hello, thank you for the article.
Question:
I have a docker container with python which connects to postgres over psycopg2.
My goal is to make the connection ssl-secure. Do I need another keys, I mean client keys or smth for sslmode = require?
I have both containers described in docker-compose. Try to implement your solution.
Thanks.

Collapse
 
danvixent profile image
Daniel Oluojomu • Edited

Hi Alexey, yes you do need to request another keypair for the client. The last section in the article explains this.

Collapse
 
paulushcgcj profile image
Paulo Gomes da Cruz Junior

Hey man, nice article, I'm just worried about baking the certs into the image as it can be copied and this will be a problem.

Any tips on how to avoid this?

Collapse
 
slidenerd profile image
slidenerd • Edited

how would you run ssl-conf.sh if you had a docker-compose file that refers to this postgres dockerfile. Also where are the client certs?