permission denied for schema public

背景

最近在使用opengauss进行postgres数据库的替换，创建用户后不能使用该用户创建public表

创建用户的语句

create user spst with password '********';
create database sps_dbt owner spst dbcompatibility='PG';
grant all privileges on database sps_dbt to spst ;

执行

sps_dbt=> CREATE TABLE qrtz_locks (
    SCHED_NAME VARCHAR(120) NOT NULL,
    LOCK_NAME  VARCHAR(40) NOT NULL,
    PRIMARY KEY (SCHED_NAME,LOCK_NAME)
); 
ERROR:  permission denied for schema public

解决办法

GRANT ALL PRIVILEGES TO spst;

思考

为什么会出现该情况？华为的解释是是因为安全原因,不允许普通客户在public模式下操作

但是我使用下面语句进行public赋权限依旧没有办法操作public,这就有点凌乱了

grant all on schema public to spst;

进一步分析

分别对比执行grant all on schema public to spst的spst和GRANT ALL PRIVILEGES TO sps的sps用户
对比权限

SELECT * FROM pg_roles where rolname in ('sps','spst')

查看角色

不同支持多了个rolsystemadmin角色权限。

查看shcme权限

 select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname in ('sps','spst') group by a.nspname,b.rolname;

两个的权限一致

数据库权限

 select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname in ('sps','spst')group by a.datname,b.rolname;

权限也是一样的

扩展

查看表权限

select table_name,table_schema,grantee,string_agg(privilege_type,',') from information_schema.table_privileges where grantee='sps' group by table_name,table_schema,grantee;

结论

public 是一个特殊的schema，不能通过schema授权获得操作权限，需要赋予rolsystemadmin角色的权限才能操作。
有人这样解释可以把schema理解成操作系统的目录文件夹所不同的是schema只有一层不能嵌套创建，确实可以这样理解。

select *  from pg_namespace ;

可以看出public 单独占用一个pg_namespace