PostgreSQL create extension with schema pg_catalog的权限异常case

背景

pg_catalog这个schema是PostgreSQL默认的元数据schema，所有的元数据都在这里.

另一方面，pg_catalog这个schema默认就在搜索路径search_path里面，并且是删除不掉的，而且它的优先级排在所有schema的前面。

比如有两个重名的pg_class分别在这两个schema里面。pg_catalog.pg_calss, public.pg_class, 如果你执行select * from pg_class，那么搜索的是pg_catalog.pg_class.

因此我们在创建extension或者创建一些共用对象时，可以创建在public里面，也可以创建在pg_catalog里面。创建在pg_catalog里面的好处是优先级最高，所有用户可见。

create extension extension_name with schema pg_catalog;   

即可创建到pg_catalog中。

或者通过这种方式

alter extension extension_name set schema pg_catalog;  

但是目前来看，有一些小问题。

比如当extension中包含普通的复合类型，表等对象，使用create extension extension_name with schema pg_catalog; 语法，会报错

cd $PGHOME/share/extension  
  
cp btree_gist--1.2.sql btree_gist--9.9.9.sql  
  
vi btree_gist--9.9.9.sql  
-- 添加如下  
create type new_type as (  
  id int,  
  c1 int  
);  

测试

postgres=# create extension btree_gist with schema pg_catalog;  
CREATE EXTENSION  
postgres=# drop extension btree_gist ;  
DROP EXTENSION  
  
由于新增了复合类型,导致创建失败  
postgres=# \set VERBOSITY verbose  
postgres=# create extension btree_gist with schema pg_catalog version '9.9.9';  
ERROR:  42501: permission denied to create "pg_catalog.new_type"  
DETAIL:  System catalog modifications are currently disallowed.  
LOCATION:  heap_create, heap.c:280  

对于包含table, seq, 复合类型的extension，如果要创建到pg_catalog中，可以使用如下方法修复

修复方法1

先创建到普通schema中，然后使用alter extension set schema pg_catalog修改。

postgres=# create extension btree_gist version '9.9.9';;  
CREATE EXTENSION  
postgres=# alter extension btree_gist set schema pg_catalog;  
ALTER EXTENSION  

修复方法2

设置allow_system_table_mods参数，允许修改

不能直接修改  
postgres=# set allow_system_table_mods=on;    
ERROR:  55P02: parameter "allow_system_table_mods" cannot be changed without restarting the server  
LOCATION:  set_config_option, guc.c:5899  
  
  
修改到配置文件中，重启数据库  
vi $PGDATA/postgresql.conf  
allow_system_table_mods=on  
  
pg_ctl restart -m fast  
  
然后就可以直接创建到pg_catalog中了  
postgres=# create extension btree_gist with schema pg_catalog version '9.9.9';  
CREATE EXTENSION  

这个报错的原因，归根到底是某个pg_catalog不允许被修改，然而非复合类型缺被允许。

截取部分原始btree_gist--1.2.sql中的内容，包含了create type , operator等操作，这些操作允许执行在pg_catalog中。  
  
CREATE TYPE gbtreekey_var (  
        INTERNALLENGTH = VARIABLE,  
        INPUT  = gbtreekey_var_in,  
        OUTPUT = gbtreekey_var_out,  
        STORAGE = EXTENDED  
);  

怀疑是PG的某处BUG，已报给社区。

digoal’s 大量PostgreSQL文章入口