Using SSL Client Certificates with Npgsql

Hi all!

Recently, Jarrod Kinsley asked on our Forums how to establish an SSL connection. As Laurenz Albe pointed out, normally you just need to change your connection string to put "SSL=True;Sslmode=Require;" in your connection string and "ssl=on" in postgresql.conf and you are ready to go.

The problem was that this works in the general case where you don't have to deal with client certificates and other stuff. Npgsql has a lot of callbacks to help you to validate and talk to the server.

The last callback added to the chain by Frank Bollack was to provide a way to pass client certificates to server.

Later on the thread, Jennifer Marienfeld was also trying to connect and was stuck in the client certificate part. Jennifer eventually got success to establish connection to the server and I decided to create this post to show the code so others can benefit from this.

Here is Jennifer's code so you all can use as a template:

using System;
using System.IO;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;

using Npgsql;
using Mono.Security.Protocol.Tls;
using Mono.Security.Authenticode;

namespace my
{
class Program
{
public static void Main(string[] args)
{
string conStr =
"Server=xxx.xxx.xxx.xxx;" +
"User Id=xxx;" +
"Password=xxx;" +
"Protocol=3;" +
"Database=xxx;" +
"SSL=True;" +
"Sslmode=Require;";

NpgsqlConnection conn = new NpgsqlConnection(conStr);


conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(
MyProvideClientCertificates
);


conn.CertificateSelectionCallback +=
new CertificateSelectionCallback(
MyCertificateSelectionCallback
);


conn.CertificateValidationCallback +=
new CertificateValidationCallback(
MyCertificateValidationCallback
);

conn.PrivateKeySelectionCallback +=
new PrivateKeySelectionCallback(
MyPrivateKeySelectionCallback
);

try
{
conn.Open();
System.Console.WriteLine("Verbindung aufgebaut");
}
catch (Exception e)
{
System.Console.WriteLine(e);
}
finally
{
conn.Close();
System.Console.ReadLine();
}
}


static void MyProvideClientCertificates(X509CertificateCollection clienteCertis)
{
X509Certificate cert = new X509Certificate("mycert.crt");
clienteCertis.Add(cert);
}


static X509Certificate MyCertificateSelectionCallback(X509CertificateCollection clienteCertis, X509Certificate serverCerti, string hostDestino, X509CertificateCollection serverRequestedCertificates)
{
return clienteCertis[0];
}

static AsymmetricAlgorithm MyPrivateKeySelectionCallback(X509Certificate certificate, string targetHost)
{
PrivateKey key =null;
try
{
//it is very important that the key has the .pvk format in windows!!!
key = PrivateKey.CreateFromFile("myKey.pvk", "xxx");
}
catch (CryptographicException ex)
{
Console.WriteLine();
Console.WriteLine();
Console.WriteLine(ex);
Console.WriteLine();
Console.WriteLine();
}

if (key == null)
return null;

return key.RSA;
}



static bool MyCertificateValidationCallback(X509Certificate certificate, int[] certificateErrors)
{
/*
* CertVALID = 0,
* CertEXPIRED = -2146762495,//0x800B0101
* CertVALIDITYPERIODNESTING = -2146762494,//0x800B0102
* CertROLE = -2146762493,//0x800B0103
* CertPATHLENCONST = -2146762492,//0x800B0104
* CertCRITICAL = -2146762491,//0x800B0105
* CertPURPOSE = -2146762490,//0x800B0106
* CertISSUERCHAINING = -2146762489,//0x800B0107
* CertMALFORMED = -2146762488,//0x800B0108
* CertUNTRUSTEDROOT = -2146762487,//0x800B0109
* CertCHAINING = -2146762486,//0x800B010A
* CertREVOKED = -2146762485,//0x800B010C
* CertUNTRUSTEDTESTROOT = -2146762484,//0x800B010D
* CertREVOCATION_FAILURE = -2146762483,//0x800B010E
* CertCN_NO_MATCH = -2146762482,//0x800B010F
* CertWRONG_USAGE = -2146762481,//0x800B0110
* CertUNTRUSTEDCA = -2146762480,//0x800B0112
*/

//error: -2146762487, -2146762481
System.Console.WriteLine(certificateErrors[0]);
return true;
}
}
}