Permission issue using volumes for SSL key/certificate with postgresql container

Hi.

I want to enable SSL for a postgresql deployment(postgres:11). I’m trying to mount the server key and certificate as volumes, so as to avoid having to maintain a custom dockerfile. That may not be the recommended practice?

At any rate, I can’t manage to get the permissions working for the server key. The postgresql documentation states that setting the owner to root and permissions to 0640 should do it, but it doesn’t seem to work. The deployment fails with FATAL: could not load private key file "/etc/postgresql/server.key": Permission denied.

Here’s the output of docker-compose run --rm database ls -l /etc/postgresql:

total 36
-rw-r--r-- 1 1000 1000   827 Mar  8 17:30 pg_hba.conf
-rw-r--r-- 1 1000 1000 24485 Mar  8 22:23 postgresql.conf
-rw-r--r-- 1 root root   863 Mar 11 14:08 server.crt
-rw-r----- 1 root root   916 Mar 11 14:08 server.key

And here’s the service definition in my compose file:

database:
    #https://hub.docker.com/_/postgres/
    image: postgres:11
    restart: always
    environment:
      POSTGRES_PASSWORD: $POSTGRES_PASSWORD
    command: |
      postgres -c 'ssl=on' -c 'config_file=/etc/postgresql/postgresql.conf'
      -c 'hba_file=/etc/postgresql/pg_hba.conf'
      -c 'ssl_cert_file=/etc/postgresql/server.crt'
      -c 'ssl_key_file=/etc/postgresql/server.key'
    volumes:
      - ./database/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - database-data:/var/lib/postgresql/data
      - ./database/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./database/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./database/server.crt:/etc/postgresql/server.crt
      - ./database/server.key:/etc/postgresql/server.key

And here’s the output of docker-compose up database:

Attaching to myproject-dev_database_1
e[36mdatabase_1           |e[0m 2019-03-11 14:53:28.364 GMT [1] FATAL:  could not load private key file "/etc/postgresql/server.key": Permission denied
e[36mdatabase_1           |e[0m 2019-03-11 14:53:28.364 GMT [1] LOG:  database system is shut down
e[36mmyproject-dev_database_1 exited with code 1
e[0m

Thanks!

Hi drpyser, I’m facing a similar problem with my postgres docker container.
I didn’t use a docker compose script but I’ve run it with the following docker run command (I was a newbie at that time) :face_with_hand_over_mouth:

docker run  -d --restart on-failure --net=grafana --name postgres -p 5432:5432 -d -e "POSTGRES_DB=db_name" -e "POSTGRES_USER=myuser" -e "POSTGRES_PASSWORD=mypassword"  -v /home/pi/postgresql/data:/var/lib/postgresql/data postgres

and know (for security reason) I’m wondering if it will be possible to enable SSL on the docker image I’m already running.

Being the Postgres DB the database supporting my running in poroduction Grafana dashoard installation I cannot accept impacts/modfication/deletion of the data stored in the db (that has been mounted outside the docker container with the command “-v /home/pi/postgresql/data:/var/lib/postgresql/data postgres”).
Is there a way to enable the SSL support for the docker image that I’ve already pulled without impacting the existing data stored in the DB?

Any help/ suggestions or feedback is highly appreciated :wink:

Hi. I think I can help you, I’ve since resolved the issue.

First of all, don’t fear about losing your data. As long as you don’t delete the volume directory you’re mounting on the container(/home/pi/postgresql/data), the data is persisted. You can(and should) make backups if you want to be safe(either using pg_dump or simply by copying the data directory after stopping the database).

To enable ssl connections for postgresql, you need to

  • turn on ssl support, either through command line flag or in postgresql.conf file

  • use hostssl in the pghba.conf file to allow grafana connections only using ssl.

  • Put the ssl certificates in the container, and point postgres to them

You can put postgresql.conf and pghba.conf in the data volume, which is where postgres expects the configs to be by default().

For the ssl certificates, there are two ways to handle permissions for the ssl key file:

https://www.postgresql.org/docs/9.6/ssl-tcp.html
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). That setup is intended for installations where certificate and key files are managed by the operating system. The user under which the PostgreSQL server runs should then be made a member of the group that has access to those certificate and key files.
TL;DR:

  • the key is owned by root, and has group read permissions(chmod g+r /path/to/server.key). I think the group should be that of the postgres user in the container, which is 999 by default.

  • the key has octal permission bits set to 0600(chmod 0600 /path/to/server.key). But then, the owner must be the postgres user in the container(chown 999 path/to/server.key).

I find the second solution to be better, in that it doesn’t require sudo to manage the key.

However, an issue is that 999 might be any user and group on your host machine, which you might not want to give those permissions to(especially in a shared environment, or one which you do not control fully). I solved this by changing the uid:gid of the postgres user in the container, using the --user runtime docker flag. However, this also requires mounting a modified /etc/passwd file to make the uid and gid of the postgres user match. see https://hub.docker.com/_/postgres (“Arbitrary --user Note”)

1 Like

Thanks for your prompt and really exhaustive reply. I’ll go through yuor guide and I’ll let you know.
:slightly_smiling_face:

1 Like