Using Mutual Mode TLS Server Authentication with Vertica: Validating Your TLS Key and Certificate

To enable mutual mode TLS authentication in Vertica, you need three files.

  • server.key (private key)
  • server.crt (server certificate)
  • root.crt (root certificate)

Additionally, each authorized user certificate must be signed by the root.crt.

Your IT department might provide you with these files. Or, you can create a self-signed certificate on your own. In either case, it is important to validate your certificate and key before you add these to Vertica so you can uncover and fix any potential issues.

Note Using a commercially-signed keypair is more secure than using a self-signed certificate:  https://blog.digicert.com/the-true-cost-of-self-signed-ssl-certificates/

Creating a Self-Signed Certificate

If you are preparing the private key and certificate yourself, follow these steps to create a self-signed certificate. Skip these steps if you are receiving a certificate from your IT department.

Vertica Versions 10.0.1 or Later

In versions 10.0.1 or later, Vertica provides In-Database Cryptographic Key and Certificate Management, which allows database administrators to manage keys and certificates in Vertica.

Important If you use In-Database Cryptographic Key and Certificate Management for Mutual Mode, apply the hotfix of 10.1.1-3 or later. Otherwise, the database connection with Mutual Mode configuration will not work.

To create a self-signed key:

  1. Generate the root key (private key). The following example generates a 2048bit RSA private key.
    => CREATE KEY root_key TYPE 'RSA' LENGTH 2048;

    You can view the generated private key in the cryptographic_keys table.

    => SELECT name, type, length, key FROM cryptographic_keys WHERE name = 'root_key';
   name   | type | length |                 key
----------+------+--------+--------------------------------------
 root_key | RSA  |   2048 | -----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEA1iDkHpCNNU6rePGDs86+n+q3hFYZpsXe/cl+Qu/+fRxMk/9H
.....
udKjTmfRW5uR8vSMggRGa7PfGD8RBxmR0VzulXkZyuWGcp2lD9DZJw==
-----END RSA PRIVATE KEY-----
  2. Create a self-signed certificate authority (CA) certificate.
    3. => CREATE CA CERTIFICATE ca_cert
-> SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica Root CA'
-> VALID FOR 365
-> EXTENSIONS 'basicConstraints' = 'CA:TRUE,pathlen:0', 'subjectKeyIdentifier' = 'hash',
->            'authorityKeyIdentifier' = 'keyid,issuer', 'keyUsage' = 'critical,digitalSignature,keyCertSign',
->            'nsComment' = 'Vertica generated root CA cert'
-> KEY root_key;
    You can also view the generated certificate in the certificates table.
    => SELECT name, expiration_date, subject, serial, certificate_text FROM certificates WHERE name = 'ca_cert';
-[ RECORD 1 ]----+------------------------------------------------------------------------------------
name             | ca_cert
expiration_date  | 2022-06-21 15:01:50+09
subject          | C = country_code, ST = state_or_province, L = locality, O = organization, OU = org_unit, CN = Vertica Root CA
serial           | 74:2A:65:21:44:19:F8:33:18:BC:9A:B2:EA:6C:7F:8C:73:D7:A8:1C
certificate_text | -----BEGIN CERTIFICATE-----
MIIEAjCCAuqgAwIBAgIUdCplIUQZ+DMYvJqy6mx/jHPXqBwwDQYJKoZIhvcNAQEL
.....
Y2noG9SFzzBmowIgaUVSZ6ZRm4VQRA==
-----END CERTIFICATE-----

  3. Generate the server key (private key). The following generates a 2048bit RSA private key.

    => CREATE KEY server_key TYPE 'RSA' LENGTH 2048;
    => SELECT name, type, length, key FROM cryptographic_keys WHERE name = 'server_key';
    name    | type | length |                key
------------+------+--------+------------------------------------
 server_key | RSA  |   2048 | -----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAxjcpW2eR6VbciTc0z/LxihfTomQt8N9VHxBHAQ8lKOKm2DIr
