526互联

KingbaseES数据库运维案例之---permission denied to create "sys_catalog.xxx"

发布时间 2023-05-09 19:41:51作者: KINGBASE研究院

​ KingbaseES数据库运维案例之---permission denied to create "sys_catalog.bdsj_bdgl_test"

案例说明：
在KingbaseES数据库kingbase.conf修改了search_path='"$user",sys_catalog'后，在数据库下执行创建对象操作，出现以下故障。

适用版本：
KingbaseES V8R6

一、问题现象

如下所示，在数据库执行创建对象操作时，出现“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后，创建对象成功。