目录
文档用途
详细信息
文档用途
了解usage权限的使用与管理
详细信息
场景1 ：只授权usage on schema 权限
session 1：
--创建test用户,并将highgo模式赋予test用户。
highgo=# create user test with password 'password';
CREATE ROLE
highgo=# grant USAGE on SCHEMA highgo to test ;
GRANT
highgo=# \dn highgo
List of schemas
Name | Owner
--------+--------
highgo | highgo
session 2：
--登陆readonly用户可以查询highgo模式下****现存的所有表**。**
highgo=# \c highgo test
highgo=> select * from highgo.
highgo.big_table highgo.dept highgo.ump
highgo.bonus highgo.pgdo
highgo=> select * from highgo.ump ;
pid | event_type | event | queryid | count
---------+---------------+-------------------------+-----------------+--------
14764 | IO | DataFileRead | 0 | 3
14986 | IPC | MessageQueuInternal | 3876349911 | 1
14767 | Activity | LogicalLauncherMain | 0 | 273690
14986 | IO | DataFileImmediateSync | 0 | 1
14760 | Activity | AutoVacumMain | 0 | 273622
14765 | Extension | Extension | 0 | 268108
14757 | Activity | CheckpointerMain | 0 | 273344
16728 | Client | ClientRead | 0 | 1454
14765 | IPC | MessageQueuInternal | 4242708246 | 1
14757 | IO | DataFileSync | 0 | 1
16741 | Client | ClientRead | 0 | 44655
14758 | Activity | BgWriterHibernte | 0 | 256733
14758 | Activity | BgWriterMain | 0 | 16926
14757 | IO | DataFileWrite | 0 | 1
16425 | Client | ClientRead | 0 | 30320
14765 | LWLock | lock_manager | 0 | 1
14986 | Client | ClientRead | 0 | 253179
14759 | Activity | WalWriterMain | 0 | 273673
(18 rows)
切换到session1创建新表t1
highgo=# create table t1 as select * from ump;
切换到session2 test用户下，t1表无法查询
highgo=> select * from highgo.
highgo.big_table highgo.dept highgo.t1 highgo.bonus highgo.pgdo highgo.ump
highgo=> select * from highgo.t1;
ERROR: 42501: permission denied for relation t1
总结：如果只授予 usage on schema 权限，test只能查看 highgo 模式下已经存在的表和对象。
在授予 usage on schema 权限之后创建的新表无法查看。
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
场景2：授予usage on schema权限之后 ，再赋予 select on all tables in schema权限
针对场景1 [ERROR: 42501: permission denied for relation t1] 错误的处理
highgo=> select * from highgo.
highgo.big_table highgo.dept highgo.t1 highgo.bonus highgo.pgdo highgo.ump
highgo=> select * from highgo.t1;
ERROR: 42501: permission denied for relation t1
session1：授权test用户select on all tables 权限
highgo=# grant select on all tables in schema highgo TO test ;
GRANT
session2： test用户查询t1表
highgo=> select * from highgo.t1 ;
pid | event_type | event | queryid | count
---------+---------------+-------------------------+-----------------+--------
14764 | IO | DataFileRead | 0 | 3
14986 | IPC | MessageQueuInternal | 3876349911 | 1
14767 | Activity | LogicalLauncherMain | 0 | 273690
14986 | IO | DataFileImmediateSync | 0 | 1
14760 | Activity | AutoVacumMain | 0 | 273622
14765 | Extension | Extension | 0 | 268108
14757 | Activity | CheckpointerMain | 0 | 273344
16728 | Client | ClientRead | 0 | 1454
14765 | IPC | MessageQueuInternal | 4242708246 | 1
14757 | IO | DataFileSync | 0 | 1
16741 | Client | ClientRead | 0 | 44655
14758 | Activity | BgWriterHibernte | 0 | 256733
14758 | Activity | BgWriterMain | 0 | 16926
14757 | IO | DataFileWrite | 0 | 1
16425 | Client | ClientRead | 0 | 30320
14765 | LWLock | lock_manager | 0 | 1
14986 | Client | ClientRead | 0 | 253179
14759 | Activity | WalWriterMain | 0 | 273673
(18 rows)
session1：登陆 highgo 用户的 highgo 模式下创建新表 t2
highgo=# create table t2 as select * from ump ;
SELECT 18
session2：test用户查询t2表权限不足
highgo=> select * from highgo.t2;
ERROR: 42501: permission denied for relation t2
session1：再次赋予 grant select on all tables
highgo=# grant select on all tables in schema highgo TO test ;
GRANT
session2：test用户又可以查看 t2 表
highgo=> select * from highgo.t2 ;
pid | event_type | event | queryid | count
---------+---------------+-------------------------+-----------------+--------
14764 | IO | DataFileRead | 0 | 3
14986 | IPC | MessageQueuInternal | 3876349911 | 1
14767 | Activity | LogicalLauncherMain | 0 | 273690
14986 | IO | DataFileImmediateSync | 0 | 1
14760 | Activity | AutoVacumMain | 0 | 273622
14765 | Extension | Extension | 0 | 268108
14757 | Activity | CheckpointerMain | 0 | 273344
16728 | Client | ClientRead | 0 | 1454
14765 | IPC | MessageQueuInternal | 4242708246 | 1
14757 | IO | DataFileSync | 0 | 1
16741 | Client | ClientRead | 0 | 44655
14758 | Activity | BgWriterHibernte | 0 | 256733
14758 | Activity | BgWriterMain | 0 | 16926
14757 | IO | DataFileWrite | 0 | 1
16425 | Client | ClientRead | 0 | 30320
14765 | LWLock | lock_manager | 0 | 1
14986 | Client | ClientRead | 0 | 253179
14759 | Activity | WalWriterMain | 0 | 273673
(18 rows)
更多详细信息请登录【瀚高技术支持平台】查看 https://support.highgo.com/#/index/docContentHighgo/5ec1f71d00ef9617