The main issue in my experience are so called "user enumeration attacks", especially with sequential ids that are normally used.
This is where an attacker is able to leak information from your system just by guessing ids. If you used a sequential id then you can cycle from 1 to X and probably easily find which are valid users. You can then likely see how many valid users/ids there are and potentially pull their data if the authentication has been implemented incorrectly.
By using a UUID, the key space is so large that you cannot reasonably guess user ids. So you can't use those same brute force techniques and it makes extricating data much harder.
Obscure user IDs could be a defense in depth measure but really you need to be authorizing the data you release against the authenticated session cookie. A view meant for the user's own consumption shouldn't take a user ID at all, just pull it from the session.
This is really an ancillary benefit and not a real solution to enumeration vulnerabilities. The concern is usually over sharding the data, unique ID generation guarantees, and performance. You shouldn't really be deciding UUID vs. Int based on the possibility for ID enumeration. It's almost certainly easier to come up with a solution to slow/prevent enumeration than move from Ints to UUID.
There's also leaking financial, business data. E.g you create an order and you see your order id being 3000 the first day and 4000 the next, so then you'll be able to guess how many orders this company has etc.
The primary key serves a database function, and its data type should be optimized to suit that function. If you need an enumeration resistant id for public use, a uuid is a great idea, but it's a mistake to make the primary key in a database do double duty as a public identifier and impose inappropriate constraints on it as a consequence.
this, also there's no possibility of collision. You can't have two processes generate the same ID (which means you can generate your IDs in code and send them to the database, which is really useful in some situations).
Also, and this is the main one for me - if I mess up my code and accidentally use a document_id instead of a user_id, I'll just get "not found" instead of someone else's data.
UUID collision is not possible in practice. You can absolutely code in the absolute knowledge that your application will not generate 2 identical UUID's ever. The odds are astronomical. You have bigger concerns.
And yes, every DB backend has solved collision. But as I said, it's useful to be able to generate ID's in code sometimes. This is possible with UUID's and not possible with integers.
UUID is a loosely defined concept. There are many implementations. Some of them have no chance of collision at all, some have an astronomical chance, and some have very real odds that you'll receive a call at 3AM during the new year's celebration.
In the types of systems that need UUIDs there is probably no easy way to check for collisions. The prospect of mystery data corruption with no ability to trace it down frightens the hell out of me.
The only reason that issue was reported is because someone was actually doing the collision checking. That's not going to be the norm in UUID systems. Think about it.
Many shops (I presume, since I work at non-FAANG) have their own version of UUID generation that includes info about the machine/instance it was generated from. I don’t think we’ve formally proved it but I think ours is guaranteed unique in our fleet.
This is where an attacker is able to leak information from your system just by guessing ids. If you used a sequential id then you can cycle from 1 to X and probably easily find which are valid users. You can then likely see how many valid users/ids there are and potentially pull their data if the authentication has been implemented incorrectly.
By using a UUID, the key space is so large that you cannot reasonably guess user ids. So you can't use those same brute force techniques and it makes extricating data much harder.