Thread

1. TLS verification to intermediate trust anchor with psql

   Miroslav Pankov <miroslav.pankov@broadcom.com> — 2025-10-21T08:15:29Z

   Hi team.
   
   I would like to raise that per RFC 5280 secton 6.1
   <https://datatracker.ietf.org/doc/html/rfc5280#section-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.
   
   I think the behavior is derived from libpq and openssl. However, I would
   like to raise it for a debate on the reasoning and would appreciate the PG
   team position on it.
   
   NOTE: I am aware that OS-trust works with sslrootcert=system in PG 16+.
   
   Regards.
   Miroslav
   

2. Re: TLS verification to intermediate trust anchor with psql

   Jacob Champion <jacob.champion@enterprisedb.com> — 2025-10-21T20:44:52Z

   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
   
   
   
   

3. Re: TLS verification to intermediate trust anchor with psql

   Miroslav Pankov <miroslav.pankov@broadcom.com> — 2025-11-18T14:11:42Z

   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.champion@enterprisedb.com> wrote:
   
   > 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
   >