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.

max · Accepted Answer · 2017-09-10T12:20:30.437

5

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

edited Sep 10 '17 at 12:20

answered Sep 10 '17 at 11:25

max

96,212
14
104
165

Thank you but it is still complaining that I must be super user to create this extension. I created database using $ createuser --createdb --login -P myapp I think something is missing there so it will work successfully – Rabin Poudyal Sep 10 '17 at 11:36
Is that the same user that you are using to connect rails? – max Sep 10 '17 at 11:45
https://stackoverflow.com/questions/20723100/why-can-only-a-superuser-create-extension-hstore-but-not-on-heroku – max Sep 10 '17 at 11:50
Either [grant the user superuser privileges](https://www.postgresql.org/docs/8.1/static/sql-grant.html) or use [PostgreSQL Extension Whitelisting](https://github.com/dimitri/pgextwlist) – max Sep 10 '17 at 11:55
Successfully solved using ALTER ROLE myapp SUPERUSER; Thanks. :) – Rabin Poudyal Sep 10 '17 at 12:09
Just beware that adding the SUPERUSER role to the user connected to your web app is very insecure as it would let an SQL injection attack drop every schema on the database. – max Sep 10 '17 at 12:21
I did that on my development machine only. I tried to run the migration in heroku. It migrated without asking for privileges. I think it is safe then. – Rabin Poudyal Sep 10 '17 at 13:00
Yes Heroku uses whitelisting to let you install extensions. – max Sep 10 '17 at 13:06
In my structure.sql file I saw this line : CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; does this public means it is risky now? But on other lines on other extensions I see pg_catloge: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; – Rabin Poudyal Sep 10 '17 at 16:02
Not really: https://www.postgresql.org/docs/9.0/static/ddl-schemas.html – max Sep 10 '17 at 16:16

Cannot run migrations when postgres extension added in rails 5 application

1 Answers1