pg系的数据库在模式与权限设计上与oracle和mysql都差别很大。
这里提供一些方法,在登陆时就使用指定schema
由于同名在pg里有很多特殊的处理,下面用ABC指代。
使用的数据库为openGauss 3.1.0
数据准备
创建用户A,数据库B,数据库B下的模式C
create user useraaa identified by 'root@1234';
CREATE DATABASE dbbbb OWNER useraaa;gsql -d dbbbb -U useraaa -W root@1234 -p 5400create schema shccc;create table shccc.tab(x int);insert into shccc.tab values(3);dbbbb=> select * from shccc.tab;x
---135
(3 rows)
用户A登陆到数据库B默认模式为C
设置用户A的默认search_path为模式C即可
dbbbb=> alter user useraaa set search_path=shccc;
ALTER ROLEgsql -d dbbbb -U useraaa -W root@1234 -p 5400
用户C登陆到数据库B默认模式为C
创建同名角色C授权,不用设置search_path
dbbbb=# create user shccc identified by 'root@1234';
ERROR: schema "shccc" already exists
--这里发现同名user无法创建,但是可以创建同名schema,pg中的user和role在登录上基本等价
dbbbb=# create role shccc identified by 'root@1234';
CREATE ROLEdbbbb=# grant usage on schema shccc to shccc;
GRANT
dbbbb=# alter role shccc login;
ALTER ROLE
dbbbb=#GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA shccc TO shccc;
GRANT[omm@lin-dameng-og1group2 ~]$ gsql -d dbbbb -U shccc -W root@1234 -p 5400
用户B登陆到同名数据库B默认模式为C
创建同名用户B授权,设置owner,设置search_path。这种登陆时没有指定database,用的是用户同名的database
dbbbb=# create user dbbbb identified by 'root@1234';
CREATE ROLEalter user dbbbb set search_path=shccc;
alter schema shccc owner to dbbbb;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA shccc TO dbbbb;[omm@lin-dameng-og1group2 ~]$ gsql -U dbbbb -W root@1234 -p 5400
各种报错
登陆后查询出现找不到relation
ERROR: relation "tab" does not exist on dn_6001_6002_6003
答:alter user dbbbb set search_path=shccc;指定用户search_path.
登陆后查询出现权限问题
ERROR: permission denied for relation tab
\dt 可以看到表
答:GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA shccc TO shccc;将所有权限授予
授权时出现以下
ERROR: must be member of role "dbbbb"
ERROR: Permission denied.
答:当前操作用户权限不足,使用超级用户omm\postgres授权
登陆时查询不到表,但是search_path正确
dbbbb=> \d
No relations found.
dbbbb=> show search_path;
search_path
-------------
shddd
(1 row)
答:alter schema shccc owner to shccc;,需要将schema的owner授予他
总结
由于psql(gsql)在登陆时可以指定很多的缺省:
当只指定用户,不指定database时,使用和用户同名的database。
当只指定database为何能登陆是使用了和当前OS同名的用户。
user与schema不同名,设置search_path来指定默认schema
user与database不同名,暂时没找到缺省的办法,可能在应用端配置数据源的时候可以指定。
学习原理,孵化思路,积累工具,下笔有道。