I have an application in which I want it
_not_ to be possible to work out which
rows of a table are being/have been updated.
I can think of these different compromise scenarios:
(a) Hacker can connect to the database to
execute queries.
The severity depends on which database user
the hacker has cracked.
(i) A database user who has no select permission on
the table, but only to some stored procedures defined
using the "security definer" clause.
(ii) A database user who has select permission on the
table.
(b) Hacker has root access and can view the
table file as stored in the file system.
(c) Hacker has access to tape backups.
My analysis so far:
(a) (i) Is it still possible to work out which rows
have been touched?
(a) (ii) It seems a breach is possible via the xmin values.
In that case, what about doing updates inside a transaction
that does a trivial update of all rows, e.g.:
begin transaction;
update mytable ....; -- change one row
update mytable set id=id; -- change all rows
commit;
So now all rows have the same xmin values.
Does this work? Performance is not so good, is it?
Is there a better way?
(b) Now I really need some help. What to do
about the file that stores the table (which will
contain old versions of the data), and the
WAL file?
One approach would be to make a fresh copy
of the table at regular intervals:
create table mytablecopy (like mytable ...);
insert into mytablecopy select * from mytable;
drop table mytable;
alter table mytablecopy rename to mytable;
Is there a better way?
In any case, that doesn't solve the "problem" of the WAL.
(c) Use pg_dump only? Never do a backup of the
raw files? How to do online backups?
--
Richard Walker
Software Improvements Pty Ltd
Phone: +61 2 6273 2055
Fax: +61 2 6273 2082
