Re: TLS verification to intermediate trust anchor with psql

Thank you, Jacob.

This is a viable alternative and seems reasonable to not change the
behavior.

-- Miro

On Tue, Oct 21, 2025 at 11:45 PM Jacob Champion <
jacob(dot)champion(at)enterprisedb(dot)com> wrote:

> On Tue, Oct 21, 2025 at 6:01 AM Miroslav Pankov
> <miroslav(dot)pankov(at)broadcom(dot)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
>