public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sandeep Thakkar <[email protected]>
To: George MacKerron <[email protected]>
Cc: [email protected]
Subject: Re: Making sslrootcert=system work on Windows psql
Date: Thu, 3 Apr 2025 16:32:25 +0530
Message-ID: <CANFyU97Kyb9x+HB+FTU=weA+jV+5iR9+oL_-AB_yPrfix2J8dg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Wed, Apr 2, 2025 at 2:35 AM George MacKerron <[email protected]>
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

view thread (12+ 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], [email protected]
  Subject: Re: Making sslrootcert=system work on Windows psql
  In-Reply-To: <CANFyU97Kyb9x+HB+FTU=weA+jV+5iR9+oL_-AB_yPrfix2J8dg@mail.gmail.com>

* 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