In February 2024 I wrote about could not open certificate file “/root/.postgresql/postgresql.crt”: Permission denied. I fixed it by adding sslcertmode=disable to the connection string, and ended with: does anyone know why this might have happened? That fix has been copied into several scripts since, for example the one in Why does this PostgreSQL \copy fail when launched by the daemon?
Tonight I hit it again, and this time the cause was plain to see. I was running one of the ingress scripts by hand, as the freshports user, on dvl-ingress01:
[0:19 dvl-ingress01 dvl ~/scripts] % sudo su -fm freshports -c 'cd /usr/local/libexec/freshports && ./categories_rebuild_search_html.sh' psql: error: connection to server at "pg01.int.unixathome.org" (10.55.0.150), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
Why is the freshports user looking in /root?
Because of the -m. su -m leaves the environment alone, and that includes HOME. I started from sudo, so HOME was still /root. libpq looks under $HOME for ~/.postgresql/postgresql.crt and ~/.pgpass. The server on pg01.int.unixathome.org asks for a client certificate, so libpq tried /root/.postgresql, and freshports is not allowed in there.
Give the command the right HOME and it works:
[0:23 dvl-ingress01 dvl ~/scripts] % sudo su -fm freshports -c 'cd /usr/local/libexec/freshports && HOME=/var/db/freshports ./categories_rebuild_search_html.sh' [0:23 dvl-ingress01 dvl ~/scripts] %
No output is good output.
sslcertmode=disable is still a fair fix: these scripts authenticate with a password, so there is no certificate to find. But it only covers the certificate. With HOME wrong, libpq also looks for /root/.pgpass, so the password would be missing too. Getting HOME right fixes both.
When periodic runs these scripts, HOME is already the freshports home directory, so it only bites when I run a script by hand. Note to self: when doing that as freshports, set HOME=/var/db/freshports.
