PostgreSQL and UUID as Primary Key • Hacker News Board

Re: PostgreSQL and UUID as Primary Key

#311

Post by j45 » Sat, Jul 06, 2024, 6:45 PM UTC

TeeWEE wrote: post #131

whynotmaybe wrote: post #70

Earlier quoted context omitted.

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…

Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed…

Sequential numbers can not be used publicly.

Also, security can be built around not allowing querying records which are not yours.

I'm all for a little security through obscurity including UUIDs but it shouldn't be the sole thing. Easier to generate a UUID for the sequential and let the database do what it does best (relate many serials among each other).

The other part is being able to use what's built into the database out of the box without a lot more configuration.

Selfishly, I always appreciate learning more about Postgres though :)

Re: PostgreSQL and UUID as Primary Key

#312

Post by j45 » Sat, Jul 06, 2024, 6:46 PM UTC

disneycember wrote: post #245

rezonant wrote: post #231

Earlier quoted context omitted.

As mentioned elsewhere, it ensures the ability to perform resumable and consistent batching queries across the data set without missing records. Ordering over an insertion timestamp is not enough if two records may have the same timestamp: You may miss a record (or visit a record twice) across multiple queries.

This is solved sorting by timestamp first then by random PK UUID. Don't think a little simpler batch queries justify leaking time and quantity information or complexity of handling two types of IDs.

I agree with not baking more intelligence into a piece of data than needed, especially an index.

Re: PostgreSQL and UUID as Primary Key

#313

Post by bruce511 » Sat, Jul 06, 2024, 6:51 PM UTC

sgarland wrote: post #243

bruce511 wrote: post #167

Earlier quoted context omitted.

Yes, sure, it leaks some information - but to be fair printing an invoice also leaks information. For me the priority is security. If I get a link (visible or invisible) that contains a numeric ID, there's the possibility to tweak that link with another number. Ideally, the server treats that number as suspect. Every. Single . Time. In practice I only need one developer to miss the check in one place and I have a ser…

Call me naïve, but surely you can have fuzzing tests in CI? I’m also going to use this as yet another example of why getting rid of QA in favor of Ship It Now was a bad idea.

Relying on tests to discover the tortuous path to most security exploits would not be my recommendation.

Re: PostgreSQL and UUID as Primary Key

#314

Post by j45 » Sat, Jul 06, 2024, 7:10 PM UTC

timmytokyo wrote: post #303

thiht wrote: post #200

Earlier quoted context omitted.

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

I don't get it either. If UUIDv7 lacks security due to its revelation of a timestamp, why don't bigserials also lack security? After all, given a bigserial ID, you can tell whether it was generated before or after some other bigserial ID and thereby infer something about the time it was generated.

BigSerials come from a relational database world for me.

The use of UUIDs for documents may come from a no-sql background.

I use bigserials for relational data in relational databases, and if there is a unique document value needed, a UUID is good.

Re: PostgreSQL and UUID as Primary Key

#315

Post by westurner » Sat, Jul 06, 2024, 7:29 PM UTC

Merad wrote: post #299

westurner wrote: post #284

Earlier quoted context omitted.

The question is the same; why would you use bigint instead of the native UUID type? Why does OT compare text and UUID instead of char(32) and UUID? What advantage would there be for database abstraction libraries like SQLalchemy and Django to implement the UUID type with bigint or bigserial instead of the native pg UUID type?

Best practice in Postgres is to use always use the text data type and combine it with check constraints when you need an exact length or max length. See: https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage Also, I think you're misunderstanding the article. They aren't talking about storing a uuid in a bigint. They're talking about have two different id's. An incrementing bigint is used internally within the d…

What needs to be stored as text if there is a native uuid type?

Chapter 8. Data Types > Table 8.2. Numeric Types: https://www.postgresql.org/docs/current/datatype-numeric.htm... :

> bigint: -9223372036854775808 to +9223372036854775807

> bigserial: 1 to 9223372036854775807

2*63 == 9223372036854775807

Todo UUID /? postgres bigint UUID: https://www.google.com/search?q=postgres+bigint+uuid :

- UUIDs are 128 bits, and they're unsigned, so: 2*127

- "UUID vs Bigint Battle!!! | Scaling Postgres 302" https://www.scalingpostgres.com/episodes/302-uuid-vs-bigint-...

"Reddit's photo albums broke due to Integer overflow of Signed Int32" https://news.ycombinator.com/item?id=33976355#33977924 re: IPv6 addresses having 64+64=128 bits

FWIW networkx has an in-memory Graph.relabel_nodes() method that assigns ints to unique node names in order to reduce RAM utilization for graph algorithms: https://networkx.org/documentation/stable/reference/generate...

Re: PostgreSQL and UUID as Primary Key

