Voltage SecureData integration

• 1: How Vertica and SecureData work together
• 2: Requirements for integrating with SecureData
• 3: Best practices for safe unicode FPE
• 4: Verifying the Vertica server's access to the SecureData CA certificate
• 5: Configuring access to SecureData
• 6: Encrypting, decrypting, and hashing data
• 7: Encapsulating encryption business logic with SQL macros
• 8: Granting users access to the Voltage SecureData integration functions
• 9: Automating encryption and decryption with access policies
• 10: Querying eFPE encrypted columns
• 11: Voltage SecureData integration function reference
• 11.1: VoltageSecureAccess
• 11.2: VoltageSecureConfigure
• 11.3: VoltageSecureConfigureGlobal
• 11.4: VoltageSecureProtect
• 11.5: VoltageSecureProtectAllKeys
• 11.6: VoltageSecureRefreshPolicy

1 - How Vertica and SecureData work together

Vertica provides functions to encrypt and decrypt data using SecureData.

Voltage SecureData supplies a number of interfaces for applications to use its encryption: web-based APIs, command-line tools, and SDKs for C, C#, and Java. Vertica has developed a connector that calls the SecureData API that lets you:

• Encrypt sensitive data as it is being loaded into Vertica using SecueData's FPE feature. You can ensure data is stored in Vertica in its encrypted state (referred to as "encrypted at rest"). Authorized users can decrypt the data as needed. Unauthorized users only see the encrypted values. Decryption for authorized users can be automated using views or access policies. The data is transparently decrypted for them.

• Encrypt semi-sensitive data that is stored unencrypted in Vertica so unauthorized users only see a masked version of the data. You an also automate this on-the-fly encryption using access policies.

The encryption method you choose depends in the data you are processing. For example, regulations or contracts may require you to encrypt specific pieces of data. In these cases, use SecureData to encrypt your data as it is loaded, so it is never stored in an unencrypted format within Vertica.

In other cases, you may have semi-sensitive data that you can choose between the two options. In these cases, choose the method that requires the least number encryptions or decryptions (and therefore of calls to SecureData). If most of the queries on the data need to be masked from users who should not see the unencrypted values, then encrypt the data at rest. Alternatively, if the most of the queries will be from authorized users, with only occasional queries where the data should be masked, then store the data in an unencrypted format and use on-the-fly encryption to mask the data.

2 - Requirements for integrating with SecureData

Before you can use SecureData with Vertica, you must verify the following:

• You are using version 6.0 or later of SecureData. The Vertica integration functions use APIs introduced in version 6.0 of SecureData.

• All of the nodes in your Vertica cluster are able to communicate with all of your SecureData appliance hosts. Any firewalls between your Vertica cluster and your SecureData appliance must allow connections on the ports that SecureData uses for communications. See "Ensuring Access to SecureData Services" in the Voltage SecureData Appliance Installation Guide (downloadable with the SecureData ISO) for a list of the ports reserved by SecureData.

• Your Vertica database is not using Federal information processing standard (FIPS) mode. FIPS is incompatible with the Voltage SecureData integration. When FIPS mode is active, the SecureData integration library is not installed by default. Do not manually install this library if you are using FIPS.

• You are following the best practices when using Safe Unicode FPE formats.

3 - Best practices for safe unicode FPE

Starting with Voltage SecureData Simple API 6.0, you can use Safe Unicode Format Preserving Encryption (FPE) formats to encrypt and decrypt Unicode strings.

When encrypting Unicode with Safe Unicode FPE formats, the plaintext passed into the VoltageSecureProtect function is first encrypted with an alphabet of all Unicode code points, and then encoded with the Base32K-encoding to produce encrypted text in Unicode Normalization Form C (NFC). The encrypted text will generally consist of 3-byte Chinese and Japanese characters, as they account for a significant portion of the NFC-stable alphabet. VoltageSecureAccess reverses this process.

Unlike regular FPE, which handles ASCII strings consisting of single bytes, Safe Unicode FPE handles strings consisting of variable-length code points. This variability introduces an important complication: Safe Unicode FPE formats like UNICODE_BASE32K are not length-preserving. The encryption algorithm guarantees that the encrypted text will be larger than the original, and failing to account for this expansion can lead to truncated or otherwise improperly stored encrypted text, making decryption impossible.

Additionally, Unicode allows semantically equivalent characters to be encoded in different ways, which means that unnormalized, semantically equivalent plaintexts can have different forms when encrypted, compromising referential integrity. To prevent this, you should always normalize your plaintext strings into their NFC forms before encryption. For more information on Unicode normalization, see the Unicode Consortium's Normalization FAQ.

The encrypted text is guaranteed to be larger than the plaintext in the following ways:

• 16/15ths longer in character-length (rounded up)

• up to 4-times larger (in bytes)

For a list of predefined formats for Safe Unicode FPE, consult your copy of the Voltage SecureData Simple API documentation.

Checklist for safe unicode FPE

Before using Safe Unicode FPE:

• Normalize your plaintext to its NFC form before encryption.

• For fixed-length data types: remove any padding from the plaintext string.

• To store the encrypted text, ensure that the columns storing the encrypted text can handle:

  • strings 16/15ths times longer than your longest plaintext string

  • strings up to 4-times larger (in bytes) than your largest plaintext string

4 - 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.

5 - Configuring access to SecureData

The Vertica integration functions require several pieces of information in order to connect to and authenticate with SecureData.

SecureData global configuration settings

The SecureData integration has one required setting and two optional settings that you set globally using the VoltageSecureConfigureGlobal function. This function saves the settings to a configuration file named /voltagesecure/conf.global stored in the Vertica Distributed File System (DFS). This file system is used by Vertica to store data that can be accessed by all nodes. You must use the function to create this file before you use any of the other SecureData integration functions. All users who have access to the SecureData functions are able to access the settings in this file.

The one required setting is the URL of the SecureData policy file. This file provides the SecureData integration library with details of how the SecureData Appliance is configured. The library also uses this URL to determine the address of the SecureData Appliance.

The two optional settings are:

• allow_short_fpe: When set to True, SecureData ignores the lower length limit for encoding FPE values. Usually, SecureData does not use FPE to encrypt data shorter than a lower limit (usually, 8 bits). See the SecureData Architecture Guide's section on Data Length Restrictions for more information.

• enable_file_cache: When set to True, Vertica caches the SecureData policy file and encryption keys to disk, rather than just to memory. Defaults to false.

This example sets the policy URL globally to https://voltage-pp-0000.example.com/policy/clientPolicy.xml and the network timeout to 200 seconds.

=> SELECT VoltageSecureConfigureGlobal(USING PARAMETERS
                                       policy_url='https://voltage-pp-0000.example.com/policy/clientPolicy.xml',
                                       NETWORK_TIMEOUT=200)
                                       OVER ();

                           policy_url                            | allow_short_fpe | enable_file_cache | network_timeout
-----------------------------------------------------------------+-----------------+-------------------+-----------------
 https://voltage-pp-0000.example.com/policy/clientPolicy.xml     |                 |                   | 200
(1 row)

Manually refresh the client policy across the nodes:

=> SELECT VoltageSecureRefreshPolicy() OVER ();
                                  PolicyRefresh
-------------------------------------------------------------------------------------
Successfully refreshed policy on node [v_sandbox_node0001]. Policy on other nodes
will be refreshed the next time a Voltage operation is run on them.
(1 row)

SecureData user configuration settings

The remaining SecureData settings define SecureData user information. They are usually specific to each user accessing the integration functions. However, you can have all Vertica users share the same SecureData user configuration. These settings are:

