4 ms·
The solution is to stop using auto-incrementing IDs generated by the database, and UUIDs are not the only answer. It is trivial to have an app request and rese
by manigandham 9y ago
The solution is to stop using auto-incrementing IDs generated by the database, and UUIDs are not the only answer.
It is trivial to have an app request and reserve a pool of IDs and assign from them as needed. It guarantees no overlap, is completely distributed, requires no db roundtrip to insert, can remain as numeric data, allows every row to be uniquely identified across tables or databases, still has loose ordering, and it only requires a system with atomic increments to implement.
- twic 9y ago> It is trivial to have an app request From where? If the counter for this pool of IDs lives in the database being rolled back, it is vulnerable in the same way as auto-incrementing IDs. If it doesn't, it isn't - but the safety you're getting comes simply from the fact that it doesn't get rolled back, not the rest of the machinery. It would be enough to make sure you don't roll back autoincrement counters when rolling back the rest of the database. That said, i am a fan of the approach you outline. I learned it from the IdGenerator in ATG Dynamo (c. 2500 - 2200 BC): https://docs.oracle.com/cd/E23095_01/Platform.93/ATGProgGuide/html/s0908idgenerators01.html https://docs.oracle.com/cd/E23095_01/Platform.93/ATGProgGuid...
- manigandham 9y agoYou're right, it's an external system. Left it out since a key/value db is super simple to operate and very cheap by just using dynamo/cosmos db/cloud datastore to maintain it globally. All the benefits help offset the extra dependency and make database backups much simpler, especially since every row has a unique id.
- geofft 9y agoIs there a reason to do this other than security against DB rollbacks? It seems like a lot of complexity to add another stateful (micro?)service just to solve this problem. It's certainly trivial to implement—it's probably even interview-sized—but it doesn't sound trivial at all to operate, since it has reliability, scalability, security, and possibly NIH concerns. (Am I building this on top of another database? On top of a Paxos of some sort? What happens during an extended network partition?)
- manigandham 9y agoAll you need is a basic key/value store. Zookeeper/consul/etc if that's already running, or use the numerous cloud services where it would costs pennies to have a globally replicated key/value db. Increment the "ids" key by Y amount, get the current number N, and you now have N - Y as the range to work with. No paxos or network splits to worry about, and far better performance since its 1 call to get a batch of ids.
- geofft 9y agoIf I already have a high-availability key-value DB with persistence, sure - but probably I don't, most designs I've seen for SQL + KV on top involve using it as a pure cache with explicit permission to drop the KV store instead of restoring that to a disk snapshot whenever something goes funny. And for this use case (robustness against reverting the SQL database), making the KV store a mere cache of data in the SQL database defeats the point entirely. And, honestly, most sites I've seen don't need the cache, they can just run on top of the SQL database itself. The fact that you're not serving hundreds of queries per second doesn't mean you don't need security. Having run Zookeeper, "No paxos or network splits" isn't really how I would describe it. (Nor "super simple to operate.") When it works, it works well, but it's another thing that you need to operate. Adding a dependency on a cloud service just to supply autoincrement doesn't really sound like a great solution. Especially because, by the nature of the problem, I am outsourcing auth to the cloud service: a malicious service can pretty easily issue themselves token for an arbitrary user. And if I'm not already comfortable outsourcing auth, e.g., because everything is in this cloud already, it doesn't seem like this actually solves my original problem which was increasing security.
- manigandham 9y agoI think you're misunderstanding. Use postgres or whatever database you want, just generate the ids separate in your app and use a simple key/value store to maintain the counter. The SLAs of a something like Azure CosmosDB is several times higher than any app you can build yourself. None of this is directly related to security. The issue for them was putting ids in the tokens, and there's no way to solve that other than not doing it. However if they didn't reuse ids, then it wouldn't have mattered, and that part can be solved by moving the id generation out of the database so that backup/restores of the database are simpler. This is in addition to all the other benefits of separate id generation I first mentioned.