public inbox for [email protected]  

help / color / mirror / Atom feed

From: George MacKerron <[email protected]>
To: [email protected]
Subject: Making sslrootcert=system work on Windows psql
Date: Tue, 1 Apr 2025 22:05:08 +0100
Message-ID: <[email protected]> (raw)

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



Attachments:

  [application/octet-stream] sslrootcert-system-win.diff (643B, ../[email protected]/2-sslrootcert-system-win.diff)
  download | inline diff:
diff --git a/src/interfaces/libpq/fe-secure-openssl.c b/src/interfaces/libpq/fe-secure-openssl.c
index 5bb9d9779d828..ec5d87773df08 100644
--- a/src/interfaces/libpq/fe-secure-openssl.c
+++ b/src/interfaces/libpq/fe-secure-openssl.c
@@ -840,7 +840,11 @@ initialize_SSL(PGconn *conn)
 		 * further overridden by the SSL_CERT_DIR and SSL_CERT_FILE
 		 * environment variables.
 		 */
+#if defined(WIN32) && OPENSSL_VERSION_PREREQ(3, 2)
+		if (SSL_CTX_load_verify_store(SSL_context, "org.openssl.winstore:") != 1)
+#else
 		if (SSL_CTX_set_default_verify_paths(SSL_context) != 1)
+#endif
 		{
 			char	   *err = SSLerrmessage(ERR_get_error());
 

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Making sslrootcert=system work on Windows psql
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox