Trying to connect to DB using ssl, keep getting "verification check of remote certificate failed".

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
dragan_matic
Posts: 4
Joined: Thu 15 Nov 2018 17:39

Trying to connect to DB using ssl, keep getting "verification check of remote certificate failed".

Post by dragan_matic » Thu 15 Nov 2018 18:29

I'm trying to connect to postgreSql (10.6) using ssl. Database is configured to accept only ssl connections and certificates have been created according to this manual: https://www.howtoforge.com/postgresql-ssl-certificates

I'm trying to connect to db using the following code (simple windows.forms .net 3.5 project) :

Code: Select all

PgSqlConnection c = new PgSqlConnection("host=192.168.3.167;database=mydatabase;user id=postgres;Password=mypassword");
certsDir = "c:\\temp\\certs";
c.SslOptions.CACert = Path.Combine(certsDir, "root.crt");
c.SslOptions.Cert = Path.Combine(certsDir, "postgresql.crt");
c.SslOptions.Key = Path.Combine(certsDir, "postgresql.key");
c.SslOptions.SslMode = SslMode.Require;
c.Open();
it throws the following exception:
Verification check of remote certificate failed.
which would mean that there is a problem with certificates.

However, if I use the same certificates in odbc (using psqlODBC) I can connect to database without any problems (using SSL Mode: verify-ca).

Also, if I use the same certificates from python (using psycopg2 library) I can also connect to database without any problems

Code: Select all

db=psycopg2.connect(host="192.168.3.167", database="mydatabase", user="postgres", password="mypassword", sslmode="require", sslrootcert='root.crt', sslcert='postgresql.crt', sslkey='postgresql.key')
That leads me to the conclusion that the certificates are ok but then I can't find the problem with my c# code.

dragan_matic
Posts: 4
Joined: Thu 15 Nov 2018 17:39

Re: Trying to connect to DB using ssl, keep getting "verification check of remote certificate failed".

Post by dragan_matic » Fri 16 Nov 2018 12:54

I've now checked the postgresql logs and failed connections are logged as

"could not accept SSL connection: EOF detected"

Googling shows this link: https://stackoverflow.com/questions/471 ... f-detected , this link: https://www.postgresql.org/message-id/8 ... .pgh.pa.us and this link: https://www.postgresql.org/message-id/2 ... .gmail.com

They all suggest the problem is in the client library. Could this be dotConnect for Postgresql bug?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Trying to connect to DB using ssl, keep getting "verification check of remote certificate failed".

Post by Pinturiccio » Mon 19 Nov 2018 16:35

We could not reproduce the issue with our certificates. Could you please create test certificates, with which issue can be reproduced, and send them to us.

dragan_matic
Posts: 4
Joined: Thu 15 Nov 2018 17:39

Re: Trying to connect to DB using ssl, keep getting "verification check of remote certificate failed".

Post by dragan_matic » Fri 30 Nov 2018 10:01

Ok, I was just being an idiot. The problem was that I was using self-signed certificates in which case the property SslOptions.CaCert must not be set.

Post Reply