On Wed, May 1, 2024 at 12:31 PM Jacob Champion < jacob.champ...@enterprisedb.com> wrote:
> On Wed, May 1, 2024 at 8:17 AM Thomas Spear <speeddy...@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. I see how that could be true. > 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? > > Right, so just to be clear, all of the details from the other thread was testing done in a container running on Kubernetes, so when adding the intermediate to the "system trust" it was the container's java trust store. When that didn't work, we removed it from the Dockerfile again so the future builds didn't include the trust for that cert. > 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. > > I redid the command line tests to be sure, from Windows command prompt so that I can't rely on my bash command history from AlmaLinux and instead had to type everything out by hand. It does fail to validate for case 4 after all. I must have had a copy/paste error during past tests. With no root.crt file present, the psql command complains that root.crt is missing as well. So then it sounds like putting the MS root in root.crt (as we have done to fix this) is the correct thing to do, and there's no issue. It doesn't seem libpq will use the trusted roots that are typically located in either /etc/ssl or /etc/pki so we have to provide the root in the path where libpq expects it to be to get verify-full to work properly. Thanks for helping me to confirm this. I'll get a case open with MS regarding the wrong root download from the portal in GovCloud. --Thomas
