Drawbacks of using BYTEA for PK?

[David Garamond]

Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).

I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be the
most convenient+comp act compared to other data types currently
available. I use GUIDs for most PK columns.

--
dave

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[D. Dante Lorenso]

David Garamond wrote:

Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).

I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be
the most convenient+comp act compared to other data types currently
available. I use GUIDs for most PK columns.

GUID? Isn't that really nothing more than an MD5 on a sequence?

SELECT (MD5(NEXTVAL('m y_table_seq'))) AS my_guid;

Since 7.4 has the md5 function built-in, there's your support ;-)
Now just add that to your table's trigger and your good to go.
I think in MS products, they format the guid with dashes in the
style 8-4-4-4-12 but it still looks to me like a 32 character hex
string or a 16 byte (128 bit) value. You can choose to store the
value however you like, I'm not sure what would be optimal, but
bits are bits, right?

Dante

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

On Sunday 11 January 2004 22:05, D. Dante Lorenso wrote:

David Garamond wrote:

Are there any drawbacks of using BYTEA for PK compared to using a
primitive/atomic data types like INT/SERIAL? (like significant
performance hit, peculiar FK behaviour, etc).

I plan to use BYTEA for GUID (of course, temporarily I hope, until
PostgreSQL officially supports GUID data type), since it seems to be
the most convenient+comp act compared to other data types currently
available. I use GUIDs for most PK columns.

GUID? Isn't that really nothing more than an MD5 on a sequence?

SELECT (MD5(NEXTVAL('m y_table_seq'))) AS my_guid;

I think the point of a GUID is it's supposed to be unique across any number of
machines without requiring those machines to coordinate their use of GUID
values.

I think the typical approach is to use something like:
hash_fn( network_mac_add ress || other_hopefully _unique_constan t ||
sequence_val )
and make sure that the probability of getting collisions is acceptably low.

ISTR a long discussion a year or two back on one of the lists, for those that
are interested.
--
Richard Huxton
Archonet Ltd

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

[David Garamond]

D. Dante Lorenso wrote:

GUID? Isn't that really nothing more than an MD5 on a sequence?

SELECT (MD5(NEXTVAL('m y_table_seq'))) AS my_guid;
I know there are several algorithms to generate GUID, but this is
certainly inadequate :-) You need to make sure that the generated GUID
will be unique throughout cyberspace (or to be more precise, the GUID
should have a very very small chance of colliding with other people's
GUID). Even OID is not a good seed at all.

Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
unique seed like MD5 of '/sbin/ifconfig' output)...
Since 7.4 has the md5 function built-in, there's your support ;-)

Well, until there's a GUID or INT128 or BIGBIGINT builtin type I doubt
many people will regard PostgreSQL as fully supporting GUID. I believe
there's the pguuid project in GBorg site that does something like this.

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

[Tom Lane]

David Garamond <li***@zara.6.i sreserved.com> writes:

Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
unique seed like MD5 of '/sbin/ifconfig' output)...

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[David Garamond]

Tom Lane wrote:

David Garamond <li***@zara.6.i sreserved.com> writes:

Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
unique seed like MD5 of '/sbin/ifconfig' output)...

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.

Of course, in the above case, MD5 is used to compress the "uniqueness "
(which should be more than 128-bit, comprised of: a) [good] random
number; b) timestamp; c) a "node ID" element, either from /sbin/config
output which contain MAC address, or from the hash of harddisk content,
etc) into a 128-bit space.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[D. Dante Lorenso]

Tom Lane wrote:

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.

The cool thing about a 'GUID' (or in my example a hashed sequence number
[sure
toss in some entropy if you want it]) is that if you happen to reference
that
value as a primary key on a table, the URL that passes the argument can not
be guessed at easily. For example using a sequence:

http://domain.com/application/load_r...tomer_id=12345

Then, users of the web will assume that you have at most 12345
customers. And
they can try to look up information on other customers by doing:

http://domain.com/application/load_r...tomer_id=12346
http://domain.com/application/load_r...tomer_id=12344

....basically walking the sequence. Sure, you will protect against this with
access rights, BUT...seeing the sequence is a risk and not something you
want
to happen. NOW, if you use a GUID:
http://domain.com/application/load_r...3-1b8ce9dcccc1

Right, so now try to guess the next value in this sequence. It's a little
more protective and obfuscated (an advantage in using GUIDs).

Dante


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

[scott.marlowe]

On Mon, 12 Jan 2004, D. Dante Lorenso wrote:

Tom Lane wrote:

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.

The cool thing about a 'GUID' (or in my example a hashed sequence number
[sure
toss in some entropy if you want it]) is that if you happen to reference
that
value as a primary key on a table, the URL that passes the argument can not
be guessed at easily. For example using a sequence:

http://domain.com/application/load_r...tomer_id=12345

Then, users of the web will assume that you have at most 12345
customers. And
they can try to look up information on other customers by doing:

http://domain.com/application/load_r...tomer_id=12346
http://domain.com/application/load_r...tomer_id=12344

...basically walking the sequence. Sure, you will protect against this with
access rights, BUT...seeing the sequence is a risk and not something you
want
to happen. NOW, if you use a GUID:

Security != obscurity.

While using GUIDs may make it harder to get hacked, it in no way actually
increases security. Real security comes from secure code, period.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Stark]

"scott.marl owe" <sc***********@ ihs.com> writes:

they can try to look up information on other customers by doing:

http://domain.com/application/load_r...tomer_id=12346
http://domain.com/application/load_r...tomer_id=12344

...basically walking the sequence. Sure, you will protect against this with
access rights, BUT...seeing the sequence is a risk and not something you
want
to happen. NOW, if you use a GUID:

Security != obscurity.

While using GUIDs may make it harder to get hacked, it in no way actually
increases security. Real security comes from secure code, period.

Well, uh, you're both wrong.

On the one hand if your GUIDs are just an MD5 of a sequence then they're just
as guessable as the sequence. The attacker can try MD5 of various numbers
until he finds the one he is (it's probably on the web site somewhere anyways)
and then run MD5 himself on whatever number he feels.

On the other hand it is possible to do this right. Include a secret of some
kind in the MD5 hash, something that's not publically available. That secret
is in essence the password to the scheme. Now it's not really "obscurity" any
more than any password based scheme is "security through obscurity".

However even that isn't ideal, since you have to be able to change the
password periodically in case it's leaked. I believe there are techniques to
solve this though I can' think of any off the top of my head.

But if your only threat model is people attacking based on the publicly
visible information then an MD5 of the combination of a sequence and a secret
is a perfectly reasonable approach.

In the past I happily exposed the sequence but used an MD5 of the sequence and
a secret as a protection against spoofing. I find exposing the sequence is
very convenient for programming and debugging problems. Spoofing is a serious
security hazard, but worrying about leaking information like the size of the
customer database is usually a sign of people hoping for security through
obscurity.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[David Garamond]

Greg Stark wrote:

On the other hand it is possible to do this right. Include a secret of some
kind in the MD5 hash, something that's not publically available. That secret
is in essence the password to the scheme. Now it's not really "obscurity" any
more than any password based scheme is "security through obscurity".

However even that isn't ideal, since you have to be able to change the
password periodically in case it's leaked. I believe there are techniques to
solve this though I can' think of any off the top of my head.

But if your only threat model is people attacking based on the publicly
visible information then an MD5 of the combination of a sequence and a secret
is a perfectly reasonable approach.

We're originally talking about using MD5 as a means to generate unique
ID right (and not to store password hash to be checked against later)?

Then this "secret key" is unnecessary. Just get some truly random bits
(if the number of bits is 128, then you can use it as it is. If the
number of bits is > 128, you can hash it using MD5 to get 128 bit. If
the number of bits is < 128, you're "screwed" anyway :-)

--
dave
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)