Encrypt entire column with pgcrypto

Question

I need to encrypt some columns using AES-256 key, I have checked an official documentation of pgcrypto. But their examples makes me mad.

For instance I have database looks like this.

     id       first_name      last_name       is_active

     0        John            last_name 1        True
     1        David           last_name 2        False
     2        Vincent         last_name 3        True
     3        Dean            last_name 5        False

I have tried something like this:

     UPDATE my_table SET first_name = ENCRYPT(user_name, 'my_encryption_key')

I need to encrypt the the first_name and last_name columns. How can I implement that??

Thanks, qwew

Answer 1

Use the pgp_sym_XXX() functions with either armor() or encode() to get base-64:

update my_table
   set first_name = armor(
                      pgp_sym_encrypt(first_name, 'your_key', 'cipher-algo=aes256')
                    ),
       last_name =  armor(
                      pgp_sym_encrypt(last_name, 'your_key', 'cipher-algo=aes256')
                    );

AES-256 is slow, so it may take a long time to run against your whole table.

To decrypt:

select pgp_sym_decrypt(dearmor(last_name), 'your_key', 'cipher-algo=aes256')
  from my_table;