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