Connecting to PostgreSQL Through SSL in Delphi

Security is critical when exchanging messages between the server and client. Data protection can be achieved through several methods, including establishing an SSL-encrypted connection from a Delphi application to a remote PostgreSQL server. PostgreSQL supports data transfer over TCP/IP, both with or without SSL encryption.

You can establish a secure connection to a PostgreSQL server using SecureBridge, a solution offered by Devart. It allows you to embed an SSL client into a Delphi or C++ Builder application.

This page demonstrates how to connect to PostgreSQL with UniDAC using SSL as the encryption method.

• Connect Using SecureBridge
• Connect Using the OpenSSL library

Connect Using SecureBridge

To connect to PostgreSQL using UniDAC and SecureBridge components, no external libraries are required. SecureBridge provides the necessary SSL functionality directly within the components.

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



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



3. Select the TUniConnection component and, in Object Inspector, set the IOHandler property to the instance of TCRSSLIOHandler (CRSSLIOHandler1).



4. Double-click the TUniConnection component and, on the Options tab, fill out the fields:
   • Provider – Select PostgreSQL.
   • SSLCACert – Specify the full path of the Certificate Authority certificate file.
   • SSLCert – Specify the full path of the client certificate file.
   • SSLKey – Specify the full path of the client private key file.
   • SSLMode – Select smRequire. This value forces the application to connect only through an SSL connection. If a connection attempt fails, an exception is raised.

   

5. On the Connect tab, fill out the fields:
• Provider – Select PostgreSQL.
• Server – Enter the host name or IP address of the PostgreSQL server.
• Port – Specify the PostgreSQL server port.
• 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.
6. Click Connect to test the connection to the PostgreSQL server, then click OK to close the dialog.



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



8. Select the TDBGrid component and set the DataSource property to the instance of TDataSource (DataSource1).



9. Double-click the TUniQuery component, enter a SQL query to be run against the PostgreSQL database, and then click OK.



10. 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.



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

Connect Using the OpenSSL library

Another way to embed SSL client functionality into your Delphi app that uses UniDAC components to access PostgreSQL is by using the OpenSSL library. The OpenSSL library implements the SSL protocol and enables servers to communicate with their clients securely.

To establish an SSL connection, set the following options:

• SSLCACert – The full path of the Certificate Authority certificate file.
• SSLCert – The full path of the client certificate file.
• SSLKey – The full path of the client private key file.
• SSLCipherList – A list of allowed ciphers to use for SSL encryption.