On Wed, May 1, 2024 at 8:17 AM Thomas Spear <speeddymon@gmail.com> wrote:
> Circling back to my original question, why is there a difference in behavior?
>
> What I believe should be happening isn't what's happening:
> 1. If ~/.postgresql/root.crt contains the MS root, and I don't specify sslrootcert= -- successful validation
> 2. If ~/.postgresql/root.crt contains the MS root, and I specify sslrootcert= -- successful validation
> 3. If ~/.postgresql/root.crt contains the DigiCert root, and I don't specify sslrootcert= -- validation fails
> 4. If ~/.postgresql/root.crt contains the DigiCert root, and I specify sslrootcert= -- successful validation
Number 4 is the only one that seems off to me given what we know. If
you're saying that the server's chain never mentions DigiCert as an
issuer, then I see no reason that the DigiCert root should ever
successfully validate the chain. You mentioned on the other thread
that
> We eventually found the intermediate cert was missing from the system trust, so we tried adding that without success
and that has me a little worried. Why would the intermediate need to
be explicitly trusted?
I also notice from the other thread that sometimes you're testing on
Linux and sometimes you're testing on Windows, and that you've mixed
in a couple of different sslmodes during debugging. So I want to make
absolutely sure: are you _certain_ that case number 4 is a true
statement? In other words, there's nothing in your default root.crt
except the DigiCert root, you've specified exactly the same path in
sslrootcert as the one that's loaded by default, and your experiments
are all using verify-full?
The default can also be modified by a bunch of environmental factors,
including $PGSSLROOTCERT, $HOME, the effective user ID, etc. (On
Windows I don't know what the %APPDATA% conventions are.) If you empty
out your root.crt file, you should get a clear message that libpq
tried to load certificates from it and couldn't; otherwise, it's
finding the default somewhere else.
--Jacob
