Share this article:

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

Applies to: Vertica Versions 7.1.x and 7.2.x

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

  • server.key
  • server.crt
  • root.crt

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

Your IT department may provide you with these files. If not you can generate them manually, as shown below.

Note Using a commercially-signed keypair is more secure than using a self-signed certificate:

It’s important to validate your certificate and key before you add these to Vertica to uncover and fix any possible issues.

Create a self-signed certificate

  1. Generate the server.key.
$ openssl genrsa -out server.key
  1. Generate a certificate signing request.
$ openssl req -new -nodes -key ./server.key -out ./server.csr -config ./openssl.cnf 
  1. Create a self-signed certificate.
$ openssl x509 -req -days 365 -in ./server.csr -signkey ./server.key -out ./server.crt
  1. Use root.crt/root.key to create user certificates. For the self-signed certificates, the root.crt is the same as the server.crt. cp ./server.crt ./root.crt
$ cp ./server.crt ./root.crt
$ cp ./server.key ./root.key

Example: openssl.cnf

[ req ] \n",        
default_bits = 1024 
default_keyfile = key.pem 
default_md = sha256
string_mask = nombstr
distinguished_name = req_distinguished_name   
[ req_distinguished_name ]   
# Variable name   Prompt string   
0.organizationName = Organization Name (company)   
organizationalUnitName = Organizational Unit Name (department, division)   
emailAddress = Email Address   
emailAddress_max = 40   
localityName = Locality Name (city, district)   
stateOrProvinceName = State or Province Name (full name)   
countryName = Country Name (2 letter code)   
countryName_min = 2    
countryName_max = 2    
commonName = Common Name (hostname, IP, or your name)   
commonName_max = 64   
#-------------------Edit this section------------------------------   
countryName_default     = US   
stateOrProvinceName_default = CA   
localityName_default        = San Francisco   
0.organizationName_default  = example_company   
organizationalUnitName_default  = Information Systems   
commonName_default          = server_name   
emailAddress_default            =   

Validate the server.key, server.crt, and root.crt

  1. Make sure the certificate and key match. Note that the md5 value must be the same.
$ openssl x509 -noout -modulus -in server.crt | openssl md5
(stdin)= dc8f02021b33edd7001dff2ca6176b18
$ openssl rsa -noout -modulus -in server.key | openssl md5
 (stdin)= dc8f02021b33edd7001dff2ca6176b18
  1. Make sure the certificate has not expired and that the specified date range is current and valid.
$ openssl x509 -noout -text -in server.crt | grep -e "Before\|After"
            Not Before: Jul 21 14:19:37 2016 GMT
            Not After : Jul 21 14:19:37 2017 GMT
  1. Make sure the server.crt is valid.
$ openssl verify -verbose -CAfile root.crt  server.crt
    server.crt: OK
  1. Check that the certificate is allowed on the SSL server.
$ openssl x509 -noout -purpose -in server.crt | grep "SSL server"
    SSL server : Yes
  1. Check that the root certificate authority (CA) is an SSL Server CA certificate.
$ openssl x509 -noout -purpose -in root.crt | grep "SSL server CA"  #(for self signed certs root.crt = server.crt )
    SSL server CA : Yes
  1. Make sure the key does not require a password. 
$ ssh-keygen -y -f server.key

Important  If you are prompted for a password, do not proceed. Fix your server.key as shown below.

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

Add the server.key, server.crt, and root.crt to Vertica  

First, ensure the keys are owned by dbadmin and only readable by dbadmin.

$ chown dbadmin:verticadba server.key server.crt root.crt
$ chmod 600 server.key server.crt root.crt

Next, add the SSL keys and certificates using either:

Option 1: Add SSL keys and certs using admintools

$ admintools  -t set_ssl_params -d hercules  -k ./server.key -c ./server.crt  -a ./root.crt
 Parameters set successfully


Option 2:  Add SSL keys and certs using vsql

  1. Make sure the echo command has the contents of the server.crt file.
$ echo "select set_config_parameter ('SSLCertificate','$(cat server.crt)');" | vsql
  1. Make sure the echo command has the contents of the server.key file.
$ echo "select set_config_parameter ('SSLPrivateKey','$(cat server.key)');" | vsql 
  1. Check the parameters and restart.
$ vsql -a -c "ALTER DATABASE hercules SET SSLCA = '`cat root.crt`';"
$ vsql -x -c "select parameter_name, current_value, default_value from 
vs_configuration_parameters where parameter_name in 
('EnableSSL', 'SSLCertificate', 'SSLPrivateKey', 'SSLCA');"

Enable SSL

Enable the SSL and restart. This will only work if the previous steps have succeeded.

$ echo "ALTER DATABASE <DatabaseName> SET EnableSSL = 1;" | vsql
$    admintools -t stop_db  -d <dababase>
$    admintools -t start_db -d <dababase>

Create user certificates

If your IT department has not provided your certificate and key for each database user, you should create one using the root.crt and root.key as shown below.

  1. Create the user key.
$ openssl genrsa -out userfoo.key
  1. Run the certificate signing request (CSR). Make sure that the username set is the same one you will use in the CREATE USER statement later.
$ openssl req  -new  -out userfoo.csr -key userfoo.key
Common Name (e.g. server FQDN or YOUR name) []:userfoo
  1. Generate user certificate. The files “userfoo.crt” and “userfoo.key” are the user’s certificate and key.
$ openssl x509  -CA root.crt  -CAkey root.key  -days 3650 -req -in 
userfoo.csr -out userfoo.crt

Validate user certificates

If your IT department provided you with your certificates, you must validate them.  Follow these steps to validate your certificates.

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

  1. Check to ensure that the userfoo.crt is a SSL client certificate.
$ openssl x509 -noout -purpose -in userfoo.crt  | grep "SSL client"
SSL client : Yes
  1. Show the common name on the certificate and check that it matches the database username.
$ openssl x509 -noout -issuer -subject -dates -in userfoo.crt 
 issuer= /O=example_company/…/CN=server_name   <==  root.crt
 subject= /C=US/ST=MA/L=Boston/O=QA/CN=userfoo   <== CN=username
notBefore=Jul 21 15:59:28 2016 GMT   
notAfter=Jul 19 15:59:28 2026 GMT  <=== Cert must not be expired and user 
                                      log in must be within this date range
  1. Ensure that the root.crt has been used to sign the client certificate.
$ openssl verify   -CAfile  root.crt userfoo.crt
userfoo.crt: OK

Placing the cert and key in the correct location for vsql

Put the user cert/key in the correct location for the vsql client (as shown) and ensure 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

Configure mutual mode authentication

  1. Create the authentication method “tls” for specific users, create a user with the same username listed above, and link the two.
$ vsql
  create authentication SSLCert method 'tls' host tls '';
  create user userfoo;
  grant authentication SSLCert to userfoo;
  1. Test to see if it worked. You must use the –h argument (for connections over TCP/IP)
$ vsql -U userfoo -h -m require
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)   <== Server SSL enabled 

userfoo=> select user,authentication_method, ssl_state from sessions where 
session_id = current_session();
current_user | authentication_method | ssl_state

 userfoo      | TLS                   | Mutual          <== Mutual Client authentication
(1 row)

Share this article: