TLS overview

• 1: Generating TLS certificates and keys
• 2: Configuring client-server TLS
• 3: Managing CA bundles
• 4: Generating certificates and keys for MC
• 5: Importing a new certificate to MC
• 6: Replacing the agent certificate
• 7: Importing and exporting data with TLS

1 - Generating TLS certificates and keys

This page includes examples and sample procedures for generating certificates and keys with CREATE KEY and CREATE CERTIFICATE. To view your keys and certificates, query the CRYPTOGRAPHIC_KEYS and CERTIFICATES system tables.

For more detailed information on creating signed certificates, OpenSSL recommends the OpenSSL Cookbook.

For more information on x509 extensions, see the OpenSSL documentation.

Importing keys and certificates

Keys

You only need to import private keys if you intend to use its associated certificate to sign something, like a message in client-server TLS, or another certificate. That is, you only only need to import keys if its associated certificate is one of the following:

• Client/server certificate

• CA certificate used to sign other certificates while in Vertica

If you only need your CA certificate to validate other certificates, you do not need to import its private key.

To import a private key:

=> CREATE KEY imported_key TYPE 'RSA' AS '-----BEGIN PRIVATE KEY-----...-----END PRIVATE KEY-----';

Certificates

To import a CA certificate that only validates other certificates (no private key):

=> CREATE CA CERTIFICATE imported_validating_ca AS '-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----';

To import a CA that can both validate and sign other certificates (private key required):

=> CREATE CA CERTIFICATE imported_signing_ca AS '-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----'
KEY ca_key;

To import a certificate for server mode TLS:

=> CREATE CERTIFICATE server_mode_cert AS '-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----' KEY imported_key;

To import a certificate for mutual mode TLS or client authentication, you must specify its CA:

=> CREATE CERTIFICATE imported_cert AS '-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----'
SIGNED BY imported_ca KEY imported_key;

Generating private keys and certificates

Keys

To generate an 2048-bit RSA private key:

=> CREATE KEY new_key TYPE 'RSA' LENGTH 2048;

Self-signed CA certificates

CAs are trusted entities that use their CA certificates to sign and validate other certificates. The example that follows generates a self-signed root CA:

1. Generate or import a private key:

   => CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
   

2. Generate and sign the certificate with the private key using the following format:

   => CREATE CA CERTIFICATE certificate_name
   SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica Root CA'
   VALID FOR days_valid
   EXTENSIONS 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsComment' = 'Vertica generated root CA cert'
   KEY ca_key;
   

   
   For example:

   => CREATE CA CERTIFICATE SSCA_cert
   SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Root CA'
   VALID FOR 3650
   EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
   KEY SSCA_key;
   

Intermediate CA certificates

In addition to server certificates, CAs can also sign the certificates of other CAs. This process produces an intermediate CA and a chain of trust between the top-level CA and the intermediate CA. These intermediate CAs can then sign other certificates.

1. Generate or import the CA that signs the intermediate CA. The example that follows generates and uses a self-signed root CA:

   => CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
   

   => CREATE CA CERTIFICATE SSCA_cert
   SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Root CA'
   VALID FOR 3650
   EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
   KEY SSCA_key;
   

2. Generate or import a private key:

   => CREATE KEY intermediate_key TYPE 'RSA' LENGTH 2048;
   

3. Generate the intermediate CA certificate, specifying its private key and signing CA using the following format:

   => CREATE CERTIFICATE intermediate_certificate_name
   SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica intermediate CA'
   SIGNED BY ca_name
   KEY intermediate_key;
   

   
   For example:

   => CREATE CA CERTIFICATE intermediate_CA
   SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica Intermediate CA'
   SIGNED BY SSCA_cert
   KEY intermediate_key;
   

Client/server certificates

CREATE CERTIFICATE generates x509v3 certificates, which allow you to specify extensions to restrict how the certificate can be used. The value for the extendedKeyUsage extension will differ based on your use case:

• Server certificate:

  'extendedKeyUsage' = 'serverAuth'
  

• Client certificate:

  'extendedKeyUsage' = 'clientAuth'
  

• Server certificate for internode encryption:

  'extendedKeyUsage' = 'serverAuth, clientAuth'
  

Because these certificates are used for client/server TLS, you must import or generate their private keys.

The following example certificates are all signed by this self-signed CA certificate:

=> CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;

=> CREATE CA CERTIFICATE SSCA_cert
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Root CA'
VALID FOR 3650
EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
KEY SSCA_key;

To generate a server certificate:

=> CREATE KEY server_key TYPE 'RSA' LENGTH 2048;

