Re: PGCrypto: Realworld scenario and advice needed

Thank you, Joe.

Your solution provided below works great. Much better/simpler than my
original approach. You rock!

-Michael

_____

From: Joe Conway [mailto:mail(at)joeconway(dot)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