Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31
All platforms

Configuring SSL Trust ​

An SSL trust configuration is required to establish a replication channel between two Yellowbrick systems. 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 handshake requires a one-time configuration procedure that you complete by using 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

In addition to configuring SSL trust, make sure that the CREATE REMOTE SERVER command specifies the host name of the target system correctly; otherwise, replication operations will not be able to proceed.

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, using a well-known Certificate Authority (CA).
  • Import the self-signed certificate into the client trust store configuration. The recommended procedure is to install an SSL certificate that 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, then change both the HTTPS Yellowbrick Manager listener and PostgreSQL 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, follow these steps:

  1. On the remote system, log into a database and run the SHOW SSL SYSTEM command, which displays the public certificate that the target system has configured for system connections over HTTPS for Yellowbrick Manager and the database (port 5432). Send the results of this command to a file with a .pem extension. For example:

    $ ybsql -d yellowbrick -qAtc "SHOW SSL SYSTEM" -L repl_tgt.pem
-----BEGIN CERTIFICATE-----
MIIF2DCCA8CgAwIBAgIJAJ55ViM5MbIpMA0GCSqGSIb3DQEBCwUAMIGCMQswCQYD
VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs
IEluYy4xLTArBgNVBAsMJDQ1M0M4M0JBLTBCMEMtNDAyMS1BMjcxLTE5NzdDRkJC
MTlGQjEWMBQGA1UEAwwNeWJpbml0X2Rldl9jYTAeFw0yMjAzMTUyMzM5NThaFw0y
NDA2MTcyMzM5NThaMIGLMQswCQYDVQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNV
BAoMFlllbGxvd2JyaWNrIERhdGEsIEluYy4xLTArBgNVBAsMJGI1ODUzNjMyLTFk
ZTgtNDNlYi1hZDUwLWRiYjk2NDU3ZjVhYTEfMB0GA1UEAwwWYnJ1bXNieS55ZWxs
b3dicmljay5pbzCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAOCHqNjj
oNZI73WC4WE4UpO9GlMkhcN+jcPdsAfYiqRTwHGEGEKzumJHFT3/Vbx/pMPx9z5M
BNf7vK7Q26TfjLhQyXeScNYV0uGe/zOgntORVwVGh6xCXBw+q+oww+gld/T/QuE1
ffxVi4XGEBuIL6SU88r7wsCkRUY6u2qJvYBp5SCSsGIrGpNboq51GNb+azak65cB
i+/I3K0WbFn2vhqxJN4NEDzPo5nJ6qJd6Pi0B5kizJV79jdOHyY979bWQ2lVfc+y
z+xUgG3SDrldNH6gexLz/Q7n3poy52C+PRkB3+j/45AE8qozRAhJrbFW09D8DBPD
EyEL0Sh0boBnYWMO4Y0ZWudpSZ1x0tnY4SFiuz2qDT01Pw8H6Tk9nX25Ddh4TL9a
L28KiUnpYznCEGCCTocpY+rSGYkGEMIjutySpzwE3Riqa3hi+NngZfrkLVAaWGh8
TX2CQNlhTFaCf+++hOFiYEtymFietelZ45RVhdT5cxW6kAVkYIWTWp1M0bblE7Ou
1p20WcCLRYA7anE1QSUqH7b+yjUsAtihlZdW3CK6iDornDj4X3njbeoazyy5MbM+
hZ++2pi7HiRDhfMpJ+vGe5qIi2N5tg7UMGoke94QUqphjrws+sd4afGUNx9aSjO/
ZW7DG9J0abNDRP2nMyFn8mzWLzIHO1eVi5CxAgMBAAGjRjBEMAkGA1UdEwQCMAAw
CwYDVR0PBAQDAgXgMCoGA1UdEQQjMCGCFmJydW1zYnkueWVsbG93YnJpY2suaW+C
B2JydW1zYnkwDQYJKoZIhvcNAQELBQADggIBAB2bpJg+GqDgxUZVx30x6MY9dJjJ
h0ZqDLAAnCscnKl4FMsC7KjJwPliywG3lblrKRCNRoxSIjC0pBv2Ez8iZWADInFz
0pP6Tbl1cWV/lG2dc0FDBPHCAFfUcJkov+lE1fNyn78Cks1+rrNxrwH4nLH2pFOo
yR+s/zy+iveGSUhDIZJm8xw6WLDYZVtgG/6D6qvWIsqADjfVfhBe4OXUC2W6xs0e
Xwd2DIhcCVRPChGKeAEgLzega6zsl10fZjlWRkYvtzdDdr3D6FiHYIIhupKJO5vP
duzzrIvwCOSfVDENTp9x8Q8n9wZEPWfZxaHseplBn4qJhBMIx+UypCjPnH5SkcWS
Gzr/2iBU2ixllQwEsF5m+mLGpVtTDwt8g9rZ44yrVfBDyARWWqhwSkJBiezile7N
q2qd1xsCDYSjcMLDO/kwmEqc5w0P4wvzJXZWes5vN8DXM/enq0QbZYHH7nIGQKbM
4ldKkAo2uzaBb8u0Uf5vYkE8nHoGtdD4XM+jJkSUySfPy8MKrnCZ4IPCidZQrxY6
8ADKd5oMw13Tk6iJtafP0kOBoAfBXXfr9SVPX+pll5+7sww5NIc4Vr3maq8KLmtj
PumO0QZy6oIQWc4P09FzpH159U2O+LHxISGHg+HtcsxDkWQrnSO5o1dXAztsZGne
bFgThLLjWX89ahBe
-----END CERTIFICATE-----

    Note: After saving the contents of a certificate to a file, you can validate its contents by running an openssl command. For example:

    % openssl x509 -in repl_tgt.pem -text -noout
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 10443218342509856973 (0x90edc3c08124c8cd)
...

  2. On the source system, log into a database and import the SYSTEM configuration from the target system by pasting the results of the SHOW SSL SYSTEM command (or the contents of the .pem file) into the IMPORT SSL TRUST command. For example:

    yellowbrick=# import ssl trust from '-----BEGIN CERTIFICATE-----
