> On 25 Apr 2025, at 13:53, Daniel Gustafsson <dan...@yesql.se> wrote:
>>
>>> (2) sslrootcert=system on Windows doesn’t do a thing that would be
>>> extremely useful in some common situations. Namely: connecting securely to
>>> servers that present a certificate signed by a public CA.
>>
>> Just to be clear, does (2) happens when the OpenSSL installation has a bogus
>> OPENSSLDIR value, or does it happen regardless?
>
> I would still like to get clarity on this, do you have any insights here?
I can tell you what happens on my Windows 11 system with Postgres 17 via the
EDB installer, which has a non-bogus OPENSSLDIR.
OpenSSL appears to have been built with OPENSSLDIR="C:\Program Files\Common
Files\SSL".
This is a valid path, the directory exists, and it contains a few *.cnf files.
I’m pretty sure the EDB installer created and populated this directory.
However, the directory contains no certificates, and its location (or
existence) are not advertised anywhere. You would have to know it must exist
somewhere, and then hunt about for it.
(1) In this original state, attempting a connection produces this error:
c:\Program Files\PostgreSQL\17>.\bin\psql.exe
"postgresql://.../neondb?sslrootcert=system"
psql: error: connection to server at "…" (...), port 5432 failed: SSL error:
unregistered scheme
(2) If I create either an empty folder "C:\Program Files\Common
Files\SSL\certs" or an empty file "C:\Program Files\Common Files\SSL\cert.pem",
the error changes:
c:\Program Files\PostgreSQL\17>.\bin\psql.exe
"postgresql://.../neondb?sslrootcert=system"
psql: error: connection to server at "…" (...), port 5432 failed: SSL error:
certificate verify failed
(3) Or if I download the curl/Mozilla certificates list to "C:\Program
Files\Common Files\SSL\cert.pem", the connection then works as expected:
c:\Program Files\PostgreSQL\17>.\bin\psql.exe
"postgresql://.../neondb?sslrootcert=system"
psql (17.3, server 16.8)
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression:
off, ALPN: postgresql)
Type "help" for help.
neondb=>
(But sslrootcert=system is obviously not very useful in this case: I might as
well just point sslrootcert directly at that cert.pem file in whatever location
I like).
