pgp_sym_encrypt and decrypt an entire column in postgresql

Question

I would like to encrypt an area_code column in my postgreSQL database with aes 256 encryption. Here is what happens.. I am able to encrypt it easily but I am not able to decrypt it.

Here is the encryption query that works fine:

update mytable t1 set area_code = t2.area_code from (select pgp_sym_encrypt(area_code,'Password', 'compress-algo=1, cipher-algo=aes256') as area_code,area_name from mytable) t2 where t1.area_name = t2.area_name;

However the decryption query does not seem to work if I give something like

update mytable t1 set area_code = t2.area_code from (select pgp_sym_decrypt(area_code,'Password') as area_code,area_name from mytable) t2 where t1.area_name = t2.area_name;

or even when I try to view the decrypted area_code

select pgp_sym_decrypt((select area_code from ci), 'Password') ;

The only thing that works is when I use a single record and feed in the encrypted text directly as input.

select pgp_sym_decrypt('aes.encrypted.string.given.as.input', 'Password') ;

Here is the error: ERROR: function pgp_sym_decrypt(character varying, unknown) does not exist LINE 3: from (select pgp_sym_decrypt(area_code, 'Password') as area_... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ********** Error ********** — Subha Yoganandan
– Subha Yoganandan, Commented Oct 30, 2014 at 15:30

jjanes · Accepted Answer · 2014-10-30 17:57:05Z

15

You are supposed to store the encrypted data into a bytea column, not a text column. You might be able to cast it to bytea from text:

pgp_sym_decrypt(area_code::bytea,'Password')

Why don't you update the table directly, rather than doing a degenerate self-join on the update?

answered Oct 30, 2014 at 17:57

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pgp_sym_encrypt and decrypt an entire column in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related