Feedback-ID: ic18c41fa:Fastmail
From: George MacKerron <george@mackerron.co.uk>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_E4B2C119-5F0E-436F-8791-802979C9FF73"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\))
Subject: Making sslrootcert=system work on Windows psql
Message-Id: <6C7CE7B4-0565-4840-96FA-A1BE5C8D53AB@mackerron.co.uk>
Date: Tue, 1 Apr 2025 22:05:08 +0100
To: pgsql-hackers@postgresql.org
Archived-At: <https://www.postgresql.org/message-id/6C7CE7B4-0565-4840-96FA-A1BE5C8D53AB%40mackerron.co.uk>
Precedence: bulk


--Apple-Mail=_E4B2C119-5F0E-436F-8791-802979C9FF73
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

I was very pleased to see the sslrootcert=3Dsystem connection option =
added in Postgres 16 (I even blogged about it: =
https://neon.tech/blog/avoid-mitm-attacks-with-psql-postgres-16). But =
sslrootcert=3Dsystem has not been widely supported by psql =
installations, perhaps because people compiling Postgres haven=E2=80=99t =
always been aware of the requirement to point OpenSSL in the direction =
of the system=E2=80=99s root CA certificates.

I=E2=80=99ve 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=E2=80=99t=
 support sslrootcert=3Dsystem, 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-s=
slrootcert-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:=E2=80=9D).

I=E2=80=99m not a Windows or OpenSSL expert, but so far the patched code =
seems to work in theory and in practice (sources below, and I=E2=80=99ve =
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=3Dsystem in various packages providing psql

## Mac
Postgres.app =E2=80=94 now fixed =
(https://github.com/PostgresApp/PostgresApp/issues/801)
MacPorts =E2=80=94 now fixed (https://trac.macports.org/ticket/72080)
EDB installer =E2=80=94 now fixed =
(https://github.com/EnterpriseDB/edb-installers/issues/264)
homebrew =E2=80=94 was working already

## Linux
Debian/Ubuntu =E2=80=94 now Recommends ca-certificates =
(https://salsa.debian.org/postgresql/postgresql/-/commit/96077ad61c3638664=
6cdd9b5ce0e423a357ce73b)

## Windows
EDB installer =E2=80=94 in progress
WSL1, WSL2 (Ubuntu, openSUSE) =E2=80=94 was working already


--Apple-Mail=_E4B2C119-5F0E-436F-8791-802979C9FF73
Content-Disposition: attachment;
	filename=sslrootcert-system-win.diff
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="sslrootcert-system-win.diff"
Content-Transfer-Encoding: 7bit

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());
 

--Apple-Mail=_E4B2C119-5F0E-436F-8791-802979C9FF73--