Re: Making sslrootcert=system work on Windows psql

> On 3 Apr 2025, at 14:41, George MacKerron <george@mackerron.co.uk> wrote:

> (2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations.
Namely:connecting securely to servers that present a certificate signed by a public CA. 

Just to be clear, does (2) happens when the OpenSSL installation has a bogus
OPENSSLDIR value, or does it happen regardless?

> Your diff certainly fixes (1b), so it’s definitely an improvement.

Thanks, unless Jacob objects I propose to apply that backpatched down to when
sslrootcert=system went in.

> (2) Your idea of a new parameter, or a new value of sslrootcert, is what I was also starting to mull this morning. Is
thereany chance at all this could be done for Postgres 18 

Not really, a net new patch mere days before the feature freeze is not really
how we do development.  Especially for security related work on a platform most
developers aren't intimately familiar with.

> or, failing that, 18.1?

Minor revisions only ever get bugfixes, never features or changed behaviour of
features.

> I quite like sslrootcert=os: it’s snappy, and it implies that the Operating System root certs are going to be used
(whichis what I would have liked sslrootcert=system to mean). Some possible alternatives might be
sslrootcert=public-casor sslrootcert=os-default. 

The thing is, we don't know that sslrootcert=os will mean the operating system
root certs.  We are limited to what the OpenSSL API provides, and what can ask
OpenSSL to do is use its defaults.

> The key thing is that it should work out-of-the-box basically everywhere, so my preferred behaviour for it would be:
>
> * On Windows, use the Windows built-in cert store (per my original patch).
>
> * On Mac and Linux, just do the exact same thing sslrootcert=system currently does.
>
> Is there any realistic prospect of this?

IMV there isn't.  I can't see it being an improvement to switch the meaning of
a value based on the underlying OpenSSL version, especially since the current
meaning might be useful for some installations who would then lose that
ability.  I am convinced we need to do be able to use the defaults (as we do
now) *and* use winstore and whatever new stores come, not that one replaces the
other.

> I appreciate that it’s not the result of a lengthy, thorough and principled UX evaluation. On the other hand, it’s a
fewlines of code that could enable a pretty big improvement in security for many users’ Postgres connections much
sooner.

To be clear, wanting to make postgres more secure is a Good Thing, and your
efforts are much appreciated!  Don't take no's in this thread as an objection
to your idea and mission.  Most likely we will support winstore in some way in
v19, we just need to make sure we develop features in a way which is
sustainable wrt our available resources and our development process.

--
Daniel Gustafsson