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.
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.
PostgreSQL is one of the most popular databases out there, it's a relational database. Learn more about PostgreSQL here.
Follow the instructions here to install docker.
Create a folder postgres_ssl with a structure like so:
postgres_ssl | Dockerfile │ certs │ │ | └───out | ssl-conf.sh
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/server.key && chmod 640 /var/lib/postgresql/server.key RUN chown 0:70 /var/lib/postgresql/server.crt && chmod 640 /var/lib/postgresql/server.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/server.crt", "-c",\ "ssl_key_file=/var/lib/postgresql/server.key", "-c",\ "-c", "ssl_ca_file=/var/lib/postgresql/myCA.crt", "-c", "ssl_crl_file=/var/lib/postgresql/myCA.crl" ]
- 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.
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.