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

Huge +1 from me. On Azure we're already using public CAs to sign
certificates for our managed postgres offerings[1][2]. Right now, our
customers have to go to the hassle of downloading a specific root cert or
finding their OS default location. Neither of these allow us to give users
a simple copy-pastable connection string that uses secure settings. This
would change this and make it much easier for our customers to use secure
connections to their database.

I have two main questions:
1. From the rest of the thread it's not entirely clear to me why this patch
goes for the sslrootcert=system approach, instead of changing what
sslrootcert='' means when using verify-full. Like Tom Lane suggested, we
could change it to try ~/.postgresql/root.crt and if that doesn't exist
make it try the system store, instead of erroring out like it does now when
~/.postgresql/root.crt doesn't exist. This approach seems nicer to me, as
it doesn't require introducing another special keyword. It would also
remove the need for the changing of defaults depending on the value of
sslrootcert. NOTE: For sslmode=verify-ca we should still error out if
~/.postgresql/root.crt doesn't exist, because as mentioned upthread it is
trivial to get a cert from these CAs.

2. Should we allow the same approach with ssl_ca_file on the server side,
for client cert validation?

[1]:
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-tls-ssl
[2]:
https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/howto-ssl-connection-security

On Fri, 6 Jan 2023 at 10:42, Jacob Champion <jchampion(at)timescale(dot)com> wrote:

> On Thu, Dec 8, 2022 at 3:10 PM Jacob Champion <jchampion(at)timescale(dot)com>
> wrote:
> > For now, it's worked around in v4. This should finally get the cfbot
> > fully green.
>
> Cirrus's switch to M1 Macs changed the Homebrew installation path, so
> v5 adjusts the workaround accordingly.
>
> --Jacob
>