postgresql - Cannot run migrations when postgres extension added in rails 5 application -

i want use pg_trgm extension of postgres searching using query

select * users location '%new%' order location desc; 

since postgres don't have pg_trgm need execute command install it. migration is

class addtrigramindexlocationtousers < activerecord::migration[5.1]   def change     reversible |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 run migration giving me error:

    activerecord::statementinvalid: pg::insufficientprivilege: error:  permission denied create extension "pg_trgm" hint:  must superuser create 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 manually enter database , execute command need run migration.

when enabling extensions idea create separate migration makes easier troubleshoot:

class enabletrigramindexlocationextension < activerecord::migration[5.1]   def change     enable_extension "pg_trgm"   end end 

loading extension requires same privileges required create component objects. extensions means superuser or database owner privileges needed. user runs create extension becomes owner of extension purposes of later privilege checks, owner of objects created extension's script.

the simplest way solve using alter role myapp superuser; not secure solution works development. production server should instead use postgresql extension whitelisting.

when generating migrations make sure use either snakecase or camelcase make name readable rails g migration addtrigramindexlocationtousers or rails g migration add_trigram_index_location_to_users.

class addtrigramindexlocationtousers < activerecord::migration[5.1]   def     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