解决金仓数据库KingbaseES报错permission denied to create “sys\_catalog.“的问题

一、问题现象

如下所示，在KingbaseES数据库中，修改kingbase.conf中search_path='"$user",sys_catalog'后，在数据库执行创建对象操作时，出现“permission denied to create "sys_catalog.” 错误：

prod=# CREATE TABLE "bdsj_bdgl_test" (
prod(# "BDSJ_BDGL_NM" varchar(32) NOT NULL,
prod(# "BDSJ_BDGL_BDBH" varchar(32) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_BDGL_BDLJ" character varying(256 char) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_XMGL_NM" varchar(32) NOT NULL,
prod(# "BDSJ_BDGL_MKNM" varchar(32) NOT NULL,
prod(# "BDSJ_BDGL_BDLX" varchar(32) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_BDGL_YLBD" varchar(32) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_BDGL_SJKD" varchar(32) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_BDGL_SJSJD" character varying(64 char) NULL DEFAULT NULL::varchar,
prod(# "BDSJ_BDGL_ZT" character(1 char) NULL DEFAULT NULL::bpchar,
prod(# "BDSJ_BDGL_SCRQ" character varying(19 char) NULL DEFAULT NULL::varchar,
prod(# CONSTRAINT "bdsj_bdgl_test_pkey" PRIMARY KEY (BDSJ_BDGL_NM)
prod(# );
ERROR:  permission denied to create "sys_catalog.bdsj_bdgl_test"
DETAIL:  System catalog modifications are currently disallowed.

prod=# create table tt (id int);
ERROR:  permission denied to create "sys_catalog.tt"
DETAIL:  System catalog modifications are currently disallowed.

prod=# create table sys_catalog.t1(id int);
ERROR:  permission denied to create "sys_catalog.t1"
DETAIL:  System catalog modifications are currently disallowed.

二、问题分析

1、查看当前数据库search_path配置

prod=# show search_path;
     search_path
---------------------
 "$user",sys_catalog
(1 row)

2、默认search_path配置

test=# show search_path;
   search_path
-----------------
 "$user", public

故障原因应该是，当system用户创建对象时默认将object存储在当前和用户同名的“$user"的schema下，如果没有存储在public的schema，但是对于search_path=‘"$user",sys_catalog’，用户自定义的对象不能存储在sys_catalog的schema下，因此出现权限错误(sys_catalog schema下为系统对象)。

三、问题解决

修改search_path配置：

test=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

---如上所示，修改search_path后，创建对象成功。

更多信息，参见https://help.kingbase.com.cn/v8/index.html