Configuring SSL Support for PostgreSQL

SSL can be enabled by setting the configuration parameter SSL to on in the PostgreSQL configuration file ($PGDATA/postgresql.conf ). If SSL is enabled, the corresponding hostssl entry must be present or added in the pg_hba.conf file.

When SSL is enabled, Oracle GoldenGate uses the root certificate, root certification revocation list (CRL), server client certificate, and key from the default locations, as shown in the following snippet:

~/.postgresql/root.crt
~/.postgresql/root.crl
~/.postgresql/postgresql.crt
~/.postgresql/postgresql.key

You need to create the desired entities from this list, and store them in appropriate locations.

If the SSL configuration is setup using non-default locations, then the following environment variables should be set up as per the environment.

PGSSLROOTCERT
PGSSLCRL
PGSSLCERT
PGSSLKEY

Changes required in $ODBCINI file

The SSL support can be enabled by setting the EncryptionMethod DSN attribute to 1 or 6 in the $ODBCINI file.

If set to 0 (No Encryption), data is not encrypted.

If set to 1 (SSL), data is encrypted using the SSL protocols specified in the Crypto Protocol Version connection option. If the specified encryption method is not supported by the database server, the connection fails and the driver returns an error.

If set to 6 (RequestSSL), the login request and data are encrypted using SSL if the server is configured for SSL. If the server is not configured for SSL, an unencrypted connection is established. The SSL protocol used is determined by the setting of the Crypto Protocol Version connection option.

If the database server/client certificates also need to be validated, then the corresponding KeyStore file needs to be created and the below mentioned ODBC DSN attributes should be setup accordingly in $ODBCINI.

KeyStore=<path to .p12 keystore file>
KeyStorePassword=<keystore-passwd>
TrustStore=<path to root certificate>
ValidateServerCertificate=1