PostgreSQL and UUID as Primary Key • Hacker News Board

Re: PostgreSQL and UUID as Primary Key

#41

Post by lulzury » Fri, Jul 05, 2024, 10:52 PM UTC

spoiler wrote: post #33

rootedbox wrote: post #29

Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.

I was thinking of adopting UUIDv7 for some of my stuff. So, I'm curious: why is this the case? Is it because of the time component?

That's what the RFC states:

> Timestamps embedded in the UUID do pose a very small attack surface. The timestamp in conjunction with an embedded counter does signal the order of creation for a given UUID and its corresponding data but does not define anything about the data itself or the application as a whole. If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 (Section 5.4) SHOULD be utilized.

https://datatracker.ietf.org/doc/html/rfc9562#name-security-...

Re: PostgreSQL and UUID as Primary Key

#42

Post by [deleted] » Fri, Jul 05, 2024, 10:53 PM UTC

spoiler wrote: post #33

rootedbox wrote: post #29

Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.

I was thinking of adopting UUIDv7 for some of my stuff. So, I'm curious: why is this the case? Is it because of the time component?

[deleted]

Re: PostgreSQL and UUID as Primary Key

#43

Post by incrudible » Fri, Jul 05, 2024, 10:56 PM UTC

njtransit wrote: post #34

vbezhenar wrote: post #21

Earlier quoted context omitted.

IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.

Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!

If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.

Re: PostgreSQL and UUID as Primary Key

#44

Post by lulzury » Fri, Jul 05, 2024, 10:56 PM UTC

munk-a wrote: post #23

AtlasBarfed wrote: post #10

UUIDs are guaranteed to be unique? They often use tricks like including the MAC address of the generator machine and other ways to increase uniqueness assurances. It was my understanding that uuids are simply very very unlikely to duplicate in situations with random generation.

Your understanding is correct but you're underselling very very in this context. It is astronomically unlikely to hit a collision with the advised generation methods. If you want a possibly easier to grasp parallel git relies on SHA hashes never colliding and will break in a really awful way if you can produce two commits in a tree with the same hash - it's so astoundingly unlikely that people are okay summarizing it…

What you stated makes intuitive sense, but it does make me wonder why the RFC states the following in the security considerations:

> Implementations SHOULD NOT assume that UUIDs are hard to guess. For example, they MUST NOT be used as security capabilities (identifiers whose mere possession grants access). Discovery of predictability in a random number source will result in a vulnerability.

https://datatracker.ietf.org/doc/html/rfc9562#name-security-...

Re: PostgreSQL and UUID as Primary Key

#45

Post by nubinetwork » Fri, Jul 05, 2024, 10:59 PM UTC

If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.

Re: PostgreSQL and UUID as Primary Key

#46

Post by sbuttgereit » Fri, Jul 05, 2024, 10:59 PM UTC

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a definite conclusion.

I was hoping to see it in version 17, but can't get a really good read on what's going on with this feature.

Re: PostgreSQL and UUID as Primary Key

#47

Post by [deleted] » Fri, Jul 05, 2024, 11:02 PM UTC

njtransit wrote: post #34

vbezhenar wrote: post #21

Earlier quoted context omitted.

IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.

Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!

[deleted]

Re: PostgreSQL and UUID as Primary Key

#48

Post by rareitem » Fri, Jul 05, 2024, 11:04 PM UTC

brigadier132 wrote: post #22

My strategy is to use v4 Uuids for anything that is not inserted frequently and don't need to be ordered (think user ids) and v7 ids for things that are. If your dataset is small the overhead from Uuids wont matter, if your dataset is large the randomness of Uuids will save your ass when you migrate to a distributed solution.

Is there a reason to not always use v7 by default?

Re: PostgreSQL and UUID as Primary Key

#49

Post by klabb3 » Fri, Jul 05, 2024, 11:08 PM UTC

sbuttgereit wrote: post #46

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a defi…

What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?

Re: PostgreSQL and UUID as Primary Key

#50

Post by davisp » Fri, Jul 05, 2024, 11:08 PM UTC

nubinetwork wrote: post #45

If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.

Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work.

And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the OpenSSL bug that had removed too much entropy from the RNG setup.