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