7 ms·
We evaluated UUIDv7 and determined that it's unwise to use it as a primary key. We have applications where we control the creation of the primary key, and wher
by jph 1y ago
We evaluated UUIDv7 and determined that it's unwise to use it as a primary key.
We have applications where we control the creation of the primary key, and where the primary key will be exposed to end users, such as when using a typical web app framework built with Rails, Phoenix, Loco, Laravel, etc. For these applications, UUIDv7 time is too problematic for security, so we prefer binary-stored UUIDv4 even though it's less efficient.
We also have applications where we control the creation of the primary key, and where we can ensure the primary key is never shown to users. For these applications, UUIDv7 is slower at inserts and joins, so we prefer BIGSERIAL for primary key, and binary-stored UUIDv4 for showing to users such as in URLs.
- wpollock 1y agoI wonder if the issue is with exposing internal IDs to end users. I'm sure the experts here have already thought of this, but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense? Maybe because the extra processing is more expensive than just using UUIDv4? Using a KDF such as argon2id on the random bits of a UUIDv7 seems like it might work well for external IDs. (And why the heck are different types or variants of UUIDs called "versions"?)
- gfody 1y ago> but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense? it does make sense and it's what you should do instead of using a UUID as PK for this purpose.
- Hizonner 1y agoBecause now, for the rest of eternity, every single person who writes any code that moves data from this table to somewhere else, for any purpose, has to remember that the primary key gives away the creation time of something, which can potentially be linked to something else. A lot of people won't notice that, and a lot of people who do notice it will get the remediation wrong. And you can now forget using a simple view on the database to give any information to any person or program that shouldn't get the creation times. You've embrittled your system.
- gfody 1y agothe question was why not use encryption (sqids/hashids/etc) to secure publicly exposed surrogate keys, I don't think this reply is on point .. surrogate keys ideally are never exposed (for a slew of reasons beyond just leaking information) so securing them is a perfectly reasonable thing to do (as seen everywhere on the internet). otoh using any form of uuid as surrogate key is an awful thing to do to your db engine (making its job significantly harder for no benefit) > You've embrittled your system. this is the main argument for keeping surrogate keys internal - they really should be thought of like pointers, dangling pointers outside of your control are brittle. ideally anything exposed to the wild that points back to a surrogate key decodes with extra information you can use to invalidate it (like a safe-pointer!)
- bri3d 1y agoIt does make all kinds of sense and is a majorly underutilized tool.
- kasperset 1y agoCurrently evaluating UUIDv7 as primary key for some inventory origin. I think it should be ok to use it for such use case since it will indicate the time of creation? Any thoughts?
- gtowey 1y agoYou have to ask what problems exactly are you solving? Unless there is a compelling reason to use them, sticking with auto increment IDs is much simpler. And I say this as someone who recently has to convert some tables from auto increment IDs to uuid. In that instance, they were sharded tables that were relying on the IDs to be globally unique, and made heavy use of the IDs to scan records in time order. So uuids were something which could solve the first problem while preserving the functionality of the second requirement.
- elcritch 1y agoYeah depends a lot on scale. If the inventory system only holds thousands of items, UUIDs just add a lot of headache for little gain. Your distributed table case sounds like a great use case for UUIDv7.
- andy_ppp 1y agoIt’s a perfectly good choice, most of the complaints here are exaggerated. If your inventory has SKUs use those externally/for links and for API lookups if possible.
- wongarsu 1y agoDeploying UUIDv7 certainly requires more thought about the implications. In many cases leaking the creation time of a key is completely fine, in some cases it isn't An interesting compromise is transforming the UUIDv7 to a UUIDv4 at the API boundary, like e.g. UUIDv47 [1]. On the other hand if you are doing that you can also go with u64 primary keys and transform those 1: https://github.com/stateless-me/uuidv47 https://github.com/stateless-me/uuidv47
- bricss 1y agoIf knowing IDs has a negative impact on security, then application system design is probably a trash.
- bearjaws 1y agoYeah I am trying to imagine a universe where having the creation time of an item breaks your security model and every path I go down is that the system has terrible security.
- Hizonner 1y agoI know that the person I'm stalking created a pseudonymous account on service X around time Y. Based on other information, I have a limited number of suspect accounts. The creation time leaks to me, either via a bug which would otherwise have been harmless, or because somebody writing code "can't imagine a universe where having the creation time of an item breaks your security". I use the creation time to figure out which of my candidates is actually the target. It took me under 15 seconds to come up with that.
- bearjaws 1y agoIt took you 15 seconds because its a terrible example, _around time Y_ is doing insane lifting of this concept. Then "based on other information" okay so some other information is enabling this.
- Hizonner 1y agoIt turns out that in reality, I usually know both "around time Y" and "other information". You're going to narrow me down from 10 accounts to 1, or from 100 to 10.
- bricss 1y agoIn huge number of cases you will have timestamps in the payload anyway, since most db records will have unredacted createdOn, updatedOn fields for display in the UI.
- laughing_snyder 1y agoWhy would exposing any primary key be bad for security? If your system's security *in any way* depends on the randomness of a database private key, you have other problems. It's not the job of a primary key to add to security. Not to mention that UUIDv7 has 6 random bytes, which, for the vast majority of web applications, even finance, is more than enough randomness. Just imagine how many requests an attacker would need to make to guess even one UUID (281 trillion possible combinations for 6 random bytes, and he also would need to guess the unix timestamp in ms correctly). The only scenario I can think of is that you use the primary as a sort of API key.
- echelon 1y agoDepends how much entropy is in your primary keys. If your primary keys are monotonic or time based, bad actors can simply walk your API.
- Hizonner 1y agoBecause anything that knows the primary key now knows the timestamp. The UUID itself leaks information. It's not that it's not adding security. It's that it's actually subtracting security.
- dotancohen 1y agoThe timestamp can be recovered from the UUID?
- lucideer 1y ago> leaks information It would have to leak sensitive information to be "subtracting security", which implies you're relying on timestamp secrecy to ensure your security. This would be one of the "other problems" the gp mentioned.
- atomicnumber3 1y agoPretty much any information can be used for something. You're ignoring everything they say about how something not critical to application security may still not be desirable to be leaked for other reasons. Example: Target and Walmart may not depend on satellites being unable to image their parking lots from the perspective of loss prevention or corporate security. But it still leaks information they may not want financial analysts to know about their performance.
- moron4hire 1y agoThis is why the UUID versions should have been labeled by letter rather than number. Each UUID version doesn't replace the last. They do different things. The numbered versioning gives the impression that "higher numbers = better" and that's neither the case nor the intention.
- nighthawk454 1y agoRecently someone shared a method for encrypting the timestamp portion as well: https://news.ycombinator.com/item?id=45275973 https://news.ycombinator.com/item?id=45275973
- halayli 1y agoI think privacy fits better rather than security. If your primary key is being used as a secret then you probably got your schema wrong. how will you encrypt them when required?
- alex_duf 1y agoUUIDv7 makes sense when a distributed system needs to insert vast amount of data that will be consumed chronologically. Typically an event log table. Anything else, as you're rightly pointing it out, is a bit of a stretch.
- dietr1ch 1y agoThe distributed part is what forces creating Ids outside of the server, where UUIDs become useful, and also where systems become reliable. Last year I went to renew my Id and they told me, sorry, the (centralised) system is down, but before computers things were done in a more resilient local, offline authoring + sync when convenient way that didn't result in "Sorry, computer says no. Schedule a new appointment."