Re: [PATCH] Add `verify-system` sslmode to use system CA pool for server cert

thomas(at)habets(dot)se writes:
> On Tue, 7 Sep 2021 15:16:51 +0100, Andrew Dunstan <andrew(at)dunslane(dot)net> said:
>> can't you specify a CA cert in the system's
>> CA store if necessary? e.g. on my Fedora system I think it's
>> /etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt

> I could, but that seems more like a workaround, where I have to change
> things around as LetsEncrypt switches to another root (I believe they
> have in the past, but I'm not sure), or the server decides to switch
> from LetsEncrypt to something else. Then all clients need to update.

I experimented with loading a real (not self-signed, not from a private
CA) cert into the server, and I can confirm these results when trying
to use sslmode=verify-ca or sslmode=verify-full:

* libpq fails the connection if ~/.postgresql/root.crt is missing
or empty.

* If I put an irrelevant cert into ~/.postgresql/root.crt, then
libpq reports "SSL error: certificate verify failed". So the
verification insists that the server's cert chain to whatever
is in root.crt.

This does seem to make it unreasonably painful to use a real SSL cert
for a PG server. If you've gone to the trouble and expense of getting
a real cert, it should not be on you to persuade the clients that
it's valid. I agree with Thomas that copying the system trust store
into users' home directories is a completely horrid idea, from both
the ease-of-use and maintenance standpoints.

This is not how I supposed it worked, so I'm coming around to the idea
that we need to do something. I don't like the details of Thomas'
proposal though; specifically I don't see a need to invent a new sslmode
value. I think it should just be "if ~/.postgresql/root.crt doesn't
exist, use the system's default trust store".

regards, tom lane