Configuring SSL Trust

An SSL trust configuration is required to establish a replication channel between two Yellowbrick appliances. SSL trust protects information going over the network and ensures that the two systems communicate only with each other (and not an intermediary). The following configuration task is a prerequisite to using the Yellowbrick database replication feature.

When two Yellowbrick systems initialize communication, the connection requires an "SSL handshake" in both directions. This process requires a one-time configuration procedure that you complete with some SHOW SSL and IMPORT SSL commands. These commands import and validate SSL certificates that identify the systems in question and authorize communication to proceed.

In an SSL certificate, the common name (CN) is used to identify the host, using either its fully qualified domain name (FQDN) or a wildcard. For example, an FQDN might be yb007.bbc.jamesbond.com and a corresponding wildcard would be *.bbc.jamesbond.com

The CREATE REMOTE SERVER command must also specify the host name of the target system correctly in order for replication operations to proceed.

Tip: In the following procedure, you can use ybsql \g commands to copy and paste the output of the SSL commands directly to or from files. For example, the following command copies the results of the SHOW SSL SYSTEM command to a file name system_ssl.txt:
yellowbrick=# show ssl system \g system_ssl.txt
yellowbrick=# \q
$ more system_ssl.txt
                        SYSTEM CERTIFICATE                        
------------------------------------------------------------------
 -----BEGIN CERTIFICATE-----
 MIIF1jCCA76gAwIBAgIJAKypiRTcxKzjMA0GCSqGSIb3DQEBCwUAMIGHMQswCQYD
 VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs
 IEluYy4xJTAjBgNVBAMMHHliMDUtbWdyMS5zbGMueWVsbG93YnJpY2suaW8xIzAh
...

