postgresql - Why would I get a PHP/PDOException complaining that it can't make a Postgres connection because it can't find an app-local certificate file?

Question

I've got a PHP app with Postgresql for the datastore, and just migrated the db to a new managed offering provider. Now when I try and run the app on my local development machine (macOS 10.12.6), I encounter the following error trying to connect to the newly situated db:

PDOException: SQLSTATE[08006] [7] could not open certificate file "/var/root/.postgresql/postgresql.crt": Permission denied

There's a couple of interesting things about this:

1. If I try to connect to the same remote database via the psql client, I have no problem, it drops me straight into a psql prompt where I can query away at the DB to my heart's content.

2. I can execute PHP cli scripts that connect to the remote DB and query it without problem. PDO only throws an exception if I'm calling the connection code through a script invoked via an http request through a local Apache 2.4 server.

3. I am not at all shocked that permission is denied to access resources in /var/root, but I am shocked that anything expected to run in userland would ever be checking there. It seems like permission should be denied by default for its contents, even if there was a .postgresql/ subdirectory there (which there isn't).

#1 suggests this is not a pg_hba.conf or other client whitelist or network issue. #3 probably suggests perhaps I should have Apache switch to a non-root user, I'd guess, but beyond that I'm not sure what to make of it, and at any rate I have not placed a ~/.postgresql or postgresql.crt anywhere.

#2 suggests Apache2+libphp7.so doesn't know where to look for postgresql client certs... but the php binary does? This feels like the big clue, but I have no idea why it would be the case or how to fix it.

What are some next steps I could take to figure out how to get the web app connecting?

EDIT:

Prompted by the comment by @jjanes below, it occurred to me to try two things:

a) changing the apache config so that it was running as my personal user to see if the connection worked

b) trying to make a psql connection as root to see if that worked

When both did, that suggested to me that maybe the error message isn't complaining that it can't find a certificate for root (but somehow knows where to look for my personal account), it's that it's freaked out that it couldn't even muster permissions to check under /var/root.

So I did chmod /var/root o+x and mkdir /var/root/.postgresql/ and... it's fine. It didn't need the cert, just needed to have permission to look for it.

This feels like a bug for the sslmode=require level of things, but I'm happy to have a workaround. And I'd still be interested to hear suggestions of other ways to address situations like it.

question from:https://stackoverflow.com/questions/65622914/why-would-i-get-a-php-pdoexception-complaining-that-it-cant-make-a-postgres-con

Answer 1

Waitting for answers