could not load server certificate file "/var/lib/postgresql/certificate.crt": Permission denied

[mingmingshiliyu]

i tried use chmod 777,700,600,650, chown postgres:postgres,but neither of them work

[yosifkit]

The server cert needs to be readable by the user that the database is running as (which is user and group id 999 by default). So, it either needs other/world readable, or a user or group ownership of postgres (999) along with read permissions. Alternatively, setting ownership of the certs and database files to a different user id (say 1000, or 65000) and then running the container with --user 1000:1000 or similar would also work.

It is a bit annoying that the permissions of the server cert are not checked and they just try to read it, but if the permissions are wrong on the private key, then this is the error:

FATAL: private key file "/var/lib/postgresql/cakey.pem" has group or world access
DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root.

Working scenarios:

$ # Works
$ ls -ln
-rw-------  1  999 1000 2297 Jan  6 14:08 cert.crt
-rw-------  1  999 1000 3272 Jan  6 14:08 private.key
drwx------ 19  999 1000 4096 Jan  6 14:58 data
$ docker run ... postgres:14 ...

$ # also works
-rw-r-----  1 1000  999 2297 Jan  6 14:08 cert.crt
-rw-------  1  999 1000 3272 Jan  6 14:08 private.key
drwx------ 19  999 1000 4096 Jan  6 14:58 data
$ docker run ... postgres:14 ...

$ # should also work
-rw-r--r--  1    0    0 2297 Jan  6 14:08 cert.crt
-rw-r-----  1    0  999 3272 Jan  6 14:08 private.key
drwx------ 19  999 1000 4096 Jan  6 14:58 data
$ docker run ... postgres:14 ...

$ # also works
-rwxrwx---  1 1000 1000 2297 Jan  6 14:08 cert.crt
-rw-------  1 1000 1000 3272 Jan  6 14:08 private.key
drwx------ 19 1000 1000 4096 Jan  6 14:58 data
$ docker run ... --user 1000:1000 postgres:14 ...