Using Secure Sockets Layer (SSL) Connections v6.2

Publication server and subscription server connections to Postgres publication databases, Postgres subscription databases, and Postgres primary nodes can be accomplished using secure sockets layer (SSL) connectivity.

xDB Replication Server does not support SSL connections to Oracle and SQL Server databases used within any xDB replication system.

For a single-master replication system, the following connections can be made to Postgres databases enabled with SSL:

• Publication server connection to the publication database and to the subscription databases.
• Subscription server connection to the subscription databases.
• Migration Toolkit connection to the publication and subscription databases.

For a multi-master replication system, the following connections can be made to Postgres databases enabled with SSL:

• Publication server connection to the primary definition node and the non-MDN nodes.
• Migration Toolkit connection to the primary definition node and the non-MDN nodes.

Using SSL requires various prerequisite configuration steps performed on the database servers involved with the SSL connections as well as on the Java truststore and keystore on the hosts running the publication server and subscription server.

The Java truststore is the file containing the Certificate Authority (CA) certificates with which the Java client (the publication server and subscription server) uses to verify the authenticity of the server to which it is initiating an SSL connection.

The Java keystore is the file containing private and public keys and their corresponding certificates. The keystore is required for client authentication to the server, which is used for xDB Replication Server SSL connections.

The following is material to which you can refer to for guidance in setting up the SSL connections:

• See the section on secure TCP connections with SSL in Chapter 17 Server Setup and Operation in the PostgreSQL Core Documentation located at:

https://www.postgresql.org/docs/current/static/ssl-tcp.html

for information on setting up SSL connectivity to Postgres database servers.