#316

Post by mewpmewp2 » Sat, Jul 06, 2024, 8:12 PM UTC

sgarland wrote: post #242

mewpmewp2 wrote: post #163

Earlier quoted context omitted.

But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default. I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.

See other reply; I don’t believe that exposing this is as big an issue as people think. But even with that, there’s also no reason to do so. Internal ID friendly to the DB and eternal, random ID that does get exposed is a common practice. Or use JWE/JWT, and never show either.

I don't know. Is it not? I frequently check out of curiosity when I'm buying something the order numbers and similar things that might of interest, if I notice integers. I imagine if you become a public company that would be very sensitive information in terms of the company is doing, and there would be strong push to check for that data, to get an advantage in the stock market.

It doesn't seem right to me to expose with such ease how many sales you are doing. It's definitely not intentional to expose it.

Re: PostgreSQL and UUID as Primary Key

#317

Post by [deleted] » Sat, Jul 06, 2024, 8:40 PM UTC

riku_iki wrote: post #288

arp242 wrote: post #205

Earlier quoted context omitted.

No, my point is you accused me of lying at the drop of a hat for no good reason, and that this is demonstrably not true. Don't try and spinelessly slime your way out of that. And no, they I won't run out of IDs for them. "Potentially" anything can happen, but it won't happen here. After five year the furthest sequence is 0.86% on its way of being full.

> my point is you accused me of lying at the drop of a hat for no good reason I said you described fictional scenario, and it appears to be true, your table ID fields are very different than timezone, country and user_id.

[deleted]

Re: PostgreSQL and UUID as Primary Key

#318

Post by Merad » Sat, Jul 06, 2024, 8:46 PM UTC

westurner wrote: post #315

Merad wrote: post #299

Earlier quoted context omitted.

Best practice in Postgres is to use always use the text data type and combine it with check constraints when you need an exact length or max length. See: https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage Also, I think you're misunderstanding the article. They aren't talking about storing a uuid in a bigint. They're talking about have two different id's. An incrementing bigint is used internally within the d…

What needs to be stored as text if there is a native uuid type? Chapter 8. Data Types > Table 8.2. Numeric Types: https://www.postgresql.org/docs/current/datatype-numeric.htm... : > bigint: -9223372036854775808 to +9223372036854775807 > bigserial: 1 to 9223372036854775807 2*63 == 9223372036854775807 Todo UUID /? postgres bigint UUID: https://www.google.com/search?q=postgres+bigint+uuid : - UUIDs are 128 bits, and the…

Many people store UUID's as text in the database. Needles to say, this is bad. TFA starts by proposing that it's bad, then does some tests to show why.

I'm not quite sure what all the links have to do with the topic at hand.

Re: PostgreSQL and UUID as Primary Key

#319

Post by arp242 » Sat, Jul 06, 2024, 8:47 PM UTC

groestl wrote: post #266

arp242 wrote: post #158

Earlier quoted context omitted.

A million rows is quite small. A string will use 36 bytes per row. bigserial will use 8 bytes per row. At 4 billion rows that's about 100G. Now imagine a row with 3 foreign keys to other tables with string UUIDs and you're wasting 300G (vs UUID type) or 400G (vs. bigserial), for no good reason. And doing things like "where id = ?" will be slower. You will be able to keep fewer rows cached in memory. Etc. It's absolut…

> And migrating all of this later on can be a right pain so it's worth getting it right up-frong I've never had to move from uuids to integers. I've had to move from integers to uuids plenty of times though.

I've migrated tables to more compact formats. Inefficient storage is the sort of of thing that works fine for a lot of things, right up to the point where you're running out of disk space or memory and it's no longer fine.

I'm not against UUIDs nor saying you should optimize everything, I'm just saying you should think about things, and that thinking about things really isn't that time-consuming or that much effort.

Re: PostgreSQL and UUID as Primary Key

#320

Post by rezonant » Sat, Jul 06, 2024, 8:50 PM UTC

disneycember wrote: post #245

rezonant wrote: post #231

Earlier quoted context omitted.

As mentioned elsewhere, it ensures the ability to perform resumable and consistent batching queries across the data set without missing records. Ordering over an insertion timestamp is not enough if two records may have the same timestamp: You may miss a record (or visit a record twice) across multiple queries.

This is solved sorting by timestamp first then by random PK UUID. Don't think a little simpler batch queries justify leaking time and quantity information or complexity of handling two types of IDs.

You wouldn't expose the numeric IDs publically, and ideally you'd use your database's automatic ID selection to avoid any complexity.

The UUID sorting works in the common case, but if you happen to end your batch near the current time, you still run the risk of losing a few records if the insert frequency is sufficiently high. Admittedly this is only a problem when you are batching through all the way to current insertions.