could not open certificate file “/root/.postgresql/postgresql.crt”: Permission denied

I went to work on a brilliant new idea for a FreshPorts feature and I immediately hit a snag on my dev system. I noticed that the package importing system had not run anything recently. The reason why was in the logs:

Traceback (most recent call last):
  File "/usr/local/libexec/freshports/./check_repos_for_new_stuff.py", line 36, in <module>
    dbh = psycopg2.connect(DSN)
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "pg03.int.unixathome.org" (10.55.0.34), port 5432 failed: could not stat private key file "/root/.postgresql/postgresql.key": Permission denied

In this post:

FreeBSD 14.0
PostgresQL 16.1
py39-psycopg2-2.9.9
Python 3.9.18

First, why did this start happening? Is this a new server-side configuration change?

I’m positive the problem is the connection is asking for a client certificate. I checked my pg_hba.conf configuration and found no lines mentioning cert.

I started experimenting with psql to verify the DSN worked. Here’s one example:

[21:40 dev-ingress01 dan /usr/local/libexec/freshports] % psql "host=pg03.int.unixathome.org dbname='freshports.dev' user='packager_dev'"                
Password for user packager_dev: 
psql (16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

freshports.dev=>

It took about an hour of reading and searching before I found a solution: sslcertmode

I added sslcertmode=disable to the DSN – problem solved.

Does anyone know why this might have happened?

Fix committed here.