View Only

 How to Decrypt Column Values in Pentaho Report Designer which were encrypted using custom secret key in Database

  • General
  • ReportingAndAnalytics
  • Pentaho
  • Pentaho
Praveen Abburi's profile image
Praveen Abburi posted 05-11-2020 08:51

I have a problem like this, I'm using Pentaho Report Designer Community edition for reporting and analysis purposes. In my side I have a postgresql table with encrypted values which were encrypted using a secret key. When I fetch data using Pentaho those values comes as encrypted values. How can I get the decrypted values. Is there a work around in Pentaho Report Designer ? or have to use PDI?


Development team have shared the decryption key ,i am not sure how and where i should use those to show decrypted values in reports.

Andrew Cave's profile image
Andrew Cave

Can't you use PGP_SYM_DECRYPT in the query itself to get the postgres server to decrypt for you?

Praveen Abburi's profile image
Praveen Abburi

Hi @Andrew Cave​ ,


Thanks for your time.


For Encryption application development team is using Third party tool(CryptoJS) not the DB encryption method so using PGP_SYM_DECRYPT doesnt work.


Is there a way to decrypt in Pentaho?

Andrew Cave's profile image
Andrew Cave

Hmmm...that's a risky way of doing it. I'd rather use SSL and let the db encrypt. However..all us devs get stuck dealing with somebody else's bad choices


You can use a 'Modified Javascript value' step and follow the instructions here

to import your custom JS file. In the script, incoming fields are available as variables (of the same name as the field) and just create a new variable (and add it in the output grid at the bottom) to get the output back into the normal pentaho stream.