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

General
3

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:

PostgreSQL: Documentation: 9.6: Secure TCP/IP Connections with SSL
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 Docker (“Arbitrary --user Note”)