Using UUIDs as primary keys in database

When it comes to autogenerating primary keys, there are generally two strategies followed:

  • Generate from a sequence as integer value
  • Generate from a secure random source as uuid (to be specific, uuid4)

From a security point of view, if you are exposing the primary keys externally, like in REST enpoint URLs, you should definitly consider using UUIDs.

Avoiding enumeration attacks

The key differentiator between these two types of primary keys is if an external observer can guess what values can be present as primary keys in your tables. It is common to use primary keys as part of REST end points. For instance, https://api.example.com/v1/users/1234, where 1234 is the primary key in your database. An external observer can look at a few endpoint URLs and easily guess that you are using sequences as your primary key. They they can start retrieving all user information by substituting 1234 with 1232, 1231, 1230, .... If you look at any well designed secure REST endpoint URLs, they will avoid exposing primary keys if they are from a sequence.

You can avoid this pitfall by using UUID as primary key.

Consistent mapping

You may be convinced now that you should use UUID for all your primary keys. But there is a specific use case where you need an UUID generated based on a set of inputs. These inputs could be values from other columns in your table or an input from your program. In such cases, instead of lettting the database generate a UUID, you can generate a UUID based on uuid5.

Think of uuid5 as a seeded hash function. It takes two arguments: a seed and an input. In this case seed is another UUID that is acting like a namespace. Input can be concatenation of all the values from other columns you want to use. This will produce the same UUID as long as the inputs are the same. This is a weak form of enforcing unique constraint. If you really need to enforce uniqueness, you must define a UNIQUE CONSTRAINT on those columns as well.

References

links

social