This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

TLS protocol

TLS (Transport Layer Security) is a cryptographic protocol used to secure communications between servers, their nodes, and clients.

TLS (Transport Layer Security) is a cryptographic protocol used to secure communications between servers, their nodes, and clients.

When enabled, a Vertica database and the clients that connect to it use TLS 1.2.

Although TLS, SSL, and TLS/SSL are often used interchangeably, the Vertica documentation always uses TLS to reference the protocol. Some Vertica parameters and components use SSL, and in these cases the documentation uses SSL to reference them, but these too can be categorized under the TLS umbrella.

Enabling TLS is a multi-step process. First, check the status of your security configuration with SECURITY_CONFIG_CHECK. Then, you can configure TLS authentication records to reject non-TLS connections.

1 - TLS overview

To secure communications and verify data integrity, you can configure Vertica and database clients to use TLS.

To secure communications and verify data integrity, you can configure Vertica and database clients to use TLS. The TLS protocol uses a key and certificate exchange system along with a trusted third party called a Certificate Authority (CA). Both the owner of a certificate and the other party that relies on the certificate must trust the CA to confirm the certificate holder's identity.

Vertica also supports the following authentication methods using the Transport Layer Security (TLS) v1.2 protocol. Both methods encrypt and verify the integrity of the data in transit:

  • Server Mode - In server mode, the client must confirm the server's identity before connecting. The client verifies that the server's certificate and public key are valid and were issued by a certificate authority (CA) listed in the client's list of trusted CAs. This helps prevent man-in-the-middle attacks.

  • Mutual Mode - In mutual mode, the client and server must verify each other's identity before connecting.

In addition to the requirements detailed in this section, you must create TLS authentication records to reject non-TLS client connections.

TLS handshake process

The following is a high-level/simplified overview of one possible "handshake" process for the client to verify the identity of the server in Server Mode. Additional actions taken in Mutual Mode for the server to identify the client are marked as such.

Public and Private Key Pairs - Key pairs are generated by clients and servers. The owner of a public key must be verified by a certificate authority. The key pairs are used to encrypt messages. For example, suppose Alice wants to send confidential data to Bob. Because she wants only Bob to read it, she encrypts the data with Bob's public key. Even if someone else gains access to the encrypted data, it remains protected. Because only Bob has access to his corresponding private key, he is the only person who can decrypt Alice's encrypted data back into its original form.

Certificates - Certificates contain a public key and identify the owner of the key. They are issued by the certificate authority (CA).

Certificate Authority (CA) - A certificate authority is a trusted party that verifies the identity of public key owners.

Client and Server Random - Client Random and Server Random are random strings that used to created a shared secret which encrypts communication if the handshake succeeds.

  1. Before connecting, the server and client generate their own public and private key pairs. The CA then distributes identifying certificates to the server and client for their respective public keys.

  2. The client sends its Client Random to the server and requests the server's certificate.

  3. The server sends its certificate and its Server Random, encrypted with its private key, to the client. In Mutual Mode, the server also requests the client's certificate.

  4. In Mutual Mode, the client sends its certificate.

  5. The client uses the certificate to verify that the server owns its public key, then decrypts the Server Random with the server's public key to verify that the server owns its private key.

  6. In Mutual Mode, the server uses the certificate to verify that the client owns its public key.

  7. The server and client use the Client and Server Randoms to generate a new secret, called a session key, which encrypts future communication.

1.1 - TLS configurations

A TLS Configuration is a database object that encapsulates all settings and certificates needed to configure TLS.

A TLS Configuration is a database object that encapsulates all settings and certificates needed to configure TLS. After setting up a TLS Configuration, you can use it by setting it as the value for one or more of the following database parameters, each of which controls TLS for a certain type of connection between the Vertica database and a client or server:

  • ServerTLSConfig

  • LDAPLinkTLSConfig

  • LDAPAuthTLSConfig

  • InternodeTLSConfig

These parameters are set to predefined TLS Configurations by default so if you just want to configure TLS, you should use ALTER TLS CONFIGURATION to modify a predefined TLS Configuration. Otherwise, you can use CREATE TLS CONFIGURATION to create a custom TLS Configuration.

Reusing an existing TLS configurations

To reuse an existing TLS Configuration, use ALTER TLS CONFIGURATION.

The following table lists each TLS connection type parameter with its associated connection type and predefined TLS Configuration:

