PostgreSQLcreateextensionwithschemapg_catalog的許可權異常case

標籤

PostgreSQL , create extension , pg_catalog

---

背景

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

社群將修復此問題，統一許可權風格。