Thread: 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