postgresql16, postgresql17: sslrootcert=system fails

[jawj (George MacKerron)]

The sslrootcert=system option on connection strings passed to psql is broken. For example:

/opt/local/bin/psql17 'postgresql://user:pass@ep-broad-dew-a5k9hi1k.us-east-2.aws.neon.tech/neondb?sslrootcert=system'
psql17: error: connection to server at "ep-broad-dew-a5k9hi1k.us-east-2.aws.neon.tech" (3.131.64.200), port 5432 failed: SSL error: certificate verify failed

This is a great shame, since it blocks wider adoption of this helpful security feature.

To reproduce the issue, simply install postgresql16 or postgresql17 on the latest macOS using the latest MacPorts, then point psql at any free neon.tech database, having swapped sslmode=require for sslrootcert=system on the end of the connection string.

You can verify that it should work using an installation of Postgres 16 or 17 via homebrew.

I haven't figured out exactly why it's broken, but I do have a list of some installations that are and that aren't: ​https://gist.github.com/jawj/57bc9d1f350ffd5250942cf24957b3a7

And this response from the Postgres.app maintainers may be helpful: ​https://github.com/PostgresApp/PostgresApp/issues/801

[jawj (George MacKerron)]

Hey guys. Any thoughts how long it might take to address this? I could be willing to help, but I have no experience with MacPorts so would need some guidance (for instance: is this actually a change that needs making to MacPorts' openssl, and might that have wider ramifications?).

[dgilman (David Gilman)]

Apologies for the delay, had put this off. I was not familiar with this feature at all. Looking at the PostgreSQL source, it looks like it just calls the OpenSSL function SSL_CTX_set_default_verify_paths for this. OpenSSL docs make it sound like that is supposed to be an all-inclusive function that takes care of all the configuration details for you. So I don't think there's anything for PostgreSQL to do here, it's doing what it needs to do with OpenSSL, but the MacPorts OpenSSL's SSL_CTX_set_default_verify_paths is not configuring itself correctly.

I can confirm that libpq is linking against openssl3. I would reassign this ticket to the openssl3 maintainer to see if they have any input?

[jawj (George MacKerron)]

Thanks. I did a small amount of additional digging around OPENSSLDIR:

% /opt/local/bin/openssl version -a
...

OPENSSLDIR: "/opt/local/libexec/openssl3/etc/openssl"
...

% ll /opt/local/libexec/openssl3/etc/openssl
...

lrwxr-xr-x  1 root  admin    40B 18 Feb 09:02 cert.pem -> /opt/local/share/curl/curl-ca-bundle.crt
...

% ll /opt/local/share/curl/curl-ca-bundle.crt
ls: /opt/local/share/curl/curl-ca-bundle.crt: No such file or directory

So then I tried:

sudo port install curl-ca-bundle
/opt/local/bin/psql17 'postgresql://user:pass@ep-broad-dew-a5k9hi1k.us-east-2.aws.neon.tech/neondb?sslrootcert=system'

And now sslrootcert=system works just fine!

So: I think the fix for this problem is probably just to add the port curl-ca-bundle as a dependency for both the postgresql16 and postgresql17 ports. Are you happy to do that?

There's no need to go back further, since Postgres 16 was the first version to support this connection parameter value.

Of course, there might be a case for adding curl-ca-bundle as a dependency of openssl3 as well/instead. But perhaps the openssl3 maintainers have their own reasons for not doing so.

[dgilman (David Gilman)]

Yes I can add it. Agreed it might be time to add it to OpenSSL but I can do it here first.

[jawj (George MacKerron)]

Fantastic — thanks.

[dgilman (David Gilman)]

Digging into this some more, there's a smarter way to set up the dependency so it works with both curl-ca-bundle and certsync which is the MacPorts native way to dump the system cert store. I will set up the dependency to support both, but pull in certsync by default because that's more of the intent here anyway.

[dgilman (David Gilman)]

In 60f294d1ecc7118a0f779dcbf1c8e68532c83d67/macports-ports (master):

postgresql17: add dependency on cert file

Closes: #72080