psycopg.pool don't allow group access rw(0640)

[laozaim]

I understand that typically the private key file needs to be permission 0600 or less. However, the docs also mentions that

Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions).

See 2nd paragraph in this: https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT
In my CA Operator deployed env, I have these permissions:

-rw-r-----. 1 root 1000710000 1155 Mar 21 17:22 CERT
-rw-r-----. 1 root 1000710000 1675 Mar 21 17:22 KEY
-rw-r-----. 1 root 1000710000 1452 Mar 21 17:22 ROOTCERT

The run user is 1000710000 , the files are owned by root user. The group permission are Read, and the world permission in None.
But when I upload a document, I see this in the Backend log:

2023-03-21 20:50:25.398 |    INFO | Q5A7H5 | <tid> | <ont> | ca.api.lib.project_resolver | Running function: query_tenant_info
2023-03-21 20:50:25.400 |    INFO | Q5A7H5 | <tid> | <ont> | ca.api.lib.project_resolver | Query database with project_id to get tenant_id and ontology.
2023-03-21 20:50:25.423 | WARNING | 000000 | <tid> | <ont> | psycopg.pool | error connecting in 'pool-1': connection failed: private key file "/etc/db-certs/KEY" has group or world access; permissions should be u=rw (0600) or less
...
psycopg_pool.PoolTimeout: couldn't get a connection after 30.0 sec

Any idea why the Read permission for group is not allowed? As per the docs, if the file is owned by root, and the run user is not root, it seems like these permissions should be allowed. Thanks!

[dvarrazzo]

Hello,

By looking at your message, it seems that the error you are getting is not the one available in the current libpq: grepping the libpq source I see:

piro@sherekhan:~/dev/fs/postgresql-15.1/src/interfaces/libpq$ ag --cc 'private key.*group or world' 
fe-secure-openssl.c
1403:							  libpq_gettext("private key file \"%s\" has group or world access; file must have permissions u=rw (0600) or less if owned by the current user, or permissions u=rw,g=r (0640) or less if owned by root\n"),

looking where the message changed, pointed me at postgres/postgres@a59c795 which suggests that

• the statement in the doc only regards the server, not the client
• the client also accepts a 0x640 permission, but only from libpq 15.2

I think that the latest version of psycopg-binary was packaged using 15.0, so, if you want to use this feature, you will need to install psycopg-c instead, or no speedup extension, and make sure that libpq at least 15.2 is installed on your client system. I will make sure to upgrade libpq with the next psycopg-c release.

Leaving this open to remember.

[dvarrazzo]

Released in 3.1.11.