Cannot run migrations when postgres extension added in rails 5 application

Question

I want to use pg_trgm extension of Postgres for searching using query

SELECT * FROM USERS WHERE location like '%new%' ORDER BY location DESC;

Since Postgres already don't have pg_trgm I need to execute a command to install it. So my migration to do so is

class Addtrigramindexlocationtousers < ActiveRecord::Migration[5.1]
  def change
    reversible do |direction|
        direction.up {
            execute %{
                CREATE EXTENSION IF NOT EXISTS pg_trgm;
                CREATE INDEX index_users_trigram_on_location ON users USING gin (location gin_trgm_ops);
            }
        }
        direction.down {
            execute %{
                DROP INDEX index_users_trigram_on_location;
            }           
        }
    end
  end
end

So when I run this migration it is giving me this error:

    ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pg_trgm"
HINT:  Must be superuser to create this extension.
: 
                DROP INDEX index_users_on_location;
                CREATE EXTENSION IF NOT EXISTS pg_trgm;
                CREATE INDEX index_users_trigram_on_location ON users USING gin (location gin_trgm_ops);

This works if I manually enter to database and execute the command but I need to run it from migration.

Answer 1

When enabling extensions its a good idea to create a separate migration as it makes it much easier to troubleshoot:

class EnableTrigramIndexLocationExtension < ActiveRecord::Migration[5.1]
  def change
    enable_extension "pg_trgm"
  end
end

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.

The simplest way to solve this is by using ALTER ROLE myapp SUPERUSER; which is not a very secure solution but works for development. For a production server you should instead use PostgreSQL Extension Whitelisting.

When generating migrations make sure to use either snakecase or camelcase to make the name readable rails g migration AddTrigramIndexLocationToUsers or rails g migration add_trigram_index_location_to_users.

class AddTrigramIndexLocationToUsers < ActiveRecord::Migration[5.1]
  def up
    execute %{ 
      CREATE INDEX index_users_trigram_on_location ON users USING gin (location gin_trgm_ops); 
    }
  end

  def down
    remove_index :users, :index_users_trigram_on_location
  end
end