From: H. Hall <hhall1001@reedyriver.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Steve Atkins <steve@blighty.com>
Subject: Re: Using PK value as a String
Date: Tue, 12 Aug 2008 14:43:42 -0400
Message-ID: <48A1D9DE.1060509@reedyriver.com> (raw)
In-Reply-To: <20080812114840.8f7468ed.wmoran@collaborativefusion.com>
References: <18986.60888.qm@web25807.mail.ukl.yahoo.com>
<48A15ECF.5030203@sime.com>
<87od3ywg4n.fsf@oxford.xeocode.com>
<0673579A-B049-4619-B6E4-EE427FB0DDBA@globalinn.com>
<1343069D-263F-4D99-BF7E-269C8F0E7F1F@houseofdesign.de>
<20080812110416.8e0d6e1c.wmoran@collaborativefusion.com>
<4D558373-E7B0-40EC-95EF-FDFE7046ABA2@houseofdesign.de>
<20080812112136.72af4bff.wmoran@collaborativefusion.com>
<233F1248-E3EA-4572-9310-4E4BC13995DE@blighty.com>
<20080812114840.8f7468ed.wmoran@collaborativefusion.com>
Bill Moran wrote:
> In response to Steve Atkins <steve@blighty.com>:
>
>
>> On Aug 12, 2008, at 8:21 AM, Bill Moran wrote:
>>
>>
>>> In response to Moritz Onken <onken@houseofdesign.de>:
>>>
>>>
>>>> Am 12.08.2008 um 17:04 schrieb Bill Moran:
>>>>
>>>>
>>>>> In response to Moritz Onken <onken@houseofdesign.de>:
>>>>>
>>>>>
>>>>>> We chose UUID as PK because there is still some information in an
>>>>>> integer key.
>>>>>> You can see if a user has registered before someone else
>>>>>> (user1.id <
>>>>>> user2.id)
>>>>>> or you can see how many new users registered in a specific period
>>>>>> of
>>>>>> time
>>>>>> (compare the id of the newest user to the id a week ago). This is
>>>>>> information
>>>>>> which is in some cases critical.
>>>>>>
>>>>> So you're accidentally storing critical information in magic values
>>>>> instead of storing it explicitly?
>>>>>
>>>>> Good luck with that.
>>>>>
>>>> How do I store critical information? I was just saying that it easy
>>>> to get some information out of a primary key which is an incrementing
>>>> integer. And it makes sense, in some rare cases, to have a PK which
>>>> is some kind of random like UUIDs where you cannot guess the next
>>>> value.
>>>>
Interesting. Ordered chronologically and the next value is unguessable.
>>> I just repeated your words. Read above "this is information which
>>> is in
>>> some cases critical."
>>>
>>> If I misunderstood, then I misunderstood.
>>>
>>>
>> I think Moritz is more concerned about leakage of critical information,
>> rather than intentional storage of it. When a simple incrementing
>> integer
>> is used as an identifier in publicly visible places (webapps, ticketing
>> systems) then that may leak more information than intended.
>>
I think there are better ways to accomplish this than encoding and
decoding/decrypting a PK. Store the sensitive data in a session variable
or store session data in the database neither of which is accessible to
users.
It is usually a big mistake to de-normalize a table by encoding several
fields in a single column PK or not. If you want to do something with
the encoded data such as find one or more rows with an encoded value
then you will have to inspect every row and decode it and then compare
it or add it or whatever.
>
> Then I did misunderstand.
>
>
--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com
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-performance@postgresql.org
Cc: hhall1001@reedyriver.com, wmoran@collaborativefusion.com, steve@blighty.com
Subject: Re: Using PK value as a String
In-Reply-To: <48A1D9DE.1060509@reedyriver.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