• Your authentication credentials for SecureData. The exact information you need depends on your SecureData Appliance's configuration. There are four potential settings you that you can use:

  • username: a username that you use to identify yourself. Your user name is either defined by LDAP (when using LDAP authentication) or by the SecureData appliance when using shared secret authentication.

  • identity: the SecureData identity to use. See the SecureData Administrator Guide for more information about identities in SecureData. The identity usually takes the form of an email address. When your SecureData Appliance uses LDAP authentication, your LDAP account must have access to this identity.

  • shared_secret: a password set in SecureData.

  • password: the LDAP password to use to authenticate with SecureData.

You can supply both a username and identity, depending on the SecureData Appliance's configuration. Your SecureData Appliance can be configured to supply the identity based on your SecureData username.

However, you can only use a shared_secret or a password. If you set both parameters, the Vertica SecureData functions exit with an error.

You have two options for setting these configuration values: setting them in user-defined session parameters, or saving the values in a configuration file stored in the Vertica distributed file system.

Setting SecureDate user session parameters

Use the ALTER SESSION statement to set parameters for the voltagesecurelib library. This library contains all of the SecureData functions. The following example demonstrates configuring the session to access SecureData using shared secret authentication.

=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib identity='alice@example.com';
ALTER SESSION
=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib username='alice';
ALTER SESSION
=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib shared_secret='my_shared_secret';
ALTER SESSION

Once set, these parameters only last for the duration of your session.

Saving parameters in configuration files

You can use the VoltageSecureConfigure function to save SecureData user parameters to a configuration file in the DFS. You must supply a file name for your configuration file. You can choose to store your SecureData parameters in a user-specific file by supplying just a file name, such as securedata.conf. Behind the scenes, your configuration file is stored in DFS under a directory named for your username. This directory prevents your configuration file from conflicting with other users' files. You do not use this directory name when accessing your configuration file.

You can also store the configuration parameters in an absolute file named /voltagesecure/conf. All users who have access to the SecureData integration functions can use this configuration file in their function calls. You can use this file if all of the users accessing the SecureData functions share the same SecureData user settings.

Values in the session parameters override values in configuration files.

You call VoltageSecureConfigure with the parameters you want to save to the configuration file. Values that you do not set in this function call are not set in the configuration file. You must supply those values to the other SecureData integration functions using session parameters.

If you choose to save either the password or shared secret to your configuration file, you do not directly pass them to the VoltageSecureConfigure function. Instead, you set the value in the appropriate session variable, and then set either VoltageSecureConfigure's store_password or store_shared_secret parameter to true. When either of these parameters are true, VoltageSecureConfigure reads the value from the session variable and saves it in the configuration file.

Example: creating a user-specific configuration file

The following example shows how you can create a user-specific configuration file. This example does not store information such as the password or shared secret, so you must still set session parameters for these values before you can call the other SecureData integration functions.

=> \x
Expanded display is on.
=> SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
                                 username='alice', identity='alice@example.com', store_password=false
                                ) OVER ();
-[ RECORD 1 ]-----+----------------------------------------------------------------
config_dfs_path   | voltage.conf
identity          | alice@example.com
username          | alice

6 - Encrypting, decrypting, and hashing data

Once you have set up authentication with the SecureData Appliance (SDA), call the VoltageSecureProtect and VoltageSecureAccess functions to encrypt/hash or decrypt your data, respectively. At a minimum, you pass these functions the value to be encrypted, hashed, or decrypted and value's FPE format as defined in the Voltage SDA. You can also capture these parameters in SQL Macros and access policies.

Connecting Vertica to the SecureData appliance

The following example demonstrates how to fetch the SDA client policy with Vertica and then how to set the session parameters for authentication to the SDA.

1. Add the SecureData CA certificate to Vertica.

2. Set the client policy with VoltageSecureConfigureGlobal.

   => SELECT VoltageSecureConfigureGlobal(USING PARAMETERS
       policy_url='https://voltage-pp-0000.example.com/policy/clientPolicy.xml')
       OVER ();
                              policy_url                            | allow_short_fpe | enable_file_cache
   -----------------------------------------------------------------+-----------------+-------------------
    https://voltage-pp-0000.example.com/policy/clientPolicy.xml     |                 |
   (1 row)
   

3. (Optional) Retrieve the latest version of the client policy with VoltageSecureRefreshPolicy.

   => SELECT VoltageSecureRefreshPolicy() OVER ();
                                     PolicyRefresh
   -------------------------------------------------------------------------------------
   Successfully refreshed policy on node [v_node0001]. Policy on other nodes
   will be refreshed the next time a Voltage operation is run on them.
   (1 row)
   

4. Set the authentication parameters to authenticate to the SDA as a Vertica user with ALTER SESSION or VoltageSecureConfigure. For details on this process, see Configuring access to SecureData.

   ALTER SESSION sets these parameters for the duration of your session and override parameters set in configuration files.

   
   => ALTER SESSION SET UDPARAMETER FOR voltagesecurelib identity='alice@example.com';
   ALTER SESSION
   => ALTER SESSION SET UDPARAMETER FOR voltagesecurelib username='alice';
   ALTER SESSION
   => ALTER SESSION SET UDPARAMETER FOR voltagesecurelib shared_secret='my_secret';
   ALTER SESSION
   

   VoltageSecureConfigure saves these parameters to the specified config_dfs_path. This configuration file can then be passed to other Voltage functions.

   => SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
      username='alice', identity='alice@example.com', store_password=false) OVER ();
   
    config_dfs_path | identity          | username
   -----------------+-------------------+----------
    voltage.conf    | alice@example.com | alice
   
   => SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS
       format='ssn',
       config_dfs_path='voltage.conf');
   
    VoltageSecureProtect
   ----------------------
    376-69-6789
   

Encrypting and decrypting data

VoltageSecureProtect takes a format-preserving encryption (FPE) format defined in your SDA and VARCHAR data and returns format-preserved ciphertext, which can then be decrypted with VoltageSecureAccess. That is, if the plaintext follows the form of a phone number (###-###-####), the resulting ciphertext will be in the same form. Formats can be customized in various ways, like masking certain values, leaving some part of the string unencrypted, etc.

One of the many predefined formats in the SDA is ssn. The ssn format encrypts all but the last four digits of the social security number, which can be useful for certain roles like customer support, which may need the last four digits of the SSN to authenticate individuals over the phone.

=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='ssn');
 VoltageSecureProtect
----------------------
 376-69-6789
(1 row)

=> SELECT VoltageSecureAccess('376-69-6789' USING PARAMETERS format='ssn');
 VoltageSecureAccess
---------------------
 123-45-6789
(1 row)

Another predefined format is auto, which can handle a wide variety of data, including SSNs. In contrast to ssn, auto is configured in the SDA to encrypt all characters in the plaintext.

=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='auto');
 VoltageSecureProtect
----------------------
 820-31-5110
 (1 row)

Note that you can only decrypt ciphertext with the same format that generated it. In this case, ciphertext created with ssn must be decrypted with ssn. Passing the format auto with a ciphertext generated with ssn, for instance, would simply return an incorrect plaintext.

Encrypting data during load

When handling sensitive data, you often want to encrypt it as you load it so the unencrypted values are never stored in your database.

The following example demonstrates loading data using COPY. Suppose you have a data filled with customer information with the following fields: id number, first name, last name, social security number, card verification number, and date of birth:

5345,Thane,Ross,559-32-0670,376765616314013,618,05-09-1996
5346,Talon,Wilkins,540-48-0784,4716511603424923,111,09-17-1941
5347,Daquan,Phelps,785-34-0092,342226134491834,294,05-08-1963
5348,Basia,Lopez,011-85-0705,4595818418314603,503,04-29-1940
5349,Kaseem,Hendrix,672-57-0309,4556 078 73 7944,693,03-11-1942
5350,Omar,Lott,825-45-0131,6462 0541 0799 6261,555,02-17-1956
5351,Nell,Cooke,637-50-0105,646 59756 30903 530,818,02-14-1995
5352,Illana,Middleton,831-47-0929,648 23640 86684 267,883,12-29-1949
5353,Garrett,Williamson,408-73-0207,5334 2702 1360 8370,869,11-06-1955
5354,Hanna,Ware,694-97-0394,543 38494 19219 254,586,08-08-1967

To encrypt the social security number and the credit card number columns, call VoltageSecureProtect to encrypt these columns in the COPY statement you use to load the data:

=> CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR,
                           ssn VARCHAR(11), cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE
=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
        cc_num_raw FILLER VARCHAR(25), cvv, dob,
        ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS format='ssn',
                                    config_dfs_path='voltage.conf'),
     cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS format='cc',
                                       config_dfs_path='voltage.conf'))
     FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
 Rows Loaded
-------------
         100
(1 row)

=> SELECT * FROM customers ORDER BY id ASC LIMIT 10;
  id  | first_name | last_name  |     ssn     |       cc_num        | cvv |    dob
------+------------+------------+-------------+---------------------+-----+------------
 5345 | Thane      | Ross       | 072-52-0670 | 405939553794013     | 618 | 1996-05-09
 5346 | Talon      | Wilkins    | 348-30-0784 | 5350908688294923    | 111 | 1941-09-17
 5347 | Daquan     | Phelps     | 983-53-0092 | 133383311411834     | 294 | 1963-05-08
 5348 | Basia      | Lopez      | 490-63-0705 | 7979155436134603    | 503 | 1940-04-29
 5349 | Kaseem     | Hendrix    | 268-74-0309 | 3212 314 45 7944    | 693 | 1942-03-11
 5350 | Omar       | Lott       | 872-03-0131 | 4914 1839 6801 6261 | 555 | 1956-02-17
 5351 | Nell       | Cooke      | 785-90-0105 | 332 34312 95233 530 | 818 | 1995-02-14
 5352 | Illana     | Middleton  | 947-60-0929 | 219 06376 36044 267 | 883 | 1949-12-29
 5353 | Garrett    | Williamson | 333-23-0207 | 1126 1022 5922 8370 | 869 | 1955-11-06
 5354 | Hanna      | Ware       | 661-57-0394 | 106 09915 59049 254 | 586 | 1967-08-08
(10 rows)

Encrypting Non-VARCHAR data

VoltageSecureProtect function only encrypts VARCHAR values, so if you need to encrypt other data types, such as DATE or INTEGER, you must cast these values to VARCHAR in your function call and then cast them back to DATE or INTEGER when storing them.

Building off the previous example, the following encrypts the date of birth (dob) column, which holds date type DATE.

=> CREATE TABLE customers2 (id INTEGER, first_name VARCHAR, last_name VARCHAR, ssn VARCHAR(11),
                            cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE
=> COPY customers2 (id, first_name, last_name, ssn, cc_num, cvv, dob_raw FILLER DATE,
                    dob AS VoltageSecureProtect(dob_raw::VARCHAR USING PARAMETERS
                                                format='birthday',
                                                config_dfs_path='voltage.conf')::DATE)
        FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
 Rows Loaded
-------------
         100
(1 row)

=> SELECT * FROM customers2 ORDER BY id ASC LIMIT 10;
  id  | first_name | last_name  |     ssn     |       cc_num        | cvv |    dob
------+------------+------------+-------------+---------------------+-----+------------
 5345 | Thane      | Ross       | 559-32-0670 | 376765616314013     | 618 | 1902-03-09
 5346 | Talon      | Wilkins    | 540-48-0784 | 4716511603424923    | 111 | 2023-07-22
 5347 | Daquan     | Phelps     | 785-34-0092 | 342226134491834     | 294 | 2091-01-18
 5348 | Basia      | Lopez      | 011-85-0705 | 4595818418314603    | 503 | 1921-08-17
 5349 | Kaseem     | Hendrix    | 672-57-0309 | 4556 078 73 7944    | 693 | 1962-08-23
 5350 | Omar       | Lott       | 825-45-0131 | 6462 0541 0799 6261 | 555 | 1930-01-12
 5351 | Nell       | Cooke      | 637-50-0105 | 646 59756 30903 530 | 818 | 2098-01-01
 5352 | Illana     | Middleton  | 831-47-0929 | 648 23640 86684 267 | 883 | 1956-09-07
 5353 | Garrett    | Williamson | 408-73-0207 | 5334 2702 1360 8370 | 869 | 2079-03-25
 5354 | Hanna      | Ware       | 694-97-0394 | 543 38494 19219 254 | 586 | 1903-07-16
(10 rows)

Note that you cannot use the auto format for DATE types; ciphertext generated by auto uses the full range of numbers and letters and only preserves the number of characters and any separators, so the resulting ciphertext cannot typically be cast to a date.

=> SELECT VoltageSecureProtect('07-16-1969' USING PARAMETERS format='auto',
                               config_dfs_path='/voltagesecure/conf');
 VoltageSecureProtect
----------------------
 45-86-8651
(1 row)

=> SELECT VoltageSecureProtect('07-16-1969' USING PARAMETERS format='auto',
                               config_dfs_path='/voltagesecure/conf')::DATE;
ERROR 2992:  Date/time field value out of range: "45-86-8651"
HINT:  Perhaps you need a different "datestyle" setting

Decrypting values in queries

To decrypt ciphertext stored in tables, call VoltageSecureAccess on the encrypted column in your query.

The following example queries a table and decrypts the SSN column:

=> SELECT id,
          first_name,
          last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf') AS ssn_plaintext,
          dob
      FROM customers
      WHERE dob < '1970-1-1'
      ORDER BY id ASC
      LIMIT 10;

  id  | first_name | last_name  |     ssn_plaintext     |    dob
------+------------+------------+-----------------------+------------
 5346 | Talon      | Wilkins    | 540-48-0784           | 1941-09-17
 5347 | Daquan     | Phelps     | 785-34-0092           | 1963-05-08
 5348 | Basia      | Lopez      | 011-85-0705           | 1940-04-29
 5349 | Kaseem     | Hendrix    | 672-57-0309           | 1942-03-11
 5350 | Omar       | Lott       | 825-45-0131           | 1956-02-17
 5352 | Illana     | Middleton  | 831-47-0929           | 1949-12-29
 5353 | Garrett    | Williamson | 408-73-0207           | 1955-11-06
 5354 | Hanna      | Ware       | 694-97-0394           | 1967-08-08
 5355 | Quinn      | Pruitt     | 818-91-0359           | 1965-11-14
 5356 | Clayton    | Santiago   | 102-56-0010           | 1958-02-02
(10 rows)

=> SELECT first_name,
          VoltageSecureAccess(first_name USING PARAMETERS format='auto',
                              config_dfs_path='/voltagesecure/conf')
                              AS decrypted_first_name
          FROM customers LIMIT 10;
 first_name | decrypted_first_name
------------+----------------------
 Omar       | Rftd
 Illana     | Clfkow
 Hanna      | Bodng
 Keith      | Hklnw
 Constance  | Cicgtmgtw
 Kirk       | Jwdv
 Eagan      | Hiksm
 Branden    | Ytqgngp
 Hope       | Tqzc
 Keane      | Pdcax
(10 rows)

Decrypting Non-VARCHAR columns

Like VoltageSecureProtect, VoltageSecureAccess can only handle VARCHAR values. When you decrypt ciphertext from a non-VARCHAR column, you must cast it to a VARCHAR before passing it to VoltageSecureAccess. If your query or client application depends on the original data type of the column, you must then cast the returned plaintext back to the column's original data type.

The following example decrypts the dob column by casting the column data from DATE to VARCHAR, passing it to VoltageSecureAccess, and then casting the plaintext back to DATE.

=> SELECT id, first_name, last_name,
          VoltageSecureAccess(dob::VARCHAR USING PARAMETERS format='birthday',
                              config_dfs_path='/voltagesecure/conf')::DATE AS dob
          FROM customers2 ORDER BY id LIMIT 10;
  id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 1996-05-09
 5346 | Talon      | Wilkins    | 1941-09-17
 5347 | Daquan     | Phelps     | 1963-05-08
 5348 | Basia      | Lopez      | 1940-04-29
 5349 | Kaseem     | Hendrix    | 1942-03-11
 5350 | Omar       | Lott       | 1956-02-17
 5351 | Nell       | Cooke      | 1995-02-14
 5352 | Illana     | Middleton  | 1949-12-29
 5353 | Garrett    | Williamson | 1955-11-06
 5354 | Hanna      | Ware       | 1967-08-08
(10 rows)

Masking decrypted values

If the ciphertext was created with a format that specifies a form of "masking," plaintexts returned by VoltageSecureAccess can be masked by using the mask=true parameter. If you don't specify this parameter, the full plaintext will be returned regardless of the format.

This format, as defined in the Voltage SDA, obscures all but the last two characters of the decrypted plaintext.

=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf');
 VoltageSecureAccess
----------------------
 1234567890

=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf',
                                  mask=true);
 VoltageSecureAccess
----------------------
 XXXXXXXX90

Tweaking ciphertext

Analogous to a salt, you can pass an additional "tweak" value to add to the key used by VoltageSecureProtect to create unique ciphertexts from the same plaintext. This "tweaked" ciphertext can then only be decrypted by passing the same format and "tweak" value to VoltageSecureAccess. Support for this feature is set in the SDA.

The following example uses the custom format ssn-tweak and the value tweakvalue123 to tweak the ciphertext.

=> SELECT VoltageSecureProtect('681-09-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 721-21-2913

=> SELECT VoltageSecureAccess('721-21-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 681-09-2913

Tweaking an entire column

A common use case is to use the values from one column to tweak the ciphertext in another.

The following example loads the data in a similar manner to the previous section, but uses the values from the last_name column to tweak the dates of birth of each customer.

=> CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR, ssn VARCHAR(11),
    cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE

=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
           cc_num_raw FILLER VARCHAR(25), cvv,
           dob_raw FILLER DATE, ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS format='ssn',
                                                            config_dfs_path='voltage.conf'),
           /* cast the dob_raw to VARCHAR and tweak with last_name, then cast back to DATE */
           dob AS VoltageSecureProtect(dob_raw::VARCHAR , last_name USING PARAMETERS format='date-tweak',
                                       config_dfs_path='voltage.conf')::DATE,
           cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS format='cc', config_dfs_path='voltage.conf'))
           FROM 'customers.csv' DELIMITER ',';

 Rows Loaded
-------------
         100
(1 row)

=> SELECT id, first_name, last_name, dob FROM customers ORDER BY id;
  id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 2086-03-07
 5346 | Talon      | Wilkins    | 1936-06-09
 5347 | Daquan     | Phelps     | 1953-12-01
 5348 | Basia      | Lopez      | 1979-11-30
 5349 | Kaseem     | Hendrix    | 2090-01-07
 5350 | Omar       | Lott       | 1984-07-27
 5351 | Nell       | Cooke      | 2053-03-20
 5352 | Illana     | Middleton  | 1988-03-06
 5353 | Garrett    | Williamson | 1927-03-03
 5354 | Hanna      | Ware       | 1998-08-28
 5355 | James      | May        | 1941-10-24
(11 rows)

To decrypt the column, include the tweak column in the call to VoltageSecureAccess. The tweak column must contain the same data it did when it was used to encrypt the column.

=> SELECT id, first_name, last_name,
   VoltageSecureAccess(dob::VARCHAR , last_name USING PARAMETERS format='date-tweak',
   config_dfs_path='voltage.conf')::DATE AS dob
   FROM customers ORDER BY id;
 id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 1996-05-09
 5346 | Talon      | Wilkins    | 1941-09-17
 5347 | Daquan     | Phelps     | 1963-05-08
 5348 | Basia      | Lopez      | 1940-04-29
 5349 | Kaseem     | Hendrix    | 1942-03-11
 5350 | Omar       | Lott       | 1956-02-17
 5351 | Nell       | Cooke      | 1995-02-14
 5352 | Illana     | Middleton  | 1949-12-29
 5353 | Garrett    | Williamson | 1955-11-06
 5354 | Hanna      | Ware       | 1967-08-08
 5355 | James      | May        | 1967-08-08
(11 rows)

Tweaking columns with stored procedures

Since tweak values become part of the encryption key, a tweak column's contents must be the same at the time of both encryption and decryption. To avoid losing access to the plaintext, when modifying a tweak column, you must:

1. decrypt the ciphertext

2. modify the tweak column

3. re-encrypt the plaintext data with the contents of the modified tweak column

You can automate this process by using stored procedures. For example, a stored procedure to update a customer's date of birth when tweaked with their last name might be:

=> CREATE PROCEDURE update_dob (employee_id INT, tweak_value VARCHAR) AS $$
  BEGIN
    PERFORM UPDATE customers
      SET dob = (
        WITH dob_ AS (SELECT VoltageSecureAccess(dob::VARCHAR , last_name
          USING PARAMETERS format='date-tweak', config_dfs_path='voltage.conf')::DATE AS dob
            FROM customers
            WHERE id = employee_id)
        SELECT VoltageSecureProtect(dob::VARCHAR, tweak_value
          USING PARAMETERS format='date-tweak', config_dfs_path='voltage.conf')::DATE
            FROM dob_)
      WHERE ID = employee_id;

    PERFORM UPDATE customers SET last_name = tweak_value WHERE ID = employee_id;

    PERFORM COMMIT;
  END;
$$;

You can then call the procedure to update the encrypted value and the tweak value:

=> CALL update_dob(5349, 'Henderson');

Hashing data

VoltageSecureProtect can also hash your data while preserving its format, which can be useful in contexts where you need to anonymize data to comply with privacy standards while retaining accessibility for client code.

You should keep in mind the following:

• hashing is a one way operation, so the original plaintext is unrecoverable

• the nature of format-preserving hash functions and plaintexts with low entropy (names, dates, SSNs, etc.) mean that collisions are much more likely than with standard hashing functions

The following example encrypts an SSN using a customFPH format, ssnHash.

=> SELECT VoltageSecureProtect('681-09-2913' USING PARAMETERS
                                  format='ssnHash',
                                  config_dfs_path='voltage.conf');
 VoltageSecureProtect
----------------------
 841-68-2913

7 - Encapsulating encryption business logic with SQL macros

The VoltageSecureProtect and VoltageSecureAccess functions are fairly low-level functions and require a lot of preparation to use correctly. This includes, but is not limited to, information on the data type you're decrypting/encrypting, which Voltage format to use, casting the input to VARCHAR and then back to your desired data type, and identity management. Writing queries with this many moving parts can be tedious.

To streamline this process, you can encapsulate this information in SQL macros and decide its behavior in a more dynamic way with case expressions. This approach offers several benefits:

• You can associate a Voltage format with the data's purpose and Vertica will automatically encrypt and decrypt the value accordingly.

• You can automate the required type casting to and from your desired data type to VARCHAR.

• The THROW_ERROR function can provide a form of input validation for your macros.

• You can specify an identity for a given case expression during encryption which restricts decryption privileges to the same identity.

To view your macros, query the USER_FUNCTIONS system table.

