Re: Making sslrootcert=system work on Windows psql

On Tue, Apr 1, 2025 at 2:05 PM George MacKerron <george@mackerron.co.uk> wrote:
>
> I was very pleased to see the sslrootcert=system connection option added in Postgres 16 (I even blogged about it: https://neon.tech/blog/avoid-mitm-attacks-with-psql-postgres-16). But sslrootcert=system has not been widely supported by psql installations, perhaps because people compiling Postgres haven’t always been aware of the requirement to point OpenSSL in the direction of the system’s root CA certificates.
>
> I’ve recently been trying to get it more widely supported, with some success (details at end of this message).

(Thank you!)

> However, psql via the EnterpriseDB Windows installer still doesn’t support sslrootcert=system,

Hm. I've been in contact with Kritika recently for the EDB macOS
fixes; hopefully we can get something figured out for Windows too.

> and I think a tiny patch is needed. The diff is attached, and can be seen in context here: https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows
>
> Essentially, on Windows with OpenSSL 3.2+, it replaces SSL_CTX_set_default_verify_paths(SSL_context) with SSL_CTX_load_verify_store(SSL_context, "org.openssl.winstore:”).
>
> I’m not a Windows or OpenSSL expert, but so far the patched code seems to work in theory and in practice (sources below, and I’ve compiled and tested it working on Windows 11 x64).

While this will get things working -- if you plan to use the Windows
store! -- I worry that it's an incompatible change, and anyone who is
actually happy with the way things currently work (i.e. not using the
EDB installers) will be broken. The meaning of `sslrootcert=system` is
"do whatever OpenSSL wants to do by default." That includes
modification by the OpenSSL environment variables, which (I think)
this patch disables.

The winstore is new to me. Is there no way to get OpenSSL to switch
its default store without code changes?

--Jacob