PostgreSQL - SSL_do_handshake = 0

[ertank]

Hello,

I am using Delphi 10.2.2, UniDAC 7.1.3.

I have a custom signed certificate for 10 years. Certificate is created exactly one month ago. Today, I started to receive following UniDAC error on my clients:

Code: Select all

SSL_do_handshake = 0
SSL_get_error(..., r2) = 1
r2 = 0.

This is not the first time that I hit by such a thing, I would like to fix my certificate creation mistake.

Script that I used to create certificates is below:

Code: Select all

rm -rf server.crt server.key root.crt
echo "Generating server certificate"
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/[email protected]'
cp server.crt root.crt
cp server.crt root.crt server.key /var/lib/postgresql/9.6/main
chown postgres:postgres /var/lib/postgresql/9.6/main/server.crt /var/lib/postgresql/9.6/main/root.crt /var/lib/postgresql/9.6/main/server.key
chmod 400 /var/lib/postgresql/9.6/main/server.crt /var/lib/postgresql/9.6/main/server.key /var/lib/postgresql/9.6/main/root.crt
/etc/init.d/postgresql restart
echo "Generating client ceriticate"
openssl genrsa -des3 -out /tmp/postgresql.key 1024
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=postgres'
openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
echo "*******************************************"
echo "*** COPY /tmp/postgresql.crt and /tmp/postgresql.key files to PC"
echo "*******************************************"

Fifth line from top above says to generate a certificate for about 10 years. But, I do not have deep SSL knowledge and that script is a sample taken from internet somewhere.

I appreciate to know how I can have a server and client certificate to be valid for 10 years. BTW, PostgreSQL server still happily loads server side certificate and has no issues. Only problem I have is with my clients, I assume.

I will be glad to provide 1 month old generated certificates for further investigation in a non-public devart e-mail.

Thanks & regards,
Ertan Küçükoğlu

[azyk]

The question of certificate generation is not related to UniDAC functionality. For information on generation/obtaining digital certificates, please contact the certification authorities or digital certificate providers.

[ertank]

For any person fall in my position, below is the working script. There is simply one more additional "-days" parameter for client certificate creation.

Server certificate (same as above):

Code: Select all

rm -rf server.crt server.key root.crt
echo "Generating server certificate"
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/[email protected]'
cp server.crt root.crt
cp server.crt root.crt server.key /var/lib/postgresql/9.6/main
chown postgres:postgres /var/lib/postgresql/9.6/main/server.crt /var/lib/postgresql/9.6/main/root.crt /var/lib/postgresql/9.6/main/server.key
chmod 400 /var/lib/postgresql/9.6/main/server.crt /var/lib/postgresql/9.6/main/server.key /var/lib/postgresql/9.6/main/root.crt
/etc/init.d/postgresql restart

Client certificate

Code: Select all

echo "Generating client ceriticate"
openssl genrsa -des3 -out /tmp/postgresql.key 1024
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=postgres'
openssl x509 -req -days 3650 -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
echo "*******************************************"
echo "*** COPY /tmp/postgresql.crt and /tmp/postgresql.key files to PC"
echo "*******************************************"

Solution is in 4th line from bottom.

I hope that will be helpful to others.