Re: TLS verification to intermediate trust anchor with psql - Mailing list pgsql-bugs

On Tue, Oct 21, 2025 at 6:01 AM Miroslav Pankov
<miroslav.pankov@broadcom.com> wrote:
> I would like to raise that per RFC 5280 secton 6.1, TLS verification could be established with a trust anchor which is an intermediate CA and not the root CA in the chain. However, working with psql CLI, sslmode=verify-ca or verify-full, I need to specify sslrootcert to a file containing the root CA.

We can also handle other trust anchors, but the rules for those in
OpenSSL are more complicated than "it exists in sslrootcert". From
[1]:

> A certificate, which may be CA certificate or an end-entity certificate,
> is considered a trust anchor for the intended use if and only if all the
> following conditions hold:
>
> - It is an element of the trust store.
>
> - It does not have a negative trust attribute rejecting the EKU
>   associated with the intended purpose.
>
> - It has a positive trust attribute accepting the EKU associated with
>   the intended purpose or it does not have any positive trust attribute
>   and one of the following compatibility conditions apply: It is
>   self-signed or the -partial_chain option is given (which corresponds
>   to the X509_V_FLAG_PARTIAL_CHAIN flag being set).

You can add that trust attribute yourself, using `openssl x509`:

  $ psql 'sslrootcert=intermediate.crt sslmode=verify-full'
  psql: error: connection to server at "127.0.0.1", port 5432 failed:
SSL error: certificate verify failed
  $ openssl x509 -addtrust serverAuth -in intermediate.crt -out
trusted_intermediate.crt
  $ psql 'sslrootcert=trusted_intermediate.crt sslmode=verify-full'
  postgres=#

This new cert looks slightly different from the original: the PEM
contains "BEGIN TRUSTED CERTIFICATE" and contains additional bits at
the end.

That seems easy enough, if underdocumented. I don't imagine that we
want to start setting _PARTIAL_CHAIN, for the same reason OpenSSL
hasn't switched to that by default [2]: this is long-standing behavior
that should probably not catch people by surprise during an upgrade.
(And as Viktor Dukhovni tells it in that thread, he'd have preferred
that self-signed certificates were not trusted by default either, but
obviously that can't be changed now.)

--Jacob

[1] https://docs.openssl.org/master/man1/openssl-verification-options/#trust-anchors
[2] https://github.com/openssl/openssl/issues/7871