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 is 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 Privacy Enhanced Mail (.pem) format. The file name does not matter, as long as it has the .pem extension.

• 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 necessary CA file to Vertica:

1. Login to one of the Vertica nodes as the dbadmin user.

2. Copy the .pem file to the /opt/vertica/packages/voltagesecure/trustStore/ directory. You only need to copy this file to a single node. Vertica takes care of distributing the file to the rest of the nodes in the cluster.

3. On the Linux command line, execute the following command to 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, suppose:

• Your certificate file is named my_ca.cert.pem, and you have copied it to the dbadmin home directory on node in your cluster.

• Your database is named VMart.

Then the process of installing the CA file would look like this:

$ cp my_ca.cert.pem /opt/vertica/packages/voltagesecure/trustStore/
$ admintools -t install_package -d VMart -p dbadminpassword --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.

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.