dotConnect for PostgreSQL allows you to establish secure network connections. You can do it using SSL or SSH protocol. This article describes basic concepts for these technologies and how to use it in dotConnect for PostgreSQL.
The article consists of following sections:

• Why Using Secure Connections?
• Using SSL Connections
• Setting Up SSL Connection
• Using SSL Connection without authentication
• Using SSH Connections
• Setting Up SSH Connection

Why Using Secure Connections?

When PostgreSQL client communicates with PostgreSQL server, all communication (with the exception of the user password) is done in plain text. This means that anyone who gets between your client and the server can have full access to all information transmitted, and even change the data while it is in transit. In order to protect your information you need to encrypt communications between the PostgreSQL server and the client application.

Using SSL Connections

The Secure Sockets Layer protocol enables two parties to negotiate a 'secure' communications channel, ensuring the privacy, authenticity, and integrity of message data. The negotiation mechanism follows:

1. The client requests a SSL connection from the server.
2. The two parties negotiate a common ciphersuite, which consists of a key exchange algorithm, a certificate verification algorithm, an encryption algorithm, and an integrity check.
3. The server provides its certificate so it can be authenticated by the client. Optionally, the client may provide its certificate to be authenticated by the server.
4. The two parties compute the cryptographic parameters used in the ciphersuite, such as the secret keys needed for data encryption.
5. The two parties exchange application data, using the generated session key, negotiated algorithms and computed cryptographic parameters.

To check whether your PostgreSQL server supports SSL you can examine the value of the 'ssl' system variable:

    SHOW VARIABLES LIKE 'have_openssl'

Setting Up SSL Connection

Once server is ready to use SSL connections you only have to setup client properly. You must have 3 additional files reachable:

• authority certificate (for example root.crt) - used to verify identity of client and server
• client certificate (for example client.crt) - used to encrypt and decrypt data during connection.
• private key (for example client.crt) - proves client certificate sent by owner; does not indicate certificate owner is trustworthy.

Please refer to OpenSSL site for information about these files. The later part assumes you have valid certificates and private key.

The property SslOptions.CipherList by default is empty, which means that client agrees to use any of available ciphers. The following ciphers are allowed in dotConnect for PostgreSQL:

• DES(40)
• DES or DES(56)
• 3DES or DES(168)
• AES(128)
• AES or AES(256) (used by default)
• RC4(40)
• RC4 or RC4(128)
• RC2 or RC2(40)

You can use ALL keyword to indicate whole set of ciphers. To exclude certain cipher from the set use "-" sign. For instance, value of SslOptions.CipherList property "ALL:-RC4" means that any cipher but RC4 can be used. This property allows you to control performance-security trade-off using this property. To gain better performance you can use RC2 cipher. To achieve maximal traffic security pay attention at 3DES or AES ciphers. By default dotConnect for PostgreSQL tries to use ciphers beginning with most secure rather than fast ones.

PgSqlConnection.SslOptions property points to object that holds all information necessary to establish SSL connection. Here is brief explanation on what you have to specify in this object:

You may store and load the necessary keys or certificates in various ways in your applications: as files or as compiled resources or in a certificate store. dotConnect for PostgreSQL also offers a convenient way to store certificates and keys in memory, so you can load them once, during application start, and then use these certificates from the in memory storage. For this, you can use the MemCryptStorage class.

You can add certificates to a memory storage in the following way:

MemCryptStorage.AddCa("my_ca", File.ReadAllBytes("D:\\Temp\\root.crt"));  
MemCryptStorage.AddCert("my_cert", File.ReadAllBytes("D:\\Temp\\postgresql.crt"));  
MemCryptStorage.AddKey("my_key", File.ReadAllBytes("D:\\Temp\\postgresql.key"));

MemCryptStorage.AddCa("my_ca", File.ReadAllBytes("D:\Temp\root.crt"))
MemCryptStorage.AddCert("my_cert", File.ReadAllBytes("D:\Temp\postgresql.crt"))
MemCryptStorage.AddKey("my_key", File.ReadAllBytes("D:\Temp\postgresql.key"))

The first parameter here is the certificate or key id, that will be used to reference the certificate.

Certificates and keys are passed to the MemCryptStorage class in the PEM format as binary raw or string. They can contain LF, CR/LF, or no line break. We don't recommend changing the original format of certificates. It is better to pass them as is.

Here is how you can specify the locations of certificates:

• As file in the system: file://C:\Temp\Example.pem
• As item of certificate store: storage://Name.Id
• As compiled resource: resource://Example.pem
• Loaded to memory, using MemCryptStorage class: memory://certificate_id

Private key can be specified only as file in the system, as a compiled resource, or via the MemCryptStorage class.

The sample code below illustrates establishment of SSL connection.

PgSqlConnection conn = new PgSqlConnection("user id=postgres;password=postgres;host=localhost;");
conn.SslOptions.CACert = "E:\\Test\\root.crt";
conn.SslOptions.Cert = "E:\\Test\\client.crt";
conn.SslOptions.Key = "E:\\Test\\client.key";
conn.SslOptions.SslMode = SslMode.Require;
conn.Open(); 

Dim conn As PgSqlConnection = New PgSqlConnection("user id=postgres;password=postgres;host=localhost;")
conn.SslOptions.CACert = "E:\Test\root.crt"
conn.SslOptions.Cert = "E:\Test\client.crt"
conn.SslOptions.Key = "E:\Test\client.key"
conn.SslOptions.SslMode = SslMode.Require
conn.Open() 

Using SSL Connection without authentication

You can use SSL connection without authentication on both server and client side. If you don't want server to verify the client certificate, you need to configure PostgreSQL server without the root.crt, and if you don't want client to verify the server sertificate, don't set the SslOptions.CACert property of the connection.

Remember, when you disable authentication, the security of the connection between the client and server decreases.

Using SSH Connections

SSH connection is established between client and SSH server. SSH server in turn communicates with PostgreSQL server in an unencrypted mode. This is called SSH tunneling. A benefit of SSH tunneling is that it allows you to connect to a PostgreSQL server from behind a firewall when the PostgreSQL server port is blocked. PostgreSQL server does not need to be attuned for this type of connection and functions as usual. To connect to PostgreSQL server a client must first be authorized on SSH server.

You can download free SSH server at www.openssh.org. The server can run either on the same machine with PostgreSQL server or on a different one. Note that you have to create a user on SSH server to be authorized.

Setting Up SSH Connection

To set up an SSH conecton, set up the corresponding SSH options as described below. When any of SSH options are set for a connection, it automatically switches to the SSH protocol.

PgSqlConnection.SshOptions property points to object that holds all information necessary to connect to SSH server. Alternatively, you may specify the corresponding parameters in the connection string. Here is brief explanation on what you have to specify in this object:

Locations of private key can be specified in three ways:

• As file in the system: file://C:\Temp\client.key
• As item of certificate store: storage://Name.Id (examples RSA.Client.key or DSA.Client.key)
• As compiled resource: resource://client.key
• Loaded to memory, using MemCryptStorage class: memory://key_id (see the information about the MemCryptStorage class above)

PostgreSQL server address that you specify in connection string is the address for SSH server to refer. For instance, if both servers are running on the same machine you have to specify "host=localhost" in the connection string.

The property SshOptions.CipherList contains the list of the ciphers that client agrees to use, separated by colons. By default it is empty, which means that client agrees to use any of available ciphers. The appropriate values for the CipherList property are listed below, highlighted in bold.

dotConnect for PostgreSQL supports two modes of block ciphering: Cipher-block chaining (CBC) and Counter (CTR). The following ciphers are available for SSH connections in the CBC mode:

• 3DES or 3DES(168) - Triple Data Encryption Algorithm. Key size 168 bits.
• Blowfish - Symmetric-key block cipher, designed in 1993 by Bruce Schneier. Key size 128 bits.
• AES(128) - Advanced Encryption Standard. Key size 128 bits.
• AES(192) - Advanced Encryption Standard. Key size 192 bits.
• AES or AES(256) - Advanced Encryption Standard. Key size 256 bits.

In the CTR mode the AES ciphers are used.

