[GENERAL] How to lose transaction history (xmin values, WAL, etc.)?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to lose transaction history (xmin values, WAL, etc.)?
Peter Hunsberger wrote: If you really need security of some form at the physical database level then don't screw around with convoluted hacks. Encrypt the critical data in the database and be done with it. If the hacker gets root access so they can read the raw database files, they most likely also have access to the means to decrypt any encrypted data. This is particularly so if the database updates are being done by stored procedures. If encryption/decryption happens on a separate (not-also-compromised) client, then OK. Do you know of a way to deal with this if the application is on the same computer as the database? -- Richard Walker Software Improvements Pty Ltd Phone: +61 2 6273 2055 Fax: +61 2 6273 2082 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to lose transaction history (xmin values, WAL, etc.)?
Alvaro Herrera wrote: Excerpts from Richard Walker's message of jue may 20 02:19:17 -0400 2010: (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? The easiest way to do this is probably VACUUM FREEZE. Thank you very much - that works perfectly to solve case (a) (ii). It turns out it doesn't solve my case (b) in which the hacker can read the raw files. After a little bit of experimenting I found that VACUUM FREEZE followed by CLUSTER gives me a fresh raw table file with no transaction history. Now all I need is a way to deal with the WAL . . . -- Richard Walker Software Improvements Pty Ltd Phone: +61 2 6273 2055 Fax: +61 2 6273 2082 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
