Re: PGCrypto: Realworld scenario and advice needed - Mailing list pgsql-sql

From Moran.Michael
Subject Re: PGCrypto: Realworld scenario and advice needed
Date
Msg-id B27C8914860EE24E865D189A3735EA5310097B@lasexch03.is.ad.igt.com
Whole thread Raw
In response to PGCrypto: Realworld scenario and advice needed  ("Moran.Michael" <Michael.Moran@IGT.com>)
List pgsql-sql
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 



pgsql-sql by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: row-attribute in EXPLAIN-output doesn't match count(*)
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: row-attribute in EXPLAIN-output doesn't match count(*)