=> CREATE CERTIFICATE server_cert
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica server/emailAddress=example@example.com'
SIGNED BY SSCA_cert
EXTENSIONS 'nsComment' = 'Vertica server cert', 'extendedKeyUsage' = 'serverAuth'
KEY server_key;

To generate a client certificate:

=> CREATE KEY client_key TYPE 'RSA' LENGTH 2048;

=> CREATE CERTIFICATE client_cert
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica client/emailAddress=clientexample@example.com'
SIGNED BY SSCA_cert
EXTENSIONS 'nsComment' = 'Vertica client cert', 'extendedKeyUsage' = 'clientAuth'
KEY client_key;

To generate an internode TLS certificate:

=> CREATE KEY internode_key TYPE 'RSA' LENGTH 2048;

=> CREATE CERTIFICATE internode_cert
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=data channel'
SIGNED BY SSCA_cert
EXTENSIONS 'nsComment' = 'Vertica internode cert', 'extendedKeyUsage' = 'serverAuth, clientAuth'
KEY internode_key;

2 - Configuring client-server TLS

Vertica offers two connection modes for client-server TLS:

• In Server Mode, the client must verify the host's certificate. Hosts must have a server private key and certificate.

• In Mutual Mode, the client and host must each verify the other’s certificate. Hosts must have a server private key, server certificate, and CA certificate(s).

Keep in mind that this page covers the connection step between Vertica and clients, not the following authentication step for Vertica users to the database. To configure authentication for TLS connections, see TLS authentication.

Setting certificates with TLS CONFIGURATION

1. Generate or import the following according to your use case:

   • Server Mode: server certificate private key, server certificate

   • Mutual Mode: server certificate private key, server certificate, CA certificate(s)

2. Run the following commands according to your desired configuration. New connections will use TLS.

   • To use Server Mode, set the server certificate for the server's TLS CONFIGURATION.

     => ALTER TLS CONFIGURATION server CERTIFICATE server_cert;
     

   • To use Mutual Mode, set a server and CA certificate. This CA certificate is used to verify client certificates.

     => ALTER TLS CONFIGURATION server CERTIFICATE server_cert ADD CA CERTIFICATES ca_cert;
     

     To use multiple CA certificates, separate them with commas:

     => ALTER TLS CONFIGURATION server CERTIFICATE server_cert
        ADD CA CERTIFICATES intermediate_ca_cert, ca_cert;
     

3. Enable TLS (disabled by default). Choose one of the following TLSMODEs, listed in ascending security. For Server Mode, choose ENABLE. For Mutual Mode, choose TRY_VERIFY or higher.

   • DISABLE: Disables TLS. All other options for this parameter enable TLS.

   • ENABLE: Enables TLS. Vertica does not verify client certificates.

   • TRY_VERIFY: Establishes a TLS connection if one of the following is true:

     • The client presents a valid certificate.
     • The client doesn't present a certificate

     If the client presents an invalid certificate, the connection is rejected.

   • VERIFY_CA: Connection succeeds if Vertica verifies that the client certificate is from a trusted CA. If the client does not present a client certificate, the connection is rejected.

   For example, for Server Mode:

   => ALTER TLS CONFIGURATION server TLSMODE 'ENABLE';
   

   For example, for Mutual Mode:

   => ALTER TLS CONFIGURATION server TLSMODE 'TRY_VERIFY';
   

See also

• Internode TLS
• SECURITY_CONFIG_CHECK

3 - Managing CA bundles

Certificate authority (CA) bundles allow you to group CA certificates together and use them to validate connections to your database.

You can view existing CA bundles by querying the CA_BUNDLES system table.

Creating a CA bundle

To create a CA bundle, use CREATE CA BUNDLE and specify one or more CA certificates. If you don't specify a CA certificate, the CA bundle will be empty.

This example creates a CA bundle called ca_bundle that contains CA certificates root_ca and root_ca2:

=> CREATE CA BUNDLE ca_bundle CERTIFICATES root_ca, root_ca2;
CREATE CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274026954 | ca_bundle | 45035996273704962 | [45035996274026764, 45035996274026766]
(1 row)

Modifying existing CA bundles

CA_BUNDLES only stores OIDs. Since operations on CA bundles require certificate and owner names, you can use the following query to map bundles to certificate and owner names:

=> SELECT user_name AS owner_name,
       owner     AS owner_oid,
       b.name    AS bundle_name,
       c.name    AS cert_name