Connection Type Parameter Default TLS Configuration Example
Client-server where Vertica is the server ServerTLSConfig server Configuring client-server TLS
Connections for the LDAP Link service LDAPLinkTLSConfig LDAPLink TLS for LDAP link
Connections between Vertica and an LDAP server to authenticate users LDAPAuthTLSConfig LDAPAuth TLS for LDAP authentication
Connections between Vertica nodes InternodeTLSConfig data_channel Internode TLS

Creating custom TLS configurations

You can create TLS Configurations with CREATE TLS CONFIGURATION.

The following example creates a TLS Configuration and enables it for client-server TLS by setting it in ServerTLSConfig:

  1. Create the keys and certificates:

    
    -- create CA certificate
    => CREATE KEY k_ca TYPE 'RSA' LENGTH 4096;
    => CREATE CA CERTIFICATE ca
       SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Root CA'
       VALID FOR 3650
       EXTENSIONS 'nsComment' = 'Vertica generated root CA cert'
       KEY k_ca;
    
    -- create server certificate
    => CREATE KEY k_server TYPE 'RSA' LENGTH 2048;
    => CREATE CERTIFICATE server
        SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Cluster/emailAddress=example@example.com'
        SIGNED BY ca
        KEY k_server;
    
  2. Create the TLS Configuration with the server's certificate:

    => CREATE TLS CONFIGURATION new_tls_config CERTIFICATE server TLSMODE 'ENABLE';
    
  3. Set the ServerTLSConfig parameter to use the new TLS Configuration for client-server TLS:

    => ALTER DATABASE DEFAULT SET ServerTLSConfig = 'new_tls_config';
    

1.2 - Applying chain of CA certificates to the agent

This page provides information about applying a chain of CA certificates to the agent.

You can now apply multiple certificates to your agent. You can configure the agent to present the CA chain along with the server certificate during TLS handshake.

  1. Stop the agent on the Vertica node.

    $ sudo /opt/vertica/sbin/vertica_agent stop
    
    New invocation of vertica_agent. Called with 1 arguments: stop
    Stopping vertica agent:
    $
    
  2. Back up agent certificates from the Vertica node.

    $ cd /opt/vertica/config/share
    $ mv agent.cert agent.cert.bck
    $ mv agent.key agent.key.bck
    $ mv agent.pem agent.pem.bck
    $ ls
    agent.cert.bck  agent.key.bck   agent.pem.bck   license.key
    
  3. Create a chain of CA certificates. For more information, see Generating TLS certificates and keys.

    => CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
    => CREATE CA CERTIFICATE SSCA_cert
       SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica Root CA'
       VALID FOR 3650
       EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
       KEY SSCA_key;
    => CREATE KEY intermediate_key TYPE 'RSA' LENGTH 2048; 
    => CREATE CA CERTIFICATE intermediate_ca_cert
       SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica intermediate CA'
       SIGNED BY SSCA_cert
       KEY intermediate_key;
    => CREATE KEY internode_key TYPE 'RSA' LENGTH 2048;
    => CREATE CERTIFICATE internode_cert
       SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=data channel'
       SIGNED BY intermediate_ca_cert
       EXTENSIONS 'nsComment' = 'Vertica internode cert', 'extendedKeyUsage' = 'serverAuth, clientAuth'
       KEY internode_key; 
    
  4. Enable TLS mode to verify the newly created certificates.

    => ALTER TLS CONFIGURATION data_channel CERTIFICATE internode_cert TLSMODE 'TRY_VERIFY’;
    => select * from tls_configurations;
      name       |  owner | certificate  | ca_certificate | cipher_suites| mode     
    -------------+--------+--------------+----------------+--------------+--------
     server      |dbadmin |              |                |              | DISABLE
    LDAPLink     |dbadmin |              |                |              | DISABLE
    LDAPAuth     |dbadmin |              |                |              | DISABLE
    data_channel |dbadmin |internode_cert| SSCA_Cert      |              |TRY_VERIFY 
      (4 rows)
    
  5. Create the agent.cert file.

    $ select certificate_text  FROM certificates where name='SSCA_cert';
    $ select certificate_text FROM certificates where name='intermediate_ca_cert';
    $ select  certificate_text FROM certificates where name='internode_cert';
    $ cd /opt/vertica/config/share
    

    Edit the agent.cert file.

    
    $ cat agent.cert
    
  6. Create the agent.key file.

    $ select key from cryptographic_keys where name='SSCA_key';
    $ select key from cryptographic_keys where name='intermediate_key';
    $ select key from cryptographic_keys where name='internode_key';
    $ sudo vi agent.key
    $ cd /opt/vertica/config/share
    

    Edit the agent.cert file.

    
    $ cat agent.key
    
  7. Generate the agent.pem file from the agent.cert file.

    $ openssl x509 -in agent.cert -out agent.pem -outform PEM
    $ ls
    agent.cert  agent.cert.bck  agent.key   agent.key.bck   agent.pem   agent.pem.bck   license.key
    
  8. Ensure that agent.cert, agent.key and agent.pem files are available in /opt/vertica/config/share.

  9. Start the Vertica agent on the node.

    $ sudo /opt/vertica/sbin/vertica_agent start 
    
  10. Move all agent certificates to other machines in the cluster. Ensure that target machines have read and write permissions for agent certificates.

    $ ls -altr /opt/vertica/config/share
    agent.cert  agent.cert.bck  agent.key   agent.key.bck   agent.pem   agent.pem.bck
    $ chmod -R 600 /opt/vertica/config/share/agent.*
    $ scp agent.* dbadmin@<privateip>:/opt/vertica/config/share/
    

    where privateip is a non-internet facing IP address used in an internal network. For example, <10.11.12.157> could be your privateip.

  11. Restart the Vertica agent on the cluster machines.

    $ sudo /opt/vertica/sbin/vertica_agent status
    $ sudo /opt/vertica/sbin/vertica_agent stop
    $ sudo /opt/vertica/sbin/vertica_agent start
    
  12. Check the newly-applied certificates.

    $ openssl s_client -prexit -connect localhost:5444
    
  13. Download the agent.pem file from /opt/vertica/config/share to a folder on your local machine.

  14. Upload the agent.pem file in the MC Settings page.

    • Navigate to Home > MC Settings > SSL/TLS Certificates.
    • In the Manage Authentication Certifcates area, click Add New Certificate and choose Agent.
    • Click Browse to select the agent.pem file.
    • Click Add New Certificate.
    • Click Restart MC.
  15. Import the Vertica node to MC. For more information, see Importing an existing database into MC.

