25

I am trying to connect to my PostgreSQL server but psql is complaining that I don't have a valid client certificate. Here is how I create my certificates:

Self-signed server certificate:

openssl req -new -text -nodes -keyout server.key -out server.csr -subj '/C=US/ST=California/L=Fremont/O=Example/OU=CoreDev/CN=192.168.0.100' # CN is the server's IP address
openssl req -x509 -text -in server.csr -key server.key -out server.crt
cp server.crt root.crt
rm server.csr
chmod og-rwx server.key

Client certificate:

openssl req -new -nodes -keyout client.key -out client.csr -subj '/C=US/ST=California/L=Fremont/O=Example/OU=CoreDev/CN=postgres' # postgres is the database user name
openssl x509 -req -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt
rm client.csr

After copying the necessary files (client.crt, client.key, root.crt) onto the client machine and changing permission (i.e., chmod og-rwx client.key), I do the following:

psql 'host=192.168.0.100 port=5432 dbname=postgres user=postgres sslmode=verify-full sslcert=client.crt sslkey=client.key sslrootcert=root.crt'

and then I get:

psql: FATAL:  connection requires a valid client certificate

Am I doing the client certificate signing process wrong?

I tried:

openssl verify -CAfile root.crt -purpose sslclient client.crt

and I get:

client.crt: OK

Using Wireshark, here is the capture I got for the communication between the client (192.168.0.103) and the server (192.168.0.100):

enter image description here

Why does this happen?

It seems like the server does not send the CertificateRequest message to the client.. as you can see below:

enter image description here

but this is weird because in pg_hba.conf, I have:

hostssl all             postgres        192.168.0.103/32        cert
Adriaan
  • 17,741
  • 7
  • 42
  • 75
platypus
  • 1,165
  • 5
  • 27
  • 47
  • Please do not add answers to the question body itself. Instead, you should add it as an answer. [Answering your own question is allowed and even encouraged](https://stackoverflow.com/help/self-answer). – Adriaan Dec 05 '22 at 11:15

2 Answers2

12

In this situation I tend to pull out Wireshark and snoop the SSL negotiation to make sure the client certificate is really being offered by the client.

I suggest using openssl to verify the client->root signing link, too. 

openssl verify -CAfile root.crt -purpose sslclient client.crt

Edit: It's necessary to specify clientcert=1 even when cert authentication is chosen. Yes, that's weird.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • see edit, I don't know how to interpret the capture very well. – platypus Aug 29 '13 at 01:04
  • @platypus First you should tell Wireshark to interpret it as SSL. Analyze -> Decode As -> Transport -> SSL. Then look at the handshake process, and see if the client actually offered a certificate to the server. – Craig Ringer Aug 29 '13 at 02:10
  • @platypus Try appending `clientcert=1`. – Craig Ringer Aug 29 '13 at 05:14
  • thanks so much. I am so grateful for your help. It has taken a good chunk of my day trying to understand what was going wrong. Again thank you so very much!!! (see edit #3 :-)) – platypus Aug 29 '13 at 05:26
  • @platypus Looks like we need to improve the documentation then. – Craig Ringer Aug 29 '13 at 05:53
  • I went back and re-read 17.9.1. Using Client Certificates. It did mention about clientcert=1, so I must have forgotten about what I read. It might be helpful to add some information about this in section 19.4. Authentication Problems, so that people who forgot to turn clientcert=1 can search through this section and find what they are missing. – platypus Aug 29 '13 at 06:58
1

Just adding another answer here for someone else. I was getting this same error when connecting from a jdbc using the org.postgresql.driver. The issue is that the format for the key I was passing for the sslkey parameter was a pem format. This link explains it.

https://jdbc.postgresql.org/documentation/head/connect.html#:~:text=keyfile.-,sslkey,-%3D%20String

Note: The key file must be in PKCS-12 or in PKCS-8 DER format. A PEM key can be converted to DER format using the openssl command:

openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES
Cory
  • 196
  • 2
  • 8