From: Josh Berkus <josh@agliodbs.com>
To: mark@mark.mielke.cc
Cc: pgsql-sql@postgresql.org, nathan wagner <nw@hydaspes.if.org>
Subject: Re: uuid type (moved from HACKERS)
Date: Wed, 7 Sep 2005 13:35:56 -0700
Message-ID: <200509071335.56822.josh@agliodbs.com> (raw)
In-Reply-To: <20050907201016.GA24672@mark.mielke.cc>
References: <20050906135057.GA24136@granicus.if.org>
<200509071131.17161.josh@agliodbs.com>
<20050907201016.GA24672@mark.mielke.cc>
Mark, Nathan,
I'm moving this over to the PGSQL-SQL list, away from -hackers, as it's no
longer a -hackers type discussion. Hope you don't mind!
> On Wed, Sep 07, 2005 at 11:31:16AM -0700, Josh Berkus wrote:
> > 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.
>
> By making the picture identifier unguessable, it discourages the most
> common sort of abuse of the system. If the number is unguessable, and
> they can't access the directory as a listing, it will be sufficiently
> difficult as to discourage the common abuser of the system. On the
> other hand, an obviously guessable identifier may *encourage* the
> common person to consider abuse.
>
> 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.
>
> I'm not under the impression that it is impossible for a competent
> person to dump my database. I am under the impression that the people
> who would do such a thing, tend not to be intelligent, and will be
> stopped by this simple tactic.
>
> I could use any identifier at all. It could be a random sequence of
> characters. The UUID appeals to me, as I don't have to re-invent
> the concept. This use of UUID falls outside the scope of using it
> to join tables. It's a handle that is associated with the data,
> for external identification of the object.
Seems like this would be better served by simply encrypting the three-part
universal key (server|table|row) using an encryption key which is not
public/obvious. That would preserve the obscurity of object naming while
still allowing the UUID to contain useful information.
> I happen to also use it as an internal primary key for the objects
> that fit this category, as I wish to benefit from the built-in merge
> capabilities of UUID over SERIAL, and I don't currently see the
> point of keeping a SERIAL and a UUID for each object. On the last
> point, I did start to do this, but every single one of my queries
> become more complicated as a result. Using the SERIAL for joining,
> and the UUID for identifying a set of rows was becoming a little
> ridiculous for my purposes. Using only the UUID to provide for
> all my purposes is suiting my requirements for the cost of 1.5X
> the size of a primary key index, 2X the size of a index for
> a n to n relation mapping UUID to UUID, and an far less significant
> increase in table space (much less than 1.5X, although I haven't
> finished calculating it yet).
>
> 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.
Oh, you won't get any argument from me on that one -- no need for *two*
surrogate keys in a table. IME, most tables don't need even *one*.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mark@mark.mielke.cc, nw@hydaspes.if.org
Subject: Re: uuid type (moved from HACKERS)
In-Reply-To: <200509071335.56822.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox