From: Kristo Kaiv <kristo.kaiv@skype.net>
To: Campbell, Lance <lance@uiuc.edu>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Random Unique Integer
Date: Thu, 14 Jun 2007 23:58:28 +0300
Message-ID: <42F997D8-E95C-41F0-A94B-0417131BCDF9@skype.net> (raw)
In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395E47@SAB-DC1.sab.uiuc.edu>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E40@SAB-DC1.sab.uiuc.edu>
<20070614132540.GA70344@winnie.fuhr.org>
<A3AC4FA47DC0B1458C3E5396E685E63302395E47@SAB-DC1.sab.uiuc.edu>
On 14.06.2007, at 22:40, Campbell, Lance wrote:
> I have a web application that is used to create web surveys and web
> forms. Users can create any number of surveys or forms at any time.
> The primary key on one of my tables defines the ID for any given
> form or
> survey. I do NOT want the number sequential. Since I use the
> number in
> the web URL as a parameter, I don't want people guessing what
> survey or
> form is next. By creating a very large random number the users cannot
> easily guess another form or survey's ID. This number is not designed
> for security but to keep people from being nosey and poking around
> easily into someone else's form or survey.
>
> Can a table have a primary key that is randomly generated?
you could do it like this:
test=# create table testsurvey (id_survey serial primary key, survey
text);
NOTICE: CREATE TABLE will create implicit sequence
"testsurvey_id_survey_seq" for serial column "testsurvey.id_survey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testsurvey_pkey" for table "testsurvey"
CREATE TABLE
test=# insert into testsurvey (survey) values ('first');
INSERT 0 1
test=# insert into testsurvey (survey) values ('second');
INSERT 0 1
test=# insert into testsurvey (survey) values ('third');
INSERT 0 1
test=# select *, md5(id_survey) from testsurvey;
id_survey | survey | md5
-----------+--------+----------------------------------
1 | first | c4ca4238a0b923820dcc509a6f75849b
2 | second | c81e728d9d4c2f636f067f89cc14862c
3 | third | eccbc87e4b5ce2fe28308fd9f2a7baf3
(3 rows)
so you actually show out only the PK id-s md5 hash
test=# create index idx_survey_md5 on testsurvey (md5(id_survey));
CREATE INDEX
test=# select * from testsurvey where md5(id_survey) =
'c81e728d9d4c2f636f067f89cc14862c';
id_survey | survey
-----------+--------
2 | second
The hashing algorithm you chose depends on the amount of rows you
expect there.
And also how difficult cracking it needs to be.
In case you have many rows and you think collision checking is
necessary you could
store the calculated hash in a new column. for replication management
purposes
its better to keep the PK as serial / bigserial.
Example: 100K surveys,
hashtext(id) : 4B int
100 000 / 256 ** 4 = 42949 (1:42949 chance that the next number will
lead to a valid survey entry)
keep the hashed pk value in a separate row as you will deplete
1/42949-th of the hash space with 100K rows
peace,
Kristo
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: kristo.kaiv@skype.net, lance@uiuc.edu, mike@fuhr.org
Subject: Re: Random Unique Integer
In-Reply-To: <42F997D8-E95C-41F0-A94B-0417131BCDF9@skype.net>
* 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