Cc: "Vivek Khera" <vivek@khera.org>,
  <pgsql-sql@postgresql.org>
Message-Id: <A74CFEB2-8C70-4639-BC76-75EE383B90E8@myemma.com>
From: Erik Jones <erik@myemma.com>
To: "Campbell, Lance" <lance@uiuc.edu>
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760CDDC34@SAB-FENWICK.sab.uiuc.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Re: Create on insert a unique random number
Date: Tue, 18 Mar 2008 13:59:50 -0500
References: <B10E6810AC2A2F4EA7550D072CDE8760CDDC32@SAB-FENWICK.sab.uiuc.edu>
	<268179.51357.qm@web54301.mail.re2.yahoo.com>
	<20080318165138.GA6578@a-kretschmer.de>
	<B10E6810AC2A2F4EA7550D072CDE8760CDDC33@SAB-FENWICK.sab.uiuc.edu>
	<6649C6F5-BEDE-4A66-A9A3-33CE95633A83@khera.org>
	<B10E6810AC2A2F4EA7550D072CDE8760CDDC34@SAB-FENWICK.sab.uiuc.edu>


On Mar 18, 2008, at 1:40 PM, Campbell, Lance wrote:

> Thanks for all of your input.  It appears that the best way to do this
> is to create a default random number in the primary id field in the
> table definition and then return that value after insert.  If an
> exception occurs because of duplicates I will simple perform the same
> insert statement again. I doubt there would be many duplicate hits =20
> if I
> use a really large number.
>
> Why use a random number as a primary key?  Security via obscurity.
>
> I build web applications for a living.  In most of my applications =20
> it is
> preferable to use a random primary key.  Why?
>
> Example:
>
> I built a web application called the Form Builder.  It allows
> individuals to create web forms.  After a user is done building their
> web form the tool provides a URL for the user to access the form.
> Obviously the URL has the random ID of the form in it.  Most of the
> forms created with this tool can be accessed and filled out by the
> general public.
>
> So why not use a sequential number?  So if I used a sequential number
> then a student or outside individual could easily change the number in
> the URL to see what other forms there are.  It is not that they don't
> have access to the forms but they should not be messing with them if
> they really don't have a reason to.  So by using a very large random
> number the users filling out a form cannot easily guess what another
> form ID is.
>
> Does that make sense?  I have at least a dozen tools that I prefer to
> use this approach on.
>
> This is not a security approach.  It is more about not giving obvious
> access to people that want to mess around.

I'd say it makes total sense and we do much the same thing with =20
unsubscribe links in the emails we push here.  However, we keep the =20
primary key based on sequences and for sent messages generate a random =20=

id using md5(now()::text).  In a sense, then, we have "public" and =20
"private" keys.

Erik Jones

DBA | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com