> On 3 Apr 2025, at 11:41, Daniel Gustafsson <dan...@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-time option, 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 like it 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: connecting securely 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 problem in the Postgres community. I strongly suspect many users don’t realise that it offers no protection _at all_ against MITM attacks. I know it took me a while to figure that point out, because sslmode=require just _sounds_ reassuringly secure. 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 connection strings, and Postgres providers aren’t going to put it in their connection strings until it has a damn good chance of 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 suspect a _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 to remain 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 security on 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 build process, so as to use the Windows store in this one case. Personally, I think that would be a clear improvement; but I 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 there any 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 (which is what I would have liked sslrootcert=system to mean). Some possible alternatives might be sslrootcert=public-cas or 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
