ODBC Driver for PostgreSQL

SSH Connection Description

ODBC Driver for PostgreSQL allows connection to PostgreSQL server from a local computer using SSH tunneling.

SSH (Secure Shell) is the protocol for secure access to remote computers over insecure communication channels.

The general chart of computer ties when connecting through the SSH tunnel is presented below:

SSH-scheme

This connection method provides secure connection between your application using ODBC Driver for PostgreSQL and SSH server that can go through insecure communication channels, like Internet.

Connections between DB server and SSH server are insecure, therefore they should go through secure communication channels. In the confluent case, DB server and SSH server can be located on the same computer.

The principle of working of the SSH connections is described below. The SSH server listens to the specified TCP/IP port. When the client tries to connect to this port, the SSH server authenticates the client. If the authentication passes, the connection is established. Then the client should create connections to DB server objects. The client sends an inquiry to establish necessary connection to SSH server, and the server establishes it.

SSH Configuration using OpenSSH or other third-party SSH tunnel

The following is the step-by-step sequence of actions for the easiest case of using OpenSSH for Windows. The detailed description of each command you can see in the documentation for OpenSSH.

  1. Download OpenSSH for Windows from http://sourceforge.net/projects/sshwindows/
  2. Install SSH server:
    • Choose a machine that will be used as SSH server. It does not have to be the same machine that is a PostgreSQL server, but communication channel between SSH server and PostgreSQL server must be protected
    • Using Windows Control Panel create a user and set a password for him. For example, SSHUser with password SSHPass
    • Install Open SSH. It is enough to install only Server components
    • Open OpenSSH/bin folder
    • Add SSHUser to the list of allowed users:
      mkpasswd -l -u SSHUser >> ..\etc\passwd 
    • Use mkgroup to create a group permissions file
      mkgroup -l >> ..\etc\group 
    • Run OpenSSH service
      net start opensshd 

Note:

There is no need to install SSH client, since ODBC Driver for PostgreSQL itself implements SSH client functionality.


About the methods of higher protection (key authentication, etc.) see documentation for OpenSSH.

How to establish SSH connection to PostgreSQL

To establish a SSH connection to PostgreSQL, the corresponding connection options have to be set, like shown below:

Using ODBC Data Source Administrator:

SSHConnection

SSH Options Description:

Option

Description

Use SSH

Enables or disables SSH tunneling.

SSH Host name

Holds the host name or IP address of the SSH server to connect to.

SSH Port

Used to specify the port number for SSH connection.

SSH User Name

Sets the SSH user name for authentication.

SSH Password

Sets the SSH user password for authentication.

SSH Client Key

Used to specify the path to the file containing the SSH Private Key.

If the authentication by key is used, the user must have his pair of keys. The public key should be transferred to the server, while the private key will be used by the client to sign data,

that will be used by server to authenticate the user. The server verifies the specified user name and Private Key - and if the Server Key corresponds to this user, connection may be established.

SSH Server Key

Used to specify the path to the file that contains SSH Public Key.

SSH StoragePath

Used to specify the path to the directory where encyption keys are stored.

Example Connection String:

Data Source=localhost;User ID=postgres;Password=postgres;Database=postgres;Schema=public;Use SSH=true;SSH Hostname=localhost;SSH Port=22;SSH Username=SSHUser;SSH Password=SSHPass;SSH Client Key='C:\User\...\PrivateKey.pem';SSH Server Key='C:\User\...\PublicKey.pem';SSH StoragePath='D:\...';

© 2015-2017 Devart. All Rights Reserved. Request Support ODBC Forum Provide Feedback