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

> On 3 Apr 2025, at 11:41, Daniel Gustafsson <daniel@yesql.se> wrote:
>
> org.openssl.winstore isn't by OpenSSL defined as the default even on Windows,
> but a future version might change that.

Right — there’s definitely an argument that OpenSSL should in future make it possible to have this be the default via a
compile-timeoption, at least. 


> 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.)

I guess my issue here was twofold:

(1) sslrootcert=system on Windows doesn’t do what it says on the tin. In other words, it doesn’t do (a) what it sounds
likeit does or (b) what it says it does in the docs. 

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

Your diff certainly fixes (1b), so it’s definitely an improvement. But of course it does nothing for (2). :(


> 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.

To give a bit of context here, my feeling is that the widespread use of sslmode=require is a pretty serious security
problemin the Postgres community. I strongly suspect many users don’t realise that it offers no protection _at all_
againstMITM attacks. I know it took me a while to figure that point out, because sslmode=require just _sounds_
reassuringlysecure. 

That’s why I was so pleased to read about sslrootcert=system in Postgres 16: I thought it was going to improve this
situation.But sslrootcert=system (or similar) isn’t going to be widely used until Postgres providers put it in their
connectionstrings, and Postgres providers aren’t going to put it in their connection strings until it has a damn good
chanceof just working. 

On Linux and Mac, I would say the ‘damn good chance of just working’ bar has now been reached. But on Windows, I
suspecta _lot_ of devs are using psql as installed by the EDB Installer (it’s the only option listed at
https://www.postgresql.org/download/windows/,after all). So until that works, sslrootcert=system (or similar) is going
toremain a no-go. 

What I am saying is: it would be _really_ nice not to have to wait another whole release cycle to get a level of
securityon many people’s Postgres connections that’s simply on par with the security of visiting some random web page. 

So: what can be done?

(1) I could ask the EDB installer guys if they’re willing to apply my patch to the Postgres source as part of their
buildprocess, so as to use the Windows store in this one case. Personally, I think that would be a clear improvement;
butI don’t know if they’ll like the idea. Based on Sandeep’s comment, it seems this is also dependent on OpenSSL 3.5
(LTS)becoming available prior to the Postgres 18 release. 

(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 or, failing that, 18.1? 

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 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? 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. 

(3) Any other ideas?

--
George MacKerron