From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-hackers@postgresql.org
Subject: Re: uuid type for postgres
In-Reply-To: <20050907201016.GA24672@mark.mielke.cc>
Date: Thu,  8 Sep 2005 13:45:10 -0000
Message-ID: <f170b46b6dfc94268eda5ee84dc1f506@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>> I'm also a little baffled to come up with any real application where making
>> an id number for most tables "unguessable" would provide any kind of real
>> protection not far better provided by other means.   For your "users"
>> table, sure, but that's a very special case.

> It should never be the sole means of defense, however, it can be quite
> effective at prevention.

> For a rather simple example, consider a site that associates a picture
> with each member. If the pictures are named 1.jpg, 2.jpg, 3.jpg, etc.
> it makes it ridiculously easy to write a script to pull all of the
> pictures off the site. This can be bothersome, as the only type of
> person who would do this, is the type of person with an illegitimate
> motivation. I want the data to be easily and freely accessible as
> specific objects, but I do not wish to provide an easy way of
> dumping all of the data as a unit.

I don't think security through obscurity is a good reason to add something
to PostgreSQL. Either make the pictures accessible to everyone, or put
some other security mechanism in place. As far as pictures, you could
certainly used the SHA1 checksum as your primary key, which would satisfy
all your requirements.

> In my case, it isn't only pictures. I don't want people pulling all
> the data off the site as a dump, and using it how they wish, but I do
> wish to make the data freely available, and easily accessible from a
> web browser.

.htaccess? Cookies? Encrypted data? iptables? All of these are better
solutions than random filenames.

> Not that everybody should rip out SERIAL and replace it with UUID,
> but it really isn't that bad, and in some cases, such as mine,
> I don't see the point of using both, and choose to instead allow
> UUID to solve many of my concerns at the same time, with an
> acceptable for me cost in database pages.

We're doing something similar to UUIDs here - LSID (Life Science
Identifiers). But being as the whole point of LSIDs is to share
data, the "random" factor is not needed. If ever PG did do something
like UUID (and despite the paragraphs above, I could see a use for it),
I'd rather do something like picking from a predetermined range of values
at random and deplete the pool as you go rather than create large
values with a low (but finite) probability of collision.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509080939
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkMgQAwACgkQvJuQZxSWSsgaOQCgl2Mb0cY5NuaTVuIAGdYKXqNE
oQYAoJ5pm3xHcu3Wr382Jja59Ll1/YOP
=IeqX
-----END PGP SIGNATURE-----