Skip to content

Trino UDFs

These are details on how to install and configure the Privacera crypto plug-in in Trino to use Privacera-supplied Encryption UDFs in Trino to encrypt or decrypt data.

For conceptual background, see PEG Architecture and Flow.

Note

The protect and unprotect UDFs work properly with privacera_starburstenterprise but not with privacera_hive. Starburst has three possible configurations (Hive, System, and Hive + System), of which only the system-level has been verified.

Syntax of Privacera Encryption UDFs for Trino

The Privacera crypto plug-in includes the following UDFs:

  • Encrypt: With the quoted <encryption_scheme_name>, the protect UDF encrypts all values of <column_name> in a table:

    select protect(<column_name>, '<encryption_scheme_name>') from <table_name>;
    
  • Decrypt: With the <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table:

    select unprotect(<column_name>, '<encryption_scheme_name>') from <table_name>;
    
  • Decrypt with obfuscation: With the quoted <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table, further obfuscates the decrypted data via <presentation_scheme_name>, and writes the decrypted, obfuscated data to <optional_column_name_for_obfuscated_data>:

    select unprotect(<column_name>, '<encryption_scheme_name>', <presentation_scheme_name>) <optional_column_name_for_obfuscated_data> from <table_name>;
    
  • Decrypt with obfuscation: With the quoted <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table, further obfuscates the decrypted data via <presentation_scheme_name>, and writes the decrypted, obfuscated data to <optional_column_name_for_obfuscated_data>:

    select unprotect(<column_name>, '<encryption_scheme_name>', <presentation_scheme_name>) <optional_column_name_for_obfuscated_data> from <table_name>;
    
    For example usage, see Example Queries to Verify Privacera-supplied UDFs.

Prerequisites for Installing Privacera Crypto Plug-in for Trino

The following should already be ready:

  • A fully functional installation of Trino. In these examples, the location of the installed Trino software is shown as:

    <absolute_path_to_trino_home_directory>
    
  • The users who will use the UDFs have sufficient access to the pertinent tables.

Determine required paths to crypto jar and crypto.properties

Encryption for Trino relies on the Privacera-supplied crypto jar and crypto.properties file.

The paths where the crypto jar and crypto.properties files must be installed depend on whether you have deployed Trino in a container (such as Docker) or not.

These different paths are detailed in comments in the procedures that follow.

Install Privacera Crypto Plug-in via Privacera Manager

You update Privacera Manager to get a shell script that downloads the Privacera Encryption crypto plug-in for Trino.

Run the following commands:

 # Change to Privacera Manager directory
 cd ~/privacera/privacera-manager
 # Upgrade Privacera Manager itself
 ./privacera-manager.sh upgrade-manager

Configure Privacera Crypto Plug-in for Trino

 # Copy Trino properties file to Privacera Manager config/custom-vars directory
 cp config/sample-vars/vars.starburst.enterprise.trino.yml config/custom-vars/
 # Set property STARBURST_TRINO_ENABLE to true
 vi config/custom-vars/vars.starburst.enterprise.trino.yml
 ...
 STARBURST_TRINO_ENABLE: "true"
 ...
 # Save the file
 # Edit starburst-trino-crypto.yml to specify Trino home directory
 vi ansible/privacera-docker/roles/defaults/main/starburst-trino-crypto.yml
 ...
 STARBURST_TRINO_INSTALL_DIR: <absolute_path_to_trino_home_directory>
 ...
 # Save the file

Run Shell Script to Install Privacera Crypto Plug-in

 # Change to Privacera Manager directory
 cd ~/privacera/privacera-manager
 # Update Privacera Manager to get shell script
 ./privacera-manager.sh update
 # Change to new directory created by privacera-manager update
 cd output/starburst-trino-crypto/
 # Make the script executable
 chmod +x privacera_crypto_trino_setup.sh
 #
 ######################################
 # NOTE: You must copy the script to your Trino or Starburst instance
 ######################################
 #
 #  Run the script on your instance from where you copied it
 ./privacera_crypto_trino_setup.sh

Verify that the Script Ran Correctly

Verify the following on your Trino or Starburst instance.

  • The location of the Privacera crypto jar:

    # For non-container deployment
    ls -l <absolute_path_to_trino_home_directory>/plugin/privacera/privacera-crypto-jar-with-dependencies.jar
    # For container deployment
    ls -l /data/starburst/plugin/privacera/privacera-crypto-jar-with-dependencies.jar
    
  • The location of the crypto.properties file in Trino's etc directory:

    # Verify existence of crypto.properties file
    # For non-container deployment
    ls -l <absolute_path_to_trino_home_directory>/etc/crypto.properties
    # For non-container deployment
    ls -l /data/starburst/etc/crypto.properties
    

Restart Trino to Register the Privacera Crypto UDFs for Trino

 # Go to Trino bin directory
 cd /<trino_installation_directory>/bin
 # Restart Trino
 ./launcher restart

privacera.unprotect with Optional Presentation Scheme

The unprotect UDF supports an optional specification of a presentation scheme that further obfuscates the decrypted data.

For an example of data transformation with the optional prsentation scheme, see Example of Data Transformation with /unprotect and Presentation Scheme.

Syntax:

select <id>, privacera.unprotect(<COLUMN_NAME>, <ENCRYPTION_SCHEME_NAME>, <PRESENTATION_SCHEME_NAME>) <OPTIONAL_NAME_FOR_COLUMN_TO_WRITE_OBFUSCATED_OUPUT> from <DB_NAME>.<TABLE_NAME>;

where:

  • <PRESENTATION_SCHEME_NAME> is the name of the chosen Privacera presentation scheme with which to further obfuscate the decrypted data.
  • <OPTIONAL_NAME_FOR_COLUMN_TO_WRITE_OBFUSCATED_OUTPUT> is a "pretty" name for the column that the obfuscated data is written to.
  • Other arguments are the same as in the preceding unprotect example.

Example Queries to Verify Privacera-supplied UDFs

See the syntax detailed in Syntax of Privacera Encryption UDFs for Trino.

  • Encrypt: The following example query with the protect UDF encrypts the cleartext CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted'EMAIL' encryption scheme:

    select protect(CUSTOMER_EMAIL, `EMAIL`) from CUSTOMERS;
    
  • Decrypt: The following example query with the unprotect UDF decrypts the encrypted CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted 'EMAIL' encryption scheme:

    select unprotect(CUSTOMER_EMAIL, 'EMAIL') from CUSTOMERS;
    
  • Decrypt with obfuscation: The following example query with the unprotect UDF decrypts the encrypted CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted 'EMAIL' encryption scheme, obfuscates the decrypted data with the presentation scheme PRESENTATION_EMAIL, and writes the decrypted, obfuscated data to OPTIONAL_OUTPUT_COLUMN_FOR_OBFUSCATED_DATA:

    select unprotect(CUSTOMER_EMAIL, 'EMAIL', PRESENTATION_EMAIL) OPTIONAL_OUTPUT_COLUMN_FOR_OBFUSCATED_DATA from CUSTOMERS;