Re: Making sslrootcert=system work on Windows psql

> On 25 Apr 2025, at 13:53, Daniel Gustafsson <daniel@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
createdand populated this directory. 

However, the directory contains no certificates, and its location (or existence) are not advertised anywhere. You would
haveto 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\CommonFiles\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
thenworks 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
thatcert.pem file in whatever location I like).