Re: Making sslrootcert=system work on Windows psql

> On 3 Apr 2025, at 15:26, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
>
>> I quite like sslrootcert=os: it’s snappy, and it implies that the Operating System root certs are going to be used (which is what I would have liked sslrootcert=system to mean). Some possible alternatives might be sslrootcert=public-cas or 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 few lines 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.

Thanks for your appreciation! It might be good to start thinking about how things might look in v19, then?

Perhaps I can start things off with one smaller idea and one bigger one.

SMALLER IDEA

I’d suggest two new special sslrootcert values:

(1) sslrootcert=openssl

This does exactly what sslrootcert=system does now, but is less confusingly named for Windows users. sslrootcert=system becomes a deprecated synonym for this option.

(2) sslrootcert=os

This does what I was proposing in my patch: it uses winstore on Windows and behaves the same as sslrootcert=openssl elsewhere, where openssl *is* the operating system SSL provider.

These changes would be fully backwards-compatible.

BIGGER IDEA

A much bigger, backwards-incompatible shake-up of libpq security parameters might incorporate the above changes, and then proceed something like this:

* Entirely remove the current default, sslmode=prefer, and make explicitly asking for sslmode=prefer an error. After all, as the docs themselves point out for sslmode=prefer: “this makes no sense from a security point of view”.

* Rename sslmode=require to sslmode=insecure, because it’s vulnerable to MITM attacks, and ideally people would get a sense of that without reading the relevant page of the docs. Make asking for sslmode=require an error (with a helpful explanation pointing out the rename to sslmode=insecure).

* Retain sslmode=verify-ca and sslmode=verify-full.

* Create a new option, sslmode=secure, which means sslmode=verify-full + sslrootcert=os. Make this the default!

In summary, you end up with these as sslmode values:

* disabled
* insecure (formerly known as require)
* verify-ca
* verify-full
* secure (the new default, meaning sslmode=verify-full + sslrootcert=os)

Obviously this would need to be well-trailed ahead of time, as some people would need to make changes to how they use psql/libpq. But it would peg the default security of a Postgres connection at the same level as the security of any random blog page (which I think is a bare minimum one might aspire to).

Please do all suggest better ideas!

All the best,
George