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.