When using v7, I need some sort of audit that checks in every API contract for the usage of v7 and potential information leakage.
Detecting V7 uuids in the API contract would probably require me to enforce a special key name (uuidv7 & uuid for v4) for easier audit.
Engineers will get this wrong more than once - especially in a mixed team of Jr/sr.
Also, the API contracts will look a bit inconsistent: some resources will get addressed by v7, others by v4. On top, by using v4 on certain resources, I'd leak the information that those resources addressed by v4 will contain sensitive information.
By sticking to v4, I'd have the same identifier for all resources across the API. When needed, I can expose the creation timestamp in the response separately. Audit is much simpler since the fields state explicitly what they will contain.
UUIDv4 is explicitly forbidden in some high-reliability/high-assurance environments because there is a long history of engineers using weak entropy sources to generate UUIDv4 despite the warnings to use a strong entropy source, which is only discovered when it causes bugs in production. Apparently some engineers don't understand what "strong entropy source" means.
Mixing UUID types should be detectable because type is part of the UUID. But then many companies have non-standard UUID that overwrite the type field mixed with standard UUID across their systems. In practice, you often have to treat UUID as an opaque 128-bit integer with no attached semantics.
Unless I'm missing something, check it on receipt, and reject it if it doesn't match. `uuid.replace("-", "")[12]` or `uuid >> 76 & 0xf`.
Regardless of difficulty, this comes down to priorities. Potential security concerns aside (I maintain this really does not matter nearly as much as people think for the majority of companies), it's whether or not you care about performance at scale. If your table is never going to get over a few million rows, it doesn't matter. If you're going to get into the hundreds of millions, it matters a great deal, especially if you're using them as PKs, and doubly so if you're using InnoDB.
Good luck if you're operating at a decent scale, and need to worry about db maintenance/throughput. Ask the DBA at your company what they would prefer.
>> So this basically defeats the entire performance improvement of UUIDv7. Because anything coming from the user will need to look up a UUIDv4, which means every new row needs to create an extra random UUIDv4 which gets inserted into a second B-tree index, which recreates the very performance problem UUIDv7 is supposedly solving.
> This is only really true if leaking the creation time of the record is itself a security concern.
No, as "leaking the creation time" is not a concern when API's return resources having properties representing creation/modification timestamps.
Where exposing predictable identifiers creates a security risk, such as exposing UUIDv7 or serial[0] types used as database primary keys, is it enables attackers to be able to synthesize identifiers which match arbitrary resources much quicker than when random identifiers are employed.
0 - https://www.postgresql.org/docs/current/datatype-numeric.htm...
If your security relies on attacker don't know your ID (you don't do proper data permission check), your security is flawed.
Primary keys using UUID v7 are (potentially) an HR violation.
https://mikenotthepope.com/primary-keys-using-uuid-v7-are-po...
If it’s the latter (which, reading wikipedias summary suggests it is), then the entire premise that k-sortable uuids are a “HR violation” is bunk.
The problem with arguing about timestamps leaking this kind of information is that _anything_ can leak this kind of vaguely dated information.
- Seen on a website that ceased to exist after 2010? Gotchya!
- Indexed by Waybackmachine? Gotchya!
- Used <different uuid scheme> for records created before 2022? Gotchya!
The only way to prevent divulging temporal clues about an entity is to never reveal its existence in any kind of correlatable way (which, as far as I’m prepared to think right now, seems to defeat the point of revealing it to a UI at all).
I submit my application in 2025 and get rejected.
20 years later I submit another application to the same company, using my existing 20 years old user profile, and now get rejected because somebody figures out I'm old by looking at my user id?
I don't understand why you considered UUIDv7 in the first place.
UUIDv4 removes all three of those vectors. UUIDv7 still removes two of three. It doesn't leak record count or the rate at which you create them, only creation time. And you still can't guess adjacent keys. It's a pretty narrow information leakage for something you routinely reveal on purpose.
I can see it being bad for tracking IDs, but not order IDs, unless you are allowed to view any orders that do not belong to your account, which is just fundamentally bad security and using UUIDv4 or a random string would simply be obscuring security.
With UUIDv7 the creation time is always leaked without any sampling. A casual attacker could quite easily lookup the time and become motivated in probing and linking the account further
You wouldn't be publishing patient visits publically, the only folks that'd legitimatly see that record would be those which access to that visit, and they'd most likely need to know the time of said visit. This access should be controlled via AuthN, AuthZ and audited.
You'd also generally do a lot of time-based lookups on this data; what visits do I have today, this week, and so on. You might also want an additional DateTime field for timezones and offsets, but the v7 is probably better than v4 for this usecase.
Always thought that was elegant (the attach not using the time as the seed).
Knowing approximate age is a relatively small leak compared to that.
Bank security does not depend on your bank account being private information. Pretty much all bank security rounds to the bank having a magic undo button, so they can undo any bad transactions after it comes to light that it was a bad transaction. Sure they do some filtering on the front-end now to eliminate the need to use the magic undo button, but that's just extra icing to keep the undo button's use to a dull roar.
There was previously an article linked here about recovering access to some bitcoin by feeding all possible timestamps in a date range to the password creation tool they used, and trying all of those passwords.
A UUIDv7 primary key seems to reduce / eliminate those problems.
If there is also an indexed UUIDv4 column for external id, I suspect it would not be used as often as the primary key index so would not cancel out the performance improvements of UUIDv7.
[1] https://www.cybertec-postgresql.com/en/unexpected-downsides-...
Very true, as detailed by the link you kindly provided. Which is why a technique I have found useful is to have both an internal `id` PK `serial`[0] column (never externalized to other processes) and another column with a unique constraint having a UUIDv4 value, such as `external_id`, explicitly for providing identifiers to out-of-process collaborators.
0 - https://www.postgresql.org/docs/current/datatype-numeric.htm...
That doesn't matter because it's the creation of the index entry that matters, not how often it's used for lookup. The lookup cost is the same anyways.
> Since workloads commonly are interested in recently inserted rows
That's only true for very specific types of applications. There's nothing general about that.
Plenty of applications grab rows from all time, and there's nothing special about the most recent ones. The most recent might also be the least popular rows, since few things reference them.
There is no need to put the privacy preserving ID in a database index when you can calculate the mapping on the fly
You put in 128 bits, you get out 128 bits. The encryption is strong, so the clients won't be able to infer anything from it, and your backend can still get all the advantages of sequential IDs.
You also can future-proof yourself by reserving a few bits from the UUID for the version number (using cycle-walking).
If your UUIDv4 is cached, your still suffering from extra storage and index. Not a issue on a million row system but imagine a billion, 10 billion.
And what if its not cached. Great, now your hitting the disk.
Computers do not suffering from lacking CPU performance, especially when you can deploy CPU instruction sets. Hell, you do not even need encryption. How about making a simple bit shift where you include a simple lookup identifier. Black box sure, and not great if leaked but you have other things to worry about if your actual shift pattern is leaked. Use extra byte or two for iding the pattern.
Obfuscating your IDs is easy. No need for full encryption.
If it does matter for your application, then don't expose it - use an opaque id with something like AEAD, and expose that.
You probably shouldn't / don't need to use v7 for your Users table because the age of your User probably has limted to no bearing on the look up patterns. For example, our Steam and Amazon accounts are pretty old, but we likely still use them.
However, your Orders table is significantly more likely to be looked up based on time, so a v7 makes a lot of sense here.
Now I'd argue the security implications are overblown, but in general tems you might also allow someone to look up a user, i.e. you can view my Steam profile, or maybe my Amazon wishlist. You probably don't need to be looking up another Users Order.
Alternativly, if your building an Enterprise Risk Solution, you could take a view that you don't want people knowing how old the risk is, but most solutions would show you some history and would believe that to be pertinent information.
There will be instances of getting it wrong, but it isn't actually _that_ complicated.
And then having uuidv7 as primary and foreign keys, can give you a performance gain.
The id would be exposed to users. An integer would expose the number of records in it.
Am I using right guys?
IMO, a major problem solved by UUIDs is the ability to create IDs on the client-side, hence, they are inherently user-facing. A major reason why this is an important use case for UUIDs is because it allows clients to avoid accidental duplication of records when an insertion fails due to network issues. It provides insertion idempotence.
For example, when the user clicks on a button on a form to insert a record into a database, the client can generate the UUID on the client-side, then attach it to a JSON object, then send the object to the server for insertion; in the meantime, if there is a network issue and it's unclear whether or not the record was inserted, the code can automatically retry (or user can manually retry) and there is no risk of duplication of data if you use the same UUID.
This is impossible to do with auto-incrementing IDs because those are generated by the database in a centralized way so the user cannot know the ID head of time and thus, if there is a network failure while submitting a form, the client cannot automatically know whether or not the record was successfully inserted; if they retry, they may create a duplicate record in the database. There is no way to make the operation idempotent without relying on some kind of fixed ID which has a uniqueness constraint on the database side.
I'm sure there might be a middle ground where most of the performance gains remain but the deanonymizing risk is greatly reduced.
Edit: encrypting the value in transit seems a simpler solution really
So this basically defeats the entire performance improvement of UUIDv7. Because anything coming from the user will need to look up a UUIDv4, which means every new row needs to create an extra random UUIDv4 which gets inserted into a second B-tree index, which recreates the very performance problem UUIDv7 is supposedly solving.
In other words, you can only use UUIDv7 for rows that never need to be looked up by any data coming from the user. And maybe that exists sometimes for certain data in JOINs... but it seems like it might be more the exception than the rule, and you never know when an internal ID might need to become an external one in the future.