How to read and write encrypted values on Postgresql with Talend Data Integration

Two Stars

How to read and write encrypted values on Postgresql with Talend Data Integration

Hi there,

 

I'm new at Talend Data Integration and now I'm facing the challenge to read and write data from a Postgresql DB which is encrypted with the database encryption function pgp_sym_encrypt.

 

How can I read and wirte values from this DB?

 

Thank you for your support.

 

Best regards,

Ricardo

 


Accepted Solutions
Two Stars

Re: How to read and write encrypted values on Postgresql with Talend Data Integration

So I solved it by myself! It was easier than I expected... (such a noob)

 

To read decrypted you just have to change the select query, for example:

SELECT pgp_sym_decrypt( [DB].[SCHEMA].[TABLE].[FIELD],[ENCRYPTION_KEY]) as [FIELD_NAME],
FROM [DB].[SCHEMA].[TABLE];

[DB].[SCHEMA].[TABLE]

 

To insert is a little bit "trickier" but also quite simple... In your tPostgresqlOutput, go to "Advanced settings" and use Additional columns and replace the ones you want to encrypt with the expression "pgp_sym_encrypt( ? ,[ENCRYPTION_KEY])"

 

ADDITIONAL.png

 

Sorry for the simple question.

 

Best regards,

Ricardo


All Replies
Two Stars

Re: How to read and write encrypted values on Postgresql with Talend Data Integration

So I solved it by myself! It was easier than I expected... (such a noob)

 

To read decrypted you just have to change the select query, for example:

SELECT pgp_sym_decrypt( [DB].[SCHEMA].[TABLE].[FIELD],[ENCRYPTION_KEY]) as [FIELD_NAME],
FROM [DB].[SCHEMA].[TABLE];

[DB].[SCHEMA].[TABLE]

 

To insert is a little bit "trickier" but also quite simple... In your tPostgresqlOutput, go to "Advanced settings" and use Additional columns and replace the ones you want to encrypt with the expression "pgp_sym_encrypt( ? ,[ENCRYPTION_KEY])"

 

ADDITIONAL.png

 

Sorry for the simple question.

 

Best regards,

Ricardo

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now