How-To Troubleshoot Vertica’s Voltage SecureData Function Issues

Vertica provides functions to encrypt and decrypt data using Voltage SecureData. The configuration to integrate with Voltage SecureData is straightforward. However, you may find issues when the integration functions do not work as expected.

Configuring Vertica

In cases where the functions do not work as expected, the configuration in Vertica may be the cause. To integrate with Voltage SecureData, there are 2 steps involved:

  • Adding the Certificate File on All Nodes
  • Configuring Authentication Information

Adding the Certificate File on All Nodes

To add the certificate file to Vertica,

  1. Copy the .pem file to the /opt/vertica/packages/voltagesecure/trustStore/ directory on a single node.

  2. Reinstall the Voltage SecureData integration library. The certificate file is distributed to all Vertica nodes with the reinstallation process.

  3. Run the following command on all nodes to verify Vertica has the correct certificate file:

    $ curl -k --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

    If the command returns an error message, follow the instructions.

    If the command returns different outputs on certain nodes, check the timestamp and the contents of the certificate file copied to the /opt/vertica/packages/voltagesecure/trustStore/ directory on that node.

    If you see any differences in the certificate file, reinstall the Voltage SecureData integration library and check the log messages in adminTools.log and vertica.log to see if there was any error.

Configuring Authentication Information

The authentication information configured by VoltageSecureConfigureGlobal and VoltageSecureConfigure functions is stored in the dfs file. Ensure the authentication information is stored on all nodes correctly.

To check the authentication information,

  1. Run the following query to get the location path and the block ID as the dfs file stores the information configured by the VoltageSecureConfigureGlobal function on each node:

    => SELECT node_name, file_name, location_path, TO_HEX(block_id) block_id FROM dfs_storage WHERE directory ILIKE '%voltage%' ORDER BY node_name;
     
    node_name   |  file_name  |       location_path      |    block_id
    ---------------+-------------+--------------------------+----------------
    v_db_node0001 | conf.global | /data/v_db_node0001_data | a0000000030f61
    v_db_node0002 | conf.global | /data/v_db_node0002_data | b0000000030f23
    v_db_node0003 | conf.global | /data/v_db_node0003_data | c0000000030f13

  2. Run the command to display the information stored in the dfs file. Following is an example for node 1:

    $ cd /data/v_db_node0001_data
    $ find ./ -type f -name "*a0000000030f61*" -exec cat {} \;
    {"allow_short_fpe":true,"policy_url":"https://SecureData_applicance_hostname/policy/clientPolicy.xml","enable_file_cache":true}

    If you see any difference in the stored information, run the VoltageSecureConfigureGlobal function again.

  3. Run the following query to get the location path and the block ID as the dfs file stores the information configured by the VoltageSecureConfigure function on each node:

    => SELECT node_name, directory, file_name, location_path, TO_HEX(block_id) block_id FROM dfs_storage WHERE file_name = '<config_dfs_path>' ORDER BY node_name, directory;
     
    node_name   | directory |  file_name   |       location_path      |    block_id
    ---------------+-----------+--------------+--------------------------+----------------
    v_db_node0001 | dbadmin   | voltage.conf | /data/v_db_node0001_data | a0000000030f67
    v_db_node0002 | dbadmin   | voltage.conf | /data/v_db_node0002_data | b0000000030f43
    v_db_node0003 | dbadmin   | voltage.conf | /data/v_db_node0003_data | c0000000030f19

    Each database user has the dfs file. Check the value of the directory column.

  4. Run the command to display the information stored in the dfs file. Following is an example for node 1:

    $ cd /data/v_db_node0001_data
    $ find ./ -type f -name "*a0000000030f67*" -exec cat {} \;
    {"identity":"PII@xxx.xxxxx.com","shared_secret":"xyz"}

    If you see any difference in the stored information, run the VoltageSecureConfigure function again.

Issue in the Environment

There may be an issue in the environment when the VoltageSecureProtect and VoltageSecureAccess functions do not work as expected or the curl command may return an error when verifying the certificate file. In this case, check all network-related components between the Vertica nodes and Voltage SecureData servers to see if the request from Vertica nodes can reach the Voltage SecureData server.

The following errors were reported by users who had this issue due to network misconfiguration:

=> SELECT public.VoltageSecureAccess('123-45-6789' USING PARAMETERS format='ssn');
ERROR 5861:  Error calling setup() in User Function VoltageSecureAccess at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/udx/supported/voltagesecure/VoltageBase.hpp:124], error code: 590, message: Error initializing: Unrecognized Voltage SecureData error encountered. Please contact Voltage SecureData support for help.
 
=> SELECT public.VoltageSecureAccess('123-45-6789' USING PARAMETERS format='ssn');
ERROR 5861:  Error calling setup() in User Function VoltageSecureAccess at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/udx/supported/voltagesecure/VoltageBase.hpp:124], error code: 574, message: Error initializing: Unrecognized Voltage SecureData error encountered. Please contact Voltage SecureData support for help.
 
$ curl -k --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
<html><body><h1>503 Service Unavailable</h1>
No server is available to handle this request.
</body></html>

Vertica uses Voltage SecureData Simple API to communicate with Voltage SecureData servers. This API supports C, Java, and C# languages. If you have access to the Simple API package, the API includes sample test programs that may be helpful to prepare the custom program to narrow down the causes. If your custom program also has the issue, it most likely is an issue in the environment.