Re: Random Unique Integer - Mailing list pgsql-sql

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