Обсуждение: 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
On Thu, May 20, 2010 at 1:19 AM, Richard Walker <richard@softimp.com.au> wrote: > > 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. > Why? 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. -- Peter Hunsberger
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. --
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
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
On Thu, May 20, 2010 at 8:03 PM, Richard Walker <richard@softimp.com.au> wrote: > 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. Only if they also get at the keys. > > 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? 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 recall correctly, there's a version of DB2 with this already baked into the product. No idea on where it sits on the free to expensive scale....
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
On Thu, May 20, 2010 at 11:29 PM, Richard Walker <richard@softimp.com.au> wrote: > > 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. Well, a pool of users, but essentially yes. > That's not the case in my scenario. The row being > updated is not specifically "owned" by an individual > application user. So who does own it? > 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. > Like I said originally; why? What's the real point in all this? If it's anonymous records of some kind you could build FK strings that are essentially variations on the scheme I've given you, but I'm not sure I'd want to deal with the performance implications... However, I will point out that if you can't read the data you may be able to tell who created a given row, but so what? All the variations on your scenario that I can think of at the moment all seem to boil down to a security issue of some form... -- Peter Hunsberger
On Thu, May 20, 2010 at 09:33:23PM -0500, Peter Hunsberger wrote: > On Thu, May 20, 2010 at 8:03 PM, Richard Walker <richard@softimp.com.au> wrote: > > 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. > > Only if they also get at the keys. It's very difficult (with a conventional OS) to remove the *entirety* of the server hardware and software from the TCB. Hence you should assume that if PG ever sees a key it's also possible for an attacker to see the same key. The options are pretty much do all crypto away from the database server (either client side, or on another server that you can trust) or you have to trust (also in the technical sense) the database server itself and things become greatly simplified. > > 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? > > Can you use an external key store? How does this help? if the database has been compromised, what would stop the attacker from inserting some code that records the responses from this "external key store"? -- Sam http://samason.me.uk/
On Fri, May 21, 2010 at 6:40 AM, Sam Mason <sam@samason.me.uk> wrote: > On Thu, May 20, 2010 at 09:33:23PM -0500, Peter Hunsberger wrote: >> On Thu, May 20, 2010 at 8:03 PM, Richard Walker <richard@softimp.com.au> wrote: >> > 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. >> >> Only if they also get at the keys. > > It's very difficult (with a conventional OS) to remove the *entirety* of > the server hardware and software from the TCB. Hence you should assume > that if PG ever sees a key it's also possible for an attacker to see the > same key. Fair enough. The scenario the OP was describing seemed to be a concern with browsing the database and not at the level of inspecting running code, but who knows, he hasn't said what he is really trying to achieve. There are some hardware level work arounds for parts of this, but if you're running everything on the same server I doubt that you're also looking at that kind of scenario. > The options are pretty much do all crypto away from the database server > (either client side, or on another server that you can trust) or you > have to trust (also in the technical sense) the database server itself > and things become greatly simplified. Absolutely the best idea, the OP seems to have ruled that out however. As such, you can only make things reasonably safe from direct inspection.... > >> > 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? >> >> Can you use an external key store? > > How does this help? if the database has been compromised, what would > stop the attacker from inserting some code that records the responses > from this "external key store"? > Again, I had the impression that code injection did not seem to be the issue here. The issue seemed to be inspection of the data in the database after the fact. Like I said, it would be good to know what the real requirements are... However, that aside, to answer your question; among other things, the key request includes a timestamped hash of internal memory to ensure a non compromised server and the keys store returns functions with embedded one time keys to do the actual work. The keys are composite with portions that must match a compile time stored hash (you can't have a compromised server requesting the key), and at run time the same hash must be yielded (impossible to know without a previously compromised server), or you will get garbage. Replay attacks won't work since the server will check the time stamp on the original request (which we already know can't be compromised) before yielding up the decryption function. Much of the key exchange process is essentially standard Kerberos with the client and the server authenticating themselves to each other as usual, but you do need some extensions to manage the extra integrity checks and create and manage the additional message contents. -- Peter Hunsberger