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

On 9/7/21 10:57 AM, thomas@habets.se wrote:
> On Tue, 7 Sep 2021 15:16:51 +0100, Andrew Dunstan <andrew@dunslane.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.
>
> Such a decision could actually be made by whoever runs the webserver,
> not the database, and the database just reuses the cert and gets a
> free ride for cert renewals.
>
> So in other words postgresql currently doesn't use the system database
> at all, and the workaround is to find and copy from the system
> database. I agree that is a workaround.
>
> If you think this is enough of a corner case that the workaround is
> acceptable, or the added complexity of another sslmode setting isn't
> worth fixing this edge case, then I assume you have more knowledge
> about postgres is used in the field than I do.
>
> But it's not just about today. I would hope that now with LE that
> every user of SSL starts using "real" certs. Postgres default settings
> imply that most people who even enable SSL will not verify the CA nor
> the name, which is a shame.


It would be if it were true, but it's not. In talks I give on
PostgreSQL+SSL I highly recommend people use verify-full. And the CA
doesn't have to be one that's publicly known. We cater for both public
and private CAs.

You don't have to copy anything to achieve what you want. Just set the
sslrootcert parameter of your connection to point to the system file. e.g.

psql "sslmode=verify-full sslrootcert=/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt ..."


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com