Pentaho

 View Only

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

  • General
  • Reporting and Analytics
  • 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.


#ReportingandAnalytics
#Pentaho
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

https://stackoverflow.com/questions/43067622/pentaho-data-integration-how-to-load-and-call-method-of-external-library-js-at#43069776

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.