Thread: PGCrypto: Realworld scenario and advice needed

Hello all,
I'm looking for advice on real-world PGCrypto usage.
I understand how to programmatically encrypt/decrypt data with PGCrypto --
no problem.
My question is:
What is the best way to update massive amounts of *existing* encrypted data
with a new encryption passphrase, assuming you know the old passphrase? 
For example:
Let's say that periodically there must be a one-time change of the
encryption passphrase and ALL existing encrypted data has to be encrypted
with the new passphrase.
My initial attack plan was to do the following:
1. Call decrypt() with the old-passphrase to decrypt each table's existing
data.
2. Temporarily store the decrypted data in temp tables.
3. Delete all rows of encrypted data from the original tables -- thereby
clearing the tables of all data encrypted with the old passphrase.
4. Call encrypt() with the new passphrase to encrypt all data in the temp
tables -- thereby repopulating the production tables with data encrypted
with the new passphrase.
5. Blow away the temp tables.
But this seems like a tedious procedure.
Is there any simple way to update ALL existing encrypted data with a new
passphrase, assuming you know the old passphrase and encryption type (i.e.
AES, Blowfish, etc.) without having to go through the 5-step process
mentioned above?
Thank you and best regards,
Michael Moran

Moran.Michael wrote:
> My initial attack plan was to do the following:
>  
> 1. Call decrypt() with the old-passphrase to decrypt each table's existing
> data.
> 2. Temporarily store the decrypted data in temp tables.
> 3. Delete all rows of encrypted data from the original tables -- thereby
> clearing the tables of all data encrypted with the old passphrase.
> 4. Call encrypt() with the new passphrase to encrypt all data in the temp
> tables -- thereby repopulating the production tables with data encrypted
> with the new passphrase.
> 5. Blow away the temp tables.
>  
> But this seems like a tedious procedure.
>  
> Is there any simple way to update ALL existing encrypted data with a new
> passphrase, assuming you know the old passphrase and encryption type (i.e.
> AES, Blowfish, etc.) without having to go through the 5-step process
> mentioned above?

Why not use a single UPDATE command, e.g. something like:

UPDATE tbl SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes');

Joe

Thank you, Joe.
Your solution provided below works great. Much better/simpler than my
original approach. You rock!
-Michael
 _____  

From: Joe Conway [mailto:mail@joeconway.com]
Sent: Mon 4/11/2005 3:26 PM
To: Moran.Michael
Cc: PostgreSQL
Subject: Re: [SQL] PGCrypto: Realworld scenario and advice needed



Moran.Michael wrote: 
> My initial attack plan was to do the following: 
>  
> 1. Call decrypt() with the old-passphrase to decrypt each table's existing

> data. 
> 2. Temporarily store the decrypted data in temp tables. 
> 3. Delete all rows of encrypted data from the original tables -- thereby 
> clearing the tables of all data encrypted with the old passphrase. 
> 4. Call encrypt() with the new passphrase to encrypt all data in the temp 
> tables -- thereby repopulating the production tables with data encrypted 
> with the new passphrase. 
> 5. Blow away the temp tables. 
>  
> But this seems like a tedious procedure. 
>  
> Is there any simple way to update ALL existing encrypted data with a new 
> passphrase, assuming you know the old passphrase and encryption type (i.e.

> AES, Blowfish, etc.) without having to go through the 5-step process 
> mentioned above? 

Why not use a single UPDATE command, e.g. something like: 

UPDATE tbl  SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes'); 

Joe