[laughing_snyder]

Why 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.

[btown]

One of the big things here is de-anonymization and account correlation. Say you have an application where users'/products' affiliation with certain B2B accounts is considered sensitive; perhaps they need to interact with each other anonymously for bid fairness, perhaps people might be scraping for "how many users does account X have onboarded" as metadata for a financial edge.

If users/products are onboarded in bulk/during B2B account signup, then, leaking the creation times of each of them with any search that returns their UUIDs, becomes metadata that can be used to correlate users with each other, if imperfectly.

Often, the benefits of a UUID with natural ordering outweigh this. But it's something to weigh before deciding to switch to UUIDv7.

[8organicbits]

> system's security in any way depends on the randomness of a database private key

Unlisted URLs, like YouTube videos are a popular example used by a reputable tech company.

> UUIDv7 has 6 random bytes

Careful. The spec allows 74 bits to be filled randomly. However you are allowed to exchange up to 12 bits for a more accurate timestamp and a counter of up to 42 bits. If you can get a fix on the timestamp and counter, the random portion only provides 20 bits (1M possiblities).

Python 3.14rc introduces a UUIDv7 implementation that has only 32 random bits, for example.

Basically, you need to see what your implementation does.

[bearjaws]

only 32bits, so 4 billion guesses per microsecond... Even if youtube has 1 million videos per microsecond you would never guess them before rate limits.

[8organicbits]

You're mixing a couple things. The 32 bit random occurs in the Python implementation, which uses a millisecond counter.

The numbers you provided are suspicious, but seem quite feasible to attack. 1M IDs in 4B means each guess has ~ 1-in-4000 chance. You can make 4000 requests in an hour at a one-per-second rate. A successful attack can guess one ID, it doesn't need to enumerate all of them.

[bearjaws]

Ah I was looking at the pg_uuidv7 python package.

The backwards compatibility is a wild trade off.

Either way my comment was hyperbole, but the concept is the same, 10000 records per millisecond and you get the point. For 99.999% of SQL use cases UUIDv7 is good.

I only advocate for UUID so much because 3 separate times in my career I have been the one to have to add UUIDs so we don't leak number of patients, let users scrape the site by just incrementing (amongst other protections). So much easier to just UUID everything.

[Incipient]

Not sure if this is helpful here, but you're still looking at 32 bits of randomness, regardless of the time window. Use it for anything that you feel that's enough randomness to secure - a private home video of a cat braking a cup? Sure.

File sharing endpoints for a business? No. Use another uuid4 based 'sharing uuid' that you map internally to the PK uuid7.

[rhplus]

The German Tank Problem springs to mind. While not precisely the same problem, it’s still a case where more information that necessary is leaked in seemingly benign IDs. For the Germans, they leaked production volumes. For UUID v7, you’re leaking timing and timestamps.

https://en.wikipedia.org/wiki/German_tank_problem

[andy_ppp]

The rest of the ID will be random enough that guessing it will take an extremely long time, unless all the tanks were inserted in the same microsecond of course. I’m not sure this is a security issue with UUID though!

[Hizonner]

Because 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.

[andy_ppp]

There’s every chance the API has timestamps on when it was inserted. Honestly I’d rather my data was ordered correctly than imagining the extremely rare situations that leaking the insert time is going to bring the world falling down. You usually want that information.

And I’m honestly not a fan of public services using primary keys publicly for anything important. I’d much rather nice or shorter URLs.

What might be an improvement is if you can look up records efficiently from the random bits of the UUID automatically, replacing the timestamp with an index.

[dotancohen]

The timestamp can be recovered from the UUID?

[lucideer]

> 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]

Pretty 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.

[lucideer]

You've used an analogy instead of an example to demonstrate your point: analogies can be helpful for explaining concepts but are rarely accurate enough to prove logical parity.

It would be much easier to discuss the merits of your argument if you had an example of the dangers of leaking creation timestamps for database entries.

Otherwise, carparks & database creation timestamps have nothing in common that is meaningfully relevant to your argument. You cannot just generalise all worldly concepts & call it a day.

[atomicnumber3]

The other post literally mentions using creation timestamps to judge growth rates of companies on a platform.

My analogy was meant for a reader with a modicum of ability to connect dots to better interpret the parent and aunt/uncle replies.

[lucideer]

> a reader with a modicum of ability to connect dots

Genuinely, without any snark intended: please presume I'm an idiot here because I fully acknowledge I may be missing something blatantly obvious & am just trying to understand your argument better.

> The other post

> the parent and aunt/uncle replies.

I've gone & re-read the parent / grand parent replies in this thread on the assumption I had missed something but I can't find any reference to estimating growth rates of online companies via publicly exposed db record timestamps.

Nor can I conceive of an obvious system in my head by which one would do so. I acknowledge that such a hypothetical system almost certainly exists, but it seems non-obvious (to me) & as such it's quite difficult to reason about & discuss.

[limagnolia]

Sam Walton used to fly investors in his plane over Walmart stores and ask them to count the cars in the parking lot, then he would fly them over competitors stores and ask the same. Just a fun fact about how this is a very real scenario!

[sz4kerto]

Example: if user IDs are not random but eg Bigserial (autoincremented) and they're exposed through some API, then API clients can infer the creation time of said users in the system. Now if my system is storing eg health data for a large population, then it'll be easy to guess the age of the user. Etc. This is not a security problem, this is an information governance problem. But it's a problem. Now if you say that I should not expose these IDs - fine, but then whatever I expose is essentially an ID anyway.

[andy_ppp]

I really don’t think using primary keys publicly is ever good, just because UUID4 has allowed people to smash junk into the URL doesn’t mean it’s good for the web or the users over a slug or a cleaner ID.

[echelon]

Depends how much entropy is in your primary keys.

If your primary keys are monotonic or time based, bad actors can simply walk your API.