Encryption and decryption macro templates

In the following macros, the data_purpose parameter describes what the data is used for (e.g. temperature) and the value parameter indicates value being encrypted and its data_type (e.g. INT). The data_type must be the same across the entire macro.

The data_purpose parameter is also associated with both the voltage_format and voltage_identity parameters, which control how the data is encrypted/decrypted and which identity has access to it, respectively.

If you pass into the function a data_purpose without a corresponding case expression, the THROW_ERROR function will return a user-specified error, which itself must be casted to the function's return data_type.

Note that the standard casting operator :: will terminate the query entirely if it encounters types incompatible with the specified cast, which can be problematic when casting larger datasets. To prevent the query from terminating, you can use the ::! operator to first attempt the specified cast, but to return a NULL value for incompatible types. For more information, see Cast failures.

Encryption macro with VoltageSecureProtect

=> CREATE FUNCTION encryptDataType(data_purpose VARCHAR, value data_type) RETURN data_type
    AS BEGIN
    RETURN(CASE
        WHEN (data_purpose='data_purpose')
            then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                format='voltage_format',
                identity='voltage_identity')::data_type
        ELSE
            THROW_ERROR('no matching data_purpose')::data_type
        END);
    END;

Decryption macro with VoltageSecureAccess

=> CREATE FUNCTION decryptDataType(data_purpose VARCHAR, value data_type) RETURN data_type
    AS BEGIN
    RETURN(CASE
        WHEN (data_purpose='data_purpose')
            then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                format='voltage_format',
                identity='voltage_identity')::data_type
        ELSE
            THROW_ERROR('no matching data_purpose')::data_type
        END);
    END;

Example

The following example shows how to manage encryption and decryption for a column with dates of birth. In this case, you might want to define a separate identity for encrypting customer and employee data.

=> SELECT * FROM customer_dob;
   dates
-----------
 1955-11-04
 1991-12-01
 1977-07-07
(3 rows)

For encryption, define the following macro.

=> CREATE FUNCTION encryptDOB(data_purpose VARCHAR, value DATE) RETURN DATE
    AS BEGIN
    RETURN
        (CASE
            --The data_purpose parameter controls which identity is used during encryption;
            WHEN (data_purpose='customer')
            --Format, identities, and casting from DATE to VARCHAR and back to DATE are all encapsulated in the case expression;
                then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='customer_data@example.com')::DATE
            WHEN (data_purpose='employee')
                then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='employee_data@example.com')::DATE
            ELSE
                THROW_ERROR('Unsupported data_purpose -- You must pass ''customer'' when
                encrypting customer data or ''employee'' when encrypting employee data')::DATE
                --Because the return type of this macro is DATE, THROW_ERROR must also be casted to type DATE;
        END);
    END;

To encrypt the dates column in the customer_dob table:

=> SELECT encryptDOB('customer', dates) FROM customer_dob;
 encryptDOB
------------
 2048-08-09
 1917-03-05
 2022-01-07

To encrypt a value individually:

=> SELECT encryptDOB('customer', '1955-11-04');
 encryptDOB
------------
 2048-08-09

You can define a matching macro for decryption. Since encrypted data can only be decrypted with matching identities, these case expressions use the same data_purpose and identities for decryption.

For decryption, define the following macro:

=> CREATE FUNCTION decryptDOB(data_purpose VARCHAR, value DATE) RETURN DATE
    AS BEGIN
    RETURN
        (CASE
            --The case expressions and parameters must match the ones for encryption;
            WHEN (data_purpose='customer')
                then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='customer_data@example.com')::DATE
            WHEN (data_purpose='employee')
                then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='employee_data@example.com')::DATE
            ELSE
                THROW_ERROR('Unsupported data_purpose -- You must pass ''customer'' when
                decrypting customer data or ''employee'' when decrypting employee data')::DATE
        END);
    END;

To decrypt an encrypted column in a nested call:

=> SELECT decryptDOB('customer', encryptDOB('customer', dates)) FROM customer_dob;
 decryptDOB
------------
 1955-11-04
 1991-12-01
 1977-07-07
(3 rows)

To decrypt a value individually:

=> SELECT decryptDOB('customer', '2048-08-09');
 decryptDOB
------------
 1955-11-04

To drop these macros:

=> DROP FUNCTION encryptDOB(VARCHAR, DATE);
DROP FUNCTION
=> DROP FUNCTION decryptDOB(VARCHAR, DATE);
DROP FUNCTION

8 - Granting users access to the Voltage SecureData integration functions

By default, Vertica users do not have access to the Voltage SecureData Integration Functions. Users attempting to call the integration functions without the correct privileges will receive the following error:

=> SELECT id,
          first_name,
          last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf') AS ssn,
          dob
          FROM customers
          WHERE dob < '1970-1-1'
          ORDER BY id ASC
          LIMIT 10;
ERROR 6482:  Failed to parse Access Policies for table "customers" [Function
public.VoltageSecureProtect(varchar) does not exist, or permission is denied for
public.VoltageSecureProtect(varchar)]

Users must have EXECUTE access to the integration functions in order to use them. These functions are part of the PUBLIC schema. The functions in the Voltage SecureData integration library are:

The following example demonstrates granting the user named Alice access to the VoltageSecureAccess function to be able to decrypt data.

=> \c vmart dbadmin
You are now connected to database "vmart" as user "dbadmin".
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureProtect(VARCHAR) TO alice;
GRANT PRIVILEGE
=> \c vmart alice
You are now connected to database "vmart" as user "alice".
=> SELECT id, first_name, last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf')
                              AS ssn,
          dob
          FROM customers
          WHERE dob < '1970-1-1'
          ORDER BY id ASC
          LIMIT 10;
  id  | first_name | last_name |     ssn     |    dob
------+------------+-----------+-------------+------------
 5345 | Thane      | Ross      | 559-32-0670 | 1902-03-09
 5348 | Basia      | Lopez     | 011-85-0705 | 1921-08-17
 5349 | Kaseem     | Hendrix   | 672-57-0309 | 1962-08-23
 5350 | Omar       | Lott      | 825-45-0131 | 1930-01-12
 5352 | Illana     | Middleton | 831-47-0929 | 1956-09-07
 5354 | Hanna      | Ware      | 694-97-0394 | 1903-07-16
 5358 | Mallory    | Vaughn    | 870-53-0272 | 1961-03-09
 5363 | Kirk       | Robinson  | 155-08-0085 | 1964-06-28
 5366 | Branden    | Coffey    | 709-38-0423 | 1923-06-11
 5367 | Raven      | Keith     | 250-31-0269 | 1918-07-31
(10 rows)

See GRANT (user defined extension) for a detailed explanation of granting access to UDxs to users.

Creating roles for SecureData users

Instead of granting access to each function to individual users, you can create roles that you grant access to the functions. Then you can grant users who need to access the SecureData functions access to these roles.

Consider creating at least two roles: one for access to the VoltageSecureConfigure function and another for access to the other functions. In most cases, not all users need to access VoltageSecureConfigure, especially if you choose to create a single, global configuration file. See Configuring access to SecureData for more information about using VoltageSecureConfigure.

The following example:

• Creates two roles: secure_data_users, who are granted access to the protect and access functions, and secure_data_admins, who are granted access to the SecureDataConfigure function.

• Grants the secure_data_user role to a user named Alice

• Sets the new role as her default role.

• Switches to Alice

• Calls several of the SecureData functions.

