Deploying postgresql docker with ssl certificate and key with volumes

It is possible to mount the key and certificate into the postgres container, and for postgres to use them from there. But you will have to face the issue with the owner and permissions of the server.key.

From the PostgreSQL Documentation on this subject:

On Unix systems, the permissions on server.key must disallow any access to world or group; achieve this by the command chmod 0600 server.key. Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions).

This means that you have to:

1. Set the owner of the server.key file to either root or postgres.
2. Depending on the owner of the server.key file, you will have to set respectively 600 or 640 permissions on it. (Update: It is implied here, that the group owner of the file, is a group that contains the postgres user, like the default postgresgroup)

If you are working from a Windows host, you will have a hard time with this. Because the permissions on any file in the volume that you map into the container will be -rwxr-xr-x (755), and the owner will be root. And you will not be able to change this as long as the file is mounted from your windows volumes. If you try using chmod on the file, it will just fail silently.

If you on the other hand are on a linux host, this can be done with minimum effort. The permissions from the host system will be preserved into the image. And the ownership will too, litterally. By this I mean, that the numerical owner and group owner of the server.key will be preserved when they are volume mapped into the container. Between the host and the container, they share the linux ACL, so they are just observing the same properties of the files. (Owner, group owner, permissions). So if your local linux user on the host machine has UID:GID 1000:1000, and you create the server.key file, then the UID:GID of the file will also be set to 1000:1000. If you then map the file into the container, and observe it from inside - it will also just see 1000:1000. This means, that we can control the UID:GID from both inside and outside the container, when mapped from a linux host.

Note. There does not have to be a user with the UID that you assign as owner on a file, it is allowed to set non existent UID:GID owners of files.

In the postgres alpine derivative image, the postgres user/group has UID:GID 70:70. On the debian derivative the postgres UID:GID is 999:999. And not supprising, root has 0:0 on both of them.

This means either have to:

1. Change the UID:GID of the file server.key after we start the container, when the volume is already mounted.
2. Change the UID:GID of the file server.key before we start the container

Since setting this after the container starts, would imply tampering with the startup scripting of the postgres image - let's opt to set them before we start the container. In the local filesystem where you are mounting them from.

Setting 600 permissions and postgres as owner of the server.key

In case you are going with the alpine derivative, you need to change the owner/group to 70:70. If you are using the debian derivative, then 999:999.

There may not be a user on your host with for example UID: 70, but that is not a problem.

Example:

chown 70:70 server.key # 70:70 for alpine, 999:999 for debianchmod 600 server.key

Setting 640 permissions and root as owner of the server.key

This example is also for the alpine image

Example:

chown 0:70 server.keychmod 640 server.key

At this point you are good to go. You just need to map the key and certificate into the container, and start postgres like always.

Solution (linux/unix/macOS)

I will include a script snippet here, that will do all of this for you for the alpine derivative. This example will set the root owner of the server.key and the postgres group owner.

# generate the server.key and server.crtopenssl req -new -text -passout pass:abcd -subj /CN=localhost -out server.reqopenssl rsa -in privkey.pem -passin pass:abcd -out server.keyopenssl req -x509 -in server.req -text -key server.key -out server.crt# set postgres (alpine) user as owner of the server.key and permissions to 600chown 0:70 server.keychmod 640 server.key# start a postgres docker container, mapping the .key and .crt into the image.docker run -d --name postgres \   -v "$PWD/server.crt:/var/lib/postgresql/server.crt:ro" \  -v "$PWD/server.key:/var/lib/postgresql/server.key:ro" \  postgres:11-alpine \  -c ssl=on \  -c ssl_cert_file=/var/lib/postgresql/server.crt \  -c ssl_key_file=/var/lib/postgresql/server.key

I hope this clears things up?

Source of the key generating key and certificate is this gist.

Building the image yourself (Windows solution)

I will include a small guide to how you can build an image yourself, so that you can have a postgres database container with ssl. This will work on Windows aswell.

Here's the Dockerfile that will do it for you:

Dockerfile

FROM postgres:11-alpine# On Windows root will own the files, and they will have permissions 755COPY server.key /var/lib/postgresql/server.keyCOPY server.crt /var/lib/postgresql/server.crt# update the privileges on the .key, no need to touch the .crt  RUN chmod 600 /var/lib/postgresql/server.keyRUN chown postgres:postgres /var/lib/postgresql/server.key

Build the image with:

docker build -t mypg:01 .

And run with:

docker run -d --name postgres mypg:01 \  -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt \  -c ssl_key_file=/var/lib/postgresql/server.key

In my particular case I was interested in enabling SSL while using the standard postgres image via docker-compose. This solution allows initdb to run as usual which is useful for setting up the DB and users.

docker-compose.yaml

version: '3'services:  postgres:    image: postgres:12.2    environment:      - POSTGRES_PASSWORD=password      - POSTGRES_USER=myuser      - POSTGRES_HOST_AUTH_MTHOD=trust    volumes:      - ./postgres-initdb:/docker-entrypoint-initdb.d/      - ./postgres-certs/:/var/lib/postgresql/certs/

postgres-initdb/config.sql

ALTER SYSTEM SET ssl_cert_file TO '/var/lib/postgresql/certs/server.crt';ALTER SYSTEM SET ssl_key_file TO '/var/lib/postgresql/certs/server.key';ALTER SYSTEM SET ssl TO 'ON';

This will work for any configuration. For SSL you will also want to generate the certs (taken from a Gist):

set -euo pipefailmkdir postgres-certscd postgres-certsopenssl req -new -text -passout pass:abcd -subj /CN=localhost -out server.req -keyout privkey.pemopenssl rsa -in privkey.pem -passin pass:abcd -out server.keyopenssl req -x509 -in server.req -text -key server.key -out server.crtchmod 600 server.keytest $(uname -s) == Linux && chown 999 server.key