Skip to main content

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;
}
}
}

Comments

Thanks for sharing, nice post! Post really provice useful information!

Hương Lâm chuyên cung cấp máy photocopy, chúng tôi cung cấp máy photocopy ricoh, toshiba, canon, sharp, đặc biệt chúng tôi có cung cấp máy photocopy màu uy tín, giá rẻ nhất.

Mark weins said…
Some truly nice stuff on this site, I love it. webflow designers
Unknown said…
Thanks for taking the time to discuss this topic. I really appreciate it. I’ll stick a link of this entry in my blog. webflow designer
Stream Line said…
you could have a fantastic weblog here! would you wish to make some invite posts on my weblog? web development services company

Popular posts from this blog

UUID datatype and COPY IN/OUT support added to cvs

Hi all! It was just added support to uuid datatype in cvs head. This type will be available in next Postgresql release 8.3. Thanks to David Bachmann for his patch! You can get more info about this patch in this mailing list post . Also was added support for copy in and copy out operations. Now, users can provide streams which can be copied directly to and from Postgresql tables! Thanks to Kalle Hallivuori for providing a patch! Thanks to Truviso for giving support to Kalle. More info about that including a demo and ready to use compiled Npgsql.dll versions can be found here . That's it! As soon as we get more features added, I will post info about them here. Stay tuned! :)

Npgsql Tips: Using " in (...)" queries with parameters list and "any" operator

Hi, all! We have received some users questions about how to send a list of values to be used in queries using the "in" operator. Something like: select foo, bar from table where foo in (blah1, blah2, blah3); Npgsql supports array-like parameter values and the first idea to have this working would try to use it directly: NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial in (:parameterlist)", conn); ArrayList l = new ArrayList(); l.Add(5); l.Add(6); command.Parameters.Add(new NpgsqlParameter("parameterlist", NpgsqlDbType.Array | NpgsqlDbType.Integer)); command.Parameters[0].Value = l.ToArray(); NpgsqlDataReader dr = command.ExecuteReader(); but unfortunately this won't work as expected. Npgsql will send a query like this: select * from tablee where field_serial in ((array[5,6])::int4[]) And Postgresql will complain with the followin

Stream seek error

Hi all! Since Npgsql RC1, we started to receive some error reports about problems when closing connections. The typical stack trace looked like this: System.NotSupportedException : This stream does not support seek operations. at System.Net.Sockets.NetworkStream.Seek(Int64 offset, SeekOrigin origin) at System.IO.BufferedStream.FlushRead() at System.IO.BufferedStream.WriteByte(Byte value) − at Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlQuery.cs:line 62 − at Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlReadyState.cs:line 64 − at Npgsql.NpgsqlConnector.ReleasePlansPortals() in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlConnector.cs:line 373 − at Npgsql.NpgsqlConnectorPool.UngetPooledConnector(NpgsqlConnection Connection, NpgsqlConnector Connector) in C:\Npgsql\Npgsql2\src\Npgsql\NpgsqlConnectorPool.cs:line 541 − at Npgsql.NpgsqlConnectorPool.ReleasePooledConnector(NpgsqlConn