How to retrieve encrypted column data from PostgreSQL in Bold BI

Encryption and decryption support in PostgreSQL column data can be achieved using various methods, depending on your requirements for security, performance, and compliance. Here’s an overview of one of the common approaches:

pgcrypto Extension

PostgreSQL offers a Built-in PostgreSQL Functions called pgcrypto extension, which provides functions for encryption and decryption. Common functions include:

1. Symmetric Encryption (pgp_sym_encrypt and pgp_sym_decrypt): This method uses a single password for both encryption and decryption.
2. Asymmetric Encryption (pgp_pub_encrypt and pgp_pub_decrypt): This method uses a public key for encryption and a private key for decryption. It’s more secure for sharing data, as the decryption key (private key) doesn’t need to be shared.

CREATE EXTENSION pgcrypto; 

Retrieve encrypted column data in Bold BI using Code View mode

Currently, a direct method for accessing column data is unavailable. However, you can connect and access both Symmetric Encryption and Asymmetric Encryption in Bold BI through the code view.

Example
Consider the following table as provided below:

CREATE TABLE sales_report (
   id SERIAL PRIMARY KEY,
   sales_person_name BYTEA,
   sales_amount BYTEA,
   sales_date BYTEA
);

-- Encrypting and inserting multiple records into the sales_report table
INSERT INTO sales_report (sales_person_name, sales_amount, sales_date)
VALUES 
   (pgp_sym_encrypt('John Doe', 'MySecretKey123'), pgp_sym_encrypt('10000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-11', 'MySecretKey123')),
   (pgp_sym_encrypt('Jane Smith', 'MySecretKey123'), pgp_sym_encrypt('15000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-10', 'MySecretKey123')),
   (pgp_sym_encrypt('Robert Brown', 'MySecretKey123'), pgp_sym_encrypt('12000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-09', 'MySecretKey123')),
   (pgp_sym_encrypt('Emily Davis', 'MySecretKey123'), pgp_sym_encrypt('17000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-08', 'MySecretKey123')),
   (pgp_sym_encrypt('Michael Wilson', 'MySecretKey123'), pgp_sym_encrypt('9000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-07', 'MySecretKey123')),
   (pgp_sym_encrypt('Sarah Johnson', 'MySecretKey123'), pgp_sym_encrypt('16000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-06', 'MySecretKey123')),
   (pgp_sym_encrypt('David Lee', 'MySecretKey123'), pgp_sym_encrypt('14000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-05', 'MySecretKey123')),
   (pgp_sym_encrypt('Laura Martinez', 'MySecretKey123'), pgp_sym_encrypt('11000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-04', 'MySecretKey123')),
   (pgp_sym_encrypt('Chris Garcia', 'MySecretKey123'), pgp_sym_encrypt('13000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-03', 'MySecretKey123')),
   (pgp_sym_encrypt('Megan Robinson', 'MySecretKey123'), pgp_sym_encrypt('18000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-02', 'MySecretKey123')),
   (pgp_sym_encrypt('James Thompson', 'MySecretKey123'), pgp_sym_encrypt('20000', 'MySecretKey123'), pgp_sym_encrypt('2024-08-01', 'MySecretKey123'));

Establish a PostgreSQL Data Source connection

1. Create a PostgreSQL live Data Source with your credential.

2. Drag and drop the table onto the query designer page.
   

   

3. Enable the code view mode.
   

   

4. Update the query to include decryption, then click the ‘Run’ button to validate it.
   

   

Maintain the secret key in site-level custom attribute and how to access the custom attribute in Query.

1. Setting up a custom attribute at the site level.
   

   
   

2. Utilize the site-level custom attribute in the query. Now the secret key is not directly visible to end users.
   

   

Additional References

• Live Mode PostgreSQL.
• Custom attribute hierarchy.
• Dashboard Parameter
• How to decrypt encrypted data in SQL server using Bold BI
• How to access the SQL Server encrypted columns in an SQL connector in Bold BI