postgres的赋权是层层赋权:
(1) 先把schema的权限授予用户
(2)把schema.table的权限授予用户
注意:如果直接跳过第一步,第二步grant select on schema.table to user,这种赋值会成功,但是查询还是没有权限。
如果要跳过第一步,可以再创建schema的时候指定AUTHORIZATION user,即:create schema XXX AUTHORIZATION user;然后再进行grant.
举例:
例子中我使用了role对权限进行管理,先将操作的权限赋role,然后将role赋给dbuser(和直接赋权给dbuser一样).
(1)层层赋权测试
postgres=# \c
You are now connected to database "postgres" as user "gpadmin".
postgres=# create schema shm4;
CREATE SCHEMA
postgres=# create table shm4.t(id int,m varchar(10));
CREATE TABLE
#使用dbuser用户进行查询shm4.t
postgres=> \c
You are now connected to database "postgres" as user "dbuser".
#没有权限查询
postgres=> select * from shm4.t;
ERROR: permission denied for schema shm4
LINE 1: select * from shm4.t;
#先赋权 ,role1为角色,赋予了用户dbuser(前面已经建好,这里可以忽略)
postgres=# grant usage on schema shm4 to role1;
GRANT
postgres=# grant select on shm4.t to role1;
GRANT
postgres=> \c
You are now connected to database "postgres" as user "amr_out".
#已经有权限查询
postgres=> select * from shm4.t;
id | m
----+---
(0 rows)
(2)AUTHORIZATION测试
postgres=# \c
You are now connected to database "postgres" as user "gpadmin".
#执行默认的AUTHORIZATION
postgres=# create schema shm5 AUTHORIZATION role1;
CREATE SCHEMA
postgres=# create table shm5.t(id int,m varchar(10));
#如果直接查询shm5.t,查询失败
postgres=> \c
You are now connected to database "postgres" as user "amr_out"
postgres=> select * from shm5.t;
ERROR: permission denied for relation t
#再gpadmin用户下对权限进行grant
postgres=# \c
You are now connected to database "postgres" as user "gpadmin".
postgres=# grant select on shm5.t to role1;
GRANT
#已经有权限查询
postgres=> \c
You are now connected to database "postgres" as user "amr_out".
postgres=> select * from shm5.t;
id | m
----+---
(0 rows)