From: Joe Conway <mail@joeconway.com>
To: Moran.Michael <Michael.Moran@IGT.com>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: PGCrypto: Realworld scenario and advice needed
Date: Mon, 11 Apr 2005 15:26:22 -0700
Message-ID: <425AF98E.3030004@joeconway.com> (raw)
In-Reply-To: <B27C8914860EE24E865D189A3735EA53100974@lasexch03.is.ad.igt.com>
References: <B27C8914860EE24E865D189A3735EA53100974@lasexch03.is.ad.igt.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, Michael.Moran@IGT.com
Subject: Re: PGCrypto: Realworld scenario and advice needed
In-Reply-To: <425AF98E.3030004@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox