Verifying the Vertica server's access to the SecureData CA certificate

Before you can use SecureData with Vertica, you must verify that the root certificate authority (CA) and any intermediate certificate authority used to sign the SecureData Appliance's certificate are rehashed and stored in the Vertica server's trust store (/opt/vertica/packages/voltagesecure/trustStore/). Vertica supplies many standard root certificates in this directory. If your SecureData Appliance uses a certificate signed by a standard CA authority, it is likely already in the trust store.

If your SecureData Appliance is using a certificate signed by your own internal CA authority , you must add this CA Certificate to the Vertica trust store.

If you are unsure whether your CA Certificate is in the Vertica trust store, follow the steps under Troubleshooting Certificate Problems to test whether the Vertica already has the CA certificate. If you are able to retrieve the client policy XML file from the SecureData Appliance, then your Vertica cluster has the correct CA certificate to access SecureData.

Adding the CA certificate to Vertica

You must add the CA to Vertica trust store before using the SecureData Integration if you used:

Your own CA certificate to sign your SecureData Appliance's certificate.
A third-party CA that is not in the Vertica trust store.

To add the CA certificate to the Vertica trust store, you need:

The certificate authority (CA) file used to sign the SecureData Appliance's certificate. This file must be in .pem format.
The openssl or c_rehash utility.
Access to the dbadmin account on the Vertica nodes. This access is required in order to copy the certificate file to trust store directory in the Vertica installation.

To add the CA file to Vertica:

Run either openssl rehash or c_rehash, specifying the directory containing the CA certificate. This creates a symbolic link hash.0:
```
$ openssl rehash directory_of_certificate
```
```
$ c_rehash directory_of_certificate
```
On all nodes, use cp -L to copy the certificate to /opt/vertica/packages/voltagesecure/trustStore/. If the previous step produced more than one symbolic link, run the following command on all of them:
```
$ cp -L hash.0 /opt/vertica/packages/voltagesecure/trustStore/
```

Reinstall the SecureData integration library:

$ admintools -t install_package -d database_name -p 'password' --package voltagesecure --force-reinstall

When Vertica reinstalls the SecureData integration library, it copies the CA authority file to the all nodes in the cluster. After the file is distributed, all Vertica nodes can authenticate with the SecureData Appliance.

For example, for a database VMart and and a certificate file my_ca_cert.pem:

Rehash the certificate:

$ openssl rehash my_certs
$ ls -l my_certs
total 4
lrwxrwxrwx 1 dbadmin dbadmin    8 Oct 12 14:35 9da13359.0 -> my_ca_cert.pem
-rw-rw-r-- 1 dbadmin dbadmin 1245 Oct 12 14:16 my_ca_cert.pem

On all nodes, copy the file certificate to the trust store:

$ cp -L 9da13359.0 /opt/vertica/packages/voltagesecure/trustStore/

Run the following on one node:

$ admintools -t install_package -d VMart -p dbadmin_password --package voltagesecure --force-reinstall
Installing package voltagesecure...
...Success!

Troubleshooting certificate problems

You can test whether the Vertica trust store has the correct certificate by executing the following statement from the Linux command line:

curl --capath /vertica_catalog_directory/Libraries/\
$(vsql -A -t -c "SELECT sal_storage_id from user_libraries WHERE lib_name = 'VoltageSecureLib';")\
https://SecureData_applicance_hostname/policy/clientPolicy.xml

Where:

vertica_catalog_directory is the absolute path to the Vertica catalog directory. See Understanding the catalog directory for more information about the catalog directory.
SecureData_appliance_hostname is the host name of your Voltage SecureData Appliance.

For example, suppose you are connected to node0001 of the example VMart database. Also, your Voltage SecureData appliance's host name is voltage-pp-0000.example.com. Then you would use the following command to test your certificate installation.

$ curl --capath /home/dbadmin/VMart/v_vmart_node0001_catalog/Libraries/\
$(vsql -A -t -c "SELECT sal_storage_id from user_libraries WHERE lib_name = 'VoltageSecureLib';") \
https://voltage-pp-0000.example.com/policy/clientPolicy.xml


<clientPolicy version="2">

<server name="SecureDataAppliance" version="6.4.2.232000" />

<localDomains>example.com</localDomains>

<userWhitelist></userWhitelist>

<defaultDistrict value="0" />

<sendUniversalReader value="1" />

<messageFooterGlobal></messageFooterGlobal>

<parameterAggressiveDistricts>example.com</parameterAggressiveDistricts>

<localPolicyLocked value="0" />

<trustedDistricts></trustedDistricts>

<fallThroughDistrict>example.com</fallThroughDistrict>
      .    .    .

The <clientPolicy>... output (which is the content of the clientPolicy.xml file) indicates that the Vertica node was able to use its CA certificate to connect to the SecureData Appliance.

Tip

If you are unsure which Format Preserving Encryption (FPE) formats are defined in your SecureData Appliance, examine the output of the curl command. Look for the tags with formatName attributes which describe each of the formats.

If the CA certificate you installed on Vertica does not match the certificate installed on the SecureData Appliance, you will see an error similar to the following:

$ curl --capath /home/dbadmin/VMart/v_vmart_node0001_catalog/Libraries/$(vsql\
 -A -t -c "SELECT sal_storage_id from user_libraries WHERE lib_name = 'VoltageSecureLib';")\
 https://voltage-pp-0000.example.com/policy/clientPolicy.xml

curl: (60) Peer certificate cannot be authenticated with known CA certificates
More details here: http://curl.haxx.se/docs/sslcerts.html

In this case, verify that you have installed the correct CA certificate in Vertica, and that its file name has a .pem extension.

If you see other errors, such as "couldn't connect to host," verify that your firewall configuration allows your Vertica nodes to access your SecureData Appliance.