PgRandomId

Allow usage of pseudo-random IDs in Postgresql databases. Changes sequential surrogate ids (1, 2, 3...) into a pseudo-random sequence of unique 30-bits nonnegative integer values (eg. 760280231, 110168588, 1029278017...) or 6-character human-friendly-ish strings (eg. kn5xx1, qy2kp8, e5f67z...).

Since surrogate IDs are often used in REST-ful URLs, this makes the addresses less revealing and harder to guess (while preserving the straightforward mapping from URLs to database IDs):

• http://example.com/products/1 → http://example.com/products/134178313
• http://example.com/products/2 → http://example.com/products/121521131
• http://example.com/widgets/1 → http://example.com/widgets/2agc30
• http://example.com/widgets/2 → http://example.com/widgets/4zkabg

Although the code is 100% database-side, it has been packaged into Ruby functions plugging into ActiveRecord and Sequel migrations for ease of use.

Installation

Add this line to your application's Gemfile:

gem 'pg_random_id'

And then execute:

$ bundle

Or install it yourself as:

$ gem install pg_random_id

Synopsis

ActiveRecord

class InstallRandomId < ActiveRecord::Migration
  def up
    # install the necessary SQL functions in the DB
    create_random_id_functions
  end

  def down
    drop_random_id_functions
  end
end

class CreateProducts < ActiveRecord::Migration
  def up
    create_table :products do |t|
      t.string :name
    end
    
    # make the table use random ids
    random_id :products
  end

  def down
    # the random id will be removed along with the table
    drop_table :products
  end
end

class RandomizeIdsOnWidgets < ActiveRecord::Migration
  def up
    # make ids on a previously created table 
    # 'widgets' random (using string ids)
    random_str_id :widgets, :widget_id # you can specify id column name
  end
  
  def down
    remove_random_id :widgets, :widget_id
  end
end

Sequel

Sequel.migration do
  up do
    # install the necessary SQL functions in the DB
    create_random_id_functions
  end

  down do
    drop_random_id_functions
  end
end

Sequel.migration do
  up do
    create_table :products do
      primary_key :id
      String :name
    end
    
    # make the table use random ids
    random_id :products
  end

  down do
    # the random id will be removed along with the table
    drop_table :products
  end
end

Sequel.migration do
  up do
    # make ids on a previously created table 
    # 'widgets' random (using string ids)
    random_str_id :widgets, :widget_id # you can specify id column name
  end

  down do
    remove_random_id :widgets, :widget_id
  end
end

Upgrading

If you want to upgrade a live database from before 1.0.0 version, you need to call create_random_id_functions in a migration again, as key storage changed from being in the default value to a separate key table to allow prefetching values.

Note that existing random ids will still use the old format and you won't be able to use pri_nextval on them, though they'll continue to work.

If you don't have a live database to upgrade, you don't need to do anything. Migration methods haven't changed and will work using the new format; just make sure to recreate schema.sql if you're using it.

Prefetching values

Installing pg_random_id in the database allows using pri_nextval and pri_nextval_str SQL functions in much the same manner as the standard nextval function, eg.:

  next = DB["SELECT pri_nextval('foo_id_seq'::regclass)"].first.values[0]

Note that this feature is only available since version 1.0.0.

Considerations

No model modification is necessary, just use the table as usual and it will simply work. Each table will use its own unique sequence, chosen at random at migration time.

Foreign keys

If you use random_str_id make sure to use a string type in foreign key columns:

class CreateContraptions < ActiveRecord::Migration
  def up
    create_table :contraptions do |t|
      t.string :widget_id, limit: 6
    end
  end
end

Sequel.migration do
  up do
    create_table :contraptions do
      String :widget_id, size: 6
    end
  end
end

Non-serial columns

Both these functions are meant to replace standard serial columns, which means they expect a #{table}_#{column}_seq sequence to be present. It is automatically created by Postgres when you create a serial column (which is what AR implicitly does on create_table unless you say id: false, and what Sequel does when you say primary_key :id), but if you're not doing this, make sure to create the sequence, like so:

create_table :groups do
  String :id, primary_key: true
  String :name, null: false
end

execute "CREATE SEQUENCE groups_id_seq OWNED BY groups.id"
random_str_id :groups # changes id type to char(6)

# Change type back to varchar if you want to be able to use longer ids (ie. not autogenerated)
execute "ALTER TABLE group ALTER COLUMN id SET DATA TYPE varchar"

Notes

The random_id function changes the default value of the ID column to a scrambled next sequence value. The scrambling function is a simple Feistel network, with a variable parameter which is used to choose the sequence.

With random_str_id function, the column type is changed to character(6) and the sequence is additionally base32-encoded with Crockford encoding.

Contributing

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request