Re: Create on insert a unique random number - Mailing list pgsql-sql

At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote:
>On Tue, 18 Mar 2008 12:23:35 -0700
>Steve Midgley <public@misuse.org> wrote:
> > 1) Create a second field (as someone recommend on this list) that 
> is an
> > MD5 of your primary key. Use that as your "accessor" index from the 
> web
>
>I strongly disagree for three reasons.  First, if you are going to
>generate a key then don't store it.  Just generate it every time.
>Second, don't generate it based on a known field.  You may think that
>it is secure but what if you private key is compromised?  Do you then
>change everyone's security code?  Third, what if one person's
>code is compromised?  If it is based on a calculation then you
>can't change that one person's security code.
>
>Generate a random number and store that.  You will be much happier 
>when
>something goes wrong and something always goes wrong.
>
>--
>D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three 
>wolves

Hi D'Arcy,

I'm not clear on your concern here - an MD5 hash doesn't have a private 
key that can be compromised, afaik. It's a one way hash. I don't see 
much difference between making an MD5 of the primary key and generating 
a random number for the "public primary key", except that you shouldn't 
get index collisions with the MD5 method (whereas eventually you will 
with a random number, though of course using a GUID would eliminate 
that concern for practical purposes).

The issue raised by the OP, I believe, is not about security of the 
primary key # itself or its ability to provide unauthorized access to 
the underlying records. The system in question protects its records 
from unauthorized access already.

The issue is about creating an index into a sparse hash so that each 
record is somewhat randomly located in a sparse hash "index space". 
(One valid reason to do this would be if you wanted to hide the total 
number of records in your table from competitors or customers). (Just 
for reference of my view on the problem: 
http://en.wikipedia.org/wiki/Hash_table)

Whether SHA-1 or MD5, I think the point is that if you don't care about 
speed in generating the hash index (which the OP doesn't apparently), 
hash indexing via an encryption algorithm will ensure that the hash 
index is relatively free of "clustering" - which as I understand it, is 
the point of this exercise. Encryption as a hash index generator is 
imperfect for sure, as the Wikipedia article goes at length to discuss, 
but from my perspective it "does the job" - at least as far as the OP 
describes it (or I understood it!). [smile]

I may be way off here of course, and I appreciate the input - any 
thoughts?

Steve