.....
1dbzXpNTcQxfJ/EO0eOQOHhZLWWEcwA95qF0R8yXmFeZyt2z+pGNag==
-----END RSA PRIVATE KEY-----
  4. Generate and sign the server certificate with the self-signed CA certificate.
    5. => CREATE CERTIFICATE server_crt
-> SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=common_name'
-> SIGNED BY ca_cert
-> VALID FOR 365
-> EXTENSIONS 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsCertType' = 'server',
->            'extendedKeyUsage' = 'serverAuth',
->            'subjectAltName' = 'DNS.1:alt_hostname,IP:IP_address'
-> KEY server_key;
    => SELECT name, expiration_date, subject, serial, certificate_text FROM certificates WHERE name = 'server_crt';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------
name             | server_crt
expiration_date  | 2022-06-21 15:07:38+09
subject          | C = country_code, ST = state_or_province, L = locality, O = organization, OU = org_unit, CN = common_name
serial           | 1C:79:49:0E:5E:BA:2C:AD:D1:B7:52:12:1C:B4:A9:19:2F:73:50:9C
certificate_text | -----BEGIN CERTIFICATE-----
MIIEDzCCAvegAwIBAgIUHHlJDl66LK3Rt1ISHLSpGS9zUJwwDQYJKoZIhvcNAQEL
.....
PLIRKFw9DskWDJDje1TotbZkuS/IVRNtmJDVU1Iqz8Qtm/I=
-----END CERTIFICATE-----

Vertica Versions 10.0 and Earlier

In Vertica versions 10.0 and earlier, you need to use the OpenSSL command to create and manage your keys and certificates. You can follow this step in 10.0.1 or later if you don't want to use In-Database Cryptographic Key and Certificate Management.

