DEV Community

How to Setup PostgreSQL with SSL inside a Docker Container

Daniel Oluojomu on February 02, 2021

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. ...

Read full post

Neil Moore • Apr 9 '21

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}

Daniel Oluojomu • Aug 28 '21

Thanks a lot Neil!, fixed.

Richard Stanley • Jan 5 '22 • Edited

CMD [ "-c", "ssl=on" , "-c", "ssl_cert_file=/var/lib/postgresql/server.crt",

needs to be:

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

and,

    "ssl_key_file=/var/lib/postgresql/server.key", "-c",\

needs to be:

    "ssl_key_file=/var/lib/postgresql/postgresqldb.key", "-c",\

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

Daniel Oluojomu • Jan 5 '22 • 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!

Richard Stanley • Jan 5 '22

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

Richard Stanley • Jan 5 '22

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

Daniel Oluojomu • Jan 5 '22

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

Abdurrahman Shofy Adianto • May 13 '21

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

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

Daniel Oluojomu • Aug 28 '21

Thanks Abdurrahman!

Ciarán • May 17 '21

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.

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 ">"

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

"-c",\
"-c"

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

Daniel Oluojomu • Aug 28 '21

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

Alexey • Dec 21 '23 • 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.