Omnis Technical Note TNSQ0031Updated March 2017

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.

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

*OpenSSL libraries for Windows are provided under the Apache license . 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

On Mac OS X, the dependencies are:

libssl.dylib
libcrypto.dylib

OpenSSL toolkit library
provides OpenSSL cryptography functions

and on Linux, the dependencies are:

libssl.so
libcrypto.so

OpenSSL toolkit library
provides OpenSSL cryptography 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.

On Linux, there is a console command to show library dependencies:

ldd libpq.so

and on Mac OSX, if you have Developer Tools installed, you can use:

otool -L libpq.dylib

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.akadia.com/services/ssh_test_certificate.html
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