Unable to connect to PostgreSQL DB as root user when private key is owned by root with permission 640

PostgreSQL Team,

We are using PostgreSQL 13.3 since last December. We are using SSL based connection to connect to PostgreSQL.

Recently we updated to PostgreSQL 13.7 (Please see list of rpms used below).

After update we have noticed an issue when connecting to Database as ‘root’ user when private key file is owned by root and has permission 640.

psql: error: private key file "/swlibrary/keystore/data_store.pem" 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

When using PostgreSQL 13.3, the file ownership is admin:admin with 600 permission. Most of the operations related to DB are performed by ‘admin’. Some operations are performed by ‘root’ user. So, in 13.3 release both ‘admin’ and ‘root’ user were able to communicate with PostgreSQL with this configuration.

root >ls -l /swlibrary/keystore/data_store.pem

-rw-------. 1 admin admin 4600 May 20 10:03 /swlibrary/keystore/data_store.pem

root >export PGDATABASE=avmgmt; export PGUSER=avaya_system_data; export PGSSLCERT=/swlibrary/keystore/data_store.pem; export PGSSLKEY=/swlibrary/keystore/data_store.pem; export PGSSLMODE=verify-ca; export PGSSLROOTCERT=/swlibrary/keystore/default_truststore.pem; /usr/pgsql-13/bin/psql -q -h 127.0.0.1

avmgmt=> select version();

                                                version

--------------------------------------------------------------------------------------------------------

PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

(1 row)

avmgmt=>

After updating the binaries to 13.7, we first saw below error when connecting with root user.

root >ls -l /swlibrary/keystore/data_store.pem

-rw-------. 1 admin admin 4604 May 20 09:52 /swlibrary/keystore/data_store.pem

root >export PGDATABASE=avmgmt; export PGUSER=avaya_system_data; export PGSSLCERT=/swlibrary/keystore/data_store.pem; export PGSSLKEY=/swlibrary/keystore/data_store.pem; export PGSSLMODE=verify-ca; export PGSSLROOTCERT=/swlibrary/keystore/default_truststore.pem; /usr/pgsql-13/bin/psql -q -h 127.0.0.1

psql: error: private key file "/swlibrary/keystore/data_store.pem" must be owned by the current user or root

root >

So, we checked the 13.7 release notes (https://www.postgresql.org/docs/release/13.7/) and found one changelog.

• Make libpq accept root-owned SSL private key files (David Steele)

This change synchronizes libpq's rules for safe ownership and permissions of SSL key files with the rules the server has used since release 9.6. Namely, in addition to the current rules, allow the case where the key file is owned by root and has permissions rw-r----- or less. This is helpful for system-wide management of key files.

As per changelog, we should be able to set private key file ownership to root and set 640 permission. We tried this but we are getting below error.

root >ls -l /swlibrary/keystore/data_store.pem

-rw-r-----. 1 root admin 4604 May 20 09:52 /swlibrary/keystore/data_store.pem

root >export PGDATABASE=avmgmt; export PGUSER=avaya_system_data; export PGSSLCERT=/swlibrary/keystore/data_store.pem; export PGSSLKEY=/swlibrary/keystore/data_store.pem; export PGSSLMODE=verify-ca; export PGSSLROOTCERT=/swlibrary/keystore/default_truststore.pem; /usr/pgsql-13/bin/psql -q -h 127.0.0.1

psql: error: private key file "/swlibrary/keystore/data_store.pem" 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

root >

The release notes clearly mention that if the file is owned by root with 640 permission, such use case will be allowed. Even the error says it.

The only way ‘root’ user can connect to PostgreSQL DB is when the file is owned by root and has permissions 600. But we cannot use this configuration as ‘admin’ user will not be able to access the private_key

root >ls -l /swlibrary/keystore/data_store.pem

-rw-------. 1 root admin 4604 May 20 09:52 /swlibrary/keystore/data_store.pem

root >export PGDATABASE=avmgmt; export PGUSER=avaya_system_data; export PGSSLCERT=/swlibrary/keystore/data_store.pem; export PGSSLKEY=/swlibrary/keystore/data_store.pem; export PGSSLMODE=verify-ca; export PGSSLROOTCERT=/swlibrary/keystore/default_truststore.pem; /usr/pgsql-13/bin/psql -q -h 127.0.0.1

avmgmt=> select version();

                                                 version

---------------------------------------------------------------------------------------------------------

PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

(1 row)

The only log I see is below line. (I had set log_error_verbosity = verbose in postgresql.conf file)

May 20 11:12:56 smgr247 postgres[1712491]: [17-1] 2022-05-20 11:12:56.516 IST [1712491] LOG:  could not accept SSL connection: Success

13.7 rpm used

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/postgresql13-13.7-1PGDG.rhel8.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/postgresql13-server-13.7-1PGDG.rhel8.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/postgresql13-contrib-13.7-1PGDG.rhel8.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/postgresql13-libs-13.7-1PGDG.rhel8.x86_64.rpm

Platform - Red Hat Enterprise Linux release 8.4 (Ootpa)

If you require any more information please do let us know.

P.S. – We have tried update to 13.6 release and we do not see this issue.

Regards,

Yogendra