7 ms·

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, b

by GGO 2y ago

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.

dajtxx 2y ago
The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.
- inopinatus 2y ago
  It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level. Just goes to show you can’t inadvertently disclose anything these days.
whynotmaybe 2y ago
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the time information.
- TeeWEE 2y ago
  Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed data creation.
  - badestrand 2y ago
    You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.
    - stoperaticless 2y ago
      Amen (or similar)
    - kroolik 2y ago
      Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?
      - blackenedgem 2y ago
        Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.
        groestl 2y ago
        Ha? Please elaborate.
        kroolik 2y ago
        When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
        asah 2y ago
        would creation/lastmod timestamps cover this requirement?
        deleted 2y ago
- arp242 2y ago
  For almost all use cases just showing a UUIDv7 or sequential ID is fine. There are a few exceptions, but it's not the common case.
  - mewpmewp2 2y ago
    How would it be fine, e.g. for e commerce which is arguably very large portion of the use cases? You would be immediately leaking how many orders a day your business is getting with sequential id.
    - arp242 2y ago
      > You would be immediately leaking how many orders a day your business is getting with sequential id. Which is fine for almost all of them. All brick and mortar stores "leak" this too; it's really not that hard to guess number of orders for most businesses, and it's not really a problem for the overwhelming majority. And "Hi, this is Martin, I'd like to ask a question about order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" doesn't really work. Neither does "John, did you already pay order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" or "Alice, isn't 2bf8aa01-6f4e-42ae-8635-9648f70a9a05 the same as what we ordered with 7bb027c3-83ea-481a-bb1e-861be18d21ea?" Especially for order IDs UUIDs are huge PITA because unlike user IDs and other more "internal" IDs, people can and do want to talk about them. You will need some secondary human-friendly unique ID regardless (possibly obfuscated, if you really want to), and if you have that, then why bother giving UUIDs to people?
      - badestrand 2y ago
        Best solution is to have a serial identifier internally and a generated ID for external. And yes it shouldn't be a UUID as they are user-hostile, it should be something like 6-10 letters+digits.
    - inopinatus 2y ago
      There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats) For extra spice, in some places this is legislated as a per-seller sequence, and in others as a per-customer sequence, so there’s no policy you can apply globally, and this once again highlights the separation of concerns between a primary key and a record locator/identifier.
- sebazzz 2y ago
  > As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. Are you then not doing security by randomness if that is the thing that worries you?
- OskarS 2y ago
  Can I ask (as a humble application developer, not a backend/database person), if the two requirements are: 1. The UUIDs should be ordered internally, for B-tree performance 2. The UUIDs should not be ordered externally, for security reasons Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out: SELECT encrypt(id) FROM table WHERE something = whatever; and when putting stuff in: UPDATE table SET something = whatever WHERE id = decrypt(<encrypted-key>) Seems like the best of both worlds, and you don't need to store separate things.
  - spencerap 2y ago
    If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.
    - OskarS 2y ago
      You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.
      - zxexz 2y ago
        Ideally if you do this, you store the key in a separate schema with proper roles so that you can call encrypt() with the database role, which can't select the key. Even then, the decrypted metadata should not be particularly sensitive - and should immutably reference a point in time so you can validate against some known key revocation retroactively. My take is it's rarely necessary to have a token, that you give to an external entity, that has any embedded metadata all - 99.9% of apps aren't operating at a scale where even a million-key hashmap sitting in ram and syncing changes to disk on update would cause any performance difference.
- thiht 2y ago
  I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?
  - cqqxo4zV46cp 2y ago
    I don’t understand this thinking. If you understand what’s at play, you can infer the potential security implications. What you’re advocating for is being entirely reactive instead of also being proactive.
    - thiht 2y ago
      No, I don’t. Even with a timestamp uuids are not enumerable, and honestly I don’t care that the timestamp they were created at is public. Is the version of uuid used being a part of the uuid considered a leak too?
  - whynotmaybe 2y ago
    The draft spec for uuid v7 has details about the security considerations : https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html#name-security-considerations https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u... The way I see it is that uuid v7 in itself is great for some use but not for all uses. You always have to remember that a v7 always carries the id's creation time as metadata with it, whether you want it or not. And if you let external users get the v7, they can get that metadata. I'm not a security expert but I know enough to know that you should only give the minimal data to a user. My only guess is that v7 being so new, attacks aren't widespread for now, and I know why the author decided not to focus on "if UUID is the right format for a key", because the answer is no 99% of the time.
    - thiht 2y ago
      That just seems overly cautious. I’d rather use UUIDv7 unless I have a reason not to. The convenience of sortable ids and increased index locality are very much worth the security issues associated with UUIDv7. Maybe I wouldn’t use UUIDv7 for tokens or stuff like that, but DB IDs seem pretty safe.
mixmastamyk 2y ago
You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.
- hoffs 2y ago
  The details part is so miniscule that I doubt it even matters. You'd have difficult time trying to enumerate uuidv7s anyways.
  - mixmastamyk 2y ago
    Leaking time leaks information about customer growth and usage. It may matter to your competitors.

