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.
Top comments (15)
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}
Thanks a lot Neil!, fixed.
needs to be:
and,
needs to be:
thank you for putting this together - certstrap helped save a lot of time also - much appreciated. :-)
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!
lol - my bad - it's so easy to mess up. :-)
...i wrote it correctly in the Containerfile - so... i don't know what's wrong with me today.
Haha, i was asking myself the same thing after reading your comment.
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:
source: gist.github.com/mrw34/c97bb03ea105...
Thanks Abdurrahman!
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
Thanks a lot Ciarán!, i fixed all.
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.
Hi Alexey, yes you do need to request another keypair for the client. The last section in the article explains this.
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?
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?