Re: Making sslrootcert=system work on Windows psql

> On 3 Apr 2025, at 03:21, Jacob Champion <jacob(dot)champion(at)enterprisedb(dot)com> wrote:
>
> On Wed, Apr 2, 2025 at 7:15 AM George MacKerron <george(at)mackerron(dot)co(dot)uk> wrote:
>>> But happily, I don’t think we need to choose. Can’t we just use the Windows system store if neither of the relevant environment variables is set?

The env vars are only overrides for the default, they are not required to be
set, so their presence (or lack thereof) cannot be used to base any decision on
really.

>> Thinking about this a little more, I guess the remaining concern is about people on Windows compiling their own psql from source, using an OpenSSL build that has a meaningful OPENSSLDIR baked in.
>
> Right. In a past life I shipped client stacks on Windows that looked
> kind of like that; I would have been less than happy if a client
> suddenly stopped using the certificate bundle I'd set up.

Agreed. I don't think it's productive to assume that OPENSSLDIR is bogus as a
general rule, OpenSSL sure doesn't. Also, remember that the API we use sets
OpenSSL to use the default dir, file or *store*, not just file/dir:

SSL_CTX_set_default_verify_paths() specifies that the default locations
from which CA certificates are loaded should be used. There is one default
directory, one default file and one default store. The default CA
certificates directory is called certs in the default OpenSSL directory,
and this is also the default store.

org.openssl.winstore isn't by OpenSSL defined as the default even on Windows,
but a future version might change that.

>> My preference would be for "org.openssl.winstore:" to be the compile-time default, though, because the option is called sslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or documented as using OpenSSL’s default CA roots).
>
> If we'd decided to do that from the beginning, maybe... but it looks
> like the winstore URI wasn't released yet when we designed that, so
> "the system" couldn't have meant anything except for OpenSSL. Maybe
> the documentation needs to be more specific now that OpenSSL is
> supporting more stuff.

I don't think we need to be more specific regarding what OpenSSL support, but
in hindsight I wonder if we should be more specific around that "system"
actually means. The attached (untested) small diff tries to make that more
clear. (Line reflow omitted for review ease.)

> Even if we want to change that definition sometime in the future, we'd
> still have to wait at least until OpenSSL 3.1 was no longer supported;
> I don't think it would be very helpful for our definition of "system"
> to change abruptly when upgrading OpenSSL past the 3.2 boundary. All
> this to say, I'd like to support the winstore, but I'm not convinced
> it should take over the existing meaning of "system". Even just adding
> it as a fallback has some risk to any packagers who have gotten it
> working.

Right now sslrootcert can have two different values, a filename or "system". I
don't think altering what "system" means is a good idea, but I also don't think
limiting ourselves to those two values is helpful. We either need to make a
new param. to over time replace sslrootcert with, which can handle multiple
different values; or we need to retrofit a DSL/syntax to sslrootcert for
differentiating. Both have in common that the coding task is magnitudes easier
than figuring out the user experience.

Something to consider for v19 work.

--
Daniel Gustafsson