Hello @huynhdung ,I double checked and I was not aware that the client certificate authentication is part of TLS layer negotiation, so my previous comment might be not completely correct.
At this point, I believe your error is definitively connected to the certificates used in the mTLS authentication.
I did some tests (under Linux and with PEM format) and the environment below worked fine for me - it's not a production environment.
1 - Creating the CA certificate on server machine:
openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=192.168.1.71"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
2 - Creating the server's CSR and the certificate on server machine:
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=192.168.1.70"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
3 - Creating client's CSR on client machine:
openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=user"
chmod og-rwx client.key
4 - Move the client's CSR (client.csr) on the server and create the client certificate on server machine:
openssl x509 -req -in client.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out client.crt
5 - Move the client certificate (client.crt) and the CA root certificate (root.crt) on the client machine.
6 - Configuring postgresql.conf on server machine:
# - SSL -
ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
7 - Configuring pg_hba.conf on server machine:
hostssl all user 127.0.0.1/32 cert clientcert=verify-full
Now, you should have your environment configured and from the client machine you should be able to authenticate throught certificate (mTLS).
In my environment it works with the following command:
psql 'host=127.0.0.1 port=5432 dbname=postgres user=user sslcert=client.crt sslkey=client.key sslrootcert=root.crt'
Back to the error you posted "connection require a valid client certificate", it is thrown when the server is expenting a certificate authentication but the client DOESN'T PROVIDE A CERTIFICATE.
With the previous configured environment is easy to reproduce the error:
psql 'host=127.0.0.1 port=5432 dbname=postgres user=user'
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: connection requires a valid client certificate
At this point I belive there might be two cases here:
1 - From a client perspective, "sslkey=C:/xxx/postgresql.key" is not a valid PKCS-8 DER format; follow the JDBC documentation to also convert the key: https://jdbc.postgresql.org/documentation/use/; (openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES)
2 - The client is not able to traslate your advanced commands into the driver so it is not sending a certificate.
I'm more prone to the #1, but I believe you can troubleshoot it with the following steps:
1 - Try to authenticate to your PGSQL server without the JDBC driver and with the current certificate files.
2 - If it works, try to convert the PEM into a PKCS-8 DER format and change the advance configuration of your JDBC driver as following: "sslkey=C:/xxx/postgresql.pk8"
Thnanks for helping me dig deeper and hoping it helps!
I followed this documentation:
https://jdbc.postgresql.org/documentation/use/
https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION
