UUID primary keys with Postgres and ROM

UUIDs are cool. Using a UUID as the primary key in your database tables helps to protect your app against enumeration (or ‘resource guessing’) attacks, and, if your application stores data across multiple databases, it ensures that the key for a given record (say a customer) will be unique across all of them.

Because of the way they’re generated, the chance that two UUIDs will be the same is practically nil.

What is a UUID?

A UUID (or ‘Universally Unique Identifier’) is a 128-bit string (comprised of 32 alphanumeric characters separated with 4 dashes) used to uniquely identify an object or record in a computing system.

In the case of a web application, exposing a UUID primary key in your URLs is a more secure alternative to using integer primary keys, as the predictably incrementing nature of these can leave your application susceptible to enumeration attacks, and may also reveal other sensitive information about your app, such as the number of users it has, or in the case of an e-commerce app, the number of orders that have been placed.

The benefits of UUIDs

If your web application follows the RESTful architectural style, it’s likely that you’re exposing database primary keys in routes throughout your app (you’ve probably got your own views as to whether or not this is a good idea, so I won’t touch on that here).

Using integer primary keys is great because it enables you to determine, at a glance, the order in which records in your database were created (which helps to make debugging easier). There’s one big downside to this though — the sequential nature of integer primary keys makes them easily guessable.

Let’s say your app is an online store and enables customers to view the status of their order (along with billing and shipping address info) at the following URL:

example.com/orders/123/status

The first issue here is that it allows this user to make a (probably accurate) assumption that their order was the 123rd order your store has taken, which, if the user runs a store that’s a competitor to yours is probably information you don’t want to disclose.

The second issue that is that if you don’t have any sort of authentication in place for this URL (such as only allowing signed-in users to view the status of their own orders), you leave your app susceptible to enumeration attacks, where a user gains access to a resource (in this case an order) they shouldn’t have access to, simply by incrementing the ID used to identify it in the URL.

However, let’s say you were to use a UUID as the primary key for the orders table. In such a case, the URL for the status page for an order would look something like this:

example.com/orders/12eb6b3f-b925-4e3d-bd41-78270530fb17/status

For that sneaky user who runs a store that’s a competitor of yours, this URL gives nothing away in relation to the number of orders your store has taken. It also doesn’t provide an easy way for a rogue user to ‘guess’ their way to accessing the status page for an order that’s not theirs .

UUID versions

While the RFC 4122 standard defines five different UUID versions, those most suitable for generating truly random UUIDs are v1 and v4.

UUID v1

The generation of a v1 UUID uses the MAC address of the generating computer together with a timestamp, which means the chance of a collision between two UUIDs is virtually nil. The downside of this means of generation however is that v1 UUIDs are ‘guessable’.

UUID v4

v4 UUIDs are completely randomly generated (they don’t use a MAC address and timestamp in their generation) and as a result are more secure than v1. The downside however is that there is a (vanishingly) small chance of a collision between two UUIDs.

UUIDs as primary keys in Postgres

Over the last couple of years, ROM has become our persistence toolkit of choice at Icelab, as, together with Sequel, it’s a perfect fit for the dry-rb-based stack we now favour using when building complex web applications.

In a recent project using ROM, I wanted to use a UUID as the primary key for two relations (‘discussions’ and ‘messages’), with a has_many/belongs_to relationship between them. Fortunately, given ROM is able to automatically infer UUID attributes this process was pretty simple.

Out of the box, Postgres supports storing UUIDs but not generating them. To do that, I needed to use Postgres’ uuid-ossp extension, which I added to the DB in a migration like so:

ROM::SQL.migration do
  up do
    execute <<-SQL
      CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    SQL
  end

  down do
    execute <<-SQL
      DROP EXTENSION IF EXISTS "uuid-ossp";
    SQL
  end
end

The next step was to add migrations for the ‘discussions’ and ‘messages’ relations and ensure a UUID primary key was being automatically-generated for any newly-inserted records.

First the discussions relation:

create_table(:discussions) do
  column :id, :uuid, null: false, default: Sequel.function(:uuid_generate_v4)
  primary_key [:id]
  foreign_key :personal_user_id, :personal_users, null: false, index: true, on_delete: :cascade
  foreign_key :business_user_id, :business_users, null: false, index: true, on_delete: :cascade
end

Here I needed to define the id column as being of type uuid, and used Sequel’s function method to call the uuid-ossp extension’s uuid_generate_v4 function to generate an ID for any new records on insertion.

Then the messages relation:

create_table(:messages) do
  column :id, :uuid, null: false, default: Sequel.function(:uuid_generate_v4)
  primary_key [:id]
  foreign_key :discussion_id, :discussions, type: :uuid, null: false, index: true, on_delete: :cascade
  column :content, String, null: false
  column :sent_by, String, null: false
  column :status, String, null: false
end

Here I defined id as before, but also needed to explicitly define the discussion_id foreign key as being of type uuid.

And that’s it! The app’s paths are now using non-enumerable UUIDs as the resource identifier, with a UUID primary key automatically generated for any new record as it’s inserted into the database.