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?
Thanks,
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):
Do you know how to make sense of this?
Okay, I did what you said, and it seems like the server does not send the CertificateRequest message to the client.. as you can see below:
but this is weird because in pg_hba.conf, I have:
hostssl all postgres 192.168.0.103/32 cert
What do you think?
I changed the pg_hba.conf to contain:
hostssl all postgres 192.168.0.103/32 cert clientcert=1
and changed postgresql.conf to add in the "Security and Authentication" section:
ssl_ca_file = 'root.crt'
AND IT WORKED! Thank you so much!
Connect to your PostgreSQL database using psql connection parameters to specify the location of your client certificate, private key, and root CA certificate. Setting the sslmode parameter to verify-full also ensures that the PostgreSQL server name matches the name in the certificate it presents to clients.
PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security.
sslrootcert. This parameter specifies the name of a file containing SSL certificate authority ( CA ) certificate(s). If the file exists, the server's certificate will be verified to be signed by one of these authorities.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With