Re: Making sslrootcert=system work on Windows psql - Mailing list pgsql-hackers

> On 3 Apr 2025, at 15:26, Daniel Gustafsson <daniel@yesql.se> wrote:
>
>> 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.


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
becomesa 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
thenproceed 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
senseof that without reading the relevant page of the docs. Make asking for sslmode=require an error (with a helpful
explanationpointing 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
randomblog page (which I think is a bare minimum one might aspire to). 


Please do all suggest better ideas!

All the best,
George