yellowbrick'# MIIF2DCCA8CgAwIBAgIJAJ55ViM5MbIpMA0GCSqGSIb3DQEBCwUAMIGCMQswCQYD
yellowbrick'# VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs
yellowbrick'# IEluYy4xLTArBgNVBAsMJDQ1M0M4M0JBLTBCMEMtNDAyMS1BMjcxLTE5NzdDRkJC
yellowbrick'# MTlGQjEWMBQGA1UEAwwNeWJpbml0X2Rldl9jYTAeFw0yMjAzMTUyMzM5NThaFw0y
yellowbrick'# NDA2MTcyMzM5NThaMIGLMQswCQYDVQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNV
yellowbrick'# BAoMFlllbGxvd2JyaWNrIERhdGEsIEluYy4xLTArBgNVBAsMJGI1ODUzNjMyLTFk
yellowbrick'# ZTgtNDNlYi1hZDUwLWRiYjk2NDU3ZjVhYTEfMB0GA1UEAwwWYnJ1bXNieS55ZWxs
yellowbrick'# b3dicmljay5pbzCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAOCHqNjj
yellowbrick'# oNZI73WC4WE4UpO9GlMkhcN+jcPdsAfYiqRTwHGEGEKzumJHFT3/Vbx/pMPx9z5M
yellowbrick'# BNf7vK7Q26TfjLhQyXeScNYV0uGe/zOgntORVwVGh6xCXBw+q+oww+gld/T/QuE1
yellowbrick'# ffxVi4XGEBuIL6SU88r7wsCkRUY6u2qJvYBp5SCSsGIrGpNboq51GNb+azak65cB
yellowbrick'# i+/I3K0WbFn2vhqxJN4NEDzPo5nJ6qJd6Pi0B5kizJV79jdOHyY979bWQ2lVfc+y
yellowbrick'# z+xUgG3SDrldNH6gexLz/Q7n3poy52C+PRkB3+j/45AE8qozRAhJrbFW09D8DBPD
yellowbrick'# EyEL0Sh0boBnYWMO4Y0ZWudpSZ1x0tnY4SFiuz2qDT01Pw8H6Tk9nX25Ddh4TL9a
yellowbrick'# L28KiUnpYznCEGCCTocpY+rSGYkGEMIjutySpzwE3Riqa3hi+NngZfrkLVAaWGh8
yellowbrick'# TX2CQNlhTFaCf+++hOFiYEtymFietelZ45RVhdT5cxW6kAVkYIWTWp1M0bblE7Ou
yellowbrick'# 1p20WcCLRYA7anE1QSUqH7b+yjUsAtihlZdW3CK6iDornDj4X3njbeoazyy5MbM+
yellowbrick'# hZ++2pi7HiRDhfMpJ+vGe5qIi2N5tg7UMGoke94QUqphjrws+sd4afGUNx9aSjO/
yellowbrick'# ZW7DG9J0abNDRP2nMyFn8mzWLzIHO1eVi5CxAgMBAAGjRjBEMAkGA1UdEwQCMAAw
yellowbrick'# CwYDVR0PBAQDAgXgMCoGA1UdEQQjMCGCFmJydW1zYnkueWVsbG93YnJpY2suaW+C
yellowbrick'# B2JydW1zYnkwDQYJKoZIhvcNAQELBQADggIBAB2bpJg+GqDgxUZVx30x6MY9dJjJ
yellowbrick'# h0ZqDLAAnCscnKl4FMsC7KjJwPliywG3lblrKRCNRoxSIjC0pBv2Ez8iZWADInFz
yellowbrick'# 0pP6Tbl1cWV/lG2dc0FDBPHCAFfUcJkov+lE1fNyn78Cks1+rrNxrwH4nLH2pFOo
yellowbrick'# yR+s/zy+iveGSUhDIZJm8xw6WLDYZVtgG/6D6qvWIsqADjfVfhBe4OXUC2W6xs0e
yellowbrick'# Xwd2DIhcCVRPChGKeAEgLzega6zsl10fZjlWRkYvtzdDdr3D6FiHYIIhupKJO5vP
yellowbrick'# duzzrIvwCOSfVDENTp9x8Q8n9wZEPWfZxaHseplBn4qJhBMIx+UypCjPnH5SkcWS
yellowbrick'# Gzr/2iBU2ixllQwEsF5m+mLGpVtTDwt8g9rZ44yrVfBDyARWWqhwSkJBiezile7N
yellowbrick'# q2qd1xsCDYSjcMLDO/kwmEqc5w0P4wvzJXZWes5vN8DXM/enq0QbZYHH7nIGQKbM
yellowbrick'# 4ldKkAo2uzaBb8u0Uf5vYkE8nHoGtdD4XM+jJkSUySfPy8MKrnCZ4IPCidZQrxY6
yellowbrick'# 8ADKd5oMw13Tk6iJtafP0kOBoAfBXXfr9SVPX+pll5+7sww5NIc4Vr3maq8KLmtj
yellowbrick'# PumO0QZy6oIQWc4P09FzpH159U2O+LHxISGHg+HtcsxDkWQrnSO5o1dXAztsZGne
yellowbrick'# bFgThLLjWX89ahBe
yellowbrick'# -----END CERTIFICATE-----';
IMPORT SSL TRUST

  3. Validate the import by running the SHOW SSL TRUST command. For example:

    yellowbrick=# show ssl trust;
    hash    |                                                     details                                                     |                           certificate                            
