Re: Making sslrootcert=system work on Windows psql

> On 24 Apr 2025, at 18:45, Jacob Champion <jacob(dot)champion(at)enterprisedb(dot)com> wrote:
>
> On Wed, Apr 23, 2025 at 8:47 AM George MacKerron <george(at)mackerron(dot)co(dot)uk> wrote:
>> 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.
>
> Stealing the word "system" from the existing sslrootcert domain had at
> least two hazards: a) existing users might have a file named "system"
> that would now be ignored, and b) users might accidentally use
> sslrootcert=system on older versions of libpq, picking up an
> unexpected file named "system" and doing the Wrong Thing. Problem (a)
> can be worked around by saying "./system" instead, so honestly I
> wasn't too concerned about that, and I considered (b) to be more of a
> theoretical problem that was outweighed by the benefit of getting
> OpenSSL to just Do The Thing people wanted it to do.
>
> A couple years on, I think (b) is less theoretical than I had
> originally hoped. As evidence I point to Stack Overflow questions like
> [1], where both the asker and the answerer are a bit confused about
> how connection string versioning works. If we steal more words, I
> think that problem is going to get worse. So I'm leaning toward's
> Daniel's earlier position that sslrootcert has kind of run its course,
> and if you want to select OpenSSL stores, we need a more fully
> featured syntax and probably a completely new option to be able to
> pass that through safely.

If we stick to ‘system’ as the only special value, then (b) gets more theoretical with every passing day, as more people upgrade their Postgres installs.

But it’s true that adding a new special value makes it day 0 again. So I guess I’m persuaded that adding new special values is probably not a great idea. That makes me all the keener to get sslrootcert=system working for average Windows users!

> You should ideally tell us what you want, and either get it or fail.

The key thing I want (I am a stuck record on this point!) is a reliably cross-platform way to use the operating system’s trust store when evaluating the credentials of the Postgres server I’m connecting to.

This is what sslrootcert=system promised to be, and sounded like it would be, but turned out not to be on Windows, because for ordinary Windows users (i.e. those who don’t maintain an OpenSSL cert store on their machines) it always fails.

I know the documentation has now been changed to reflect that ‘system’ actually means OpenSSL. But I still think it would be better for it to really mean the operating system. On Windows, that’s the winstore.

Which is why I still think my patch (or perhaps Jelte’s suggestion of a compile-time option, as an alternative) is an improvement on the status quo … ?