by GGO 2y ago

dajtxx 2y ago

The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.

inopinatus 2y ago
It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level. Just goes to show you can’t inadvertently disclose anything these days.

whynotmaybe 2y ago

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the time information.

TeeWEE 2y ago
Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed data creation.
- badestrand 2y ago
  You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.
  - stoperaticless 2y ago
    Amen (or similar)
  - kroolik 2y ago
    Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?
    - blackenedgem 2y ago
      Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.
      - groestl 2y ago
        Ha? Please elaborate.
        kroolik 2y ago
        When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range
        asah 2y ago
        would creation/lastmod timestamps cover this requirement?
        deleted 2y ago
arp242 2y ago
For almost all use cases just showing a UUIDv7 or sequential ID is fine. There are a few exceptions, but it's not the common case.
- mewpmewp2 2y ago
  How would it be fine, e.g. for e commerce which is arguably very large portion of the use cases? You would be immediately leaking how many orders a day your business is getting with sequential id.
  - arp242 2y ago
    > You would be immediately leaking how many orders a day your business is getting with sequential id. Which is fine for almost all of them. All brick and mortar stores "leak" this too; it's really not that hard to guess number of orders for most businesses, and it's not really a problem for the overwhelming majority. And "Hi, this is Martin, I'd like to ask a question about order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" doesn't really work. Neither does "John, did you already pay order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" or "Alice, isn't 2bf8aa01-6f4e-42ae-8635-9648f70a9a05 the same as what we ordered with 7bb027c3-83ea-481a-bb1e-861be18d21ea?" Especially for order IDs UUIDs are huge PITA because unlike user IDs and other more "internal" IDs, people can and do want to talk about them. You will need some secondary human-friendly unique ID regardless (possibly obfuscated, if you really want to), and if you have that, then why bother giving UUIDs to people?
    - badestrand 2y ago
      Best solution is to have a serial identifier internally and a generated ID for external. And yes it shouldn't be a UUID as they are user-hostile, it should be something like 6-10 letters+digits.
  - inopinatus 2y ago
    There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats) For extra spice, in some places this is legislated as a per-seller sequence, and in others as a per-customer sequence, so there’s no policy you can apply globally, and this once again highlights the separation of concerns between a primary key and a record locator/identifier.
sebazzz 2y ago
> As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. Are you then not doing security by randomness if that is the thing that worries you?
OskarS 2y ago
Can I ask (as a humble application developer, not a backend/database person), if the two requirements are: 1. The UUIDs should be ordered internally, for B-tree performance 2. The UUIDs should not be ordered externally, for security reasons Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out: SELECT encrypt(id) FROM table WHERE something = whatever; and when putting stuff in: UPDATE table SET something = whatever WHERE id = decrypt(<encrypted-key>) Seems like the best of both worlds, and you don't need to store separate things.
- spencerap 2y ago
  If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.
  - OskarS 2y ago
    You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.
    - zxexz 2y ago
      Ideally if you do this, you store the key in a separate schema with proper roles so that you can call encrypt() with the database role, which can't select the key. Even then, the decrypted metadata should not be particularly sensitive - and should immutably reference a point in time so you can validate against some known key revocation retroactively. My take is it's rarely necessary to have a token, that you give to an external entity, that has any embedded metadata all - 99.9% of apps aren't operating at a scale where even a million-key hashmap sitting in ram and syncing changes to disk on update would cause any performance difference.
thiht 2y ago
I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?
- cqqxo4zV46cp 2y ago
  I don’t understand this thinking. If you understand what’s at play, you can infer the potential security implications. What you’re advocating for is being entirely reactive instead of also being proactive.
  - thiht 2y ago
    No, I don’t. Even with a timestamp uuids are not enumerable, and honestly I don’t care that the timestamp they were created at is public. Is the version of uuid used being a part of the uuid considered a leak too?
- whynotmaybe 2y ago
  The draft spec for uuid v7 has details about the security considerations : https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html#name-security-considerations https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u... The way I see it is that uuid v7 in itself is great for some use but not for all uses. You always have to remember that a v7 always carries the id's creation time as metadata with it, whether you want it or not. And if you let external users get the v7, they can get that metadata. I'm not a security expert but I know enough to know that you should only give the minimal data to a user. My only guess is that v7 being so new, attacks aren't widespread for now, and I know why the author decided not to focus on "if UUID is the right format for a key", because the answer is no 99% of the time.
  - thiht 2y ago
    That just seems overly cautious. I’d rather use UUIDv7 unless I have a reason not to. The convenience of sortable ids and increased index locality are very much worth the security issues associated with UUIDv7. Maybe I wouldn’t use UUIDv7 for tokens or stuff like that, but DB IDs seem pretty safe.

mixmastamyk 2y ago

You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.

hoffs 2y ago
The details part is so miniscule that I doubt it even matters. You'd have difficult time trying to enumerate uuidv7s anyways.
- mixmastamyk 2y ago
  Leaking time leaks information about customer growth and usage. It may matter to your competitors.