This section discusses how to connect to PostgreSQL through SSH. Secure Shell (SSH) is cryptographic network protocol for secure remote login, command execution and file transfer over untrusted networks. SSH uses client-server architecture, connecting an SSH client with an SSH server. The client and server authenticate each other and pass commands and output back and forth. To secure the transmitted data, SSH employs forms of symmetric encryption, assymetric encryption, and hashing.
In symmetric key cryptography, a single key is used by the sending and receiving parties to encrypt and decrypt messages. Assymetric encryption requires two associated keys, the private key and the public key. The public key encrypts messages that can only be decrypted by the private key. The public can key can be freely shared with anyone to autenticate another party, while the private key must be kept secret. The client public key must be stored in a location that is accessible by the SSH server to authenticate the server by the client; conversely, the server public key must be placed on the client side to authenticate the client by the server. Assymetrical encryption is used during the initial key exchange process to produce the shared secret (session key) to encrypt messages for the duration of the session.
The SSH server listens on default port 22 (this port can be changed) for incoming TCP connections. The SSH client begins the initial TCP handshake with the server and verifies the server's identity. The client and server agree upon the encryption protocol and negotiate a session key. The server then authenticates the client and spawns the right environment. The ODBC driver for PostgreSQL implements the SSH client feature to connect to the SSH server on the remote machine at the specified port. The SSH server authenticates the client and enables the driver to establish a secure direct connection to PostgreSQL. Below is a simplified diagram representing the SSH tunneling.
Note: You don't have to install the SSH client since ODBC Driver for PostgreSQL implements the SSH client functionality.
To establish an SSH connection to PostgreSQL, specify the connection parameters on the SSH Options
tab under Security Settings
.
Option |
Description |
---|---|
|
Enables SSH connections. |
|
The host name or IP address of the SSH server. |
|
The SSH port number (22 by default). |
|
The username for the account on the SSH server. |
|
The password for the account on the SSH server. |
|
The filename of the client private key for key-based authentication. |
|
The passphrase for the client private key. |
|
The filename of the SSH server public key. |
|
The directory where the encryption keys are stored. |
DRIVER=Devart ODBC Driver for PostgreSQL;Data Source=myHost;Database=myDatabase;User ID=myUsername;Password=myPassword;Port=myPort;Schema=mySchema;Use SSH=True;SSH Host name=mySshHost;SSH User Name=mySshUsername;SSH Password=mySshPassword;SSH Client Key=myPrivateClientKey.pem;SSH Client Key Password=myClientKeyPassphrase;SSH Server Key=myPublicServerKey.pem;SSH Storage Path=myDirectoryWithKeys |