As an administrator for your organization, you may decrypt the table data to analyze and measure Insight+ usage patterns.

Important information

Enter the following prompts in your PostgreSQL database exactly as shown in the below example, adhering to the same format.

  • Replace the following in the example code snippet:

    • <ENTER PRIVATE KEY> with your Private key in line no. 27 and 31.

    • 'ENTER PRIVATE KEY PASSPHRASE' with your passphrase in line no. 28 and 32.

    • <ENTER TENANT ID> with your Insight+ Tenant ID.

  • Some data provided in the example code snippet isn't encrypted, for example—id, user_id. You can view this information without having to decrypt them.

  • Data, for example—user_name, document_title are encrypted and can be decrypted using the safe_decrypt command.

To decrypt the table data, for example—document_previewed as shown below, run the following command in PostgreSQL database > Tables.

-- Create a function for safe decryption
CREATE OR REPLACE FUNCTION safe_decrypt(encrypted_text text, private_key text, passphrase text)
RETURNS text AS $$
BEGIN
IF encrypted_text IS NULL OR encrypted_text = '' THEN
RETURN NULL;
END IF;
BEGIN
RETURN pgp_pub_decrypt(
dearmor(encrypted_text),
dearmor(private_key),
passphrase
)::text;
EXCEPTION WHEN OTHERS THEN
RETURN '[DECRYPTION_ERROR]';
END;
END;
$$ LANGUAGE plpgsql;
 
-- Use the function in queries
SELECT
id,
user_id,
safe_decrypt(user_name, '-----BEGIN PGP PRIVATE KEY BLOCK-----
 
<ENTER PRIVATE KEY>
-----END PGP PRIVATE KEY BLOCK-----', 'ENTER PRIVATE KEY PASSPHRASE') AS decrypted_user_name,
safe_decrypt(document_title, '-----BEGIN PGP PRIVATE KEY BLOCK-----
 
<ENTER PRIVATE KEY>
-----END PGP PRIVATE KEY BLOCK-----', 'ENTER PRIVATE KEY PASSPHRASE') AS decrypted_document_title
FROM ip_ku_<ENTER TENANT ID>_source.document_previewed;