------------+-----------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------
 140a8eed.0 | notBefore=Mar 15 23:39:58 2022 GMT                                                                             +| -----BEGIN CERTIFICATE-----                                     +
            | notAfter=Jun 17 23:39:58 2024 GMT                                                                              +| MIIF2DCCA8CgAwIBAgIJAJ55ViM5MbIpMA0GCSqGSIb3DQEBCwUAMIGCMQswCQYD+
            | issuer= /C=US/ST=CA/O=Yellowbrick Data, Inc./OU=453C83BA-0B0C-4021-A271-1977CFBB19FB/CN=ybinit_dev_ca          +| VQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNVBAoMFlllbGxvd2JyaWNrIERhdGEs+
            | subject= /C=US/ST=CA/O=Yellowbrick Data, Inc./OU=b5853632-1de8-43eb-ad50-dbb96457f5aa/CN=brumsby.yellowbrick.io+| IEluYy4xLTArBgNVBAsMJDQ1M0M4M0JBLTBCMEMtNDAyMS1BMjcxLTE5NzdDRkJC+
            | serial=9E7956233931B229                                                                                        +| MTlGQjEWMBQGA1UEAwwNeWJpbml0X2Rldl9jYTAeFw0yMjAzMTUyMzM5NThaFw0y+
            |                                                                                                                 | NDA2MTcyMzM5NThaMIGLMQswCQYDVQQGEwJVUzELMAkGA1UECAwCQ0ExHzAdBgNV+
            |                                                                                                                 | BAoMFlllbGxvd2JyaWNrIERhdGEsIEluYy4xLTArBgNVBAsMJGI1ODUzNjMyLTFk+
            |                                                                                                                 | ZTgtNDNlYi1hZDUwLWRiYjk2NDU3ZjVhYTEfMB0GA1UEAwwWYnJ1bXNieS55ZWxs+
            |                                                                                                                 | b3dicmljay5pbzCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAOCHqNjj+
