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