• For information on JDBC client connectivity using SSL see the section on configuring the client in Chapter 4 Using SSL in the The PostgreSQL JDBC Interface (https://jdbc.postgresql.org/documentation/94/ssl.html).

The following sections provide additional information for the configuration steps of using SSL with the xDB Replication Server.

• Configuring SSL on a Postgres database server (Section Configuring SSL on a Postgres Database Server)
• Configuring SSL on a JDBC client for the publication and subscription servers (Section Configuring SSL for the Publication Server and Subscription Server)
• Requesting SSL connection to the xDB Replication Server databases (Section Requesting SSL Connection to the xDB Replication Server Databases)

Configuring SSL on a Postgres Database Server

This section provides an example of configuring SSL on a Postgres database server to demonstrate the use of SSL with xDB Replication Server. A self-signed certificate is used for this purpose.

Step 1: Create the certificate signing request (CSR).

In the following example the generated certificate signing request file is server.csr. The private key is generated as file server.key.

$ openssl req -new -text -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=EnterpriseDB/OU=XDB/emailAddress=support@enterprisedb.com/CN=enterprisedb' -keyout server.key -out server.csr
Generating a 1024 bit RSA private key
......................................................++++++
.++++++
writing new private key to 'server.key'
-----

Alternatively, user name maps can be used as defined in the pg_ident.conf file to permit more flexibility for the common name and database user name. Steps 8 and 9 describe the use of user name maps.

Step 2: Generate the self-signed certificate.

The following generates a self-signed certificate to file server.crt using the certificate signing request file, server.csr, and the private key, server.key, as input.

$ openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Signature ok
subject=/C=US/ST=Massachusetts/L=Bedford/O=EnterpriseDB/OU=XDB/emailAddress=support@enterprisedb.com/CN=enterprisedb
Getting Private key

Step 3: Make a copy of the server certificate (server.crt) to be used as the root Certificate Authority (CA) file (root.crt).

$ cp server.crt root.crt

Step 4: Delete the now redundant certificate signing request (server.csr).

$ rm server.csr

Step 5: Move or copy the certificate and private key files to the Postgres database server data directory, POSTGRES_INSTALL_HOME/data.

$ mv root.crt /var/lib/edb/as14/data
$ mv server.crt /var/lib/edb/as14/data
$ mv server.key /var/lib/edb/as14/data

Step 6: Set the file ownership and permissions on the certificate files and private key file.

Set the ownership to the operating system account that owns the data subdirectory of the Postgres database server, which is either enterprisedb or postgres depending upon the chosen installation mode (Oracle compatible or PostgreSQL compatible) when you installed your Postgres database server.

$ chown enterprisedb root.crt server.crt server.key
$ chgrp enterprisedb root.crt server.crt server.key
$ chmod 600 root.crt server.crt server.key
$ ls -l
total 140
        .
        .
        .
-rw------- 1 enterprisedb enterprisedb  1346 Mar 15 09:31 root.crt
-rw------- 1 enterprisedb enterprisedb  1346 Mar 15 09:30 server.crt
-rw------- 1 enterprisedb enterprisedb  1704 Mar 15 09:28 server.key

Step 7: In the postgresql.conf file, make the following modifications.

ssl = on                               # (change requires restart)
ssl_cert_file = 'server.crt'           # (change requires restart)
ssl_key_file = 'server.key'            # (change requires restart)
ssl_ca_file = 'root.crt'               # (change requires restart)

Step 8: Modify the pg_hba.conf file to enable SSL usage on the desired publication, subscription, or primary node databases.

In the pg_hba.conf file, the hostssl type indicates the entry is used to validate SSL connection attempts from the client (the publication server and the subscription server).

The authentication method is set to cert with the option clientcert=1 in order to require an SSL certificate from the client against which authentication is performed using the common name of the certificate (enterprisedb in this example).

The map=sslusers option specifies that a mapping named sslusers defined in the pg_ident.conf file is to be used for authentication. This mapping allows a connection to the database if the common name from the certificate and the database user name attempting the connection match the SYSTEM-USERNAME/PG-USERNAME pair listed in the pg_ident.conf file.

The following is an example of the settings in the pg_hba.conf file if the publication and subscription databases (edb and subnode) must use SSL connections.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections for for Postgres v13:
hostssl   edb,subnode   all  192.168.2.0/24   cert clientcert=verify-ca map=sslusers

# IPv4 local connections for for Postgres v14:
hostssl   edb,subnode   all  192.168.2.0/24   cert clientcert=verify-full map=sslusers

Step 9: The following shows the user name maps in the pg_ident.conf file related to the pg_hba.conf file by the map=sslusers option. These user name maps permit you to specify database user names pubuser, subuser, MMRuser, or enterprisedb in the User field of the Add Database or Update Database dialog box when adding the publication, subscription, or primary node databases in the xDB Replication Console.

In other words, these are the permitted set of database user names that can be used by the publication server and subscription server to connect to the publication, subscription, or primary node databases.

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
sslusers        enterprisedb            pubuser
sslusers        enterprisedb            subuser
sslusers        enterprisedb            MMRuser
sslusers        enterprisedb            enterprisedb

Step 10: Restart the Postgres database server after you have made the changes to the Postgres configuration files.

Configuring SSL for the Publication Server and Subscription Server

After you have configured SSL on the Postgres database server, the following steps provide an example of generating a certificate and keystore file for the publication server and subscription server (the JDBC clients).

Before you begin, configure the client for SSL with trigger mode.

• If you are using PostgreSQL, on the SSL-enabled Postgres database server:

  Make the following client/cert files available on the publication/subscription server using an SSL connection:

  • postgresql.crt
  • postgresql.pk8
  • root.crt

  In our example, we use the copy of this self-signed certificate and key generated for the database server on the client side.

  The default location of these files is {user.home}/.postgresql(e.g/var/lib/edb/.postgresql/). The file location can be overridden using SSL connection parameters or Postgres SSL environmental variables, see Setting Non-default Paths using Environment Variables for more information.

  Copy and rename the files:

  Toggle WrapCopy

  $ cd /var/lib/pgsql/.postgresql/
  $ cp /var/lib/pgsql/14/data/server.crt postgresql.crt
  $ cp /var/lib/pgsql/14/data/root.crt .
  $ cp /var/lib/pgsql/14/data/server.key postgresql.key
  $ openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES -nocrypt

  Note

  This completes the SSL configuration for the PostgreSQL publication server and subscription server.

• If you are using EDB Postgres Advanced Server, on the SSL-enabled Postgres database server:

  Make the following client/cert files available on the publication/subscription server using an SSL connection:

  • xdb.keystore
  • xdb_pkcs.p12

Step 1: Using files server.crt and server.key located under the Postgres database server data subdirectory, create copies of these files and move them to the host where the publication server and subscription server are running.

cp server.crt xdb.crt
cp server.key xdb.key

For this example, assume file xdb.crt is a copy of server.crt and xdb.key is a copy of server.key.

Step 2: Create a copy of xdb.crt.

$ cp xdb.crt xdb_root.crt
$ ls -l
total 12
-rw-r--r-- 1 user user 1346 Mar 15 09:58 xdb.crt
-rw-r--r-- 1 user user 1704 Mar 15 09:58 xdb.key
-rw-r--r-- 1 user user 1346 Mar 15 10:00 xdb_root.crt

Step 3: Create a Distinguished Encoding Rules (DER) format of file xdb_root.crt. The generated DER format of this file is xdb_root.crt.der. The DER format of the file is required for the keytool program in the next step.

$ openssl x509 -in xdb_root.crt -out xdb_root.crt.der -outform der
$ ls -l
total 16
-rw-r--r-- 1 user user 1346 Mar 15 09:58 xdb.crt
-rw-r--r-- 1 user user 1704 Mar 15 09:58 xdb.key
-rw-r--r-- 1 user user 1346 Mar 15 10:00 xdb_root.crt
-rw-rw-r-- 1 user user  954 Mar 15 10:05 xdb_root.crt.der

Step 4: Use the keytool program to create a keystore file (xdb.keystore) using xdb_root.crt.der as the input. This process adds the certificate of the Postgres database server to the keystore file.

The keytool program can be found under the bin subdirectory of the Java Runtime Environment installation.

You will be prompted for a new password. Save this password for the next step.

$ /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64/jre/bin/keytool -keystore xdb.keystore -alias xdbstore -import -file xdb_root.crt.der
Enter keystore password:
Re-enter new password:
Owner: CN=enterprisedb, EMAILADDRESS=support@enterprisedb.com, OU=XDB, O=EnterpriseDB, L=Bedford, ST=Massachusetts, C=US
Issuer: CN=enterprisedb, EMAILADDRESS=support@enterprisedb.com, OU=XDB, O=EnterpriseDB, L=Bedford, ST=Massachusetts, C=US
Serial number: d7e9966b48e91523
Valid from: Tue Mar 15 08:30:37 GMT-05:00 2016 until: Wed Mar 15 08:30:37 GMT-05:00 2017
Certificate fingerprints:
   MD5:  5D:32:AB:47:A2:44:48:84:0B:CA:EC:9E:C9:28:CE:64
   SHA1: 31:14:C4:0A:E6:93:AA:2C:3E:4B:09:77:AB:94:DB:71:CB:58:99:D9
   SHA256: 2B:EA:59:35:E6:5B:07:07:30:96:D4:80:B0:E1:13:5B:5E:45:97:2E:D0:5C:4F:D8:2F:A6:23:DA:F8:30:D6:17
   Signature algorithm name: SHA1withRSA
   Version: 1
Trust this certificate? [no]:  yes
Certificate was added to keystore
$ ls -l
total 20
-rw-r--r-- 1 user user 1346 Mar 15 09:58 xdb.crt
-rw-r--r-- 1 user user 1704 Mar 15 09:58 xdb.key
-rw-rw-r-- 1 user user 1019 Mar 15 10:18 xdb.keystore
-rw-r--r-- 1 user user 1346 Mar 15 10:00 xdb_root.crt
-rw-rw-r-- 1 user user  954 Mar 15 10:05 xdb_root.crt.der

Step 5: Generate the encrypted form of the new password specified in the preceding step.

The encrypted password must be specified with the sslTrustStorePassword configuration option of the publication server configuration file for publication server SSL connections and the subscription server configuration file for subscription server SSL connections. (See Publication and Subscription Server Configuration Options for information on the publication server and subscription server configuration files.)

Encrypt the password using the xDB Replication Server CLI encrypt command. The following example shows this process encrypting the password contained in file infile.

$ export PATH=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64/jre/bin:$PATH
$ cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin
$ java -jar edb-repcli.jar -encrypt -input ~/infile -output ~/pwdfile
$ cat ~/pwdfile
LGn6+AagiXqumxVHlOKk3w==

Step 6: Create a PKCS #12 format of the keystore file (xdb_pkcs.p12) using files xdb.crt and xdb.key as input.

You will be prompted for a new password. Save this password for the next step.

$ openssl pkcs12 -export -in xdb.crt -inkey xdb.key -out xdb_pkcs.p12
Enter Export Password:
Verifying - Enter Export Password:
$ ls -l
total 24
-rw-r--r-- 1 user user 1346 Mar 15 09:58 xdb.crt
-rw-r--r-- 1 user user 1704 Mar 15 09:58 xdb.key
-rw-rw-r-- 1 user user 1019 Mar 15 10:18 xdb.keystore
-rw-rw-r-- 1 user user 2557 Mar 15 10:34 xdb_pkcs.p12
-rw-r--r-- 1 user user 1346 Mar 15 10:00 xdb_root.crt
-rw-rw-r-- 1 user user  954 Mar 15 10:05 xdb_root.crt.der

Step 7: Generate the encrypted form of the new password specified in the preceding step.

The encrypted password must be specified with the sslKeyStorePassword configuration option of the publication server configuration file for publication server SSL connections and the subscription server configuration file for subscription server SSL connections.

Encrypt the password using the xDB Replication Server CLI encrypt command.

$ export PATH=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64/jre/bin:$PATH
$ cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin
$ java -jar edb-repcli.jar -encrypt -input ~/infile -output ~/pwdfile
$ cat ~/pwdfile
LGn6+AagiXqumxVHlOKk3w==

Step 8: Copy files xdb.keystore and xdb_pkcs.p12 to a directory location where they are to be accessed by the publication server and subscription server.

Step 9: In the publication server and subscription server configuration files, set the location of file xdb.keystore with the sslTrustStore option and the location of file xdb_pkcs.p12 with the sslKeyStore option.

The following shows the SSL configuration options set for the files generated in this example.

sslTrustStore=/tmp/sslclient/xdb.keystore
sslTrustStorePassword=LGn6+AagiXqumxVHlOKk3w==
sslKeyStore=/tmp/sslclient/xdb_pkcs.p12
sslKeyStorePassword= ygJ9AxoJEX854elcVIJPTw==

The encrypted sslTrustStorePassword is obtained from Step 5 after being specified for the keytool program in Step 4.

The encrypted sslKeyStorePassword is obtained from Step 7 after being specified for the openssl pkcs12 program in Step 6.

Section Summary of SSL Configuration Options contains a summary of the publication server and subscription server configuration options for SSL connections.

Step 10: Restart the publication and subscription servers.

Configuring publication/subscription server in case of WAL stream changeset logging

In the case of WAL stream changeset logging, while adding a publication or a subscription database that accepts only ssl connection, xDB validates if the database server is configured for logical replication using libpq connection.

chown enterprisedb postgresql.key

For the SSL connection, libpq must have the certificates and key as given in the following table along with the client certs and key you set up for trigger mode. The default directory is ${user.home}/.postgresql.

Table 7-1: libpq/Client SSL File Usage

Make sure that the name of the certificates and key is same as given in the above table.

Execute the following commands to change the permission of the certificates in ${user.home}/.postgresql.

chmod 0644 root.crt postgresql.crt
chmod 0600 postgresql.key
chown postgres postgresql.key

To setup different source and target database types (for example, source database =POSTGRESQL and target database =enterprisedb) follow the steps below:

1. Generate the certificate for POSTGRESQL database and follow the table 7-1 for placing certificate files in the default directory.

2. Copy these certificates in EDB Postgres Advanced Server data directory.

   Toggle WrapCopy

   [root@localhost data]# cp /var/lib/pgsql/14/data/root.crt .
   [root@localhost data]# cp /var/lib/pgsql/14/data/server.crt .
   [root@localhost data]# cp /var/lib/pgsql/14/data/server.key .

3. Execute the following commands to change the permissions of the certificates in the EDB Postgres Advanced Server data directory.

   Toggle WrapCopy

   [root@localhost data]# sudo chown enterprisedb root.crt server.crt server.key
   [root@localhost data]# sudo chgrp enterprisedb root.crt server.crt server.key
   [root@localhost data]# sudo chmod 600 root.crt server.crt server.key

Using different databases for the source and target

Follow these steps if you are using different databases for the source and target; for example, if you are using PostgreSQL for your source database and EDB Postgres Advanced Server for your target database.

1. Generate the certificate for the PostgreSQL database. See Configuring SSL on a Postgres database server.

2. Configure SSL for Replication Server. See the steps for PostgreSQL in Configuring SSL for the publication server and subscription server.

3. Create the same user in EDB Postgres Advanced Server which is same as the CN value used to generate the certificate for the PostgreSQL database. For example if CN=postgres if specified as shown in following command then the postgres role should be created in EDB Postgres Advanced Server.

   Toggle WrapCopy

   openssl req -new -text -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=EnterpriseDB/OU=XDB/emailAddress=muhammad.imtiaaz@enterprisedb.com/CN=postgres' -keyout server.key -out server.csr

   Create the user:

   Toggle WrapCopy

   CREATE ROLE postgres LOGIN SUPERUSER PASSWORD 'edb';

   If you specified map=sslusers for PostgreSQL and EDB Postgres Advanced Server in pg_hba.conf, add the following to pg_ident.conf using the same user name for both PostgreSQL and EDB Postgres Advanced Server:

   Toggle WrapCopy

   cat /var/lib/pgsql/14/data/pg_ident.conf
   # ----------------------------------
   # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
   sslusers        postgres                postgres

4. Copy the certificates from the PostgreSQL data directory to the EDB Postgres Advanced Server data directory:

   Toggle WrapCopy

   cd /var/lib/edb/as14/data
   [root@localhost data]# cp /var/lib/pgsql/14/data/root.crt .
   [root@localhost data]# cp /var/lib/pgsql/14/data/server.crt .
   [root@localhost data]# cp /var/lib/pgsql/14/data/server.key .

5. Restart the EDB Postgres Advanced Server service.

   Toggle WrapCopy

   systemctl restart edb-as-14.service

6. Change the permissions of the certificates in the EDB Postgres Advanced Server data directory.

   Toggle WrapCopy

   [root@localhost data]# sudo chown enterprisedb root.crt server.crt server.key
   [root@localhost data]# sudo chgrp enterprisedb root.crt server.crt server.key
   [root@localhost data]# sudo chmod 600 root.crt server.crt server.key

7. Make the required configuration changes for EDB Postgres Advanced Server see Configuring SSL on a Postgres database server and restart the service:

   Toggle WrapCopy

   systemctl restart edb-as-14.service

Requesting SSL Connection to the xDB Replication Server Databases

Once SSL connectivity has been configured, a URL option must be supplied when configuring a single-master or multi-master replication system for those databases to which an SSL connection is intended to be used.

The SSL URL option informs Java to use SSL when the publication server or subscription server attempts to connect to an xDB Replication Server database (publication, subscription, or primary node database) on which the SSL URL option has been set to true.

The configuration steps where these options are specified are as follows:

• For using SSL connections in a single-master replication system, the URL options must be specified as shown in Section Adding a Publication Database for the publication database and in Section Adding a Subscription Database for the subscription databases.
• For using SSL connections in a multi-master replication system, the URL options must be specified as shown in Section Adding the Primary definition node for the primary definition node and in Section Creating Additional Primary nodes for the non-MDN nodes.

Earlier we created self-signed certificates for the database server by specifying the value of the CN field as the database user name (for example, postgres or enterprisedb, and so on). In this case, we use the “verify-ca” value for sslmode parameter to indicate the server certificate is validated against the CA. We do this because the hostname given in the command Add Database or Update Database could not be verified against CN value present certificate, which is the database user name.

For publication, subscription, and primary node databases, in the URL Options field of the Add Database or Update Database dialog box, enter the following:

ssl=true&sslmode=verify-ca

You can specify the ssl=true&sslmode=verify-ca URL option on the Add Database dialog box.

Setting Non-default Paths using Environment Variables

You can override the default paths of certificates and keys by setting the non-default paths in a terminal using the environment variables PGSSLKEY, PGSSLCERT, and PGSSLROOTCERT. You then need to export the paths in a terminal before running any Replication Server CLI command or launching the EPRS Replication Console. For example:

$ export PGSSLKEY=/home/postgresql.pk8
$ export PGSSLCERT=/home/postgresql.crt
$ export PGSSLROOTCERT=/home/root.crt

After setting and exporting the environment variables, from the same terminal, you may either run the Replication Server CLI command or launch the EPRS Replication Console.

Setting Non-default Paths using SSL Connection Parameters

Non-default paths of certificates and keys can be overridden using SSL connection parameters sslrootcert, sslcert, and sslkey. You need to specify these parameter values in urlOptions:

-urloptions "ssl=true&sslmode=verify-ca&sslcert=/home/postgresql.crt&sslkey=/home/postgresql.pk8&sslrootcert=/home/root.crt"

You set urlOptions using either the:

• Replication Server CLI using the addpubdb/addsubdb command
• EPRS Replication Console while adding the publication and subscription database

Summary of SSL Configuration Options

The following is a summary of the publication server and subscription server configuration options that are applicable to SSL connections.

sslTrustStoreType

The sslTrustStoreType option specifies the truststore format. Set this option to the Java truststore format of the client.

sslTrustStoreType=truststore_format

The default value for truststore_format is jks for the JKS truststore file format.

sslTrustStore

The xDB Replication Server uses the default Java truststore for SSL connectivity.

The typical default location of the truststore is in directory JAVA_HOME/jre/lib/security or JAVA_HOME/lib/security in a file named cacerts. (JAVA_HOME is the Java installation directory.)

Specify the full directory path to the truststore file with this option. sslTrustStore=truststore_file

sslTrustStorePassword

Encrypt the password for the Java system truststore using the xDB Replication Server CLI encrypt command (see Encrypting Passwords) and specify the encrypted password with the sslTrustStorePassword option.

sslTrustStorePassword=encrypted_password

sslKeyStoreType

The sslKeyStoreType option specifies the keystore format. Set this option to the Java keystore format of the client.

sslKeyStoreType=keystore_format

The default value for keystore_format is pkcs12 for the PKCS #12 keystore file format.

sslKeyStore

Specify the full directory path to the keystore file with this option.

sslKeyStore=keystore_file

sslKeyStorePassword

Encrypt the password for the Java system keystore using the xDB Replication Server CLI encrypt command (see Encrypting Passwords) and specify the encrypted password with the sslKeyStorePassword option.

sslKeyStorePassword=encrypted_password

• On this page
• Configuring SSL on a Postgres Database Server
• Configuring SSL for the Publication Server and Subscription Server
• Using different databases for the source and target
• Requesting SSL Connection to the xDB Replication Server Databases
• Setting Non-default Paths using Environment Variables
• Setting Non-default Paths using SSL Connection Parameters
• Summary of SSL Configuration Options