Subject: Re: could not accept ssl connection tlsv1 alert unknown ca

From: Adrian Klaver <[email-masked]>
Date: Fri, 31 Jan 2025 08:37:24 -0800

On 1/31/25 00:57, Zwettler Markus (OIZ) wrote:
>> Von: Tom Lane <[email-masked]>

>> Those cause some additional checks to be made, but it's not like you can expect a
>> completely broken certificate to work without them.
>>
>>                          regards, tom lane
> 
> 
> 
> I don't understand why Postgres does a certificate validation with “sslmode=prefer”. Postgres should simply ignore every presented client certificate here. Regardless of whether it is trusted or not.

What are the relevant lines in pg_hba.conf?

> 
> A certificate validation should only take place in the modes “sslmode=verify-ca” and “ssmode=verify-full”. Only here should Postgres refuse a connection with non-trusted certificates.
> 
> At least that's what I read in the documentation. No?
> 
> Regards, Markus
> 

-- 
Adrian Klaver
[email-masked]

---

From: "Zwettler Markus (OIZ)" <[email-masked]>
Date: Fri, 31 Jan 2025 16:57:54 +0000

> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <[email-masked]>
> Gesendet: Freitag, 31. Januar 2025 17:37
> An: Zwettler Markus (OIZ) <[email-masked]>; Tom Lane
> <[email-masked]>; [email-masked]
> Betreff: [Extern] Re: could not accept ssl connection tlsv1 alert unknown ca
> 
> >> Those cause some additional checks to be made, but it's not like you
> >> can expect a completely broken certificate to work without them.
> >>
> >>                          regards, tom lane
> >
> >
> >
> > I don't understand why Postgres does a certificate validation with
> “sslmode=prefer”. Postgres should simply ignore every presented client certificate
> here. Regardless of whether it is trusted or not.
> 
> > A certificate validation should only take place in the modes “sslmode=verify-ca”
> and “ssmode=verify-full”. Only here should Postgres refuse a connection with non-
> trusted certificates.
> >

bash-4.4$ cat pg_hba.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
local all "postgres" peer
hostssl replication "_crunchyrepl" all cert
hostssl "postgres" "_crunchyrepl" all cert
host all "_crunchyrepl" all reject
host all "ccp_monitoring" "127.0.0.0/8" scram-sha-256
host all "ccp_monitoring" "::1/128" scram-sha-256
host all "ccp_monitoring" all reject
hostssl all all all md5

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 31 Jan 2025 09:07:13 -0800

> host all "ccp_monitoring" all reject

 From here:

https://www.postgresql.org/docs/17/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

"There are two approaches to enforce that users provide a certificate 
during login.

The first approach makes use of the cert authentication method for 
hostssl entries in pg_hba.conf, such that the certificate itself is used 
for authentication while also providing ssl connection security.

[...]

The second approach combines any authentication method for hostssl 
entries with the verification of client certificates by setting the 
clientcert authentication option to verify-ca or verify-full.  ...
"

Is the client having issues trying a connection that matches either of 
the lines below?:

---

From: "Zwettler Markus (OIZ)" <[email-masked]>
Date: Mon, 3 Feb 2025 08:37:29 +0000

> Gesendet: Freitag, 31. Januar 2025 18:07
> > hostssl replication "_crunchyrepl" all cert hostssl "postgres"
> > "_crunchyrepl" all cert host all "_crunchyrepl" all reject host all
> > "ccp_monitoring" "127.0.0.0/8" scram-sha-256 host all "ccp_monitoring"
> > "::1/128" scram-sha-256 host all "ccp_monitoring" all reject hostssl
> > all all all md5
> 
>  From here:
> 
> "There are two approaches to enforce that users provide a certificate during login.
> 
> The first approach makes use of the cert authentication method for hostssl entries
> in pg_hba.conf, such that the certificate itself is used for authentication while also
> providing ssl connection security.
> 
> 
> [...]
> 
> The second approach combines any authentication method for hostssl entries with
> the verification of client certificates by setting the clientcert authentication option
> to verify-ca or verify-full.  ...
> "
> 
> Is the client having issues trying a connection that matches either of the lines
> below?:
> 
> replication "_crunchyrepl" all cert hostssl "postgres" "_crunchyrepl" all
> cert
> 
> >
> >
> >
> 
> --
> Adrian Klaver

No, there are no errors with the lines mentioned. 

The error appears with a connection that matches the last line.

hostssl all all all md5                                     <<== user connection matching this line gives the error