Developers

Omnis Technical Note TNSQ0031Updated March 2021

Making SSL Connections using the PostgreSQL DAM

for Omnis Studio 4.3.2 and later
by Gary Ashford

Introduction
This article describes how you can use the Secure Sockets Layer (SSL) to make secure connections using the PostgreSQL DAM. To make secure connections, your DAM needs to support the $options session property, i.e. you need Omnis Studio 4.3.2 or later and/or the latest PostgreSQL DAM (updated DAMs are available from the ODPP download area).

Additionally, your PostgreSQL server must support SSL and must be configured to accept SSL connections. The PostgreSQL client library (libpq.dll / libpq.dylib / libpq.so) used on each client machine must also have SSL support "compiled-in".

Creating self-signed SHA-2 server and client certificates
For testing purposes, you can generate self-signed certificate files, (certificates for use internally which do not require a trusted Certifcate Authority). Server certificate files will need to be in place before the server can be configured and re-started. To generate SSL certificates, you need to have OpenSSL software installed, (on the server machine for example). This is available from www.openssl.org.

 Create the Server files
A summary of the commands needed to create the .key and .crt files is shown below. For a more detailed description, please refer to the links at the bottom of the page.

openssl genrsa -out server.key 2048 -sha256 openssl req -new -key server.key -out server.csr

Generates a Certificate Signing Request from the private key. During the generation of the CSR, you will be prompted for several pieces of information. These are the X.509 attributes of the certificate. One of the prompts will be for "Common Name (e.g., YOUR name)". This field must be filled in with the computer name of the PostgreSQL server, i.e. the computer's ping-able network domain name or IP address.

openssl rsa -in server.key -out server.key

Strips the passphrase (entered above) from the server key. (PostgreSQL will not be able to start-up if the key requires a passphrase since password prompting is not allowed).

openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt -sha256

Generates and self-signs the server certificate using the .csr and .key files generated previously. In this example, the certificate will be valid for 365 days.

copy server.crt root.crt

Generates a trusted root certifcate that will be common to both server and client machines. When not using self-signed certificates, this file will normally be supplied by the issuing Certificate Authority. Because we are self-signing, this file is a duplicate of the server certificate.

Copy the files: server.crt, server.key and root.crt into the PostgreSQL\...\data folder.

 Create the Client files
The commands to generate the client .key and .csr files are similar to those shown above with the exception that the resulting key and certificate files will be named postgresql.key and postgresql.crt respectively:

openssl genrsa -out postgresql.key 2048 -sha256 openssl rsa -in postgresql.key -out postgresql.key openssl req -new -key postgresql.key -out postgresql.csr

During the generation of the CSR, you will be prompted for several pieces of information. One of the prompts will be for "Common Name (e.g., YOUR name)". This field must be filled in with the user name that will be used to connect to the database, e.g. postgres.

openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial -sha256

Generates and signs the client certificate using our trusted root.crt

Copy the files: postgresql.crt , postgresql.key and root.crt to the client machine. For Windows clients, the files are copied into %appdata%\postgresql\ (e.g. "C:\Users\myUser\AppData\Roaming\postgresql"). For Linux and MacOSX, the files are copied into ~/.postgresql/
Note: On MacOSX and Linux you should additionally remove group and world permissions from the postgresql.key file:

cd ~/.postgresql chmod 600 postgresql.key

Configuring the server
Once the server certificate files are in place, you can edit the server configuration file to enable SSL support. To configure the server to accept SSL connections, you need to edit the postgresql.conf file (found in the data folder) and change the SSL setting to:

ssl = on

uncommenting the line if necessary. You should also specify the name of the root certificate:

ssl_ca_file = 'root.crt'

You need to restart the server (or Windows service) in order for changes to take effect.
If the server fails to start, or there are error messages in the Windows application event log, verify that the server certificate files were generated correctly and placed in the correct folder. Please refer to the links at the bottom of the page for further information on SSL configuration.

PostgreSQL Client Library
In order for the PostgreSQL DAM to support SSL connections, the accompanying PostgreSQL client library ( libpq.dll, libpq.dylib or libpq.so ) must have SSL support compiled-in. If your client library does not include SSL support, you can download and extract a replacement library plus dependencies from the latest PostgreSQL binary distribution.

*OpenSSL libraries are provided under both the Apache and OpenSSL licenses . Please review the licensing conditions if you intend to distribute these files.
Please Note: Due to the above restrictions, the libpq DLL shipped with Omnis Studio does not support SSL. Please remember to download the correct installer/archive for your system. The 32-bit edition of Omnis Studio requires 32-bit (x86) DLLs, and the 64-bit edition requires 64-bit (x64) DLLs

Windows

On Windows, an SSL-enabled client library will have the following additional dependencies:

ssleay32.dll libeay32.dll libintl-8.dll libiconv-2.dll

*module associated with the OpenSSL toolkit *contains encryption functions which allow encoded communications over networks provides native language support to programs provides Unicode and character set conversion functions

Update: For use with PosgreSQL v13.0 and later, libpq.dll has the following dependencies:
(If in doubt, you can use the Dependency Walker utility to discover the dependencies of your DLL).

