sql - encrypt postgres column in an update statement -
i'm using postgres 9.2 on rhel7 , have started looking @ column encryption. have no problem getting encryption work insert
statement, failing work out how update
statement table contains rows.
i have been using example url http://www.postgresonline.com/journal/archives/165-encrypting-data-with-pgcrypto.html
and insert
in format taken url.
insert testuserscards(username, cc) select robotccs.username, pgp_pub_encrypt(robotccs.cc, keys.pubkey) cc (values ('robby', '41111111111111111'), ('artoo', '41111111111111112') ) robotccs(username, cc) cross join (select dearmor('-----begin pgp public key block----- super publickey goobly gook goes here -----end pgp public key block-----') pubkey) keys;
i can't head around how update
statement work fit together, i've tried various combinations following (different tables used insert
above).
update dg_test t1 set var2 = t2.var2 (select pgp_pub_encrypt(var1,keys.pubkey) var2 dg_test) t2 t1.var1 = t2.var1 cross join (select dearmor('-----begin pgp public key block----- .... -----end pgp public key block-----') pubkey) keys;
i guess option build on insert
trigger encryption , run dump
, restore
.
has got idea how update
?
from example, problem below? (the difference join pubkey against original table in subuery)
update dg_test t1 set var2 = t2.var2 ( select pgp_pub_encrypt(var1,keys.pubkey) var2 dg_test cross join (select dearmor('-----begin...--end pgp public key block-----') pubkey) keys; ) t2 t1.var1 = t2.var1
Comments
Post a Comment