Thursday, July 18, 2013

SSLStream support added to Npgsql!

Yesterday I merged a pull request which adds support to .Net SSLStream in Npgsql.

When Npgsql development started, in 2002, there was no SSL support in .net 1.0 and 1.1. It was added later in .net 2.0 but the support to SSLStream was never added to Npgsql.

This all changed a few weeks ago when Dave G created a patch which adds support to .Net SSLStream in Npgsql. He also said that one of the motivations was the fact that the current SSL support was returning error when using client certificate authentication. (I'll post a tutorial about how to use client certificates later)

There is a very old feature request (like since 2005!) to somehow create a single Npgsql.dll which incorporates Mono.Security.dll assembly. This would easy the deployment of Npgsql and version control. This feature request generated a discussion about the removal of Mono.Security dependency and possible impact in current user code. 

In order to get the lowest impact by this change, instead of immediately remove the callbacks and break a lot of user code, I asked Dave to keep the callbacks and mark them obsolete. This way,  users will be warned about those callbacks instructed to use the SSLStream RemoteCertificateValidationCallback instead.

In order to use the new SSL code, you just need to add a callback to ValidateRemoteCertificateCallback.
It can be as simple as:

NpgsqlConnection conn = new NpgsqlConnection(CONNSTRING);
conn.ValidateRemoteCertificateCallback += (abc=> { return true; };

This callback simply returns true indicating you are accepting the server certificate. Obviously, returning true without doing any validation should be done for testing purposes only.

Please, give it a try and let me know if you have any problems. 


Maxim Sitnikov said...

Hi Francisco.

Please, tell me how to connect to PostgreSQL database with SSL encryption and client certificate. May be you have some example on VB.NET or C#.

I try to use the following code for Npgsql:

Imports System.Security.Cryptography.X509Certificates
Imports System.Security.Cryptography
Imports Npgsql

Public Class Form1

Private WithEvents cnn As NpgsqlConnection

Private Sub cmdConnection_Click(sender As Object, e As EventArgs) Handles cmdConnection.Click
System.Net.ServicePointManager.ServerCertificateValidationCallback = New System.Net.Security.RemoteCertificateValidationCallback(AddressOf bypassAllCertificateStuff)
cnn = New NpgsqlConnection
cnn.ConnectionString = ";Port=5432;Database=documents;Userid=ArchiveUser;Password=mypassword;SSL=true;SslMode=Require;"
'test connection only
End Sub

'callback for client's certificate
Private Sub cnn_ProvideClientCertificatesCallback(certificates As X509CertificateCollection) Handles cnn.ProvideClientCertificatesCallback
Dim cert As X509Certificate2 = New X509Certificate2("C:\Users\programmer\Documents" & "\client.pfx", "mypassword")
End Sub

'callback for server's certificate
Friend Function bypassAllCertificateStuff(ByVal sender As Object, ByVal cert As System.Security.Cryptography.X509Certificates.X509Certificate, ByVal chain As System.Security.Cryptography.X509Certificates.X509Chain, ByVal [error] As System.Net.Security.SslPolicyErrors) As Boolean
'trust any certificate
Return True
End Function
End Class

I have two certificates on client: "client.crt" and "client.key". This certificates can work properly with ODBC driver.
Then I make "client.pfx" from "client.crt" and "client.key" by this command:

openssl pkcs12 -inkey client.key -in client.crt -export -out client.pfx

VB.NET can load this pfx container and show information about this certificate and it's private key (HasPrivateKey property is True).

Also I can load this pfx to Windows store and get it back with private key from there. Npgsql also cannot work with this certificate from store.

I always recieve the error "28000 connection requires a valid client certificate". PostgreSQL writes log "SSL connection from (anonymous)".

CN in certificate is the same as Userid in connection string.

What is wrong?

Maxim Sitnikov.

Francisco Figueiredo Jr. said...

Hi, Maxim!

Why are you using this line:
System.Net.ServicePointManager.ServerCertificateValidationCallback = New System.Net.Security.RemoteCertificateValidationCallback(AddressOf bypassAllCertificateStuff)

You should pass this callback to NpgsqlConnection. I think this may be your problem.

I don't know very well and I may be missing somethig, but I think that as long as you set the callback to NpgsqlConnection, you should get it working as this callbacks makes use of your client certificate.

I hope it helps.