1.3 - Generating TLS certificates and keys

This page includes examples and sample procedures for generating certificates and keys with CREATE KEY and CREATE CERTIFICATE.

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

A CA is a trusted entity that signs and validates other certificates with its own certificate. The following example generates a self-signed root CA certificate:

  1. Generate or import a private key. The following command generates a new private key:

          
    => CREATE KEY ca_private_key TYPE 'RSA' LENGTH 4096;
    CREATE KEY
    
    

  2. Generate the certificate with the following format. Sign the certificate the with the private key that you generated or imported in the previous step:

          
    => CREATE CA CERTIFICATE ca_certificate
    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_private_key;
    
    

    For example:

          
    => CREATE CA CERTIFICATE SSCA_cert
    SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/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=OpenText/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=OpenText/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;

1.4 - Configuring client-server TLS

Vertica offers two connection modes for 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).

Client-server TLS secures the connection step between Vertica and clients, not the following authentication step to authenticate these clients as users in the database. To configure authentication for TLS connections or to reject plaintext connections, see TLS authentication.

Setting certificates with TLS configuration

This procedure creates keys and certificates for client-server TLS and sets them in the predefined TLS Configuration server, which is the default TLS configuration for ServerTLSConfig. To create a custom TLS configuration, see TLS configurations.

  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.

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

    TLS Configurations also support the TLSMODE VERIFY_FULL, but this TLSMODE is unsupported for client-server TLS (the connection type handled by ServerTLSConfig) and behaves like VERIFY_CA.

    For Server Mode, choose ENABLE:

    => ALTER TLS CONFIGURATION server TLSMODE 'ENABLE';
    

    For Mutual Mode, choose TRY_VERIFY or higher:

    => ALTER TLS CONFIGURATION server TLSMODE 'VERIFY_CA';
    
  4. Verify that the ServerTLSConfig parameter is set to the server TLS Configuration:

    => SHOW CURRENT ServerTLSConfig;
      level  |      name        | setting
    ---------+------------------+---------
     DEFAULT | ServerTLSConfig  | server
    (1 row)
    

    If not, set the ServerTLSConfig parameter:

    => ALTER DATABASE DEFAULT SET ServerTLSConfig = 'server';
    

See also

1.5 - Managing CA bundles

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

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

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

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

1.7 - Importing a new certificate to MC

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

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.

1.8 - Replacing the agent certificate

The uses a preinstalled Certificate Authority (CA) 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
    

1.9 - Importing and exporting data with TLS

Vertica uses TLS to secure connections and communications between clients and servers.

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.