.NET PostgreSQL Connection Strings

This documentation article details how to construct a connection string for your .NET application to connect to a PostgreSQL database using dotConnect for PostgreSQL. We'll delve into the essential connection string parameters and explore some advanced options for customizing your database connection.

PostgreSQL connection strings

These connection strings are used to establish a general connection to a PostgreSQL database without using any intermediate software.

using Devart.Data.PostgreSql;
...
PgSqlConnection connection = new PgSqlConnection();
connection.Host = "127.0.0.1";
connection.Port = 5432;
connection.UserId = "postgres";
connection.Password = "postgres";
connection.Database = "test";
connection.Schema = "public";

Connection properties table

SSL/TLS connection strings

SSL/TLS connection strings are used to configure a secure connection to a PostgreSQL database with the help of SSL/TLS protocols.

The sample code below illustrates the establishment of the SSL connection.

PgSqlConnection connection = new PgSqlConnection("Host=127.0.0.1;Port=5432;UserId=postgres;Password=postgres;Database=test;");
connection.SslOptions.CACert = "E:\\Test\\root.crt";
connection.SslOptions.Cert = "E:\\Test\\client.crt";
connection.SslOptions.Key = "E:\\Test\\client.key";
connection.SslOptions.SslMode = SslMode.Require;
connection.Open();

SSL options properties

SSH connection strings

A benefit of SSH tunneling is that it allows you to connect to the PostgreSQL server from behind a firewall when the server port is blocked. The PostgreSQL server does not need to be tuned for this type of connection and functions as usual. To connect to the server, a client must first be authorized on the SSH server.

PgSqlConnection.SshOptions property points to an object that keeps all information necessary to connect to the SSH server. Alternatively, specify the corresponding parameters in the connection string.

To set up an SSH connection, adjust the corresponding SSH options as described below:

Password authentication

PgSqlConnection connection = new PgSqlConnection("Host=127.0.0.1;Port=5432;UserId=postgres;Database=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "sshServer";
connection.SshOptions.Password = "sshPassword";
PgSqlCommand command = new PgSqlCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(command.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

Public key authentication

PgSqlConnection connection = new PgSqlConnection("Host=127.0.0.1;Port=5432;UserId=postgres;Database=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "sshServer";
connection.SshOptions.PrivateKey = "E:\\WORK\\client.key";
PgSqlCommand command = new PgSqlCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(command.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

Keyboard-interactive authentication

PgSqlConnection connection = new PgSqlConnection("Host=127.0.0.1;UserId=postgres;Database=test;");
  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";
	}
}

SSH options properties

Locations of the private key can be specified in three ways:

• As a file in the system: file://C:\Temp\client.key
• As an item of the 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

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

The property SshOptions.CipherList contains the list of the ciphers that the client agrees to use, separated by colons. By default, it is empty, which means that the client agrees to use any of the 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) is the Triple Data Encryption Algorithm. The key size is 168 bits
• Blowfish is the symmetric-key block cipher, designed in 1993 by Bruce Schneier. The key size is 128 bits
• AES(128) is the Advanced Encryption Standard. The key size is 128 bits
• AES(192) is the Advanced Encryption Standard. The key size is 192 bits
• AES or AES(256) is the Advanced Encryption Standard. The key size is 256 bits

In the CTR mode, the AES ciphers are used.

• AES(128)-CTR is the Advanced Encryption Standard. The key size is 128 bits
• AES(192)-CTR is the Advanced Encryption Standard. The key size is 192 bits
• AES-CTR or AES(256)-CTR is the Advanced Encryption Standard. The key size is 256 bits

Proxy connection strings

Proxy connection strings are used to connect to a PostgreSQL database through a proxy server, which is useful for environments with strict network controls.

PgSqlConnection connection = new PgSqlConnection();
connection.ConnectionString = "Host=127.0.0.1;UserId=postgres;Password=postgres;Database=test;";
connection.ProxyOptions.Host = "10.0.0.1";
connection.ProxyOptions.Port = 808;
connection.ProxyOptions.User = "ProxyUser";
connection.ProxyOptions.Password = "ProxyPassword";
connection.Open();

Proxy options properties

Unicode property connection strings

Unicode property connection strings ensure that all data transferred between the application and a PostgreSQL database is in the Unicode format.

PgSqlConnection connection = new PgSqlConnection();
connection.ConnectionString = "Host=127.0.0.1;UserId=postgres;Password=postgres;Database=test;Unicode=true;";
connection.Open();

PgSqlConnection connection string parameter

Pooling connection string options

Advanced connection string options