encrypt postgres column in an update statement

Question

i'm using postgres 9.2 on rhel7 and have started looking at column encryption. I have no problem with getting the encryption to work with an INSERT statement, but am failing to work out how to do this with an UPDATE statement for a table which already contains rows.

i have been using the example from this URL http://www.postgresonline.com/journal/archives/165-Encrypting-data-with-pgcrypto.html

and my INSERT is in the format taken from this URL.

INSERT INTO testuserscards(username, cc)
SELECT robotccs.username, pgp_pub_encrypt(robotccs.cc, keys.pubkey) As cc
FROM (VALUES ('robby', '41111111111111111'),
    ('artoo', '41111111111111112') ) As robotccs(username, cc)
     CROSS JOIN (SELECT dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
super publickey goobly gook goes here
-----END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;

i just can't get my head around how the UPDATE statement work fit together, i've tried various combinations which look something like the following (different tables used from the INSERT above).

update dg_test t1 
 set var2 = t2.var2 from (select pgp_pub_encrypt(var1,keys.pubkey) as var2 from dg_test) t2 
 where t1.var1 = t2.var1
     CROSS JOIN (SELECT dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
....
-----END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;

i guess another option would be to build an ON INSERT trigger to do the encryption and then run an DUMP and RESTORE.

has anyone got an idea how to do this with an UPDATE?

Vao Tsun · Accepted Answer · 2017-08-15 10:57:39Z

1

from your example, what is the problem with below? (the only difference I join pubkey against original table in the subuery)

update dg_test t1 
 set var2 = t2.var2 from (
   select pgp_pub_encrypt(var1,keys.pubkey) as var2 
   from dg_test
   CROSS JOIN (SELECT dearmor('-----BEGIN...--END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;
 ) t2 
 where t1.var1 = t2.var1

answered Aug 15, 2017 at 10:57

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

encrypt postgres column in an update statement

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