Product Documentation

Appearance

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+         
...

  5. 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

Parent topic:Setting Up Replication