=> \c vmart dbadmin
You are now connected to database "vmart" as user "dbadmin".
=> CREATE ROLE secure_data_users;
CREATE ROLE
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureAccess(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureProtect(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> GRANT EXECUTE ON TRANSFORM FUNCTION
         public.VoltageSecureProtectAllKeys(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> CREATE ROLE secure_data_admins;
CREATE ROLE
=> GRANT EXECUTE ON TRANSFORM FUNCTION public.VoltageSecureConfigure()
   TO secure_data_admins;
GRANT PRIVILEGE
=> GRANT secure_data_users TO ALICE;
GRANT ROLE
=> ALTER USER alice DEFAULT ROLE secure_data_users;
ALTER USER
=> \c vmart alice
You are now connected to database "vmart" as user "alice".
=> SET ROLE secure_data_users;
SET
=> SELECT VoltageSecureProtect('123-45-6789'
                               USING PARAMETERS format='ssn',
                               config_dfs_path='/voltagesecure/conf');
 VoltageSecureProtect
----------------------
 376-69-6789
(1 row)
=> SELECT VoltageSecureAccess('376-69-6789'
                              USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf');
 VoltageSecureAccess
---------------------
 123-45-6789
(1 row)

=> SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
                                 username='alice', identity='alice@example.com',
                                ) OVER ();
ERROR 3457:  Function VoltageSecureConfigure() does not exist, or permission
is denied for VoltageSecureConfigure()
HINT:  No function matches the given name and argument types. You may need to
add explicit type casts

Note that Alice can use the global configuration file, despite not being able to access the VoltageSecureConfigure function.

9 - Automating encryption and decryption with access policies

You can automate encryption and decryption by creating access policies. These policies let you show users with specific roles unencrypted values while users without those roles see encrypted ones. Alternatively, you can create an access policy that masks unencrypted data stored in the database when queried by users who lack a specific role.

Users do not explicitly call the SecureData integration functions when you create access policies to automatically encrypt and decrypt data. However, they still must have access to the SecureData functions and have any necessary session variables set. See Configuring access to SecureData and Granting users access to the Voltage SecureData integration functions for the necessary configuration for the access policies to work. If a user who does not have access to the SecureData integration functions queries a table with an access policy that calls these functions, the query generates an error.

Automatically decrypting table columns for privileged users

To decrypt an encrypted column automatically, create an access policy for the encrypted column that calls VoltageSecureAccess if the user has a role enabled. If the user does not have the role enabled, just return the encrypted value.

The following example:

1. Creates a role named see_ssn and grants it to the user named Alice, as well as granting Alice access to the customers table.

2. Creates an access policy on the customers table's ssn column that decrypts the column’s value if the user has the see_ssn role enabled.

3. Switches to the user named Alice.

4. Queries the customers table, without the see_ssn role enabled.

5. Enables the see_ssn role and queries the table again.

=> CREATE ROLE see_ssn;
CREATE ROLE

=> GRANT see_ssn TO alice;
GRANT ROLE

=> GRANT ALL ON TABLE customers TO alice;
GRANT PRIVILEGE

=> CREATE ACCESS POLICY ON customers FOR COLUMN ssn
     CASE
         WHEN enabled_role('see_ssn') THEN VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                                                               config_dfs_path='/voltagesecure/conf')
         ELSE ssn
     END ENABLE;
CREATE ACCESS POLICY

=> \c vmart alice;
Password:
You are now connected to database "vmart" as user "alice".

=> SELECT first_name, last_name, ssn FROM customers WHERE id < 5355 ORDER BY id ASC;
 first_name | last_name |     ssn
------------+-----------+-------------
 Gil        | Reeves    | 997-92-0657
 Robert     | Moran     | 715-02-0455
 Hall       | Rice      | 938-83-0659
 Micah      | Trevino   | 495-57-0860
(4 rows)

=> SET ROLE see_ssn;
SET
=> SELECT first_name, last_name, ssn FROM customers WHERE id < 5355 ORDER BY id ASC;
 first_name | last_name |     ssn
------------+-----------+-------------
 Gil        | Reeves    | 232-28-0657
 Robert     | Moran     | 725-79-0455
 Hall       | Rice      | 285-90-0659
 Micah      | Trevino   | 853-60-0860
(4 rows)

In the above example, you could combine the see_ssn role with a role that grants users access to the SecureData integration functions. Users who do not have the see_ssn role do not need to have access to the SecureData functions in order to see the encrypted values.

Automatically encrypting columns for unprivileged users

You can also create access policies that encrypt values. Use this technique to prevent some users from seeing values in columns that aren't sensitive enough to require encryption in the database, but should not be seen by all users. This technique is the opposite of the encryption-on-the-fly: users that have a specific role enabled get the value from the table; if they do not have the role, the access policy encrypts the value.

The following example:

1. Creates a role named see_dob and assigns it to the user Alice.

2. Creates an access policy on the dob column of the customers table. It returns the value in the column if the user has the see_dob role active and encrypts the value if not.

3. Switches to the user named Alice.

4. Queries the customers table including the dob column.

5. Sets the see_dob role and queries customers again.

=> CREATE ROLE see_dob;
CREATE ROLE

=> GRANT see_dob TO alice;
GRANT ROLE

=> CREATE ACCESS POLICY ON customers FOR COLUMN dob
     CASE
         WHEN enabled_role('see_dob') THEN dob
         ELSE VoltageSecureProtect(dob::varchar USING PARAMETERS format='birthday',
                                   config_dfs_path='/voltagesecure/conf')::date
     END ENABLE;
CREATE ACCESS POLICY

=> \c vmart alice
Password:
You are now connected to database "vmart" as user "alice".

=> SELECT first_name, last_name, dob FROM customers ORDER BY id ASC LIMIT 10;
 first_name | last_name  |    dob
------------+------------+------------
 Gil        | Reeves     | 2048-08-09
 Robert     | Moran      | 1917-03-05
 Hall       | Rice       | 2022-01-07
 Micah      | Trevino    | 2018-06-01
 Kuame      | Stephenson | 2053-02-13
 Hedda      | Cooper     | 2002-03-12
 MacKenzie  | Burks      | 2061-10-30
 Anne       | Marquez    | 2078-08-02
 Dominic    | Avery      | 1940-08-10
 Alfreda    | Mcdaniel   | 1904-04-27
(10 rows)

=> SET ROLE see_dob;
SET

=> SELECT first_name, last_name, dob FROM customers ORDER BY id ASC LIMIT 10;
 first_name | last_name  |    dob
------------+------------+------------
 Gil        | Reeves     | 1955-11-04
 Robert     | Moran      | 1991-12-01
 Hall       | Rice       | 1977-07-07
 Micah      | Trevino    | 1980-12-05
 Kuame      | Stephenson | 1979-09-12
 Hedda      | Cooper     | 1987-05-02
 MacKenzie  | Burks      | 1982-11-07
 Anne       | Marquez    | 1949-07-09
 Dominic    | Avery      | 1976-12-02
 Alfreda    | Mcdaniel   | 1975-02-08
(10 rows)

In the previous example, users who do not have the see_dob role enabled must have access to the SecureData functions in order to see the masked values. If they do not have access to the SecureData functions, querying the customers table results in an error message. The following example creates a new user named Bob and grants him access to the customers table, without any access to the SecureData functions.

=> CREATE USER bob;
CREATE USER
=> GRANT ALL ON TABLE customers TO bob;
GRANT PRIVILEGE
=> \c vmart bob
You are now connected to database "vmart" as user "bob".
=> SELECT * FROM customers LIMIT 10;
ERROR 6482:  Failed to parse Access Policies for table "customers"
[Function public.VoltageSecureProtect(varchar) does not exist, or permission
is denied for public.VoltageSecureProtect(varchar)]

10 - Querying eFPE encrypted columns

You can use a feature in Voltage SecureData called Embedded Format Preserving Encryption to encrypt your data. You choose to use eFPE when you define the format in your SecureData Appliance. This format is slightly different than the standard FPE format. It uses key rotation, and embeds identification information in the encrypted value. Due to these factors, calls to VoltageSecureProtect using an eFPE format may not result in the same encrypted value, depending on key rotation schedules and the identity of the caller. This feature makes querying columns encrypted using an eFPE format more challenging.

When you want to search a standard FPE encrypted column for a value, you can just encrypt the plain text with VoltageSecureProtect and use the output in your query. For example, suppose you want to search for the customers table for an entry with the social security number 559-32-0670. Then you could use the following query:

=> SELECT id, first_name, last_name FROM customers
       WHERE ssn = VoltageSecureProtect('559-32-0670' USING PARAMETERS
                                        format='ssn',
                                        config_dfs_path='voltage.conf');

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross
(1 row)

Querying a column that uses eFPE format encryption is not as simple, as you do not know which embedded key was used to encrypt the data. You could just use the VoltageSecureAccess function to decrypt the entire contents of the table column and search the result for the value you need. However, this is inefficient, as the SecureData function has to be called for every row of data in the table.

A better solution is to use the VoltageSecureProtectAllKeys function. This function is similar to VoltageSecureProtect. However, instead of returning a single encrypted value, it returns a table containing the value encrypted with each of the keys defined for the eFPE format.

=> SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                      format='cc_num',
                                      config_dfs_path='/voltagesecure/conf')
          OVER ();

      data       |    protected
-----------------+-----------------
 376765616314013 | XMVMRU9RJVU4013
 376765616314013 | X5FD4KO1UEE4013
 376765616314013 | M7ZXTIQVCPB4013
 376765616314013 | UBOSC9K3EXZ4013
 376765616314013 | ZJ1C50C9L9R4013
(5 rows)

In the previous example, the cc_num column is encrypted using an eFPE format. The output from VoltageSecureProtectAllKeys shows that this eFPE format has 5 keys defined. The content of the protected column contains the same value encrypted with each of the keys.

To use this function in a query for a value in an eFPE column, use a JOIN on the table you are searching and the result table from VoltageSecureProtectAllKeys. The following example demonstrates querying the customers table to find all rows that have a cc_num value that matches the unencrypted credit card value 376765616314013.

=> SELECT id, first_name, last_name FROM customers3 u
          JOIN (SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                                   format='cc_num',
                                                   config_dfs_path='/voltagesecure/conf')
          OVER ()) pak
          ON u.cc_num = pak.protected;

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross

(1 row)

11 - Voltage SecureData integration function reference

The functions in this section are part of the Vertica voltagesecure library for integrating with Voltage SecureData. These functions are automatically installed when you install or upgrade Vertica. However, you must re-install the voltagesecure library to distribute the CA certificate to all nodes in the Vertica cluster. See Verifying the Vertica server's access to the SecureData CA certificate for instructions.

11.1 - VoltageSecureAccess

Calls SecureData to decrypt ciphertexts encrypted with VoltageSecureProtect.

Syntax

VoltageSecureAccess('ciphertext' [, 'tweak'] USING PARAMETERS
                    format='format_name'
                    [, mask=is_masked]
                    [, config_dfs_path='config_file']
                    [, identity=sd_identity]);

Parameters

Examples

The following example decrypts a Social Security Number (SSN) originally encrypted with a predefined format.

=> SELECT VoltageSecureAccess('376-69-6789' USING PARAMETERS format='ssn');

 VoltageSecureAccess
---------------------
 123-45-6789
(1 row)

This example demonstrates decrypting an encrypted column within a query.

=> SELECT id,
          first_name,
          last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf') AS ssn,
          dob
      FROM customers
      WHERE dob < '1970-1-1'
      ORDER BY id ASC
      LIMIT 10;

  id  | first_name | last_name  |     ssn     |    dob
------+------------+------------+-------------+------------
 5346 | Talon      | Wilkins    | 540-48-0784 | 1941-09-17
 5347 | Daquan     | Phelps     | 785-34-0092 | 1963-05-08
 5348 | Basia      | Lopez      | 011-85-0705 | 1940-04-29
 5349 | Kaseem     | Hendrix    | 672-57-0309 | 1942-03-11
 5350 | Omar       | Lott       | 825-45-0131 | 1956-02-17
 5352 | Illana     | Middleton  | 831-47-0929 | 1949-12-29
 5353 | Garrett    | Williamson | 408-73-0207 | 1955-11-06
 5354 | Hanna      | Ware       | 694-97-0394 | 1967-08-08
 5355 | Quinn      | Pruitt     | 818-91-0359 | 1965-11-14
 5356 | Clayton    | Santiago   | 102-56-0010 | 1958-02-02
(10 rows)

The following example decrypts Unicode using a predefined format. For a full list of predefined formats, consult the Voltage SecureData documentation.

=> SELECT VoltageSecureAccess('607-Òdìçç-ぶてぴねら' using parameters format='PREDEFINED::JU_AUTO_TYPE');

 VoltageSecureAccess
----------------------
 123-Hello-こんにちは

Decrypt a SSN ciphertext with the original FPE format and tweak value:

=> SELECT VoltageSecureAccess('721-21-2913', 'tweakvalue123' USING PARAMETERS
                                  format='ssn-tweak',
                                  config_dfs_path='voltage.conf');
 VoltageSecureProtect
----------------------
 681-09-2913

Decrypt a ciphertext that was encrypted with a masking format. This format obscures all but the last two characters of the decrypted plaintext.

=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf');
 VoltageSecureAccess
----------------------
 1234567890

=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf',
                                  mask=true);
 VoltageSecureAccess
----------------------
 XXXXXXXX90

See also

• VoltageSecureConfigure
• VoltageSecureProtect
• VoltageSecureProtectAllKeys
• Encrypting, decrypting, and hashing data
• Best practices for safe unicode FPE

11.2 - VoltageSecureConfigure

Saves SecureData user access configuration parameters to a file in the Vertica Distributed File System (DFS). You then pass the file's name to the other SecureData integration functions. This function can store the configuration file in the user's own DFS directory or in a globally-accessible file named /voltagesecure/conf.

Syntax

VoltageSecureConfigure(USING PARAMETERS config_dfs_path='filename'
                              [, identity=sd_identity]
                              [, store_password=Boolean]
                              [, store_shared_secret=Boolean]
                              [, username=sd_user]
                  ) OVER ();

Paremeters

Notes

• Any SecureData session variables that are set override values from the configuration file. See Configuring access to SecureData for more information.

• The SecureData integration only supports one configuration for the SecureData Appliance at a time.

• Under normal circumstances, users are not able to directly read data from files stored in DFS. However, all users who have access to UDx functions that read from the DFS could access these files from within Vertica.

  In addition, these files are stored as plain text in every node's file system. Anyone with the proper file system access on the nodes can read the file's contents.

  You should take both of these facts into consideration when deciding whether to store sensitive information such as passwords or shared secrets in either the shared or per-user configuration files.

Example

The following example demonstrates saving configuration information to a configuration file named voltage.conf in the user's own Vertica DFS directory.

=> \x
Expanded display is on.
=> SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
                                 username='alice', identity='alice@example.com', store_password=false
                                ) OVER ();
-[ RECORD 1 ]-----+----------------------------------------------------------------
config_dfs_path   | voltage.conf
identity          | alice@example.com
username          | alice

11.3 - VoltageSecureConfigureGlobal

Saves global SecureData access configuration parameters for all users to a file in the Vertica Distributed File System (DFS). This function stores the configuration file file named /voltagesecure/conf.global in the distributed file system (DFS). You must use this function to configure at least the SecureData policy URL before you can use any of the other Voltage SecureData integration functions.

