vikram sarabhUsing SSL Server Authentication with Vertica: Validating Your SSL Key and Certificate

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

Overview

To enable SSL server authentication with Vertica and a SQL client, you need to obtain two files:

  • server.key
  • server.crt

Your IT department may provide you with these files. Or, you can create a self-signed certificate on your own.

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/

In any case, 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

2. Generate a certificate signing request.

$ openssl req -new -nodes -key ./server.key -out ./server.csr -config
 ./openssl.cnf -batch 


3. Create a self-signed certificate.

$ openssl x509 -req -days 365 -in ./server.csr -signkey ./server.key -out ./server.crt

Example: openssl.cnf

#-------------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            = admin@example.com 

Validate server.key and server.crt

1. Make sure the certificate and key match, as shown. Note that the md5 numbers must match.

$ openssl x509 -noout -modulus -in server.crt | openssl md5
(stdin)= dc8f02021b33edd7001dff2ca6176b18

$ openssl rsa -noout -modulus -in server.key | openssl md5
(stdin)= dc8f02021b33edd7001dff2ca6176b18

2. Make sure the certificate is not expired.

$ 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

3. Make sure the server.crt is valid.

$ openssl verify -verbose -CAfile root.crt  server.crt
server.crt: O

4. Check that server.crt is allowed on the SSL server.

$ openssl x509 -noout -purpose -in server.crt | grep "SSL server"
SSL server : Yes

5. Check that the root certificate authority (CA) is an SSL Server CA certificate. (For the self-signed certificates, the root.crt is the same as the server.crt. cp ./server.crt ./root.crt)

$ openssl x509 -noout -purpose -in root.crt | grep "SSL server 
CA"  #(for self signed certs root.crt = server.crt )
SSL server CA : Yes

6. Make sure server.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

Note server.crt can be a chained certificate. Append all certificates that are part of the chain into server.crt.

cat root0.crt >> chain.crt
cat root1.crt >> chain.crt
cat root2.crt >> chain.crt
cat server.crt >> chain.crt
cp chain.crt server.crt

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

First, ensure that keys are owned by dbadmin and that only the dbadmin can read the keys. 

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

 Then, add the SSL keys and certificates to Vertica using either:

Option 1: Add SSL keys and certificatess using admintools. 

$ admintools  -t set_ssl_params -d database  -k ./server.key -c ./server.crt    
   

Option 2: Add SSL keys and certificates using vsql.

1. Check that the echo command has the contents of the server.crt file.

$ echo "select set_config_parameter ('SSLCertificate','$(cat server.crt)');"  | vsql  

2. Check that the echo command has the contents of the server.key file.

$ echo "select set_config_parameter ('SSLPrivateKey','$(cat server.key)');"   | vsql  

3. Check parameters were set correctly, enable SSL, and restart.

$ vsql -x -c "select parameter_name, current_value, default_value from 
vs_configuration_parameters where parameter_name in ('EnableSSL', 
'SSLCertificate', 'SSLPrivateKey');"

Enable SSL

If the previous steps have succeeded, enable SSL as shown below.

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

Verify SSL connection

vsql
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-SHA, bits: 256)    

The last line indicates that the SSL is successfully enabled.