FROM   (SELECT name,
               STRING_TO_ARRAY(certificates) :: array[INT] AS certs
        FROM   ca_bundles) b
       LEFT JOIN certificates c
              ON CONTAINS(b.certs, c.oid)
       LEFT JOIN users
              ON user_id = owner
ORDER  BY 1;

 owner_name |     owner_oid     | bundle_name  | cert_name
------------+-------------------+--------------+-----------
 dbadmin    | 45035996273704962 | ca_bundle    | root_ca
 dbadmin    | 45035996273704962 | ca_bundle    | ca_cert
(2 rows)

Adding and removing CA certificates

If you have ownership of a CA bundle, you can add and remove certificates with ALTER CA BUNDLE.

This example modifies ca_bundle by adding ca_cert and removing root_ca2:

=> ALTER CA BUNDLE ca_bundle ADD CERTIFICATES ca_cert;
ALTER CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |                       certificates
-------------------+-----------+-------------------+-----------------------------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027348, 45035996274027396]
(1 row)

=> ALTER CA BUNDLE ca_bundle REMOVE CERTIFICATES root_ca2;
ALTER CA BUNDLE

=> SELECT * FROM CA_BUNDLES;
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027396]
(1 row)

Managing CA bundle ownership

Superusers and CA bundle owners can see whether a bundle exists by querying the CA_BUNDLES system table, but only owners of a given bundle can see the certificates inside.

In the following example, the dbadmin user owns ca_bundle. After giving ownership of the bundle to 'Alice', the dbadmin can no longer see the certificates inside the bundle:

=> => SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027396]
(1 row)

=> ALTER CA BUNDLE ca_bundle OWNER TO Alice;
ALTER CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       | certificates
-------------------+-----------+-------------------+--------------
 45035996274027356 | ca_bundle | 45035996274027586 | []
(1 row)

Dropping CA bundles

You must have ownership of a CA bundle to drop it:

=> DROP CA BUNDLE ca_bundle;
DROP CA BUNDLE

4 - Generating certificates and keys for MC

A certificate signing request (CSR) is a block of encrypted text generated on the server on which the certificate is used. You send the CSR to a certificate authority (CA) to apply for a digital identity certificate. The CA uses the CSR to create your SSL certificate from information in your certificate; for example, organization name, common (domain) name, city, and country.

Management Console (MC) uses a combination of OAuth (Open Authorization), Secure Socket Layer (SSL), and locally-encrypted passwords to secure HTTPS requests between a user's browser and MC, and between MC and the agents. Authentication occurs through MC and between agents within the cluster. Agents also authenticate and authorize jobs.

The MC configuration process sets up SSL automatically, but you must have the openssl package installed on your Linux environment first.

When you connect to MC through a client browser, Vertica assigns each HTTPS request a self-signed certificate, which includes a timestamp. To increase security and protect against password replay attacks, the timestamp is valid for several seconds only, after which it expires.

To avoid being blocked out of MC, synchronize time on the hosts in your Vertica cluster, and on the MC host if it resides on a dedicated server. To recover from loss or lack of synchronization, resync system time and the Network Time Protocol.

Create a certificate and submit it for signing

For production, you must use certificates signed by a certificate authority. You can create and submit a certificate and when the certificate returns from the CA, import the certificate into MC.

Use the openssl command to generate a new CSR, entering the passphrase "password" when prompted:

$ sudo openssl req -new -key /opt/vconsole/config/keystore.key -out server.csr
Enter pass phrase for /opt/vconsole/config/keystore.key:

When you press Enter, you are prompted to enter information to be incorporated into your certificate request. Some fields contain a default value, which you should change for security reasons. Other fields you can leave blank, such as password and optional company name. To leave the field blank, type '.'.

This information is contained in the CSR and shows both the default and replacement values:

Country Name (2 letter code) [GB]:USState or Province Name (full name) [Berkshire]:Massachusetts
Locality Name (eg, city) [Newbury]: Cambridge
Organization Name (eg, company) [My Company Ltd]:Vertica
Organizational Unit Name (eg, section) []:Information Management
Common Name (eg, your name or your server's hostname) []:console.vertica.com
Email Address []:mcadmin@vertica.com

The Common Name field is the fully qualified domain name of your server. Your entry must exactly match what you type in your web browser, or you receive a name mismatch error.

Self-sign a certificate for testing

To test your new SSL implementation, you can self-sign a CSR using either a temporary certificate or your own internal CA, if one is available.

The following command generates a temporary certificate, which expires after 365 days:

$ sudo openssl x509 -req -days 365 -in server.csr -signkey /opt/vconsole/config/keystore.key -out server.crt
Enter passphrase for /opt/vconsole/config/keystore.key:
Enter same passphrase again:

The previous example prompts you for a passphrase. This is required for Apache to start. To implement a passphrase you must put the SSLPassPhraseDialog directive in the appropriate Apache configuration file. For more information see your Apache documentation.

This example shows the command's output to the terminal window:

Signature oksubject=/C=US/ST=Massachusetts/L=Cambridge/O=Vertica/OU=IT/
CN=console.vertica.com/emailAddress=mcadmin@vertica.com
Getting Private key

You can now import the self-signed key, server.crt, into Management Console.

See also

• Configuring TLS for JDBC clients
• Configuring TLS for ODBC Clients

• Key and Certificate Management Tool

5 - Importing a new certificate to MC

Use this procedure to import a new certificate into Management Console.

1. Connect to Management Console, and log in as an administrator.

2. On the Home page, click MC Settings.

3. In the button panel on the left, click SSL certificates.

4. To the right of "Upload a new SSL certificate," click Browse to import the new key.

5. Click Apply.

6. Restart Management Console.

6 - Replacing the agent certificate

The Agent uses a preinstalled Certificate Authority (CA) certificate. You can replace it copying the your preferred certificate and its private key to the host.

To view your current agent certificate:

$ openssl s_client -prexit -connect database_IP:database_port

Generating a certificate

If you don't already have one, you can generate a self-signed certificate. For more information, see Generating TLS certificates and keys

1. Generate the private key and certificate.

   $ openssl req -new -newkey rsa:4096 -x509 -sha256 -days 365 -nodes -out agent.cert -keyout agent.key
   
   You are about to be asked to enter information that will be incorporated
   into your certificate request.
   What you are about to enter is what is called a Distinguished Name or a DN.
   There are quite a few fields but you can leave some blank
   For some fields there will be a default value,
   If you enter '.', the field will be left blank.
   -----
   Country Name (2 letter code) [AU]:US
   State or Province Name (full name) [Some-State]:MA
   Locality Name (eg, city) []:Cambridge
   Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Company
   Organizational Unit Name (eg, section) []:IT
   Common Name (e.g. server FQDN or YOUR name) []:*.mycompany.com
   Email Address []:myaddress@mycompany.com
   

2. Make a copy of the certificate in PEM format.

   $ openssl x509 -in agent.cert -out agent.pem -outform PEM
   

3. Review the certificate.

   $ openssl x509 -in agent.pem -text
   

Replacing the agent certificate on a host

The following procedure replaces the Agent's current private key and certificate on a single host. To replace this certificate and key across an entire cluster, repeat this procedure for all the hosts.

1. Stop the Agent service on the host.

   $ /etc/init.d/vertica_agent stop
   

2. Backup and rename the existing agent certificate and key.

   
   $ cd /opt/vertica/config/share
   $ mv agent.cert agent.cert.bck
   $ mv agent.key agent.key.bck
   $ mv agent.p em agent.pem.bck
   

3. Transfer the new certificate and key to the host's /opt/vertica/config/share directory.

   $ scp agent.* root@123.12.12.123:/opt/vertica/config/share
   

4. Change the owner of the certificate and key to uidbadmin and the group to verticadba.

   $ chown installed_Vertica_user:installed_Vertica_group agent.*
   

5. Make the certificate and key files read-only.

   $ chmod -R 400 agent.*
   

6. Start the Agent service.

   $ /etc/init.d/vertica_agent start
   starting agent
   Opening PID file "/opt/vertica/log/agent.pid".
   Overwriting /opt/vertica/log/agent_uidbadmin.log
   Overwriting /opt/vertica/log/agent_uidbadmin.err
   start OK for user: uidbadmin
   

7. Verify that you can view information about your database with your API key.

   $ curl -X GET https://10.20.80.145:5444/databases -H "VerticaApiKey:wCgXny3Wm+8OhEvGkAclv7v9+VIlxgXblpr4rf" -k
   

8. Verify that the Agent is using the new certificate.

   $ openssl s_client -prexit -connect 10.20.80.145:5444
   

7 - Importing and exporting data with TLS

Vertica uses TLS to secure connections and communications between clients and servers. When you import or export data between Vertica clusters, one of the clusters functions as a client, which means you can use TLS to protect that connection, too.

The ImportExportTLSMode parameter controls the strictness of TLS when importing or exporting data.

By default, ImportExportTLSMode is set to PREFER. With this setting, Vertica attempts to use TLS and falls back to plaintext; you can change this to always require encryption and, further, to validate the certificate on each connection. For more information about TLS during import and export operations, see Configuring connection security between clusters.