Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Avoid UUID Version 4 Primary Keys in Postgres

andyatkinson.com

Post reply on HN
Print view View original

351–360 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#351

Post by Dylan16807 »

lovich wrote: post #340
jonny_eh wrote: post #333

Earlier quoted context omitted.

To add to that, birthdays can clump, just like any seemingly "random" data.

A million dots scattered randomly over a graph can all land on the exact same coordinate if it’s truly random. What most people intuit as random is some sort of noise function that is generally dispersed and doesn’t trigger the pattern matching part of their brain

> A million dots scattered randomly over a graph can all land on the exact same coordinate if it’s truly random.

It won't happen though. 0.00000000% chance it happens even once in a trillion attempts.

> What most people intuit as random is some sort of noise function that is generally dispersed and doesn’t trigger the pattern matching part of their brain

Yes, people intuit the texture of random wrong in a situation where most buckets are empty. But when you have orders of magnitude more events than buckets, that effect doesn't apply. You get pretty even results that people expect.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#352

Post by PunchyHamster »

mkleczek wrote: post #89
vintermann wrote: post #65

A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…

This is actually a very deep and interesting topic. Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. So, what happens next is that the real world tries to adjust and the "data-less" identifier becomes a real world artifact. The situation becomes the same but…

Identifier is just "a piece of common token system can use to operate on same entity.

You need it. Because it's maybe one lone unchangeable thing. Taking person for example: * date of birth can be changed, if there was error and correction in documents * any and near all of existing physical characteristics can change over time, either due to brain things (deciding to change gender), aging, or accidents (fingerprints no longer apply if you burnt your skin enough) * DNA might be good enough, but that's one fucking long identifier to share and one hard to validate in field.

So an unique ID attached to few other parts to identify current iteration of individual is the best we have, and the best we will get.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#353

Post by danudey »

vintermann wrote: post #65

A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…

Perhaps you can clarify something for me, because I think I'm missing it.

> Norwegian PNs have your birth date (in DDMMYY format) as the first six digits

So presumably the format is DDMMYYXXXXX (for some arbitrary number of X's), where the XXX represents e.g. an automatically incrementing number of some kind?

Which means that if it's DDMMYYXXX then you can only have 1000 people born on DDMMYY, and if it's DDMMYYXXXXX then you can have 100,000 people born on DDMMYY.

So in order for there to be so many such entries in common that people are denied use of their actual birthday, then one of the following must be true:

1. The XXX counter must be extremely small, in order for it to run out as a result of people 'using up' those Jan 1 dates each year

2. The number of people born on Jan 1 or immigrating to Norway without knowledge of their birthday must be colossal

If it was just DDMMXXXXX (no year) then I can see how this system would fall apart rapidly, but when you're dealing with specifically "people born on Jan 1 2014 or who immigrated to Norway and didn't know their birthday and were born on/around 2014 so that was the year chosen" I'm not sure how that becomes a sufficiently large number to cause these issues. Perhaps this only occurs in specific years where huge numbers of poorly-documented refugees are accepted?

(Happy to be educated, as I must be missing something here)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#354

Post by x0x0 »

stickfigure wrote: post #337
x0x0 wrote: post #281

Earlier quoted context omitted.

It's not leaking that's the concern. It's that not having the names of objects be easily enumerable is a strongly security-enhancing feature of a system. Yes of course everyone should check and unit test that every object is owned by the user or account loading it, but demanding more sophistication from an attacker than taking "/my_things/23" and loading "/my_things/24" is a big win.

With a single sequence and a busy system, the ids for most high-level tables/collection are extremely sparse. This doesn't mean they can't be enumerated, but you will probably notice if you suddenly start getting hammered with 404s or 410s or whatever your system generates on "not found". Also, if most of your endpoints require auth, this is not typically a problem. It really depends on your application. But yes, tha…

> Also, if most of your endpoints require auth, this is not typically a problem.

Many systems are not sparse, and separately, that's simply wrong. Unguessable names is not a primary security measure, but a passive remediation for bugs or bad code. Broken access control remains an owasp top 10, and idor is a piece of that. Companies still get popped for this.

See, eg, google having a bug in 2019, made significantly less impactful by unguessable names https://infosecwriteups.com/google-did-an-oopsie-a-simple-id...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#355

Post by maxbond »

vintermann wrote: post #65

A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…

> Permanent identifiers should not carry data.

Do you have the same criticism for serial identifiers? How about hashes? What about the version field in UUIDs?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#356

Post by PunchyHamster »

liuliu wrote: post #307
sgarland wrote: post #144

Earlier quoted context omitted.

> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseco…

I read it (and regret it is a waste of my time). Their arguments are: * integer keys are faster; * uuidv7 keys are faster; * if you want obfuscated keys, using integer and do some your own obfuscation (!!!). I can get on-board of uuidv7 (with the trade-off, of course, on stronger guessability). The integer keys argument is strange. At that point, you need to come up with a custom-built system to avoid id collision in…

> (with the trade-off, of course, on stronger guessability).

you're not guessing 2^72 bit random number. And if guessing UUID does something in your app, you already fucked up

Re: Avoid UUID Version 4 Primary Keys in Postgres

#357

Post by PunchyHamster »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

UUIDv7 is not monotonic tho

Re: Avoid UUID Version 4 Primary Keys in Postgres

#358

Post by whynotminot »

dpark wrote: post #290
konart wrote: post #280

Earlier quoted context omitted.

You have one ID as a primary key. It is used for building relations in your database. The second ID has nothing to do with internal structure of your data. It is just another field. You can change your structure however you want (or type of your "internal" IDs) and you don't have to worry about an external consumer. They still get their artificial ID.

So what you meant is not to expose the primary key? That’s a more reasonable statement but I still don’t agree. This feels like one of those “best practices” that people apply without thinking and create pointless complexity. Don’t expose your primary key if there is a reason to separate your primary key from the externally-exposed key . If your primary key is the form that you want to expose, then you should just ex…

> create pointless complexity

My exact thought.

A lot else has failed in your system, from access control to API design, if this becomes a problem. Security by obscurity isn’t the answer.

If the only thing between an attacker and your DB is that they can’t guess the IDs you’re already in some serious trouble.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#359

Post by lukeschlather »

sgarland wrote: post #316
lukeschlather wrote: post #241

Earlier quoted context omitted.

If a sequential ID is exposed to the client, the client can trivially use it to determine the number of records and the relative age of any records. UUID solves this, and the use of a cryptographically secure number generator isn't really necessary for it to solve this. The author's scheme might be similarly effective, but I trust UUIDs to work well. There are obviously varying ways to hide this information other tha…

I have never seen anyone post an actual example of the German Tank problem creating an issue for them, only that it’s possible. > I don’t have to think about it And here we have the main problem of most DB issues I deal with on a daily basis - someone didn’t want to think about the implications of what they were doing, and it’s suddenly then my emergency because they have no idea how to address it.

If you can predict user IDs this is extremely useful when you're trying to come up with an exploit that might create a privileged user, or perhaps you can create some object you have access to that is owned by users that will be created in the near future.

When I say "I don't have to think about it" I mean I don't have to think about the ways an attacker might be able to predict information about my user ids which they could use to gain access to accounts, because I know they cannot predict information about user ids.

You are dismissing the implications of using something that is less secure than UUIDs and you haven't convinced me I'm the one failing to think through the implications. I know there are performance problems, I know they might require some creative solutions. I am not worried about unpredictable performance issues, I am worried about unpredictable security problems.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#360

Post by sgarland »

lukeschlather wrote: post #359
sgarland wrote: post #316

Earlier quoted context omitted.

I have never seen anyone post an actual example of the German Tank problem creating an issue for them, only that it’s possible. > I don’t have to think about it And here we have the main problem of most DB issues I deal with on a daily basis - someone didn’t want to think about the implications of what they were doing, and it’s suddenly then my emergency because they have no idea how to address it.

If you can predict user IDs this is extremely useful when you're trying to come up with an exploit that might create a privileged user, or perhaps you can create some object you have access to that is owned by users that will be created in the near future. When I say "I don't have to think about it" I mean I don't have to think about the ways an attacker might be able to predict information about my user ids which th…

Perhaps this is my bias coming through. I work with DBs day in and day out, and the main problem I face is performance from poorly-designed schemas and queries; next largest issue is referential integrity violations causing undefined behavior. The security issues I’ve found were all people doing absurdly basic stuff, like exposing an endpoint that dumped passwords.

To me, if you’re relying on having a matching PK as security, something has already gone wrong. There are ways to provide AuthN and AuthZ other than that. And yes, “defense in depth,” but if your base layer is “we have unguessable user ids,” IME people will become complacent, and break it somewhere else in the stack.

Post reply on HN