...

  4. Establish service (target system) trust of client (source system). This step exports the client's identity certificate and imports it into the service's trust store.

For replication purposes, each Yellowbrick system 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 system 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 trust store of the service.

  1. On the source system, log into a database and export the SSL "CA" configuration, using the SHOW SSL CA command. Send the results of this command to a file with a .pem extension. For example:

    $ ybsql -d yellowbrick -qAtc "SHOW SSL CA" -L repl_src_ca.pem
-----BEGIN CERTIFICATE-----
MIIF3jCCA8agAwIBAgIJAJQ3GHVRmUgUMA0GCSqGSIb3DQEBCwUAMH8xCzAJBgNV
BAYTAlVTMQswCQYDVQQIDAJDQTEfMB0GA1UECgwWWWVsbG93YnJpY2sgRGF0YSwg
SW5jLjEtMCsGA1UECwwkZjg2OTc0NjUtM2RmNi00OTZmLWI3NDctZDg5ZTRiZDZh
YjA1MRMwEQYDVQQDDApzeXNfeWJkX2NhMB4XDTIyMDMxNTIzNDEwMVoXDTI0MDYx
NzIzNDEwMVowfzELMAkGA1UEBhMCVVMxCzAJBgNVBAgMAkNBMR8wHQYDVQQKDBZZ
ZWxsb3dicmljayBEYXRhLCBJbmMuMS0wKwYDVQQLDCRmODY5NzQ2NS0zZGY2LTQ5
NmYtYjc0Ny1kODllNGJkNmFiMDUxEzARBgNVBAMMCnN5c195YmRfY2EwggIiMA0G
CSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQDkJKT20vwwF9wgah6wjpCyAcly9JB1
tsxczP5h0EMGNewOU+J4Du0oPT6CbrpolZWcvd4quVacozKcrYT2cuQWYZP39Gi+
5nx/ks8IRmRs1I+lx5fpXK6HL5u18WHNymNelrpgv24J0Llm4dyVwnQoaMUYJ0nj
H5uNI4hbZMRW/Q9cwhMKURh6m1/fBPycPjnRrt4NKdkMgmNiGQL5zwScm0hYesge
D+Fp9F5JlnK7Cc5vMK/hRAN0Q/VjUbA2d47nd5gI98mbIE2yqgBcOc/te3Baz6EV
apd+zjVB+KQzdTncyQirin/Rlv3YPvE0qiQ2zmgEQRbHfbWCQTXd/RgBIomVLpna
q6psPG5GW7KTwzcG+aHkla+JMiBYqV3DlLyK+ZhO2d1U0HyebmukHgZEjL1cG0iH
wnkA0iEYo443K8zEEPBQ1xVN3Z5ePeEfJK+du3MOpDMSOQIT1nzW4KM5ZWs+DW+I
ROeqXa17JECLDiWDlb1DzgX+8K6FKdiA0wW2e48PGbkriLamIVg+8NFnF+qBUZmQ
f8gtoSawwASaBtZr/nJ2wg7MayxfcLeCIl62ybEYyDHBHmRZbq1+W8+36ktDmJtG
/5HngvB1MSEwcVAfQlRT4G2SemrEheCsrlnkBb18v3T7qLckuXS0pXRVUCvOeebE
dsCEna58lI8b4wIDAQABo10wWzAMBgNVHRMEBTADAQH/MAsGA1UdDwQEAwIB5jAd
BgNVHQ4EFgQUQe6uiCyLW3s6oNemuEf3488F0p4wHwYDVR0jBBgwFoAUQe6uiCyL
W3s6oNemuEf3488F0p4wDQYJKoZIhvcNAQELBQADggIBAAQlpb1aa94KhY1gh+uG
GzSYGstn6KrR60aXSXY+9yjzdViZEo2T/m62g1jk1AjxIjdLlgOXnZ0+TGcX+chU
gaNOQ6FN+mBRRjj9eDsS3+qqPDsgZGZHKbC+U2VpxBahbtz+zJitgqKFhnS5JaIm
clcEGk8gbZJPXUQnCJ1suqSs/r9s0j4EW1KuKFmZE3qnNb0OD8IN02LuqT6U+Wxu
MeaJQjG4BpXAcgneWlSqp/u98SOJZPuaCKAEUceAK/n/eYRDS5BCbZq6i16elm4M
0YYoNwduduNvrVCd6sb4bFdvPf+dRyQaQkjuOERCosuozkQAGhMUkEfqZyVEsP2Y
m3NVQPlttpJa6rndBQWvztXmNij5qAIWJdcmn+/qd+/r6I9b5SS/KrbIhpqxdXS8
3ZoCIhn7hwrzsOx0BFCx9Tz5TpqCv4igtuhjwqe3uIBJI2ha9aKJ3j3Ey9yzZ/4M
L6KzedQZ5DViaOFn3UkSklT021QUklVz7+uDX5UW4FFzzJsfdILG5JAcaJmzWS1N
lcUhx7WyL1wYYUSmiAAJLX2MXBA0tG8i3rhmcLE7ng81/Mnb758PVQZjLDNNl6RA
xhe0QXPTUkeoyZLYqcTOdUOKFRJVuFYE4PoLL5Kaq6zghAE81ireG3sUkVMzkzN+
nRXZBL5f8nMUNrrhapO9KuX2
-----END CERTIFICATE-----

    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. On the target system, log into a database 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-----
...
-----END CERTIFICATE-----';
IMPORT SSL TRUST

Establishing Trust for New SSL Certificates ​

Whenever you have to replace the SSL certificates on source and target systems used for replication, you need to re-establish trust between those systems. If you don't do this, replication will no longer work.

Follow these steps to establish trust using the new certificates:

  1. Pause replication. (See Pausing and Resuming Replication.)
  2. Remove the old trust information from the source and target systems by using the REVOKE SSL TRUST command.
  3. Install new SSL certificates.
  4. Establish trust for the new certificates by following the procedure in the previous section.
  5. Resume replication. (See Pausing and Resuming Replication.)