Thank you, Joe. Your solution provided below works great. Much better/simpler than my original approach. You rock! -Michael
_____ From: Joe Conway [mailto:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
