Re: uuid type for postgres

Mark,

> I suggest that UUID be recommended in place of SERIAL for certain
> classes of applications, and that it therefore belongs in the core.
> UUID and SERIAL can be used together (although, once you have a UUID,
> it may not be useful to also have a SERIAL).

I think that, if you want to push a refactored UUID type for PostgreSQL
8.2, that you'd better separate your database design arguments from your
inclusion arguments.

For example, you might get my agreement that it would be useful to have a
UUID as a core type; you would *never* get my agreement to recommend using
UUID to newbies.   I have seen *far* too many abuses of UUIDs in really
bad database design.   People who use them should be experienced enough to
know what they're doing.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

On Tue, Sep 06, 2005 at 03:57:55PM -0400, jonah.harris@gmail.com wrote:
> I agree with Josh on the UUID type, it gets abused far too often

Out of curiosity, how does it get abused?  It doesn't seem to me
that it would be any more prone to abuse than any other type.

> and (IMHO) isn't widely enough used to belong in the core.

Seems like a self-fulfilling cycle.  There isn't one in core
or contrib (pguuid is not in contrib, IIRC), and so one doesn't
get used.  Since no one uses one, it's not in core or contrib.

> Couldn't you just fix the
> problem in pguuid rather than write a whole new type?

Not sure which you you're addressing here, but I can't fix the license
problem.  Nor can I readily fix the lack of a mac ioctl() to get
the hardware mac address.

In any case, from an economy of effort view, I'd much rather maintain
a postgres extension/interface to an existing uuid library, than fret
the details of writing a uuid library myself.  UUIDs themselves don't
really interest me, I just happen to need one for my application.

I'm basically done.  I'll tar it up and post a link to an alpha version in a
couple of hours.  As far as i can tell, it works fine.  I'm calling it alpha
because i'm not doing proper error checking, and i'm not sure how to log or
throw an error even if i detect one.  Also, I have only compiled it on
Mac OS 10.3, I'll want to test it on my linux box.

--
Nathan Wagner

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Sep 6, 2005, at 3:06 PM, mark@mark.mielke.cc wrote:

> On Tue, Sep 06, 2005 at 05:54:34PM -0400, Tom Lane wrote:
>
>> I don't see any "big opposition".  People are simply questioning the
>> idea whether it belongs in core PG.  The reason we don't want to
>> accept
>> everything-and-the-kitchen-sink in core is that we have only limited
>> manpower to maintain it.  So you've got to justify that we should
>> spend
>> our effort here and not elsewhere.  There's a fair amount of nearly
>> ...
>> been there awhile.  So one of the questions that's going to be
>> asked is
>> how useful/popular it's really going to be.
>>
>
> Sounds reasonable, and certainly no more than I expected. If Nathan
> hadn't raised the issue, it probably would have been a few months
> before I raised it myself.
>
>
>> One thing that is raising my own level of concern quite a bit is the
>> apparent portability issues.  Code that isn't completely portable
>> is a
>> huge maintainability problem; in particular, stuff that requires
>> system-dependent behavior used nowhere else in Postgres is a real
>> pain.
>> It sounds like the UUID code expects to be able to get at the
>> machine's
>> MAC address, which suggests serious issues in (a) relying on
>> not-too-standard APIs, (b) possible protection issues (will an
>> unprivileged process be able to get at the MAC address?), and (c)
>> ill-defined behavior on machines with more or less than one MAC
>> address.
>> Not to mention that MAC addresses aren't so unique as all that.
>>
>
> I'll try to prepare an answer for this. (I started to write a lot of
> information - but is it unverified from memory, and perhaps should be
> more authoritative before presented as truth)

Some modern UUID implementations prefer /dev/urandom or similar to
the time or MAC address unless you really beg them to give you a
weaker UUID.

You can take a look at the man page for the Theodore Y. Ts'o
implementation that is in Darwin's Libc here:
http://developer.apple.com/documentation/Darwin/Reference/ManPages/
man3/uuid_generate.3.html

Specifically:

        The uuid_generate function creates a new universally unique
identifier
        (UUID).   The  uuid  will be generated based on high-quality
randomness
        from  /dev/urandom,  if  available.   If  it  is  not
available,  then
        uuid_generate  will use an alternative algorithm which uses
the current
        time, the local ethernet MAC address (if available),  and
random  data
        generated using a pseudo-random generator.

The Apache Portable Runtime has a apr_os_uuid_get() that supports two
flavors of UUID for unix (Linux/Mac OS X uuid_generate and FreeBSD's
uuid_create, may be available elsewhere), and the UuidCreate API on
Win32.  apr-util's apr_uuid_get() will use apr_os_uuid_get() if
available, and otherwise will default to a relatively weak mostly-
timestamp-based UUID.

It would probably be reasonable and easy to do what Apache does
here.  A platform UUID implementation, if present, is generally going
to be better than anything included into PostgreSQL itself.

-bob

-----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-----



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

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 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.

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 the database. Nobody should be able to
trivially download all the pictures at one time.

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.

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?

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 fine
for the purposes? :-)

> > 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.

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

> > 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.

I don't need the random factor. I only need it to be difficult to guess.

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.

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.

Cheers,
mark

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

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, Sep 08, 2005 at 12:02:54PM -0400, Jonah H. Harris wrote:
> 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.

I'm sure he meant something like this. But I am still failing to see
how the below is any better than UUID. UUID is 16 bytes instead of
4+32=36. UUID doesn't require synchronization of the SEQUENCE
configuration (INCREMENT 100 START <SITE-NUMBER> as suggested by
another person) when working with tables at multiple sites that will
be merged together.

So this would leave having the primary key for the row being SERIAL,
and naming the picture as the MD5/SHA1 using data only from constant
strings, and the row data, the only part of the row data remaining
constant being the primary key. I agreed this would work, and enhanced
this by copying a trick from the SASL people where the key would be
concatenated with a constant secret string to further prevent people
from guessing how to crack the numbering scheme, but it means I have
to jump through hoops, and it doesn't solve the multisite / joining
tables after the fact, issue. I can't take my picture databases from
two separate sites, and merge them together nightly without jumping
through hoops. I might have a site in New York, and a site in Toronto.
I don't want to obtain sequence numbers remotely between sites,
forcing a real-time dependency between the sites.

I'm sure we could debate about alternatives for a long, long time. :-)
In the case I describe above, I could have a "ORIGINAL SITE" field,
and include that in a multi-column primary key.

I stubbornly wish to use a type which doesn't require alternatives, or
messy complications to the many SQL queries I will be performing.  I
want it to work out of the box, without any magic. UUID does exactly
what I want, and perhaps more than I need. Most importantly, it does
exactly what I want. I don't care at all that it does more than I need,
as something less isn't available or standard at the moment. UUID is
standard.

You guys are amusing me - not in an insulting way, but by making me
think outside my own conclusions. In the end, though, although there
have been arguments that have made me doubt my decision (1.5X the
number of index pages required over a SERIAL primary key, 2X the
number of index pages required for a multi-column primary key used in
a join table), for the most part, I've become more confident with my
decision. There's nothing wrong with what I'm doing. Yes, there are
other ways to do it - but these would only increase the complexity of
my application, with an insufficient return for this sacrifice. My
application is quite simple as is, using UUID as my preferred type
for objects that will be accessed on their own through external
interfaces.

Cheers,
mark


> 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.

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

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/

On 9/6/05, Josh Berkus <josh@agliodbs.com> wrote:

Mark,

> I suggest that UUID be recommended in place of SERIAL for certain
> classes of applications, and that it therefore belongs in the core.
> UUID and SERIAL can be used together (although, once you have a UUID,
> it may not be useful to also have a SERIAL).

I think that, if you want to push a refactored UUID type for PostgreSQL
8.2, that you'd better separate your database design arguments from your
inclusion arguments.

For example, you might get my agreement that it would be useful to have a
UUID as a core type; you would *never* get my agreement to recommend using
UUID to newbies.   I have seen *far* too many abuses of UUIDs in really
bad database design.   People who use them should be experienced enough to
know what they're doing.

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 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 <mark@mark.mielke.cc> wrote:

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 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.

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 the database. Nobody should be able to
trivially download all the pictures at one time.

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.

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?

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 fine
for the purposes? :-)

> > 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.

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

> > 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.

I don't need the random factor. I only need it to be difficult to guess.

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.

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.

Cheers,
mark

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

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/