逻辑导入时权限报错:
ERROR: must be member of role "system"
实验:
1、在超级用户system下新建用户与schema:
create user user1;
create schema s1;
修改user1的默认权限:
test=# alter default privileges in schema s1 grant all on tables to user1;
ALTER DEFAULT PRIVILEGES
test=#
test=# select * from sys_default_acl;
oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-------+------------+-----------------+---------------+------------------------
24595 | 10 | 24594 | r | {user1=arwdDxt/system}
(1 row)
2、使用普通用户user1备份
sys_dump -dtest -Uuser1 -Fp -f 1.sql -ns1
导出文件内容查看:schema:s1对应的owner是system用户,我在前面的ksql端只执行了alter default privileges修改默认权限一条命令,而在导出语句里有两条ALTER DEFAULT PRIVILEGES命令,这个逻辑如下,数据库需要先回收system用户访问schema s1中所有表的默认权限,再授予user1用户访问schema s1中所有表的权限
-- Name: s1; Type: SCHEMA; Schema: -; Owner: system
CREATE SCHEMA s1;
ALTER SCHEMA s1 OWNER TO system;
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: s1; Owner: system
ALTER DEFAULT PRIVILEGES FOR ROLE system IN SCHEMA s1 REVOKE ALL ON TABLES FROM system;
ALTER DEFAULT PRIVILEGES FOR ROLE system IN SCHEMA s1 GRANT ALL ON TABLES TO user1;
-- Kingbase database dump complete
3、如下,使用user1还原,报错的原因是user1用户导入权限有限,无法修改系统默认权限,需要授予给他system的member成员身份,或直接用system用户导入。
[kingbase5@ppo ~]$ ksql -dtest -Uuser1 -f 1.sql
SET
SET
SET
SET
SET
set_config
public
(1 row)
SET
SET
SET
SET
SET
ksql:1.sql:24: ERROR: permission denied for database test
ALTER SCHEMA
ksql:1.sql:33: ERROR: must be member of role "system"
ksql:1.sql:34: ERROR: must be member of role "system"
解决:
1、使用超级用户(system)备份还原(建议使用)
2、跳过权限不备份权限,
3、使用普通用户备份还原,需要将超级用户加入到普通用户的成员里面(例如:grant system to user1)