libssl-1_1-x64.dll libcrypto-1_1-x64.dll libintl-8.dll

OpenSSL toolkit library provides OpenSSL cryptography functions provides Unicode and character set conversion functions

Where the operating system does not already provide them, you can place these files in the Omnis folder alongside the main client library, or somewhere on the standard library search path (e.g. C:\Windows\System32 or C:\Windows\SysWOW64).

Although the PostgreSQL DAM will load in the absence of these dependencies, you will receive "Client or interface function not available" errors if you attempt to use the SSL-enabled DAM without them.

On Windows, you can use a dependency checker program to examine the dynamic dependencies of a DLL, Dependency Walker for example:

Output from Dependency Walker showing additional dependencies on SSL libraries.

macOS

On macOS, the dependencies are:

libssl.dylib libcrypto.dylib

OpenSSL toolkit library provides OpenSSL cryptography functions

macOS will search for these libraries on the standard library search path, which includes locations in the following order:

• Locations specified by the $DYLD_LIBRARY_PATH environment variable (N/A unless Omnis is started from a command line prompt).
• The local user's "lib" folder, i.e. ~/lib
• The /usr/local/lib folder.
• The /usr/lib folder.

If you are using Omnis Studio 10.X on macOS Big Sur or later, you may wish to read this tech note in conjunction with TNSQ0040 which discusses differences in the way client libraries are located in code-signed applications.

We recommend that you place libpq.dylib, together with all dependent libraries extracted from the PostgreSQL binary distribution into the User's lib folder, creating this folder if necessary. Remember that "versioned" libraries may require the creation of additional symbolic links in order to satisfy the library dependencies.

If you have Developer Tools installed, you can use the otool command to list the dependencies of a given file, e.g.

cd ~/lib otool -L libpq.5.11.dylib

and/or create symbolic links as required, e.g.

ln -s libpq.5.11.dylib libpq.dylib

Remember that libcrypto.x.dylib and libssl.x.dylib my themselves have dependencies on other specific library versions. All dependencies must be met in order for libpq.dylib to load successfully.

Linux

On Linux, the dependencies on libpq.so are:

libssl.so libcrypto.so

OpenSSL toolkit library provides OpenSSL cryptography functions

You can use a console command to show library dependencies:

ldd libpq.so

and a similar procedure applies when creating symbolic links in order to resolve the library dependencies for libpq.x.so

Using Host Based Authentication
You will need to edit the pg_hba.conf file inside the server's data folder if connection via SSL is to be mandatory. This is discussed in detail on the PostgreSQL website although in summary, host entries that begin:

host hostssl

match with both SSL and non-SSL connection attempts match with SSL connection attempts only

If your client will be attempting to match with a host entry, you will need to specify an additional logon connection option to request an SSL connection. If your client will be attempting to match with a hostssl entry, then the extra connection option is not required.

A complete pg_hba.conf entry might look similar to:

# TYPE hostssl

DATABASE all

USER all

CIDR-ADDRESS 192.168.1.0/24

METHOD cert clientcert=1

where 'cert' indicates authentication using SSL client certificates. "clientcert=1" is added to hostssl options to force the client certificates to be checked.

Making an SSL connection
Once the client and server certificate files are in place and any changes to the pg_hba.conf file are complete, you can test the connectivity using Omnis code similar to:

Do sessObj.$options.$assign('sslmode=require') Do sessObj.$logon('tigerlog-ad0f55','postgres',,'mySession') Returns #F

sslmode is used to arbitrate where the pg_hba.conf file defines two or more connection methods that the Omnis client can use. It's default value ('prefer') will cause an SSL connection method to take precedence over a non-SSL connection method, so it is not necessary to set this when pg_hba.conf defines only a single connection method. For testing purposes, setting sslmode to 'require' means that libpq will only attempt the SSL connection method(s) and will not fallback to the non-SSL method(s).
The supplied hostname should match the Common Name that was previously written into the server certificate.
You will recall that the username was previously written into the Common Name of the client certificate. The client certifcate, together with the key and root certificate guarantee the authenticity of the user.
Also note that no password is required when using SSL client certificate authentication.

You can also logon via the SQL Browser if preferred. You can access the $options property via the Advanced tab when editing session details.

Determining whether a connection is secure
To determine whether an established connection is using SSL, Studio 4.3.2.1 and 5.2 provide a $getssl() session method. $getssl() returns kTrue if the DAM is able to get SSL information from the connection, kFalse otherwise. You may optionally pass a list parameter to $getssl() in order to retrieve information from the SSL structure. Currently, only the SSL type and version are returned however.

Links and References
 
Secure TCP/IP connection using SSL
www.postgresql.org/docs/9.0/static/ssl-tcp.html

Configuring the server to accept SSL connections
www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html

Creating self-signed certificate files
www.howtoforge.com/postgresql-ssl-certificates

Using the pg_hba.conf file
developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html

Dependency Walker Utility for Windows
http://www.dependencywalker.com

OpenSSL Toolkit (multiple platforms)
http://www.openssl.org