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