Connecting to PostgreSQL Through SSH in Delphi

SSH is a protocol that allows users to securely log in 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.

You can use SSH to secure the network connection between a Delphi application and a PostgreSQL server. An SSH connection enables you to run shell commands as if you were physically operating the remote machine.

This page demonstrates how to use UniDAC to connect to a PostgreSQL server securely via an SSH tunnel.

To follow the steps on this page, you need an SSH client and an SSH server. You can create them using SecureBridge, a solution provided by Devart. It enables you to embed an SSH client into your Delphi application and, if needed, configure an SSH server. For more information, see the SecureBridge tutorial on configuring an SSH server. You can also build the SSHServer demo project, which is distributed with SecureBridge.

Your PostgreSQL server must be configured to accept connections from the SSH tunnel.

• Connect Using SecureBridge
• Connect Using OpenSSH or Any Other Third-Party SSH Tunneling Tools

Connect Using SecureBridge

You can establish a secure SSH tunnel using SecureBridge without the need for third-party applications. SecureBridge provides all the necessary components to create an SSH client within your Delphi application.

1. In RAD Studio, select File > New > Windows VCL Application - Delphi.



2. Place the following components from the Palette on the form:
   • TCRSSHIOHandler
   • TPostgreSQLUniProvider
   • TUniConnection
   • TUniQuery
   • TScFileStorage
   • TScSSHClient
   • TDataSource
   • TDBGrid
   • TButton
   These components are required to establish an SSH connection to a PostgreSQL server using UniDAC, execute a query, and display the retrieved data in a grid.



3. Select the TDBGrid component and, in the Object Inspector, set the DataSource property to the instance of TDataSource (DataSource1).



4. Select the TDataSource component and set the DataSet property to the instance of TUniQuery (UniQuery1).



5. Select the TUniQuery component and set the Connection property to the instance of TUniConnection (UniConnection1).
6. Double-click the TUniQuery component, enter a SQL query to be run against the PostgreSQL database, and click OK.



7. Select the TCRSSHIOHandler component and set the Client property to the instance of TScSSHClient (ScSSHClient1).



8. Select the TScFileStorage component and, in the Path property, specify the directory where the keys are stored.



9. Select the TScSSHClient component and assign values to the following properties:
   • Authentication – Select the value depending on the authentication method applicable for your SSH server: atPassword or atPublicKey.
   • HostKeyName – Specify the filename of the SSH server public key.
   • Hostname – Enter the host name or IP address of the SSH server.
   • KeyStorage – Set the property to the instance of TScFileStorage (ScFileStorage1).
   • Password – For password authentication, enter the password for the account on the SSH server.
   • Port – Specify the SSH port.
   • PrivateKeyName – For public key authentication, specify the filename of the client private key.
   • User – Enter the username for the account on the SSH server.



10. Select the TUniConnection component and set the IOHandler property to the instance of TCRSSHIOHandler (CRSSHIOHandler1).
11. Double-click the TUniConnection component and fill out the fields on the Connect tab:
• Provider – Select PostgreSQL.
• Server – Enter the host name or IP address of the PostgreSQL server. If the SSH server and PostgreSQL server are both running on the same machine, use localhost.
• Port – Specify the port of the PostgreSQL server.
• Username – Enter the username for the account on the PostgreSQL server.
• Password – Enter the password for the account on the PostgreSQL server.
• Database – Select the database.
12. Click Connect to test the connection to the PostgreSQL server, then click OK to close the dialog.



13. Double-click the TButton component and add code to call the Open method on the UniQuery1 object to activate the dataset when the button is clicked.



14. Press F9 to compile and run the application.
15. In the form that appears, click Button1 to run the query. Data appears in the grid.

Connect Using OpenSSH or Any Other Third-Party SSH Tunneling Tools

Using SecureBridge and its components is not obligatory—you can use any other server that implements the SSH protocol.