• AES(128)-CTR - Advanced Encryption Standard. Key size 128 bits.
• AES(192)-CTR - Advanced Encryption Standard. Key size 192 bits.
• AES-CTR or AES(256)-CTR - Advanced Encryption Standard. Key size 256 bits.

      // Password Authentication
      PgSqlConnection myConn = new PgSqlConnection("host=server;database=test;user id=postgres;");
      myConn.SshOptions.AuthenticationType = SshAuthenticationType.Password;
      myConn.SshOptions.User = "sshUser";
      myConn.SshOptions.Host = "sshServer";
      myConn.SshOptions.Password = "sshPassword";
      PgSqlCommand myCommand = new PgSqlCommand("select count(*) from dept", myConn);
      myConn.Open();
      Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
      Console.WriteLine(count);
      myConn.Close();

      // Public Key Authentication
      PgSqlConnection myConn = new PgSqlConnection("host=server;database=test;user id=postgres;");
      myConn.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
      myConn.SshOptions.User = "sshUser";
      myConn.SshOptions.Host = "sshServer";
      myConn.SshOptions.PrivateKey = "E:\\WORK\\client.key";
      PgSqlCommand myCommand = new PgSqlCommand("select count(*) from dept", myConn);
      myConn.Open();
      Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
      Console.WriteLine(count);
      myConn.Close();

      // Keyboard-Interactive Authentication
      PgSqlConnection connection = new PgSqlConnection("host=server;database=test;user id=postgres;");
	    connection.Protocol = PgSqlProtocol.Ssh;
	    // sets ssh options
	    connection.SshOptions.Host = "ssh_host";
	    connection.SshOptions.Port = 22;
	    connection.SshOptions.User = "ssh_user";
	    connection.SshOptions.AuthenticationType = SshAuthenticationType.KeyboardInteractive;
	    // Associate the AuthenticationPrompt event with your event handle
	    connection.AuthenticationPrompt += new PgSqlAuthenticationPromptHandler(connection_AuthenticationPrompt);

	    connection.Open();
	    Console.WriteLine(connection.State);

	    connection.Close();
	    Console.ReadKey();
      
      ...

      static void connection_AuthenticationPrompt(object sender, PgSqlAuthenticationPrompEventArgs e) {

	      foreach (string prompt in e.Prompts) {
		      if (prompt.Contains("Password"))
			      e.Responses[0] = "testPassword";
      		else
	  	    	if (prompt.Contains("UserId"))
		  	    e.Responses[1] = "testUserId";
	      }
      }

	    ' Password Authentication
	    Dim myConn As PgSqlConnection = New PgSqlConnection("host=server;database=test;user id=postgres;")
	    myConn.SshOptions.AuthenticationType = SshAuthenticationType.Password
	    myConn.SshOptions.User = "sshUser"
	    myConn.SshOptions.Host = "sshServer"
	    myConn.SshOptions.Password = "sshPassword"
	    Dim myCommand As PgSqlCommand = New PgSqlCommand("select count(*) from dept", myConn)
	    myConn.Open()
	    Dim count As Int64 = Convert.ToInt64(myCommand.ExecuteScalar())
	    Console.WriteLine(count)
	    myConn.Close()

	    ' Public Key Authentication
	    Dim myConn As PgSqlConnection = New PgSqlConnection("host=server;database=test;user id=postgres;")
	    myConn.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey
	    myConn.SshOptions.User = "sshUser"
	    myConn.SshOptions.Host = "sshServer"
	    myConn.SshOptions.PrivateKey = "E:\WORK\client.key"
	    Dim myCommand As PgSqlCommand = New PgSqlCommand("select count(*) from dept", myConn)
	    myConn.Open()
	    Dim count As Int64 = Convert.ToInt64(myCommand.ExecuteScalar())
	    Console.WriteLine(count)
	    myConn.Close()

	    ' Keyboard-Interactive Authentication
	    Dim connection As PgSqlConnection = New PgSqlConnection("host=server;database=test;user id=postgres;")
	    connection.Protocol = PgSqlProtocol.Ssh
	    ' sets ssh options
	    connection.SshOptions.Host = "ssh_host"
	    connection.SshOptions.Port = 22
	    connection.SshOptions.User = "ssh_user"
	    connection.SshOptions.AuthenticationType = SshAuthenticationType.KeyboardInteractive
	    ' Associate the AuthenticationPrompt event with your event handle
	    AddHandler connection.AuthenticationPrompt, AddressOf connection_AuthenticationPrompt

	    connection.Open()
	    Console.WriteLine(connection.State.ToString)

	    connection.Close()
	    Console.ReadKey()

      ...

	    Sub connection_AuthenticationPrompt(ByVal sender As Object, ByVal e As PgSqlAuthenticationPrompEventArgs)

	    	For Each prompt As String In e.Prompts
			    If prompt.Contains("Password") Then
				    e.Responses(0) = "testPassword"
			    ElseIf prompt.Contains("UserId") Then
				    e.Responses(1) = "testUserId"
			    End If
		    Next
	    End Sub

See Also

 PgSqlConnection Class  | SslOptions Class | SshOptions Class