To configure SSL trust, follow these steps:
  1. Establish client (source system) trust of service (target system) in one of two ways:
    • Install an SSL certificate for the appliance using a well-known Certificate Authority (CA).
    • Import the self-signed certificate from the appliance into the client truststore configuration.

    The recommended procedure is to install an SSL certificate for the appliance, which is signed by a commercial or custom Certificate Authority (CA). This configuration requires you to contact the appropriate administrators in your organization to obtain an SSL certificate for the appliance, then change both the HTTPS SMC listener and Postgres listener to use the certificate for all SSL communications. When these steps are complete, trust is usually established between the client and service.

    If the SSL certificate cannot be obtained in the recommended way, the alternative method is to run the following commands:
    1. On the remote system, log into a database and show the SSL SYSTEM configuration. The SHOW SSL SYSTEM command displays the public certificate that the target system has configured for system connections over HTTPS for the SMC and the database (port 5432). For example:
      yellowbrick=# \pset format unaligned
      Output format is unaligned.
      yellowbrick=# SHOW SSL SYSTEM;
      SYSTEM CERTIFICATE
      -----BEGIN CERTIFICATE-----
      MIIDjDCCAnSgAwIBAgIJAJ5N7lPpO9qOMA0GCSqGSIb3DQEBCwUAMG8xCzAJBgNV
      BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg
      SW5jLjENMAsGA1UEAwwEZW56bzEjMCEGCSqGSIb3DQEJARYUaW5mb0B5ZWxsb3di
      cmljay5jb20wHhcNMTkwOTA5MTM0MDIyWhcNMjkwOTA4MTM0MDIyWjBvMQswCQYD
      VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs
      IEluYy4xDTALBgNVBAMMBGVuem8xIzAhBgkqhkiG9w0BCQEWFGluZm9AeWVsbG93
      YnJpY2suY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAzLTxNcTi
      h3FPeALtlPPBMpa4/btvDrX78vBtoQDDrxsNY/rRohDiimXlc/GSiUUNc1c8lSN1
      O3BG9JJx5S65x3xqpukv6PD1auDmqEtK6PSE7ycvqQ82w/U6QoHKunwjrSDqfFYj
      klRcXo/U2F6uep2limixFvwPJ8IdA/FawPWV8vdp7fTW5PB6HAELG0l1RkaFFGnP
      nnelB4tLPl8+ItiKnrivXHltY36h13Q5iRWHFHQRF+A5SRdeKWUpEOqtqGp0w6gS
      eBGH3t7wFuXxBznzX5XMrW72M0s3lo0P//47WxFS+TC8ajVxoD6N1agdwS9Nxi/v
      SC2rswQESmYUhQIDAQABoyswKTAJBgNVHRMEAjAAMAsGA1UdDwQEAwIF4DAPBgNV
      HREECDAGggRlbnpvMA0GCSqGSIb3DQEBCwUAA4IBAQA0tjJgfK2rtwOnzgDajzXP
      ra0w/PPVz8hg4qIDXKcECtdGzLZH57YOXztMwNRkj6B1vtvPyA48DCur5oEHuXD5
      E6QOCylTOxMJPQzsklRfdspPdi4/YtdbXGoEkVjp/I9jj7mINhcyjGNBwUmBWTO5
      2OQ5hxN5pAyMTv7nCHfy34EecB4SuMl7AENV8Lj95MNK5N8Hzu4xIFKaLco2Urb/
      cBndVlOOE1s/Nqa7yKuqRgnfR3VRlHUgDrF+E8QGeUwEyftOdpb2TrhBDiEVv1av
      NemH8HbTg5/G9k61hf1e6X+dFBPtrTgnwe5FClWHqnNaKxTMwj/ffeLS7T7bPyQ9
      -----END CERTIFICATE-----
      (21 rows)
      Important: Use the \pset format unaligned command in ybsql to make sure that no extra spaces are introduced into the output, and the text can be safely copied to the clipboard. Copy the entire contents of the certificate from -----BEGIN CERTIFICATE----- to the -----END CERTIFICATE-----. (The Yellowbrick system understands the leading blanks and will strip them; however, openssl does not, so the verification step later in this procedure will fail.)
      You can validate the contents of this certificate by saving it to a file and running an openssl command. For example:
      % openssl x509 -in system.pem -text -noout
      Certificate:
          Data:
              Version: 3 (0x2)
      ...
    2. Copy the results of the SHOW SSL SYSTEM command to the clipboard or save the results to a file.
    3. Log into a database on the source system and import the SYSTEM configuration from the target system by pasting the results of the SHOW SSL SYSTEM command into the IMPORT SSL TRUST command. For example:
      yellowbrick=# IMPORT SSL TRUST FROM '-----BEGIN CERTIFICATE-----
      yellowbrick'# MIIDjDCCAnSgAwIBAgIJAJ5N7lPpO9qOMA0GCSqGSIb3DQEBCwUAMG8xCzAJBgNV
      yellowbrick'# BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg
      yellowbrick'# SW5jLjENMAsGA1UEAwwEZW56bzEjMCEGCSqGSIb3DQEJARYUaW5mb0B5ZWxsb3di
      yellowbrick'# cmljay5jb20wHhcNMTkwOTA5MTM0MDIyWhcNMjkwOTA4MTM0MDIyWjBvMQswCQYD
      yellowbrick'# VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs
      yellowbrick'# IEluYy4xDTALBgNVBAMMBGVuem8xIzAhBgkqhkiG9w0BCQEWFGluZm9AeWVsbG93
      yellowbrick'# YnJpY2suY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAzLTxNcTi
      yellowbrick'# h3FPeALtlPPBMpa4/btvDrX78vBtoQDDrxsNY/rRohDiimXlc/GSiUUNc1c8lSN1
      yellowbrick'# O3BG9JJx5S65x3xqpukv6PD1auDmqEtK6PSE7ycvqQ82w/U6QoHKunwjrSDqfFYj
      yellowbrick'# klRcXo/U2F6uep2limixFvwPJ8IdA/FawPWV8vdp7fTW5PB6HAELG0l1RkaFFGnP
      yellowbrick'# nnelB4tLPl8+ItiKnrivXHltY36h13Q5iRWHFHQRF+A5SRdeKWUpEOqtqGp0w6gS
      yellowbrick'# eBGH3t7wFuXxBznzX5XMrW72M0s3lo0P//47WxFS+TC8ajVxoD6N1agdwS9Nxi/v
      yellowbrick'# SC2rswQESmYUhQIDAQABoyswKTAJBgNVHRMEAjAAMAsGA1UdDwQEAwIF4DAPBgNV
      yellowbrick'# HREECDAGggRlbnpvMA0GCSqGSIb3DQEBCwUAA4IBAQA0tjJgfK2rtwOnzgDajzXP
      yellowbrick'# ra0w/PPVz8hg4qIDXKcECtdGzLZH57YOXztMwNRkj6B1vtvPyA48DCur5oEHuXD5
      yellowbrick'# E6QOCylTOxMJPQzsklRfdspPdi4/YtdbXGoEkVjp/I9jj7mINhcyjGNBwUmBWTO5
      yellowbrick'# 2OQ5hxN5pAyMTv7nCHfy34EecB4SuMl7AENV8Lj95MNK5N8Hzu4xIFKaLco2Urb/
      yellowbrick'# cBndVlOOE1s/Nqa7yKuqRgnfR3VRlHUgDrF+E8QGeUwEyftOdpb2TrhBDiEVv1av
      yellowbrick'# NemH8HbTg5/G9k61hf1e6X+dFBPtrTgnwe5FClWHqnNaKxTMwj/ffeLS7T7bPyQ9
      yellowbrick'# -----END CERTIFICATE-----';
      IMPORT SSL TRUST
    4. Validate the import by running the SHOW SSL TRUST command. For example:
      yellowbrick=# SHOW SSL TRUST;
          hash    |                                                 details                                                 |                           certificate
      ------------+---------------------------------------------------------------------------------------------------------+------------------------------------------------------------------
       da228d23.0 | notBefore=Sep  9 13:40:22 2019 GMT                                                                     +| -----BEGIN CERTIFICATE-----                                     +
                  | notAfter=Sep  8 13:40:22 2029 GMT                                                                      +| MIIDjDCCAnSgAwIBAgIJAJ5N7lPpO9qOMA0GCSqGSIb3DQEBCwUAMG8xCzAJBgNV+
                  | issuer= /C=US/ST=CA/O=Yellowbrick Data, Inc./CN=enzo/emailAddress=info@yellowbrick.com                 +| BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg+
                  | subject= /C=US/ST=CA/O=Yellowbrick Data, Inc./CN=enzo/emailAddress=info@yellowbrick.com                +| SW5jLjENMAsGA1UEAwwEZW56bzEjMCEGCSqGSIb3DQEJARYUaW5mb0B5ZWxsb3di+
                  | serial=9E4DEE53E93BDA8E                                                                                +| cmljay5jb20wHhcNMTkwOTA5MTM0MDIyWhcNMjkwOTA4MTM0MDIyWjBvMQswCQYD+
                  |                                                                                                         | VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs+         
      ...
  2. Establish service (target system) trust of client (source system). This step exports the client's identity certificate and imports it into the service's truststore.

    For replication purposes, each Yellowbrick appliance is configured to accept SSL connections from clients that are connected via a trusted client certificate that contains the CN sys_ybd_system. This system account is restricted to replication activities, and cannot be used to log in for any other purpose. Each Yellowbrick appliance is preconfigured with a unique identity (UUID) that is saved in SSL signing certificates as both a custom Certificate Authority (CA) and a signed certificate from the CA. To establish trust from the client to service, you must import the public certificate from the CA of the client to the truststore of the service.

    1. Log into a database on the source system and export the SSL "CA" configuration, using the SHOW SSL CA command. For example:
      yellowbrick=# \pset format unaligned                                                                                                                                                                                                                    Output format is unaligned.
      yellowbrick=# SHOW SSL CA;                                                                                                                                                                                                                             CA CERTIFICATE
      -----BEGIN CERTIFICATE-----
      MIID3jCCAsagAwIBAgIJAJDtw8CBJMjNMA0GCSqGSIb3DQEBCwUAMH8xCzAJBgNV
      BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg
      SW5jLjEtMCsGA1UECwwkZWRhNTAxNGYtMTg3OS00MDg2LTg4NTUtZDQ5OGQ5ZTY5
      YmVjMRMwEQYDVQQDDApzeXNfeWJkX2NhMB4XDTE5MDkwOTIwNDkxNloXDTI5MDkw
      ODIwNDkxNlowfzELMAkGA1UEBhMCVVMxCzAJBgNVBAgMAkNBMR8wHQYDVQQKDBZZ
      ZWxsb3dicmljayBEYXRhLCBJbmMuMS0wKwYDVQQLDCRlZGE1MDE0Zi0xODc5LTQw
      ODYtODg1NS1kNDk4ZDllNjliZWMxEzARBgNVBAMMCnN5c195YmRfY2EwggEiMA0G
      CSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCp/ELf6iwXp6aIYXjegJhzntM1xfPo
      vXRQ58biNawGgupeTdEF68df3Df+IjxuEYWBXk1EiBTTbsZaWJ7QUeVlD0lakQBR
      Jefrmilc6KeARF10UG01nAP4mkdGNH24FYhoWZXoKBZbLI8zVK49OToAu9wMcll8
      oXgVEBr8s8A4OWyo/DCVdBSp5/JYZ5gZPLD4wO4VyIcXnMvPTLA9+JgxgHGSxbti
      lYhojal42Qd263qXmFyn8vWix7x5Om4lNYIfRwh2WAKCtCCmWKbw5x0OTIaIXspe
      D82MAX2TTeDH1Hj/tyN3ubDWjETi9aZtmm7pYsBJVH5uK+coCfsuiJp7AgMBAAGj
      XTBbMAwGA1UdEwQFMAMBAf8wCwYDVR0PBAQDAgHmMB0GA1UdDgQWBBT7nLzBtopS
      hTAiy4TfyShnBRv2gjAfBgNVHSMEGDAWgBT7nLzBtopShTAiy4TfyShnBRv2gjAN
      BgkqhkiG9w0BAQsFAAOCAQEAAqHhJ78KwBsJxdLykNPOSyN6HFcffjbzPUZd4LuT
      a1xTkCCBfKOy0ZfVkbRTnQ+e2u/ALST5C/tSSxZn2AdTKDbW3G5D/Nt0YwVs/+qU
      mTDwsBDGRrRtlXitOxvwkkJU8uTkZ+P923pTLIoUkQgjP6xlPFruJR7I9W9Q+XlF
      8WWpPwKZFAxmjFdV7BiROCKcGmi8xqOTUwFULTHvu5VG54abK/kbwo8RbsuISx54
      bB4OGXAl5MXpMbViV7LAh0y0O7NcfJ5lbQdLzm0AhxyzHmDM3d1tLrMG9rwRClRd
      WyGvohjgPF7EGMRUXmprWLRfQCAvZlj4vbJ3tEdnht8cdA==
      -----END CERTIFICATE-----
      (23 rows)
      Note that the ybsql command \pset format unaligned is used again in this step. You can validate the contents of this certificate by saving its contents to a file (such as ca.pem) and running this openssl command:
      % openssl x509 -in ca.pem -text -noout
      Certificate:
          Data:
              Version: 3 (0x2)
              Serial Number: 10443218342509856973 (0x90edc3c08124c8cd)
      ...
      Note: This certificate contains a non-existent system CN called sys_ybd_ca. This is symbolic and is only used for signing purposes; a database account need not be created for this CN.
    2. Copy the contents of the SSL CA configuration.
    3. Log into a database on the target system and run the IMPORT SSL TRUST command to import trust of the client CA certificate. Paste the CA certificate into the command. For example:
      yellowbrick=# IMPORT SSL TRUST FROM '-----BEGIN CERTIFICATE-----
      yellowbrick'# MIID3jCCAsagAwIBAgIJAJDtw8CBJMjNMA0GCSqGSIb3DQEBCwUAMH8xCzAJBgNV
      yellowbrick'# BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg
      yellowbrick'# SW5jLjEtMCsGA1UECwwkZWRhNTAxNGYtMTg3OS00MDg2LTg4NTUtZDQ5OGQ5ZTY5
      yellowbrick'# YmVjMRMwEQYDVQQDDApzeXNfeWJkX2NhMB4XDTE5MDkwOTIwNDkxNloXDTI5MDkw
      yellowbrick'# ODIwNDkxNlowfzELMAkGA1UEBhMCVVMxCzAJBgNVBAgMAkNBMR8wHQYDVQQKDBZZ
      yellowbrick'# ZWxsb3dicmljayBEYXRhLCBJbmMuMS0wKwYDVQQLDCRlZGE1MDE0Zi0xODc5LTQw
      yellowbrick'# ODYtODg1NS1kNDk4ZDllNjliZWMxEzARBgNVBAMMCnN5c195YmRfY2EwggEiMA0G
      yellowbrick'# CSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCp/ELf6iwXp6aIYXjegJhzntM1xfPo
      yellowbrick'# vXRQ58biNawGgupeTdEF68df3Df+IjxuEYWBXk1EiBTTbsZaWJ7QUeVlD0lakQBR
      yellowbrick'# Jefrmilc6KeARF10UG01nAP4mkdGNH24FYhoWZXoKBZbLI8zVK49OToAu9wMcll8
      yellowbrick'# oXgVEBr8s8A4OWyo/DCVdBSp5/JYZ5gZPLD4wO4VyIcXnMvPTLA9+JgxgHGSxbti
      yellowbrick'# lYhojal42Qd263qXmFyn8vWix7x5Om4lNYIfRwh2WAKCtCCmWKbw5x0OTIaIXspe
      yellowbrick'# D82MAX2TTeDH1Hj/tyN3ubDWjETi9aZtmm7pYsBJVH5uK+coCfsuiJp7AgMBAAGj
      yellowbrick'# XTBbMAwGA1UdEwQFMAMBAf8wCwYDVR0PBAQDAgHmMB0GA1UdDgQWBBT7nLzBtopS
      yellowbrick'# hTAiy4TfyShnBRv2gjAfBgNVHSMEGDAWgBT7nLzBtopShTAiy4TfyShnBRv2gjAN
      yellowbrick'# BgkqhkiG9w0BAQsFAAOCAQEAAqHhJ78KwBsJxdLykNPOSyN6HFcffjbzPUZd4LuT
      yellowbrick'# a1xTkCCBfKOy0ZfVkbRTnQ+e2u/ALST5C/tSSxZn2AdTKDbW3G5D/Nt0YwVs/+qU
      yellowbrick'# mTDwsBDGRrRtlXitOxvwkkJU8uTkZ+P923pTLIoUkQgjP6xlPFruJR7I9W9Q+XlF
      yellowbrick'# 8WWpPwKZFAxmjFdV7BiROCKcGmi8xqOTUwFULTHvu5VG54abK/kbwo8RbsuISx54
      yellowbrick'# bB4OGXAl5MXpMbViV7LAh0y0O7NcfJ5lbQdLzm0AhxyzHmDM3d1tLrMG9rwRClRd
      yellowbrick'# WyGvohjgPF7EGMRUXmprWLRfQCAvZlj4vbJ3tEdnht8cdA==
      yellowbrick'# -----END CERTIFICATE-----';
      IMPORT SSL TRUST