To refresh the client policy, see VoltageSecureRefreshPolicy.

Syntax

VoltageSecureConfigureGlobal(USING PARAMETERS policy_url=url
                              [, allow_short_fpe=Boolean]
                              [, allow_file_cache=Boolean]
                              [, network_timeout=Integer]
                            ) OVER ();

Parameters

Example

To set the policy URL to https://voltage-pp-0000.example.com/policy/clientPolicy.xml and set the network timeout to 200 seconds:

=> SELECT VoltageSecureConfigureGlobal(USING PARAMETERS
                                       policy_url='https://voltage-pp-0000.example.com/policy/clientPolicy.xml',
                                       NETWORK_TIMEOUT=200)
                                       OVER ();

                           policy_url                            | allow_short_fpe | enable_file_cache | network_timeout
-----------------------------------------------------------------+-----------------+-------------------+-----------------
 https://voltage-pp-0000.example.com/policy/clientPolicy.xml     |                 |                   | 200
(1 row)

To view the current policy:

=> SELECT VoltageSecureConfigureGlobal() OVER();
                           policy_url                            | allow_short_fpe | enable_file_cache | network_timeout
-----------------------------------------------------------------+-----------------+-------------------+-----------------
 https://voltage-pp-0000.example.com/policy/clientPolicy.xml     |                 |                   | 200
(1 row)

Manually refresh the client policy across the nodes:

=> SELECT VoltageSecureRefreshPolicy() OVER ();
                                  PolicyRefresh
-------------------------------------------------------------------------------------
Successfully refreshed policy on node [v_sandbox_node0001]. Policy on other nodes
will be refreshed the next time a Voltage operation is run on them.
(1 row)

11.4 - VoltageSecureProtect

Calls SecureData to encrypt or hash a value while preserving the structure of the original plaintext.

Syntax

VoltageSecureProtect('plaintext' [, 'tweak'] USING PARAMETERS
                    format='format_name'
                    [, config_dfs_path='config_file']
                    [, identity=sd_identity]);

Parameters

Examples

Encrypt a social security number (SSN) value using both the ssn and auto FPE formats (this example assumes that all of the necessary SecureData authentication information has been set in session variables):

=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='ssn');
 VoltageSecureProtect
----------------------
 376-69-6789
(1 row)

=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='auto');
 VoltageSecureProtect
----------------------
 820-31-5110
(1 row)

Encrypt two table columns in a COPY statement, authenticating to the SecureData Appliance with the user's private configuration file saved in DFS:

=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
                   cc_num_raw FILLER VARCHAR(25), cvv, dob,
                   ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS
                                               format='ssn',
                                               config_dfs_path='voltage.conf'),
                   cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS
                                                  format='cc',
                                                  config_dfs_path='voltage.conf'))
        FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
 Rows Loaded
-------------
         100
(1 row)

Query for a particular value in an encrypted column:

=> SELECT id, first_name, last_name FROM customers
       WHERE ssn = VoltageSecureProtect('559-32-0670' USING PARAMETERS
                                        format='ssn',
                                        config_dfs_path='voltage.conf');

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross
(1 row)

Encrypting NULL values returns NULL:

=> CREATE TABLE nulltable(n VARCHAR (20));
=> INSERT INTO nulltable VALUES (NULL);

=> SELECT VoltageSecureProtect(n USING PARAMETERS format='auto') FROM nulltable;
VoltageSecureProtect
---------------------

(1 row)

Encrypt a Unicode string using a predefined format. For a full list of predefined formats, consult the Voltage SecureData documentation.

=> SELECT VoltageSecureProtect('123-Hello-こんにちは' USING PARAMETERS format='PREDEFINED::JU_AUTO_TYPE');
VoltageSecureProtect
----------------------
607-Òdìçç-ぶてぴねら

Encrypt a SSN with a tweak value:

=> SELECT VoltageSecureProtect('681-09-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 721-21-2913

=> SELECT VoltageSecureAccess('721-21-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 681-09-2913

Hash a SSN with a FPH format and a tweak value:

=> SELECT VoltageSecureProtect('681-09-2913', 'tweakvalue123' USING PARAMETERS
                                  format='ssnHash',
                                  config_dfs_path='voltage.conf');
VoltageSecureProtect
----------------------
841-68-2913

See also

• VoltageSecureAccess
• VoltageSecureProtectAllKeys
• Encrypting, decrypting, and hashing data
• Best practices for safe unicode FPE

11.5 - VoltageSecureProtectAllKeys

This function helps you locate values in a column encrypted using an Embedded Format Preserving Encryption (eFPE) format. These formats use key rotation, so the encrypted value you get back for a piece of plain text changes over time. You pass this function an unencrypted value. It returns a table consisting of two columns: the unencrypted value and the value encrypted with each of the keys defined for the eFPE. The number of rows in the table are determined by the number of keys the eFPE format contains. Usually, you use the output of this function in a join to locate a matching encrypted value in a table.

Syntax

VoltageSecureProtectAllKeys(value USING PARAMETERS format='eFPE_format'
                            [, config_dfs_path=config_file]
                            [, identity=sd_identity] )

Parameters

Examples

The following example demonstrates a simple call to VoltageSecureProtectAllKeys.

=> SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                      format='cc_num',
                                      config_dfs_path='/voltagesecure/conf')
          OVER ();

      data       |    protected
-----------------+-----------------
 376765616314013 | XMVMRU9RJVU4013
 376765616314013 | X5FD4KO1UEE4013
 376765616314013 | M7ZXTIQVCPB4013
 376765616314013 | UBOSC9K3EXZ4013
 376765616314013 | ZJ1C50C9L9R4013
(5 rows)

In this example, the cc_num eFPE format has five keys defined for it, so the return value is a table containing five rows.

The following example shows a more common use: querying a table column that is encrypted using an eFPE format.

=> SELECT id, first_name, last_name FROM customers3 u
          JOIN (SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                                   format='cc_num',
                                                   config_dfs_path='/voltagesecure/conf')
          OVER ()) pak
          ON u.cc_num = pak.protected;

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross

(1 row)

In the previous example, the customers3 table is joined to the output from VoltageSecureProtectAllKeys. Any rows in the customers3 table where the encryted cc_num column value matches values from the protected column of VoltageSecureProtectAllKeys matches appear in the output.

This function returns an error if you use it on a non-eFPE format:

=> SELECT first_name, last_name, ssn FROM customers u
        JOIN (
            SELECT VoltageSecureProtectAllKeys('232-28-0657' USING PARAMETERS format='ssn',
                                               config_dfs_path='/voltagesecure/conf')
            OVER ()
        )
        pak ON u.ssn = pak.protected;
ERROR 5861:  Error calling processPartition() in User Function VoltageSecureProtectAllKeys
at [ProtectAllKeys.cpp:21], error code: 1711, message: Error getting key numbers:
eFPE format required

See also

• Configuring access to SecureData
• VoltageSecureAccess
• VoltageSecureProtect

• VoltageSecureProtectAllKeys

11.6 - VoltageSecureRefreshPolicy

Immediately refreshes the client policy on the initiator node. Policies on non-initiator nodes are refreshed the next time a Voltage function is called on them.

Syntax

VoltageSecureRefreshPolicy()

Parameters

None

Example

Manually refresh the client policy across the nodes:

=> SELECT VoltageSecureRefreshPolicy() OVER ();
                                  PolicyRefresh
-------------------------------------------------------------------------------------
Successfully refreshed policy on node [v_sandbox_node0001]. Policy on other nodes
will be refreshed the next time a Voltage operation is run on them.
(1 row)

See also

• VoltageSecureConfigure
• VoltageSecureProtect
• VoltageSecureProtectAllKeys
• Encrypting, decrypting, and hashing data