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 B2AC2D8817 for ; Thu, 8 Sep 2005 13:12:49 -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 38104-07 for ; Thu, 8 Sep 2005 16:12:46 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id D6911D6F0D for ; Thu, 8 Sep 2005 13:12:45 -0300 (ADT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.204]) by svr4.postgresql.org (Postfix) with ESMTP id 97AEF5AFBFD for ; Thu, 8 Sep 2005 16:12:45 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id 40so306000rnz for ; Thu, 08 Sep 2005 09:10:12 -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=I8UtjDgPJDZu1EMS46geuhBqHX9XTfYkve3AdxV2+OBHmSN5knpHGAh7ou3g6z2UQJ4dVMyRzIiDnxSyUm2MTXqqZNbqiibr9JFeeDqb4nddOGDkbDIpCOAWOAOrRbiXR1J2rVMDnxNVzHwBduunMUY7ycOxqrMidLzOYshO/Zw= Received: by 10.11.119.78 with SMTP id r78mr5767cwc; Thu, 08 Sep 2005 09:02:54 -0700 (PDT) Received: by 10.11.119.38 with HTTP; Thu, 8 Sep 2005 09:02:54 -0700 (PDT) Message-ID: <36e682920509080902670ef3e1@mail.gmail.com> Date: Thu, 8 Sep 2005 12:02:54 -0400 From: "Jonah H. Harris" Reply-To: jonah.harris@gmail.com To: "mark@mark.mielke.cc" Subject: Re: uuid type for postgres Cc: Greg Sabino Mullane , pgsql-hackers@postgresql.org In-Reply-To: <20050908153003.GA14535@mark.mielke.cc> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_590_5427137.1126195374795" References: <20050907201016.GA24672@mark.mielke.cc> <20050908153003.GA14535@mark.mielke.cc> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.698 required=5 tests=[AWL=-0.301, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024, TO_ADDRESS_EQ_REAL=0.47] X-Spam-Level: X-Archive-Number: 200509/438 X-Sequence-Number: 72835 ------=_Part_590_5427137.1126195374795 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Mark, I think what Greg suggested was sha1(number) as the key instead of requirin= g=20 uuid as the key... it would perform the same function as far as you r use= =20 case is concerned. As a similar example (using MD5): CREATE SEQUENCE marks_seq START 1 INCREMENT 1; CREATE TABLE your_tbl ( your_key VARCHAR(32) NOT NULL DEFAULT md5(nextval('marks_seq')), your_picture BYTEA NOT NULL, PRIMARY KEY (your_key)); INSERT INTO your_tbl (your_picture) VALUES ('some bytea data'); select * from your_tbl ; your_key | your_picture ----------------------------------+----------------- c4ca4238a0b923820dcc509a6f75849b | some bytea data same functionality from your standpoint. On 9/8/05, mark@mark.mielke.cc wrote: >=20 > On Thu, Sep 08, 2005 at 01:45:10PM -0000, Greg Sabino Mullane wrote: > > > For a rather simple example, consider a site that associates a pictur= e > > > 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=20 > 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=20 > satisfy > > all your requirements. >=20 > I think you missed two parts. The first is, that it isn't really an > attempt at security. The pictures are still freely available. There > is no need to block accesses. >=20 > The goal is to prevent a dump of the database. Nobody should be able to > trivially download all the pictures at one time. >=20 > I'm not sure what you are suggesting the SHA1 checksum be applied to. > Not the picture, for sure, as this would require that my tables store > a primary key, and a picture key, leading me right back to something > that seems a little ridiculous. >=20 > If you mean the SHA1 checksum of some constant secret password, and > the primary key, possibly SERIAL, then sure - but that's ignoring the > other benefits of UUID. If I'm going to use UUID, I might as well use > only UUID, and use the SHA1 checksum of the constant secret password, > and the UUID. So, the idea has merit - and was suggested by another > person - but it doesn't mean using the UUID alone, without all the > extra layers on top I would have to write, is the wrong choice. Why > would I go out of my way to jump through so many hoops, when I can > very easily, use the UUID? >=20 > You're asking me to do a lot, only so that you can point out that > 'UUID' is not necessary. Heck, I could use the timestamp, and a > random key as two separate fields. There are always work arounds. >=20 > Is it good to use work arounds? Or a type that already works fine > for the purposes? :-) >=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. > > .htaccess? Cookies? Encrypted data? iptables? All of these are better > > solutions than random filenames. >=20 > Nope. The files are publically accessible. No reason the secure their > access, and securing their access makes the whole system less efficient. >=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. > > 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=20 > values > > at random and deplete the pool as you go rather than create large > > values with a low (but finite) probability of collision. >=20 > I don't need the random factor. I only need it to be difficult to guess. >=20 > The timestamps have sufficient granularity, that they are effectively > difficult to guess, and a foreach loop would be expensive. If none of > the bits were random, I would still be happy. >=20 > If I had LSID support, and no UUID support, perhaps I'd use LSID > instead. I have no special attachment to UUID, other than it satisfies > my concerns and purposes in a convenient manner. I can merge my tables > without fiddling with the sequence definitions. I can hand out > external identifiers for specific objects which are effectively > unguessable. I can avoid having two primary keys, and use only the > one. Any type that satisfied these concerns would make me happy. >=20 > Cheers, > mark >=20 > -- > mark@mielke.cc / markm@ncf.ca / markm@nortel.com_________________________= _ > . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder > |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | > | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada >=20 > One ring to rule them all, one ring to find them, one ring to bring them= =20 > all > and in the darkness bind them... >=20 > http://mark.mielke.cc/ >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >=20 --=20 Respectfully, Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation http://www.enterprisedb.com/ ------=_Part_590_5427137.1126195374795 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Mark,

I think what Greg suggested was sha1(number) as the key instead of requiring uuid as the key... it would perform the same function as far as you r use case is concerned.

As a similar example (using MD5):

CREATE SEQUENCE marks_seq START 1 INCREMENT 1;
CREATE TABLE your_tbl (
    your_key          VARCHAR(32) NOT NULL DEFAULT md5(nextval('marks_seq')),
    your_picture      BYTEA NOT NUL= L,
    PRIMARY KEY (your_key));

INSERT INTO your_tbl (your_picture) VALUES ('some bytea data');

select * from your_tbl ;
             your_key           &= nbsp; |  your_picture
----------------------------------+-----------------
 c4ca4238a0b923820dcc509a6f75849b | some bytea data

same functionality from your standpoint.



On 9/8/05, mark@mark.mielke.cc &= lt;mark@mark.mielke.cc> wrote= :
On Thu, Sep 08, 2= 005 at 01:45:10PM -0000, Greg Sabino Mullane wrote:
> > For a rath= er simple example, consider a site that associates a picture
> > with each member. If the pictures are named 1.jpg, 2.jpg, 3.j= pg, etc.
> > it makes it ridiculously easy to write a script to pu= ll all of the
> > pictures off the site. This can be bothersome, a= s the only type of
> > person who would do this, is the type of person with an illeg= itimate
> > motivation. I want the data to be easily and freely ac= cessible 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 secu= rity through obscurity is a good reason to add something
> to Postgre= SQL. Either make the pictures accessible to everyone, or put
> some o= ther 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.

I think you missed two parts.= The first is, that it isn't really an
attempt at security. The pictures= are still freely available. There
is no need to block accesses.

The goal is to prevent a dump of t= he database. Nobody should be able to
trivially download all the picture= s at one time.

I'm not sure what you are suggesting the SHA1 checksu= m be applied to.
Not the picture, for sure, as this would require that my tables storea primary key, and a picture key, leading me right back to something
t= hat seems a little ridiculous.

If you mean the SHA1 checksum of some= constant secret password, and
the primary key, possibly SERIAL, then sure - but that's ignoring theother benefits of UUID. If I'm going to use UUID, I might as well use
= only UUID, and use the SHA1 checksum of the constant secret password,
and the UUID. So, the idea has merit - and was suggested by another
pers= on - but it doesn't mean using the UUID alone, without all the
extra lay= ers on top I would have to write, is the wrong choice. Why
would I go ou= t of my way to jump through so many hoops, when I can
very easily, use the UUID?

You're asking me to do a lot, only so= that you can point out that
'UUID' is not necessary. Heck, I could use = the timestamp, and a
random key as two separate fields. There are always= work arounds.

Is it good to use work arounds? Or a type that already works finefor the purposes? :-)

> > In my case, it isn't only pictures= . I don't want people pulling all
> > the data off the site as a d= ump, 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 dat= a? iptables? All of these are better
> solutions than random filename= s.

Nope. The files are publically accessible. No reason the secure the= ir
access, and securing their access makes the whole system less efficie= nt.

> > Not that everybody should rip out SERIAL and replace i= t 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 a= llow
> > 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 ran= ge of values
> at random and deplete the pool as you go rather than c= reate large
> values with a low (but finite) probability of collision.

I = don't need the random factor. I only need it to be difficult to guess.
<= br>The timestamps have sufficient granularity, that they are effectively
difficult to guess, and a foreach loop would be expensive. If none ofthe bits were random, I would still be happy.

If I had LSID suppor= t, and no UUID support, perhaps I'd use LSID
instead. I have no special = attachment to UUID, other than it satisfies
my concerns and purposes in a convenient manner. I can merge my tables<= br>without fiddling with the sequence definitions. I can hand out
extern= al identifiers for specific objects which are effectively
unguessable. I= can avoid having two primary keys, and use only the
one. Any type that satisfied these concerns would make me happy.
Cheers,
mark

--
mark@mielke= .cc / markm@ncf.ca / markm@nortel.com     __________________________
= .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |_= _ .  |  | .|. |__ |__ | \ |__  | Ottawa, Onta= rio, Canada

  One ring to rule them all, one ring to find = them, one ring to bring them all
      &nb= sp;            =     and in the darkness bind them...

      = ;            &n= bsp;        http://mark.mielke.cc/


--= -------------------------(end of broadcast)---------------------------
T= IP 6: explain analyze is your friend



--
Respectfully,

Jonah H. Harris, Database Internals Archite= ct
EnterpriseDB Corporation
= http://www.enterprisedb.com/
------=_Part_590_5427137.1126195374795--