Re: Making sslrootcert=system work on Windows psql

On Wed, Apr 2, 2025 at 2:35 AM George MacKerron <george(at)mackerron(dot)co(dot)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).
>
> However, psql via the EnterpriseDB Windows installer still doesn’t support
> sslrootcert=system, 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:”).
>
> Please note the EDB Windows installers for PostgreSQL versions (upto v17)
use OpenSSL v3.0, which is an LTS version. PostgreSQL 18 installer may use
OpenSSL v3.5 depending on the release timeframe.

> 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).
>
>
> # Sources
>
> https://stackoverflow.com/a/79461864/338196
> https://docs.openssl.org/master/man7/OSSL_STORE-winstore/
> https://docs.openssl.org/master/man3/SSL_CTX_load_verify_locations/
>
>
> # Status of sslrootcert=system in various packages providing psql
>
> ## Mac
> Postgres.app — now fixed (
> https://github.com/PostgresApp/PostgresApp/issues/801)
> MacPorts — now fixed (https://trac.macports.org/ticket/72080)
> EDB installer — now fixed (
> https://github.com/EnterpriseDB/edb-installers/issues/264)
> homebrew — was working already
>
> ## Linux
> Debian/Ubuntu — now Recommends ca-certificates (
> https://salsa.debian.org/postgresql/postgresql/-/commit/96077ad61c36386646cdd9b5ce0e423a357ce73b
> )
>
> ## Windows
> EDB installer — in progress
> WSL1, WSL2 (Ubuntu, openSUSE) — was working already
>
>

--
Sandeep Thakkar