Connecting via SSL

Connecting to PostgreSQL Through SSL in Delphi

Security is very important when sending messages from the server to the client and vice versa. There are many data protection methods, including the use of SSL encryption to connect to a remote PostgreSQL server from a Delphi application. PostgreSQL supports data transfer via the TCP/IP protocol stack both using SSL encryption or without it.

Devart offers a solution called SecureBridge, which allows you to embed an SSL client into a Delphi or C++ Builder application to establish a secure connection to PostgreSQL server. This tutorial demonstrates how to create a sample Delphi application that connects to PostgreSQL using SSL as the encryption method.

Before connecting to PostgreSQL via SSL, create SSL certificates as explained in the PostgreSQL documentation and configure SSL parameters in postgresql.conf and pg.hba.conf files.

Sample Delphi app that connects to PostgreSQL using SSL

To create an SSL connection to PostgreSQL, PgDAC provides several values for the SSLOptions property. For this tutorial, the Mode property is set to smRequire , since it forces the application to only connect via SSL connection - if a connection attempt fails, an exeption is raised.

Example of SSLOptions property set to smRequire:

PgConnection.SSLOptions.Mode := smRequire;

After installing PgDAC and SecureBridge software on your machine, install the TCRSSLIOHandler 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\Demos\TechnologySpecific\SecureBridge\DelphiXX".

1. Run RAD Studio and select 'File -> New – > VCL Forms Application – Delphi'.
2. Place the TCRSSLIOHandler component, which allows PgDAC to connect to PostgreSQL server through SSL, onto the form. Also add the TPgConnection, TPgQuery, TDataSource, TDBGrid, and TButton components to the form - they are required to create a sample application that connects to the PostgreSQL server via SSL, runs a selection operation against the database, and displays the obtained rows in a data grid.



3. Select the TPgConnection component and asign the TCRSSLIOHandler object to the IOHandler property in the Object Inspector.
4. Expand SSLOptions in the Object Inspector and specify the server certificate in the CACert property, the client certificate in the Cert property, and the private client key in the Key property.
5. Double-click TPgConnection and specify the server address, port, username, password, and, optionally, database name. Click Connect to test connection to the PostgreSQL server.



6. Select the TDataSource component and assign the PgQuery1 object to the DataSet property.



7. Assign the DataSource1 object to the DataSource property in the TDBGrid component.



8. Double-click the TPgQuery component and add a SQL query that will be run against the PostgreSQL database.



9. Select the TButton component and create the OnClick event. Add the code that will call the Open method in the TPgQuery component when you click the button.



10. Press F9 to compile and run the application. Click the button on the form to execute the query and display data in the grid.

2. SSL Connection to PostgreSQL in Delphi Using the OpenSSL Library

Another way to embed SSL client functionality into your Delphi app, which uses PgDAC components to access PostgreSQL, is by using the OpenSSL library that implements the SSL protocol and enables servers to securely communicate with their clients. The description of the SSL connection features without the SecureBridge IOHandler usage:

The following options must be set for SSL connection:

• SSLCACert - the pathname to the certificate authority file.
• SSLCert - the pathname to the certificate file.
• SSLKey - the pathname to the key file.
• SSLCipherList - a list of allowable ciphers to use for SSL encryption.

Note:The ssleay32.dll and libeay32.dll files are required to use the SSL protocol with the OpenSSL library.