On the one hand, a serial base id like https://example.com/user/items/55, seems less secure than a UUID based approach as if all else fails the UUID is still hard to guess. On the other, some systems (github private repos for example), don't seem to worry about how easily a URL can be guessed. Is it perhaps the case that probability of successful attack is not materially reduced in with a hard to guess url in the final analysis?
Unexpected downsides of UUID keys in PostgreSQL
61–70 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#62Earlier quoted context omitted.
It's a good option purely for cosmetics but don't rely on it for any kind of serious obscurity since it's trivially reversed. I've used it to great effect in the past to encode multiple integer values like start/end ID, sort order (0/1), etc. for cursor-based pagination. But that's only because there's nothing secret in those numbers. Just purely for convenience.
Salting your hash should work? You could also use a 32 or 64 bit block cipher like skip32 if you want to prevent reversal. Or at least, it makes reversing non-trivial.
This is a common misconception. hashids is _not_ an encryption algorithm. It's just an encoding with a tiny bit of obscurity layered in.
hashids.org:
> Do not encode sensitive data. This includes sensitive integers, like numeric passwords or PIN numbers. This is not a true encryption algorithm. There are people that dedicate their lives to cryptography and there are plenty of more appropriate algorithms: bcrypt, md5, aes, sha1, blowfish.
Re: Unexpected downsides of UUID keys in PostgreSQL
#63I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
[deleted]
Re: Unexpected downsides of UUID keys in PostgreSQL
#64Earlier quoted context omitted.
I don't have a problem with UUIDv7 but I wish they had created it as a completely separate standard after UUIDv4... The version number increases imply that previous versions have been superseded but in fact, they just have different priorities... And TBH I'm concerned that UUIDv1 had a timestamp, then it was removed completely in UUIDv4 and now the timestamp concept is being added back to UUIDv7... There are legitima…
These are not versions in the sense that one supersedes the other. More like variants.
I hope devs and security consultants will see it that way.
I can easily envision a future where security consultants would write reports flagging systems as 'unsecure' on the grounds that they use 'an outdated UUID version'.
Security consultants can be quite blunt in their approach and companies will often yield to their every demand for the sake of easy compliance and to avoid having to explain stuff.
Re: Unexpected downsides of UUID keys in PostgreSQL
#65How can this be unexpected? Isn't this what Percona discussed for MySQL already several years ago? Or am I missing something?
They suggest to use UUID v7 at the end for more sequential keys. Maybe that's the new part since v7 is relatively recent.
MSSQL has had sequential GUIDs for over a decade to solve this very problem, surprised it wasn't done sooner.
Re: Unexpected downsides of UUID keys in PostgreSQL
#66Earlier quoted context omitted.
Recently, I wrote a client-side wallet app for cryptocurrency use case and it required transactions to be created and signed entirely on the client-side and so the transaction UUID had to be created on the client side (before signing) and I was surprised at how elegant and simple the application's logic turned out to be (both on the front end and back end). One of the best things about UUIDs is that if your front end…
You can achieve this without UUID keys if you attach UUIDs to the requests. This has the advantage of working for all kinds of mutating requests, not just creates, but requires that you store these request UUIDs for some period of time as well.
It's still not as elegant because it doesn't work in a multi-process or multi-server back end; if the request fails, you need to make sure that the retry request will hit the same server process (assuming you're keeping the UUIDs in memory on the server). You can use some in-memory data store like Redis to share the request UUIDs across multiple processes/hosts but that can add a lot more complexity, latency and risks and is just not worth it IMO.
Even in a single-host setup, it can be a problem because what happens if your process crashes and restarts just after a resource was created in the database but before the ID was sent to the client (with success message)? You would end up with a duplicate record in your DB after the retry since your newly restarted server would not have the UUID in its memory (even though the resource was in fact already created on the server a few milliseconds before the last crash).
With the Redis (or similar) solution, you need to make sure that the request UUIDs expire after some time and are cleaned up to avoid memory bloat which is a pain... I mean that complex solution probably uses up a lot more resources than just using UUIDs in the database as IDs.
Re: Unexpected downsides of UUID keys in PostgreSQL
#67UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…
I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed…
> Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary
How soon is soon? And how would you handle it if you already had all PKs as UUIDs
Re: Unexpected downsides of UUID keys in PostgreSQL
#68Earlier quoted context omitted.
I'm not asking about the uniqueness but about ordering across different clients.
There is probably an impossibility theory somewhere for two clients generating ordered ID without communicating with each other. What if one client was travelling near the speed of light for example? It isn't obvious what order would be correct since the two clients would have wildly different perceptions of time.
Yes, it’s impossible in theory. Same as the two generals problem, if I’m not mistaken.
Re: Unexpected downsides of UUID keys in PostgreSQL
#69UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…
I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed…
Well, I'll give another take.
If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by the fact that you need legal holds to comply with various federal laws.
Between the work involved in using two identifiers, one for joins and one for external lookups, versus the work involved in manually coding up all sorts of erasure work arounds, something I was in charge of in the past, I would strongly consider just using two IDs.
If your ORM gets in the way, just modify the ORM. This is easier than you'd think. For example, in Django just make a helpers module with something like this:
class OurModel(Model):
def get(...):
Re: Unexpected downsides of UUID keys in PostgreSQL
#70Is this a downside of UUID keys in general, or of using them as indexes? Would it be possible to create a primary index on a combination of inserted date and uuid and get opaque UUIDs and good indexing?
Yes, it's a downside of using them in b-tree indexes. However, the purpose of having an ID is usually to use it for lookup (from foreign keys, query string arguments, ...). If you create an index on two columns, you won't be able to lookup using only the second value, at least not in Postgres.
That's interesting - I don't know enough about this! If it's lookup from a foreign key, won't it just go straight to the record? What's the index for there?