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

On Wed, 23 Apr 2025 at 17:47, George MacKerron <george@mackerron.co.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=systembecomes a deprecated synonym for this option. 
>
> (2) sslrootcert=os

Okay I have some time to respond to this thread now. My main thought is this:

I think we should try as hard as possible for sslrootcert=system to do
"the right thing" on all platforms. "the right thing" being: allowing
users to connect to a Postgres server if the cert of that server. It's
a huge shame that MANY users connect to their production Postgres
databases over unauthenticated TLS. I believe the only way to fix that
is by having a *standard* & *secure* connection string that people can
copy paste from their database service provider's portal. Adding new
options that users need to choose between makes it impossible for
database providers to provide such a *standard* connection string
because the exact string will depend on the platform. Which means
they'll continue to only provide sslmode=require in their provided
connstrings. And even if we can somehow avoid that, it will reset the
clock on when most clients will actually support that *standard*
connection string. Thus increasing the time (by at least two years)
before database providers dare to put these options in their default
connection strings, in fear of their customers not being able to
connect and opening support requests or closing their accounts.

But yeah, the discussed situation is problematic for this: Windows
machines have multiple cert stores that could be reasonably considered
the system store.

So I'd like to propose a different way around that problem: Instead
adding more connection options. How about we add a *compile time*
option that allows the person that compiles libpq to choose which cert
store it should use if sslrootcert=system is provided. Something like
--system-cert-store=openssl and --system-cert-store=winstore flags for
./configure. This way users don't have to choose between the various
system stores to get behaviour that is sensible. Which one should be
the default, requires discussion, and maybe we'd want to let that
depend on the OpenSSL version or change it in the future. We could
even make it required for people compiling libpq on Windows (with an
OpenSSl version that supports winstore) to choose between these two
options, by making it a required flag.

Note: This doesn't mean we couldn't allow people to override the
compile time systemstore at runtime with e.g. sslsystemstore=winstore,
but the default would be the one that was chosen at compile time.

> BIGGER IDEA

I would really like to get to a point where libpq by default fails to
connect if you're not connecting to Postgres in a secure way: i.e. one
where you're not both encrypting traffic and authenticating the host
(unless you're connecting over unix sockets and maybe local loopback).
I think there's no point in actually working on/proposing that until
we have a secure connection string that works on all systems (i.e.
what sslrootcert=system is supposed to do)