前言
本文的目的是实现u1用户访问ud用户下的视图权限。
测试
登录system用户并创建schema，user，并授权schema的有关权限给ud用户
TEST=# select current_user;
current_user
--------------
system
(1 row)
TEST=# create schema ud;
CREATE SCHEMA
TEST=#
TEST=# create user ud;
CREATE ROLE
TEST=# grant usage on schema ud to ud;
GRANT
TEST=#
TEST=# grant select ON ALL TABLES IN SCHEMA ud to ud;
GRANT
TEST=# grant create on schema ud to ud;
GRANT
登录ud用户并执行命令
TEST=# \c - ud
You are now connected to database "TEST" as user "ud".
TEST=> create table ti (id int);
CREATE TABLE
TEST=> insert into ti values(1);
INSERT 0 1
TEST=>
TEST=> select * from ti;
id
----
1
(1 row)
TEST=> create view view_tu as select * from ti;
CREATE VIEW
TEST=>
TEST=> select * from view_tu;
id
----
1
(1 row)
登录system用户，创建role01并把视图的查询权限赋给role01
TEST=> \c - system
You are now connected to database "TEST" as user "system".
TEST=#
TEST=#
TEST=# create role role01;
CREATE ROLE
TEST=# grant select on ud.view_tu to role01;
GRANT
把role01角色赋给u1用户
TEST=# grant role01 to u1;
GRANT ROLE
TEST=#
TEST=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
ab | | {ceshi001}
ceshi001 | Cannot login | {}
manager | Cannot login | {}
role01 | Cannot login | {}
sao | No inheritance | {}
sso | No inheritance | {}
system | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
u1 | | {role01}
ud | | {role01}
登录u1用户，查询schema ud下的视图，报错权限不足
TEST=# \c - u1;
You are now connected to database "TEST" as user "u1".
TEST=>
TEST=> select * from ud.view_tu;
ERROR: permission denied for schema ud
LINE 1: select * from ud.view_tu;
^
登录system用户并把schema ud 的权限赋给u1
TEST=> \c - system
You are now connected to database "TEST" as user "system".
TEST=#
TEST=#
TEST=# grant all privileges on schema ud to u1;
GRANT
TEST=#
登录u1用户，授权后可以查询schema ud的视图
TEST=# \c - u1
You are now connected to database "TEST" as user "u1".
TEST=>
TEST=> select * from ud.view_tu;
id
----
1
(1 row)
总结
如下，我们看到角色role01有视图view_tu的查询权限
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+------+-------------------+-------------------+----------
ud | view_tu | view | ud=arwdDxt/ud +| |
| | | role01=r/ud | |
(1 row)
如下，我们看到u1用户拥有角色role01的权限
TEST=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
ab | | {ceshi001}
ceshi001 | Cannot login | {}
manager | Cannot login | {}
role01 | Cannot login | {}
sao | No inheritance | {}
sso | No inheritance | {}
system | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
u1 | | {role01}
ud | | {role01}
因为前面执行过一个授权语句：grant all privileges on schema ud to u1;
根据以下sql查询权限得知u1拥有了在schema ud的USAGE和CREATE权限
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='u1' group by a.nspname,b.rolname;
nspname | rolname | string_agg
-------------+---------+--------------
public | u1 | USAGE,CREATE
sys | u1 | USAGE
sys_catalog | u1 | USAGE
sysaudit | u1 | USAGE,CREATE
sysmac | u1 | USAGE
ud | u1 | USAGE,CREATE
(6 rows)
经过以上的授权步骤我们可以看出，如果要实现u1用户对ud用户下视图的查询权限需要经过一些列复杂的授权过程。
尤其需要注意u1对schema ud的有关权限，否则即使拥有角色role01的查询权限也无法查看其他schema下视图。