PostgreSQL扩展所有者冲突解决：多角色数据库场景方案问询

问题背景 #

template1模板库中pg_trgm扩展的所有者为role_A，导致role_G通过CREATE DATABASE创建的新数据库中，该扩展所有者仍为role_A，不符合Rails应用schema加载时“扩展需归数据库所有者所有”的要求，且数据库会被频繁删除重建。

可行解决方案 #

方案一：将template1的扩展所有者改为PUBLIC #

将pg_trgm的所有者设为PUBLIC后，新数据库的所有者（role_A/role_G）默认拥有对该扩展的完全操作权限，可满足Rails的权限要求：

1. 以超级用户身份连接template1：

   psql -d template1 -U postgres
   

2. 修改扩展所有者：

   ALTER EXTENSION pg_trgm OWNER TO PUBLIC;
   

3. 验证修改结果：

   SELECT extname, rolname 
   FROM pg_extension
   JOIN pg_roles ON pg_extension.extowner = pg_roles.oid
   WHERE extname = 'pg_trgm';
   

   确认输出的rolname为public即可。后续新建数据库会继承该设置，无需额外操作。

方案二：用事件触发器自动同步扩展所有者 #

如果要求扩展必须严格归数据库所有者所有，可通过事件触发器实现自动修改：

1. 以超级用户身份连接默认的postgres数据库，先安装dblink扩展：

   CREATE EXTENSION dblink;
   

2. 创建处理扩展所有者的函数：

   CREATE OR REPLACE FUNCTION adjust_trgm_owner()
   RETURNS event_trigger
   LANGUAGE plpgsql
   AS $$
   DECLARE
     db_name text;
     db_owner oid;
   BEGIN
     SELECT objid::regclass::text INTO db_name 
     FROM pg_event_trigger_ddl_commands() 
     WHERE command_tag = 'CREATE DATABASE';
   
     SELECT datdba INTO db_owner FROM pg_database WHERE datname = db_name;
   
     EXECUTE format('ALTER DATABASE %I CONNECTION LIMIT 1;', db_name);
     PERFORM dblink_connect(db_name);
     PERFORM dblink_exec(format('ALTER EXTENSION pg_trgm OWNER TO %s;', db_owner::regrole));
     PERFORM dblink_disconnect();
     EXECUTE format('ALTER DATABASE %I CONNECTION LIMIT -1;', db_name);
   END;
   $$;
   

3. 创建监听CREATE DATABASE事件的触发器：

   CREATE EVENT TRIGGER trg_adjust_trgm_owner
   ON ddl_command_end
   WHEN TAG IN ('CREATE DATABASE')
   EXECUTE FUNCTION adjust_trgm_owner();
   

   此后每次新建数据库，触发器会自动将pg_trgm的所有者改为该数据库的所有者。

方案三：不在template1预安装，由Rails自动创建 #

从template1中卸载pg_trgm，让各数据库所有者自行安装：

1. 连接template1并卸载扩展：

   \c template1
   DROP EXTENSION pg_trgm;
   

2. 在Rails的初始化逻辑或迁移中添加安装语句：

   # 可放在db/schema.rb开头或单独迁移文件中
   ActiveRecord::Base.connection.execute('CREATE EXTENSION IF NOT EXISTS pg_trgm;')
   

   每次重建数据库时，由当前数据库所有者执行安装，自然满足所有者要求。

内容的提问来源于stack exchange，提问作者Daniel Ashton