To create a self-signed key:

  1. Generate the root key (private key). The following generates a 2048bit RSA private key.
    $ openssl genrsa -out root.key 2048

  2. Generate a certificate signing request for CA certificate.

    $ openssl req -new -nodes -key root.key -sha256 -out root.csr
 
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) [XX]: country_code
State or Province Name (full name) []: state_or_province
Locality Name (eg, city) [Default City]: locality
Organization Name (eg, company) [Default Company Ltd]: organization
Organizational Unit Name (eg, section) []: org_unit
Common Name (eg, your name or your server's hostname) []: Vertica Root CA
Email Address []: email
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: (blank)
An optional company name []: (optional)

  3. Create a file containing certificate extensions to use.

    echo "basicConstraints=CA:TRUE,pathlen:0
subjectKeyIdentifier=hash
nsComment=\"Vertica generated root CA cert\"
authorityKeyIdentifier=keyid,issuer" > root.ext

  4. Create a self-signed certificate for CA certificate.

    $ openssl x509 -days 365 -in root.csr -req -signkey root.key -out root.crt -extfile root.ext
Signature ok
subject=/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica Root CA
Getting Private key

  5. Generate the server key (private key). The following example generates a 2048bit RSA private key.

    $ openssl genrsa -out server.key 2048

  6. Generate a certificate signing request for the server certificate.

    $ openssl req -new -nodes -key server.key -sha256 -out server.csr
 
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) [XX]: country_code
State or Province Name (full name) []: state_or_province
Locality Name (eg, city) [Default City]: locality
Organization Name (eg, company) [Default Company Ltd]: organization
Organizational Unit Name (eg, section) []: org_unit
Common Name (eg, your name or your server's hostname) []: common_name
Email Address []: email
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: (blank)
An optional company name []: (optional)

  7. Create a self-signed certificate for the server certificate.

    $ openssl x509 -CA root.crt -CAkey root.key -CAcreateserial -days 365 -req -in server.csr -out server.crt
Signature ok
subject=/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=common_name
Getting Private key

Validating Your Private Key and Certificates

If you have created a private key or certificate using In-Database Cryptographic Key and Certificate Management, you will need to export it to a file. You don't need to export a key or certificate that has been provided by another department.

To export your private key or certificate:

$ vsql -At -c "SELECT key FROM cryptographic_keys WHERE name = 'server_key';" -o server.key
$ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'server_crt';" -o server.crt
$ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'ca_cert';" -o root.crt

Once your private key or certificate has been exported, you need to validate it using the OpenSSL command. Install the OpenSSL package on your server, or use the following steps to validate it on a server where the OpenSSL package has already been installed.

  1. Ensure the certificate and key match, as shown. The md5 numbers must match. 
    $ openssl x509 -noout -modulus -in server.crt | openssl md5
(stdin)= 336be337f35f5e58d7b2ac22c08efe01
$ openssl rsa -noout -modulus -in server.key | openssl md5
(stdin)= 336be337f35f5e58d7b2ac22c08efe01
  2. Ensure the certificate is not expired. 
    $ openssl x509 -noout -text -in server.crt | grep -e "Before\|After"
            Not Before: Mar 19 05:00:09 2021 GMT
            Not After : Mar 29 05:00:09 2022 GMT
  3. Ensure that the certificate is valid. 
    $ openssl verify -verbose -CAfile root.crt server.crt
server.crt: OK
  4. Check that the certificate is allowed on the SSL server.

    $ openssl x509 -noout -purpose -in server.crt | grep "SSL server" | grep -v CA
SSL server : Yes
  5. Check that the root certificate authority (CA) is an SSL Server CA certificate. 
    $ openssl x509 -noout -purpose -in root.crt | grep "SSL server CA"
SSL server CA : Yes
  6. Ensure that the keys are owned by the dbadmin and that only the dbadmin can read the keys. If you are following these steps on a non-Vertica node, replace dbadmin with your username. This is required by the ssh-keygen command in the next step. 
    $ chown dbadmin:verticadba server.key server.crt
$ chmod 600 server.key server.crt
  7. Ensure the private key does not require a password. 
    $ ssh-keygen -y -f server.key
    ssh-rsa .....

Important If you are prompted to input a password, do not proceed. Fix your server.key using the following command:

$ mv server.key bad.server.key
$ openssl rsa -in ~/bad.server.key -out ./server.key

Configuring Vertica to Enable TLS Server Encryption

Vertica Versions 11.0 or Later

In versions 11.0 or later, Vertica implements TLS CONFIGURATION objects, which manages the TLS configuration for Vertica.

  1. Set the server and CA certificate.

    => ALTER TLS CONFIGURATION server CERTIFICATE server_crt ADD CA CERTIFICATES ca_cert;
  2. Enable the use of TLS on connections to the database.

    => ALTER TLS CONFIGURATION server TLSMODE 'VERIFY_CA';
  3. Check that the configurations were set correctly.

    => SELECT * FROM tls_configurations WHERE name = 'server';
  name  |  owner  | certificate | ca_certificate | cipher_suites |   mode
--------+---------+-------------+----------------+---------------+-----------
 server | dbadmin | server_crt  | ca_cert        |               | VERIFY_CA

Vertica Versions 10.1 or Earlier

To enable TLS server encryption in Vertica versions 10.1 and earlier:

  1. Configure the database parameters. Set the certificates and the private key using either admintools or vsql.

    1. Using admintools:

      $ admintools -t set_ssl_params -d database -k ./server.key -c ./server.crt -a ./root.crt
SSL configuration parameters set successfully

      Check that the parameters were set correctly.

      $ vsql -x -c "SELECT parameter_name, current_value, default_value FROM configuration_parameters WHERE parameter_name IN ('SSLCertificate', 'SSLPrivateKey', 'SSLCA');"
-[ RECORD 1 ]--+-------------------------------------------------------------------
parameter_name | SSLPrivateKey
current_value  | -----BEGIN RSA PRIVATE KEY-----.....-----END RSA PRIVATE KEY-----
default_value  |
-[ RECORD 2 ]--+-------------------------------------------------------------------
parameter_name | SSLCertificate
current_value  | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value  |
-[ RECORD 3 ]--+-------------------------------------------------------------------
parameter_name | SSLCA
current_value  | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value  |

    Using vsql:

    => ALTER DATABASE DEFAULT SET SSLPrivateKey = '<key column value in cryptographic_keys table for server_key OR the content of server.key>';
=> ALTER DATABASE DEFAULT SET SSLCertificate = '<certificate_text column value in certificates table for server_crt OR the content of server.crt>';
=> ALTER DATABASE DEFAULT SET SSLCA = '<certificate_text column value in certificates table for ca_cert OR the content of root.crt>';

    Note Ignore the following warning message when setting SSLPrivateKey configuration parameter:

    WARNING 9173: TLS cert is empty
    WARNING 9144: Could not create an SSL_CTX given the current values of SSLCertificate, SSLPrivateKey, and SSLCA

    Check that the parameters were set correctly.

    $ vsql -x -c "SELECT parameter_name, current_value, default_value FROM configuration_parameters WHERE parameter_name IN ('SSLCertificate', 'SSLPrivateKey', 'SSLCA');"
-[ RECORD 1 ]--+-------------------------------------------------------------------
parameter_name | SSLPrivateKey
current_value  | -----BEGIN RSA PRIVATE KEY-----.....-----END RSA PRIVATE KEY-----
default_value  |
-[ RECORD 2 ]--+-------------------------------------------------------------------
parameter_name | SSLCertificate
current_value  | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value  |
-[ RECORD 3 ]--+-------------------------------------------------------------------
parameter_name | SSLCA
current_value  | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value  |

  2. Enable the use of TLS on connections to the database.

    => ALTER DATABASE DEFAULT SET EnableSSL = 1;

Creating User Certificates

If you need to prepare the private key and certificate by yourself, you can follow these steps to create a self-signed certificate. If you already got the files from the IT department, you don't need to follow this step.

Vertica Versions 10.0.1 or Later

You can also use In-Database Cryptographic Key and Certificate Management to create the user certificates.

Important  

If you use In-Database Cryptographic Key and Certificate Management for Mutual Mode, you must apply the hotfix of 10.1.1-3 or later. Otherwise, the database connection with Mutual Mode configuration will not work.

Use the following operations to create a self-signed key:

  1. Generate the user key (private key). The following generates a 2048bit RSA private key.
    => CREATE KEY userfoo_key TYPE 'RSA' LENGTH 2048;
    => SELECT name, type, length, key FROM cryptographic_keys WHERE name = 'userfoo_key';
    name     | type | length |               key
-------------+------+--------+-----------------------------------
 userfoo_key | RSA  |   2048 | -----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAyEhS0mtBXIM+cTKi2if2fSuMGYwXIx2p6MrI4O0yTExIKcD3
.....
KmnGKTudviJ0J+4wm9YclmwnyB8S2sx481zXPhG9AhThrvnULgrs
-----END RSA PRIVATE KEY-----

  2. Generate and sign the client certificate with the self-signed CA certificate. Make sure that the Common Name is the username you will use in the CREATE USER statement later.

    => CREATE CERTIFICATE userfoo_crt
-> SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=database_username'
-> SIGNED BY ca_cert
-> VALID FOR 365
-> EXTENSIONS 'basicConstraints' = 'CA:FALSE', 'subjectKeyIdentifier' = 'hash',
->            'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsCertType' = 'client',
->            'extendedKeyUsage' = 'clientAuth'
-> KEY userfoo_key;
    => SELECT name, expiration_date, subject, serial, certificate_text FROM certificates WHERE name = 'userfoo_crt';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------------
name             | userfoo_crt
expiration_date  | 2022-06-21 15:12:41+09
subject          | C = country_code, ST = state_or_province, L = locality, O = organization, OU = org_unit, CN = database_username
serial           | 74:4C:D0:4D:DC:CF:B0:BE:9F:B6:A6:3C:E9:62:62:57:DF:13:FC:97
certificate_text | -----BEGIN CERTIFICATE-----
MIIEajCCA1KgAwIBAgIUdEzQTdzPsL6ftqY86WJiV98T/JcwDQYJKoZIhvcNAQEL
.....
Smzi7SYjO0eUmIrvdr3IuLO/Cc4zVRFbJ2CyKBVT
-----END CERTIFICATE-----

Vertica Versions 10.0 or Earlier

You need to use the OpenSSL command to create and manage the keys and certificates. You can follow this step in 10.0.1 or later if you don't want to use In-Database Cryptographic Key and Certificate Management.

  1. Create the user key (private key). The following generates a 2048bit RSA private key.

    $ openssl genrsa -out userfoo.key 2048

  2. Generate a certificate signing request for the user certificate. Ensure that the Common Name is the username you will use in the CREATE USER statement later.

    $ openssl req -new -nodes -key userfoo.key -sha256 -out userfoo.csr
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) [XX]: country_code
State or Province Name (full name) []: state_or_province
Locality Name (eg, city) [Default City]: locality
Organization Name (eg, company) [Default Company Ltd]: organization
Organizational Unit Name (eg, section) []: org_unit
Common Name (eg, your name or your server's hostname) []: userfoo (database username)
Email Address []: email
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: (blank)
An optional company name []: (optional)

  3. Generate the user certificate. The files userfoo.crt and userfoo.key are the user's certificate and key.

    $ openssl x509 -CA root.crt -CAkey root.key -CAcreateserial -days 365 -req -in userfoo.csr -out userfoo.crt
Signature ok
subject=/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=userfoo(database username)/emailAddress=email
Getting CA Private Key

Validating User Certificates

If you created the private key or certificate using In-Database Cryptographic Key and Certificate Management, you will need to export it to a file. You don't need to export a key or certificate that has been provided by another department.

To export your private key or certificate:

$ vsql -At -c "SELECT key FROM cryptographic_keys WHERE name = 'userfoo_key';" -o userfoo.key
$ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'userfoo_crt';" -o userfoo.crt

  1. Check that the certificate is allowed on the SSL client.

    $ openssl x509 -noout -purpose -in userfoo.crt | grep "SSL client" | grep -v CA
SSL client : Yes

  2. Show the information on the certificate and ensure that the common name matches the database username, the issuer is the same as root.crt, the certificate is not expired and user login must be within this date range.

    $ openssl x509 -noout -issuer -subject -dates -in userfoo.crt
issuer= /C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica Root CA
subject= /C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=userfoo(database username)
notBefore=Mar 19 06:43:43 2021 GMT
notAfter=Mar 19 06:43:43 2022 GMT

  3. Make sure the certificate is valid.

    $ openssl verify -CAfile root.crt userfoo.crt
userfoo.crt: OK

Configuring Mutual Mode Authentication

Note This procedure is for using TLS with vsql clients only. JDBC and ODBC clients require a different procedure.

  1. Placing the certificate and key in the correct location for vsql.

    Put the user certificate and key in the correct location for the vsql client (as shown) and make sure they have the right permissions and ownership.

    $ mkdir -p ~/.vsql
$ cp userfoo.crt ~/.vsql/client.crt
$ cp userfoo.key ~/.vsql/client.key
$ chmod 600 ~/.vsql/client.key ~/.vsql/client.crt
$ chown -R userfoo ~/.vsql ~/.vsql/client.key ~/.vsql/client.crt

  2. Create the authentication method "tls" for specific users, create a user with the same username listed above, and grant the authentication method to that user.

    => CREATE AUTHENTICATION SSLCert METHOD 'tls' HOST tls '0.0.0.0/0';
=> CREATE USER userfoo;
=> GRANT AUTHENTICATION SSLCert TO userfoo;

  3. Test to see if it works. You must use the –h argument for connections over TCP/IP.

    $ vsql -U userfoo -h vertica_node -m require
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: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)
 
userfoo=> SELECT user, authentication_method, ssl_state FROM sessions WHERE session_id = current_session();
 current_user | authentication_method | ssl_state
--------------+-----------------------+-----------
 userfoo      | TLS                   | Mutual

Additional Resources

For more information about secure communications with TLS protocol in Vertica, see TLS Overview in the Vertica documentation.