Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!
It’s about 100 records per second for a year and a half, or 10,000 records per second for 5 days. Both are easily achievable. As an engineer, why would you ever knowingly design such a system when it’s trivial to not have this vulnerability in the first place.
It’s like hosting an internal app at a company that contains a SQL injection. “Well, if a hacker can access this app, then that’s a problem that needs addressing either way.” Sure, that may be true, but it’s also true that you’re not a good software engineer.
> It’s like hosting an internal app at a company that contains a SQL injection
It's nothing like that at all because the wrong SQL injection can completely ruin people's lives due to leaking stuff it shouldn't whereas the worst an int exhaustion can do is bring some app offline. Whoopdie-doo. Okay, that's not brilliant, but it's not comparable at all.
And there's a reason there aren't tons of "int exhaustion attacks": because there's little point in doing so.
So taking down the apps ability to insert any rows into the table (and hence breaking the app) isn’t going to impact anyone? Including the apps ability to make money?
This does happen and break people. You usually don’t hear about it (except on the SRE side) because it is so obvious when it happens to someone they really don’t like talking about it.
I never said it's "not going to impact anyone", I said it's not comparable.
"Denial of service" is just not a security bug on the same level as "zomg my credit card is stolen". I have argued this many times before, e.g. https://news.ycombinator.com/item?id=39377795 – It's easy to knock most sites offline regardless just by flooding it with traffic, don't need long-term plans like this (which are likely to be detected before it even has effect).
> This does happen and break people
I have never heard about a deliberate attack using this method.
That's not a great analogy. SQL injection is a completely binary issue. A single malicious statement that gets through can do all the damage.
Defending against denial of service attacks is a gradual, multifaceted problem that is pretty unlikely to hinge on the size of database keys.
If your system is dimensioned to serve a few hundered users then it's not going to be a whole lot more robust if it can theoretically create thousands of trillions of user records rather than just hundereds of millions.
In fact, infinite scalability is a risk in its own right because it can bankrupt you, which is arguably worse than a temporary outage.
That said, I tend to use 64 bit IDs by default as well unless there is a good reason not to (because, you know, what if I'm the next Google?!?).
Conversely: if using bigserial for a primary key introduces crippling performance problems to your system, maybe that is a problem you need to address either way.
That’s why the original comment suggested both bigserial and a separate UUID for public exposure. More to the point the person I was replying to said:
> IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows
The implication I took from that was that they were suggesting using serial over bigserial. My comment was pushing back on that.
My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in.
Thus, not only would DB ids in principle never show in the UI, they wouldn't even be stable between sessions.
This would protect against a number of security attacks. (I don't know what I'd do about bookmarking, though I suspect most apps have a 80/20 solution they could deploy.) DB ids going out to a user really ought to be considered at least a smell, but it's pretty ingrained into our culture.
I wrote that style of session mapping for a project long ago. It was fairly easy, but a massive pain in the ass to debug. Ended up needing to record the mappings in the backend for a period of time.
I too coded that in my server. It's not hard to do, and debugging can be harder at times (but not excessively so.) As you say it's just a case of logging when debugging.
When anchors are needed I use a different column for that- not the primary index. (Usually some other unique, already-present, value like the sku or whatever.
The security implications though are substantial. I don't leak primary key info even if the PK is serialized. (These days I'm all-in on UIDS but I have older legacy systems to deal with too.)
That wouldn't work for integration scenarios where the other system need to store the ID so it can refer to it later. How would you provide IDs for integration purposes?
