我想有一个数据库的所有者,可以创建角色,并为该数据库的所有管理任务.由数据库所有者创建的所有角色必须对公共架构中的所有表具有SELECT权限,并且具有其自己的架构,在该架构中他们拥有所有权限. 所以我试着:
\connect postgres postgres;
create role db_owner with createrole password 'passwd' login;
create database db with owner db_owner;
\connect db db_owner;
grant select on all tables in schema public to public;
create table t (i int);
create role s1 with password 's1' login;
grant s1 to db_owner;
create schema authorization s1;
现在,当我try 以用户s1
的身份登录select from public.t
时,它被拒绝:
\connect db s1;
db=> select * from t;
ERROR: permission denied for table t
如果grant select
是由postgres
发行的,它是有效的:
db=> \connect db postgres
You are now connected to database "db" as user "postgres".
db=# grant select on all tables in schema public to public;
GRANT
db=# \connect db s1
You are now connected to database "db" as user "s1".
db=> select * from t;
i
---
(0 rows)
为什么public
架构中的数据库所有者不能是grant select
?怎么做呢?