CODEWITHSUNDEEP
sqlpostgresqlencryptionsql-update
davegreen100
davegreen100

Reputation: 2115

encrypt postgres column in an update statement

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?

Upvotes: 1

Views: 1587

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

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

Upvotes: 1

Related Questions