X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 99F0AD8852 for ; Thu, 8 Sep 2005 11:04:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05586-07 for ; Thu, 8 Sep 2005 14:04:17 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.196]) by svr1.postgresql.org (Postfix) with ESMTP id 37F16D882E for ; Thu, 8 Sep 2005 11:04:16 -0300 (ADT) Received: by rproxy.gmail.com with SMTP id 40so299791rnz for ; Thu, 08 Sep 2005 07:03:48 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=fHupOEgghm2w6QInqCo/iWrZJMXnM59tosMufDV7F45SI9OSScVZFEGjLUg3be4r5ucH5IQ31E2LHdmv0TallgKbyNmMJZv1Kp8Y7vbEc9R8QOvhFsT4i9qqVI1uXRcliDxq3A8+8bH8colz2hvQpZy26WpBlONXhmN9SneI4us= Received: by 10.11.117.39 with SMTP id p39mr4235cwc; Thu, 08 Sep 2005 07:03:48 -0700 (PDT) Received: by 10.11.119.38 with HTTP; Thu, 8 Sep 2005 07:03:47 -0700 (PDT) Message-ID: <36e6829205090807031e4b50f8@mail.gmail.com> Date: Thu, 8 Sep 2005 10:03:47 -0400 From: "Jonah H. Harris" Reply-To: jonah.harris@gmail.com To: Greg Sabino Mullane Subject: Re: uuid type for postgres Cc: pgsql-hackers@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_446_3012866.1126188227999" References: <20050907201016.GA24672@mark.mielke.cc> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.368 required=5 tests=[AWL=0.048, HTML_10_20=0.295, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/431 X-Sequence-Number: 72828 ------=_Part_446_3012866.1126188227999 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Greg, thanks for saying it... I was thinking the same thing. Not that it really relates to the UUID data type inclusion discussion=20 itself, but I think this application design and use case for UUID is an=20 example of using a data type for the wrong purpose. Application design-wise= ,=20 security should be handled in a true *security* way, not by obfuscating a= =20 key (unless you're Microsoft :)). And, as Greg said, why not use a SHA1 has= h=20 instead... it would be easier than writing a whole new type implementation. I haven't yet gotten a chance to check out the code but plan to soon. On 9/8/05, Greg Sabino Mullane wrote: >=20 >=20 > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 >=20 >=20 > >> I'm also a little baffled to come up with any real application where= =20 > making > >> an id number for most tables "unguessable" would provide any kind of= =20 > real > >> protection not far better provided by other means. For your "users" > >> table, sure, but that's a very special case. >=20 > > It should never be the sole means of defense, however, it can be quite > > effective at prevention. >=20 > > 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. >=20 > I don't think security through obscurity is a good reason to add somethin= g > 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. >=20 > > 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. >=20 > .htaccess? Cookies? Encrypted data? iptables? All of these are better > solutions than random filenames. >=20 > > 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. >=20 > 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. >=20 > - -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200509080939 >=20 > https://www.biglumber.com/x/web?pk=3D2529DF6AB8F79407E94445B4BC9B90671496= 4AC8 > -----BEGIN PGP SIGNATURE----- >=20 > iEYEARECAAYFAkMgQAwACgkQvJuQZxSWSsgaOQCgl2Mb0cY5NuaTVuIAGdYKXqNE > oQYAoJ5pm3xHcu3Wr382Jja59Ll1/YOP > =3DIeqX > -----END PGP SIGNATURE----- >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >=20 --=20 Respectfully, Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation http://www.enterprisedb.com/ ------=_Part_446_3012866.1126188227999 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Greg, thanks for saying it... I was thinking the same thing.

Not that it really relates to the UUID data type inclusion discussion itself, but I think this application design and use case for UUID is an example of using a data type for the wrong purpose.  Application design-wise, security should be handled in a true *security* way, not by obfuscating a key (unless you're Microsoft :)).  And, as Greg said, why not use a SHA1 hash instead... it would be easier than writing a whole new type implementation.

I haven't yet gotten a chance to check out the code but plan to soon.


On 9/8/05, Greg Sabino Mullane <greg@tu= rnstep.com> wrote:

-----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 provi= de any kind of real
>> protection not far better provided by other means.  = For your "users"
>> table, sure, but that's a very spec= ial 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 ridiculous= ly 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 illegitima= te
> motivation. I want the data to be easily and freely accessible a= s
> specific objects, but I do not wish to provide an easy way of
&= gt; dumping all of the data as a unit.

I don't think security throug= h 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 couldcertainly 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 f= rom a
> web browser.

.htaccess? Cookies? Encrypted data? iptab= les? 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 b= ad, 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 si= milar 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 so= mething
like UUID (and despite the paragraphs above, I could see a use f= or 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
va= lues with a low (but finite) probability of collision.

- --
Greg = Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509080939
https://www.biglumbe= r.com/x/web?pk=3D2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN= PGP SIGNATURE-----

iEYEARECAAYFAkMgQAwACgkQvJuQZxSWSsgaOQCgl2Mb0cY5NuaTVuIAGdYKXqNEoQYAoJ5pm3xHcu3Wr382Jja59Ll1/YOP
=3DIeqX
-----END PGP SIGNATURE-----=



---------------------------(end of broadcast)--------------= -------------
TIP 2: Don't 'kill -9' the postmaster



--
Respectfully,

Jonah H. Harris, Database Intern= als Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/
------=_Part_446_3012866.1126188227999--