4 ms·

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the

by hobs 2y ago

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.

The_Colonel 2y ago
I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.
- crabmusket 2y ago
  I love it when my competitors use sequential integer IDs.
- throwaway8486k 2y ago
  Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
  - The_Colonel 2y ago
    I'm not saying it's a bad practice, the opposite actually. > I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key. It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
    - throwaway8486k 2y ago
      > I'm not saying it's a bad practice, the opposite actually I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have. I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer > It's not an easy thing to add into an existing application, though I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints. If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
  - yellowapple 2y ago
    > I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
- deleted 2y ago
  [deleted]
- inopinatus 2y ago
  UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit. Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
  - The_Colonel 2y ago
    Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.
- hobs 2y ago
  Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"

by hobs 2y ago

The_Colonel 2y ago

I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

crabmusket 2y ago
I love it when my competitors use sequential integer IDs.
throwaway8486k 2y ago
Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German tank problem) Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know. https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem
- The_Colonel 2y ago
  I'm not saying it's a bad practice, the opposite actually. > I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key. It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.
  - throwaway8486k 2y ago
    > I'm not saying it's a bad practice, the opposite actually I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have. I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer > It's not an easy thing to add into an existing application, though I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints. If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).
- yellowapple 2y ago
  > I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.
deleted 2y ago
[deleted]
inopinatus 2y ago
UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit. Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.
- The_Colonel 2y ago
  Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.
hobs 2y ago
Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"