在Rails 5应用程序中添加了PostgreSQL扩展后无法运行迁移

Question

我想使用Postgres的pg_trgm扩展来进行查询搜索。

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

由于Postgres已经没有pg_trgm，我需要执行一个命令来安装它。因此，我的迁移操作如下：

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

当我运行此迁移时，它会给我以下错误：

    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);

如果我手动输入到数据库并执行命令，则此方法有效，但我需要从迁移中运行它。

- Rabin Poudyal

Answer 1

启用扩展时，最好创建单独的迁移，这样可以更轻松地进行故障排除。

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

加载扩展需要与创建其组件对象所需的相同特权。对于大多数扩展，这意味着需要超级用户或数据库所有者权限。运行 CREATE EXTENSION 的用户成为扩展的所有者，以供后续特权检查使用，并成为扩展脚本创建的任何对象的所有者。

解决此问题的最简单方法是使用 ALTER ROLE myapp SUPERUSER;，这不是非常安全的解决方案，但适用于开发。对于生产服务器，您应该使用 PostgreSQL Extension Whitelisting。

生成迁移时，请确保使用 snakecase 或 camelcase 使名称可读 rails g migration AddTrigramIndexLocationToUsers 或 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

- max

---

谢谢，但它仍然抱怨我必须是超级用户才能创建这个扩展。我使用 $ createuser --createdb --login -P myapp 创建了数据库，我认为还缺少一些东西，以便它可以成功运行。 - Rabin Poudyal

你用来连接Rails的用户是同一个用户吗？ - max

要么授予用户超级用户权限，要么使用PostgreSQL扩展白名单。 - max

成功使用 ALTER ROLE myapp SUPERUSER 解决了问题。谢谢。 :) - Rabin Poudyal

在我的structure.sql文件中，我看到了这一行：CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; 这个public是否意味着现在有风险？但是在其他扩展的其他行上，我看到pg_catloge：CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; - Rabin Poudyal