From: Erik Jones <erik@myemma.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: Vivek Khera <vivek@khera.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create on insert a unique random number
Date: Tue, 18 Mar 2008 13:59:50 -0500
Message-ID: <A74CFEB2-8C70-4639-BC76-75EE383B90E8@myemma.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760CDDC34@SAB-FENWICK.sab.uiuc.edu>
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
> 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
> 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
unsubscribe links in the emails we push here. However, we keep the
primary key based on sequences and for sent messages generate a random
id using md5(now()::text). In a sense, then, we have "public" and
"private" keys.
Erik Jones
DBA | Emma®
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, lance@uiuc.edu, vivek@khera.org
Subject: Re: Create on insert a unique random number
In-Reply-To: <A74CFEB2-8C70-4639-BC76-75EE383B90E8@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox