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

On 2023-01-06 Fr 05:17, Jelte Fennema wrote:
> 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.

One reason might be that it doesn't give you any way not to fall back on
the system store. Maybe that's important, maybe not. I don't know that
there would be much extra ease in doing it the other way, you're going
to have to specify some ssl options anyway.

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

+1 for doing this, although I think client certs are less likely to have
been issued by a public CA.

cheers

andrew

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