Connecting via SSH

Connecting to PostgreSQL via SSH in Delphi

SSH is a protocol that allows users to securely log onto and interact with remote systems on the Internet by connecting a client program to an SSH server. SSH provides a mechanism for establishing a cryptographically secured connection between two endpoints, a client and a remote server, which authenticate each other and exchange messages. It employs different forms of symmetrical encryption, asymmetrical encryption, and hashing.

It is possible to use SSH to secure the network connection between a Delphi application and a PostgreSQL server. You execute shell commands in the same fashion as if you were physically operating the remote machine.

Devart offers a solution called SecureBridge that allows you to create a Delphi SSH client and a server. You can embed the SSH client into your application and install the SSH server on a remote machine where your PostgreSQL server resides. The SSH client connects to the SSH server, which sends all commands to the remote PostgreSQL server. This tutorial demonstrates how to create a sample Delphi application that connects to PostgreSQL using SSH as the encryption method.

SSH key-based authentication is done by public and private keys that a client uses to authenticate itself when logging into an SSH server. The server key is used is used by the client to authenticate the SSH server and is specified in the TScSSHClient.HostKeyName property. The client key is used by the SSH server to authenticate the client and is specified in the TScSSHClient.PrivateKeyName property. Note that the private key contains the public key. See SecureBridge tutorial on configuring the SSH server.

An SSH server is required to replicate the steps in this tutorial and encrypt the network connection between the client application and the PostgreSQL server. You can build the SSH server demo project that is distributed with SecureBridge ('Documents\Devart\PgDAC for RAD Studio\Demos\TechnologySpecific\SecureBridge\Demo') and run the executable file.

After installing PgDAC and SecureBridge software on your system, install the TCRSSHIOHandler component in RAD Studio to bind PgDAC with SecureBridge. The installation instructions are provided in the Readme.html, which is located by default in "My Documents\Devart\PgDAC for RAD Studio XX\Demos\TechnologySpecific\SecureBridge\".

Sample Delphi app that connects to PostgreSQL using SSH

1. Run RAD Studio and select 'File -> New – > VCL Forms Application – Delphi'.
2. Place the following components on the form: TCRSSHIOHandler, TPgConnection, TPgQuery, TScFileStorage, TScSSHClient, TDataSource, TDBGrid, and TButton. The sample application will connect to the PostgreSQL server via SSH, run a selection operation against the database, and display the obtained rows in the grid.



3. Select the TDBGrid and set the DataSource property to DataSource1.



4. In the TDataSource component, assign PgQuery1 to the DataSet property.



5. Select the TPgQuery and set the Connection property to PgConnection1. Double-click the component and enter a SQL statement to be executed against the PostgreSQL database.



6. Double-click the TButton to switch to the unit view. Add the code to call the Open method on the PgQuery1 object to activate the dataset when the button is clicked.



7. In the TCRSSHIOHandler component, assign ScSSHClient1 to the Client property.



8. Select the TScFileStorage component and specify in the Path property the directory where keys are stored on your system. Double-click the component and generate a pair of keys for authenticating the server by the client.

   

9. Set the Authentication property to atPublicKey in the TScSSHClient component. In HostKeyName, specify the server public key. In PrivateKeyName, specify the client private key. The Hostname property holds the address of your server. Assign ScFileStorage1 to the KeyStorage property. Enter your username on the server in the User property. Specify the SSH port in the Port property.



10. Double-click the TPgConnection component. Specify your server address, port, database name (optionally), and username and password for the PostgreSQL user. Set the IOHandler property to CRSSHIOHandler1. Click Connect to check connection to the PostgreSQL server.



11. Press F9 to compile and run the project. Click the button to run the query against the database and display the data in the form.

It is not obligatory to use SecureBridge TScSSHServer component as an SSH server - you can use any other server that implements the SSH protocol.