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:”). 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
sslrootcert-system-win.diff
Description: Binary data
