Re: How to lose transaction history (xmin values, WAL, etc.)?

Peter Hunsberger wrote:
> Can you use an external key store? If not, I can't see this as being
> a serious attempt at security, but playing along, you could try
> something like the following:
>
> 1) Symmetrically encrypt a randomly generated string with something
> based on the users credentials (user name and password);
>
> 2) If the user can authenticate (many ways of checking this) then you
> decrypt the string from 1) and it becomes the basis for the encryption
> and decryption of the users data.
>
> You can have the same string encrypted by multiple users as needed for
> shared access and you can have a single user manage multiple strings
> as needed.

If I understand your scheme, this works in a scenario
where the row being updated is in some sense "owned" by a
particular user, who must provide some other data (a "password")
not otherwise stored in the database in order
to decrypt it and then encrypt it again after the update.

That's not the case in my scenario. The row being
updated is not specifically "owned" by an individual
application user.

But you've given me food for thought, by translating
the original problem into an encryption problem.

There is _other_ data in the database which
is sensitive and could usefully be encrypted. But
the data in this particular problem is not
sensitive per se, I just don't want it to be
possible to recreate a history of updates.

I think I need to figure out a way to lose the data stored
in the WAL at regular intervals.

I've tried setting archive_timeout to 60.
This gives me 3 WAL files, with a new one
created (and an old one deleted) every
five minutes (I didn't change
checkpoint settings) but even after several
hours there's still very old transaction
data stored in one of the WAL files. How to
flush old transaction data from the WAL once
and for all?

--
Richard Walker
Software Improvements Pty Ltd
Phone: +61 2 6273 2055
Fax: +61 2 6273 2082