Are SSL/TLS certificate Alternate Trust Paths supported?

[tspearconquest]

Hello! We've run into an interesting problem with regards to connecting to a Postgres server with TLS. We've implemented a workaround for it, but we'd like to understand the issue better in order to determine if we can potentially help improve jdbc-postgres in some way, or if we need to revisit our fix and maybe implement something different. Background: My role is as an infrastructure security engineer, so my team builds infrastructure for our org (cloud infrastructure) and it has to meet certain security requirements.

The problem: We're deploying Keycloak which uses jdbc-postgres to make a connection to a postgres server, and if we configure the connection string with sslmode=verify-full then when Keycloak attempts to connect to the DB server, the connection fails because a valid certificate chain can't be built from the certs provided by the server and the system trust store.

We're building the postgres server in Azure. For Azure Postgres servers, Microsoft issues their own root CA (warning: direct download link), and they have intermediate certs which sign the postgres server's certificate. The intermediate certs are cross-signed by both the Microsoft RSA Root CA linked above (which is not necessarily trusted everywhere) and the DigiCert G2 RSA Root CA, which is trusted pretty much globally by OS vendors.

Troubleshooting steps:

1. When we first encountered this issue, our initial solution was to lower the sslmode parameter from verify-full to require to just get the app up and running. That works fine but we want to maximize security so we started looking into the errors and working on a way to get it up with verify-full.
2. Eventually we landed on an error that indicated we needed a root.crt file in PEM format in a certain path, /opt/keycloak/.postgresql/root.crt for this app. Azure offers a way to download the root CA certificate from their portal, so we downloaded it and dropped it into the right path but the error persisted. We eventually found the intermediate cert was missing from the system trust, so we tried adding that without success, and additionally, the root cert we downloaded from the portal was the DigiCert G2 Root, which was already trusted on the system, so adding that to /opt/keycloak/.postgresql/root.crt didn't help.
3. Next we tried to import the MS root certificate linked above into the system java trust store (cacerts file) but found it was already present in there, and we tried importing the intermediate certs both by themselves and bundled with their root certs, and both with and without -trustcacerts but again met the same error.
4. Ultimately, we used openssl s_client -showcerts to connect to the DB server and dump all certs in order to find out what the expected trust chain should be. We found that what worked was to download the certificate linked above and put that in /opt/keycloak/.postgresql/root.crt. Even without the presence of the intermediate certificates, this is all that was required. No modification of the system java trust store was needed.

So my question around alternate trust paths being supported or not comes from the fact that the DigiCert G2 RSA Root cross-signed the intermediate certificate which signed the server cert. We can definitely confirm that jdbc-postgres did not use the java cacerts system trust store file as it should have been able to build a full chain from the public key of the DigiCert G2 Root or the MS Root (since both are present in the cacerts file) if it had been using that.

So -- why didn't dropping the DigiCert G2 root into /opt/keycloak/.postgresql/root.crt work if that cert's private key cross-signed the intermediate that the server sent? Why did we have to put the MS root into the root.crt file? I have to assume that alternate trust paths are either not supported, or not working properly, or that maybe I need to have the MS root added to the OS trust store instead of the java one; but even then -- the DigiCert G2 is there and doesn't allow the chain to be built properly.

Additionally, is this a something that can even be fixed in jdbc-postgres, or is it a limitation in Java that requires a fix upstream? Perhaps I should open a thread upstream to confirm if Java itself supports validating alternate trust paths. Please advise.

[davecramer]

I presume this works with psql ?

[tspearconquest]

That's a great question actually. So I tested a few variations. Caveat: I'm building Keycloak in a Linux container, but I can only test the psql command line from Windows. With that said here's the results:

The default value for sslrootcert= in the command line is %APPDATA%\postgresql\root.crt and when placing the DigiCert G2 Root in that path, it does not validate the chain. When placing the MS RSA Root in that path, it does validate the chain.

HOWEVER, if I explicitly specify sslrootcert= or set the PGSSLROOTCERT environment variable, combined with a file path to either of these certs, I'm able to use either one of these certs to validate the chain.

This leads me to conclude the underlying issue stems from the postgres libraries that back both psql and jdbc-postgres. I'll engage with the postgres devs. Thanks!

[davecramer]

There are quite a few Microsoft folks on the PostgreSQL Hackers list so they should be interested.