This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Voltage SecureData integration function reference

The functions in this section are part of the Vertica voltagesecure library for integrating with Voltage SecureData.

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.

1 - VoltageSecureAccess

Calls SecureData to decrypt ciphertexts encrypted with VoltageSecureProtect.

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

ciphertext A VARCHAR value that was encrypted using SecureData. You must cast other data types (for example DATE values) to VARCHAR when calling this function.
tweak

VARCHAR value analogous to a salt that allows equivalent *plaintext*s to produce different ciphertexts. The same tweak value must for encryption and decryption of a given plaintext.

When encrypting or hashing an entire column, you can pass another column for a set of tweak values.

format_name A string specifying the original FPE format used to generate the ciphertext. Note that SecureData has no way to tell if the value passed to it was actually encrypted or not, or what FPE format was used.
is_masked

A boolean, whether to mask the value when decrypting the ciphertext. Masking is defined on a per-format basis on the SecureData Appliance.

Note that since masking is optional, you must specify whether to decrypt with masking enabled. If you omit the masking parameter, the plaintext will be unmasked by default:

config_file String containing the file name of the configuration file to use when authenticating with the SecureData appliance. You must create this file using VoltageSecureConfigure. If you do not supply this parameter, you must set session parameters to configure access to SecureData. See Configuring access to SecureData. Any values set in session parameters override the values in this file.
sd_identity A string containing the identity to use when decrypting the data. Because SecureData uses the identity to determine encryption keys, this identity much match the identity used to encrypt the data. If supplied, this value overrides any identity value set in the configuration file or session parameter.

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

2 - VoltageSecureConfigure

Saves SecureData user access configuration parameters to a file in the Vertica Distributed File System (DFS).

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

config_dfs_path='filename'

Required. A string containing the path for the configuration file in DFS. This is either:

  • A file name (without any path information). The function automatically stores the file in a DFS directory named for the user. Creating this directory prevents different user's files from overwriting one another.

  • The absolute path /voltagesecure/conf. All users can use this file in calls to the other functions in the SecureData library. This path is the only absolute one that VoltageSecureConfigure allows for this parameter.

identity=sd_boolean A string containing identity to use with the SecureData Appliance. This is usually in the form of an email address. When SecureData uses LDAP authentication, it uses this value to authenticate the user.
store_password=Boolean A Boolean value. When set to true, Vertica stores the LDAP password stored in the password session parameter in the configuration file. Defaults to false.
store_shared_secret=Boolean A Boolean value. When set to true, Vertica stores the shared secret set in the shared_secret session parameter in the configuration file. Defaults to false.
username=sd_user A string containing the user name for authenticating with SecureData.

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

3 - VoltageSecureConfigureGlobal

Saves global SecureData access configuration parameters for all users to a file in the Vertica Distributed File System (DFS).

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

policy_url=url A string containing the URL of the SecureData policy file. The Vertica SecureData library uses the contents of this file, such as the formats that the SecureData Appliance supports. It also uses the URL of this file to determine the location of the SecureData Appliance.
allow_short_fpe=Boolean A Boolean value. 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=Boolean A Boolean value. When set to True, Vertica caches the SecureData policy file and encryption keys to disk, rather than just to memory. Defaults to false.
network_timeout=Integer An Integer value. Configures the network timeout in seconds. Defaults to its maximum value of 300 seconds.

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)

4 - VoltageSecureProtect

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

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

plaintext

VARCHAR value to encrypt. You must cast other data types (for example DATE values) to VARCHAR when calling this function.

NULL values return NULL.

tweak

VARCHAR value analogous to a salt that allows equivalent *plaintext*s to produce different ciphertexts. The same tweak value must for encryption and decryption of a given plaintext.

When encrypting or hashing an entire column, you can pass another column for a set of tweak values.

format_name'

String specifying a format-preserving encryption (FPE) or format-preserving hash (FPH) format to encrypt or hash the plaintext.

To encrypt your data, pass an FPE format defined in your SecureData Appliance.

To hash your data, pass an FPH format defined in your SecureData Appliance (version 6.6+). Note that hashing operations are one-way and cannot be reversed with VoltageSecureAccess.

config_file String containing the file name of the configuration file to use when authenticating with the SecureData appliance. You must create this file using VoltageSecureConfigure. If you do not supply this parameter, you must set session parameters to configure access to SecureData. See Configuring access to SecureData. Any values set in session parameters override the values in this file.
sd_identity String containing the identity to use when authenticating with SecureData. SecureData uses this value as a basis for the encryption key. This value usually takes the form of an email address. If supplied, it overrides any values set in the configuration file or session 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

5 - VoltageSecureProtectAllKeys

This function helps you locate values in a column encrypted using an Embedded Format Preserving Encryption (eFPE) format.

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

value VARCHAR containing the value to encrypt. You must cast other data types (for example DATE values) to VARCHAR when calling this function.
format=eFPE_format String containing the name of an eFPE format defined by SecureData. This format must be an eFPE format defined by your SecureData Appliance, or the function returns an error. This format must also match the format of value. VoltageSecureProtectAllKeys returns an error if value's format does not match the one you specify in eFPE_format.
config_dfs_path=config_file String containing the file name of the configuration file to use when authenticating with the SecureData appliance. You must create this file using VoltageSecureConfigure. If you do not supply this parameter, you must set session parameters to configure access to SecureData. See Configuring access to SecureData. Any values set in session parameters override the values in this file.
identity=sd_identity String containing the identity to use when authenticating with SecureData. SecureData uses this value as a basis for the encryption key. This value usually takes the form of an email address. If supplied, it overrides any values set in the configuration file or session 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

6 - VoltageSecureRefreshPolicy

Immediately refreshes the client policy on the initiator node.

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