Re: Drawbacks of using BYTEA for PK?

Answers inline.

----- Original Message -----
From: "Greg Stark" <gsstark(at)mit(dot)edu>

> On the one hand if your GUIDs are just an MD5 of a sequence then they're
just
> as guessable as the sequence. The attacker can try MD5 of various numbers
> until he finds the one he is (it's probably on the web site somewhere
anyways)
> and then run MD5 himself on whatever number he feels.
>
> On the other hand it is possible to do this right. Include a secret of
some
> kind in the MD5 hash, something that's not publically available. That
secret
> is in essence the password to the scheme. Now it's not really "obscurity"
any
> more than any password based scheme is "security through obscurity".

You still have the following problem: the PK is not really used for very
much in this case except referencing data. This is done internally
(invoices, etc), so the application is presumed to know the ID when looking
up a customer. Nothing you do will prevent any attack based on searching
the database, i.e. select customer_id from customers; if such an attack is
possible in an application. I actually think that developers should enforce
security as far back (towards the database) as possible, so if this needs to
be prevented, using a view which only provides access to the customers
required is the preferred solution. You could also use triggers.

If, however, you want a global unique id which will never collide with any
other records (f. ex. for distributed server solutions), then you have
another problem-- MD5 is NOT guaranteed to be unique. Think about it-- if
the return digest is of a set length, then there must be many different
values which will create that same digest. Instead MD5 is designed to
prevent deliberate duplication, which is not what we are talking about here
(accidental duplication) and so you may want to be cautious about hashing
your keys. In this case, a more open, transparent key would be better. For
example:

machine identifier || sequence.

You *could* hash these, but it is unnecessary and may actually create
collisions if the machine identifier is sufficiently large. However,
mac_address || ipv4 address should be sufficient, I would think. It would
still be attackable in your view, so you could add a timestamp :-) but
again, I see limited utility of guids as a security feature.

Best Wishes,
Chris Travers