Using TLS Server Authentication with Vertica: Validating Your TLS Key and Certificate
Overview
To enable TLS server authentication with Vertica and a SQL client, you need to obtain two files:
- server.key (private key)
- server.crt (server certificate)
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 before adding it to Vertica so you can uncover and fix any potential issues.
Note Using a commercially-signed key pair is more secure than using a self-signed certificate.
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.
To create a self-signed key:
- Generate the server key (private key). The following example generates a 2048bit RSA private key.
=> CREATE KEY server_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 = 'server_key';
name | type | length | key
------------+------+--------+---------------------------------
server_key | RSA | 2048 | -----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAtTQfGE8yXzz9vkz399oovOwOm2vq1iZlkbclJ7+/., xqdv0hCkU
.....
ip2TYY4qjRg7QE6Zzzv3eGauAIG0SxT19rfvUOnWlQ5/N/UceXC7
-----END RSA PRIVATE KEY-----
- Create a self-signed certificate authority (CA) certificate.
- Generate and sign the server certificate with the self-signed CA certificate.
=> CREATE CERTIFICATE server_crt
-> SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=common_name/emailAddress=email'
-> SIGNED BY ca_cert
-> 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-03-22 08:36:17+09
subject | C = country_code, ST = state_or_province, L = locality, O = organization, OU = org_unit,\
CN = verticadb, emailAddress = verticadb@vertica.com
serial | 70:9B:BA:83:EE:8E:7F:DA:A3:F4:6C:E8:18:06:66:25:98:98:AB:17
certificate_text | -----BEGIN CERTIFICATE-----
MIIENDCCAxygAwIBAgIUcJu6g+6Of9qj9GzoGAZmJZiYqxcwDQYJKoZIhvcNAQEL
.....
BtLYa+Dv8Fm2Eg68x+6iYT9usSVSWTQB
-----END CERTIFICATE-----
=> 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 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsComment' = 'Vertica generated root CA cert'
-> KEY server_key;
=> SELECT name, expiration_date, subject, serial, certificate_text FROM certificates WHERE name = 'ca_cert';
-[ RECORD 1 ]----+-------------------------------------------------------------------------------------
name | ca_cert
expiration_date | 2022-03-12 08:18:32+09
subject | C = country_code, ST = state_or_province, L = locality, O = organization, OU = org_unit, CN = Vertica Root CA
serial | 02:BB:83:5D:4A:90:02:E1:4C:A1:9E:C3:07:D3:55:EB:F2:C3:FF:69
certificate_text | -----BEGIN CERTIFICATE-----
MIIEFzCCAv+gAwIBAgIUAruDXUqQAuFMoZ7DB9NV6/LD/2kwDQYJKoZIhvcNAQEL
.....
UW6DAcSHlDpT4+5b6L/HznN/Y/SNxUiftWXS1cUaRKJmtMIkoGPo4bGVDQ==
-----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:
-
Generate the server key (private key). The following example generates a 2048bit RSA private key.
$ openssl genrsa -out server.key 2048
-
Generate a certificate signing request.
$ openssl req -new -nodes -key ./server.key -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)
-
Create a self-signing certificate.
$ openssl x509 -req -days 365 -in ./server.csr -signkey ./server.key -out ./server.crt
Signature ok
subject=/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=common_name/emailAddress=email
Getting Private key
Validating Your Private Key and Certificate
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.
Use the following operation 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 ca.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.
- Make sure the certificate and key match, as shown. 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
- Make sure the certificate is not expired.
$ openssl x509 -noout -text -in server.crt | grep -e "Before\|After" Not Before: Mar 15 04:21:42 2021 GMT Not After : Mar 15 04:21:42 2017 GMT
- Make sure that the certificate is valid.
$ openssl verify -verbose -CAfile ca.crt server.crt
server.crt: OK
- 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
- Check that the root certificate authority (CA) is an SSL Server CA certificate.
$ openssl x509 -noout -purpose -in ca.crt | grep "SSL server CA"
SSL server CA : Yes
- 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
- Make sure that 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.
- Set the server certificate.
=> ALTER TLS CONFIGURATION server CERTIFICATE server_crt;
- Enable the use of TLS on connections to the database.
=> ALTER TLS CONFIGURATION server TLSMODE 'ENABLE';
- 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 | | | ENABLE
- Verify TLS connection from vsql.
$ 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: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2) dbadmin=>
Vertica Versions 10.1 or Earlier
To enable TLS server encryption in Vertica versions 10.1 and earlier:
- Configure the database parameters. Set the server certificate and the private key using either admintools or vsql.
Using admintools:
$ admintools -t set_ssl_params -d database -k ./server.key -c ./server.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');"-[ RECORD 1 ]--+-------------------------------------------------------------------
parameter_name | SSLCertificate
current_value | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value |
-[ RECORD 2 ]--+-------------------------------------------------------------------
parameter_name | SSLPrivateKey
current_value | -----BEGIN RSA PRIVATE KEY-----.....-----END RSA PRIVATE KEY-----
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>';
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');"-[ RECORD 1 ]--+-------------------------------------------------------------------
parameter_name | SSLCertificate
current_value | -----BEGIN CERTIFICATE-----.....-----END CERTIFICATE-----
default_value |
-[ RECORD 2 ]--+-------------------------------------------------------------------
parameter_name | SSLPrivateKey
current_value | -----BEGIN RSA PRIVATE KEY-----.....-----END RSA PRIVATE KEY-----
default_value |
-
Enable the use of TLS on connections to the database.
=> ALTER DATABASE DEFAULT SET EnableSSL = 1;
-
Verify TLS connection from vsql.
$ 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: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)
dbadmin=>
Additional Resources
For more information about secure communications and TLS protocol on Vertica, see TLS Overview in the Vertica documentation.
