> On 3 Apr 2025, at 15:26, Daniel Gustafsson <dan...@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 (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
