Kafka TLS/SSL Example Part 2: Configure Vertica for Mutual Authentication

The following steps demonstrate setting up Vertica for mutual TLS/SSL authentication:

For details on steps in this example, see TLS Protocol.

Step 1: Create Server Key and Certificate

The following example creates a key and certificate for the Vertica server. It signs the certificate using the root CA created in the previous step.

  1. Generate the private key for the server:

    $ openssl genrsa -out server.key
    Generating RSA private key, 2048 bit long modulus
    ....................................................................+++
    ......................................+++
    e is 65537 (0x10001)
  2. Create a certificate request. In this step, set the Common Name field to a wildcard value for your domain—for example, *.yourdomain.tld. Using a wildcard instead of a fully-qualified domain name here ensures that this certificate is accepted for all of the Vertica nodes in your cluster:

    $ openssl req -new -key server.key -out server_reqout.txt
    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) []:
    Common Name (e.g. server FQDN or YOUR name) []:*.mycompany.com
    Email Address []:myaddress@mycompany.com
    
    Please enter the following 'extra' attributes
    to be sent with your certificate request
    A challenge password []:server_key_password
    An optional company name []:
  3. Sign the certificate request with the root CA:

    $ openssl x509 -req -in server_reqout.txt -days 3650 -sha256 -CAcreateserial -CA root.crt \
    -CAkey root.key -out server.crt
    Signature ok
    subject=/C=US/ST=MA/L=Cambridge/O=My Company/CN=*.mycompany.com/emailAddress=myaddress@mycompany.com
    Getting CA Private Key
    

Step 2: Create Client Key and Certificate

After you enable mutual authentication mode on Vertica, you must have a key and certificate for your client in order to connect to the database. The steps to create a client certificate are the same as creating the server certificate:

$ openssl genrsa -out client.key
Generating RSA private key, 2048 bit long modulus
................................................................+++
..............................+++
e is 65537 (0x10001)

$ openssl req -new -key client.key -out client_reqout.txt
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) []:
Common Name (e.g. server FQDN or YOUR name) []:*.mycompany.com
Email Address []:myemail@mycompany.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:client_key_password
An optional company name []:

$ openssl x509 -req -in client_reqout.txt -days 3650 -sha256 -CAcreateserial -CA root.crt \
  -CAkey root.key -out client.crt
Signature ok
subject=/C=US/ST=MA/L=Cambridge/O=My Company/CN=*.mycompany.com/emailAddress=myemail@mycompany.com
Getting CA Private Key

Step 3: Distribute Keys and Certificates

With the server and client keys created, you are ready to deploy the keys to your Vertica cluster:

  1. Set permissions on the certificates and key files and cleans up temporary key files:

    $ ls
    client.crt  client.key  client_reqout.txt  root.crt  root.key  root.srl  server.crt  
    server.key  server_reqout.txt
    $ chmod 600 client.key server.key
    $ chmod 644 client.crt server.crt
    $ rm client_reqout.txt server_reqout.txt
  2. Log into Vertica using vsql:

    $ vsql
    Password: dbadmin_password
    Welcome to vsql, the Vertica Analytic Database interactive terminal.
    
    Type:  \h or \? for help with vsql commands
           \g or terminate with semicolon to execute query
           \q to quit
  3. Enables SSL on the Vertica server and then exit:

    => ALTER DATABASE DEFAULT SET PARAMETER EnableSSL = 1;
    WARNING 9138:  Cannot initialize TLS until SSLCertificate and SSLPrivateKey config parameters are set
    ALTER DATABASE
    => \q
  4. Shut down the database. You must shut down it down in order to distribute the encryption keys and to have the EnableSSL configuration parameter take effect.

    $ adminTools --tool stop_db -d VMart -p dbadmin-password
            Connecting to database
            Issuing shutdown command to database
    Database VMart stopped successfully
  5. Add the server key and certificate to Vertica using the Admin Tools' command line set_ssl_params tool:

    $ adminTools --tool  set_ssl_params -d VMart -k server.key -c server.crt \
                  -a root.crt -p dbadmin_password
    Success. Change committed on a quorum of nodes.
  6. Restart the database so the configuration change takes effect:

    $ adminTools --tool start_db -d VMart -p vertica
            Starting nodes:
                    v_vmart_node0001 (10.20.110.7)
                    v_vmart_node0002 (10.20.110.8)
                    v_vmart_node0003 (10.20.110.9)
            Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
            Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
            Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
            Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
            Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
            Node Status: v_vmart_node0001: (INITIALIZING) v_vmart_node0002: (INITIALIZING) v_vmart_node0003: (INITIALIZING)
            Node Status: v_vmart_node0001: (UP) v_vmart_node0002: (UP) v_vmart_node0003: (UP)
    Database VMart: Startup Succeeded.  All Nodes are UP
    
  7. Copy the client keys to the dbadmin user's .vsql directory so that vsql is able to authenticate with Vertica:

    $ mkdir ~/.vsql
    $ cp client.key client.crt root.crt ~/.vsql
  8. Log into Vertica and query the SESSIONS system table to verify that the connection is using mutual TLS/SSL authentication.

    $ vsql
    Password: dbadmin-password
    Welcome to vsql, the Vertica Analytic Database interactive terminal.
    
    Type:  \h or \? for help with vsql commands
           \g or terminate with semicolon to execute query
           \q to quit
    
    SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)
    
    => select user_name,ssl_state from sessions;
     user_name | ssl_state
    -----------+-----------
     dbadmin   | Mutual
    (1 row)