VoltageSecureProtect

Calls SecureData to encrypt a value.

Syntax

VoltageSecureProtect(value USING PARAMETERS format=format_name
                    [,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.

NULL values return NULL.
format=format_name

String defining SecureData FPE format of value, or 'auto' to have SecureData encrypt the entire value while preserving its overall format. If you pass the name of an FPE format, it must match one of the formats SecureData defines. The value's format must match the FPE format. For example, VoltageSecureProtect returns an error if you set format_name to 'ssn' but value contains a credit card number.

Always use the same FPE format to encrypt data in a column. If you use different FPE formats in the same column (such as loading some data using 'ssn' and other data using 'auto') there is no way to tell which format was used for any particular row. In this case, you will not be able to recover the unencrypted data because you cannot tell correctly decrypted values from incorrectly decrypted ones. Formats that deal with the same source format (such as ssn and ssn-all) are still incompatible, as they encrypt and decrypt the data in different ways.
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 encrypts 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)

The following example calls VoltageSecureProtect to encrypt two table columns in a COPY statement. These calls use 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)

The following example uses VoltageSecureProtect in a query to locate 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)

The following example shows how VoltageSecureProtect handles NULL values by returning NULL.

=> CREATE TABLE nulltable(n VARCHAR (20));
=> INSERT INTO nulltable VALUES (NULL);
				
=> SELECT VoltageSecureProtect(n USING PARAMETERS format='auto') FROM nulltable;
VoltageSecureProtect
---------------------
 
(1 row)

The following example encrypts Unicode 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ìçç-ぶてぴねら

See Also