2023年6月23日发(作者：)

postgres的权限管理（⼆）：赋权管理postgres的赋权是层层赋权：(1) 先把schema的权限授予⽤户(2)把的权限授予⽤户注意：如果直接跳过第⼀步，第⼆步grant select on to user，这种赋值会成功，但是查询还是没有权限。如果要跳过第⼀步，可以再创建schema的时候指定AUTHORIZATION user,即：create schema XXX AUTHORIZATION user;然后再进⾏grant.

举例： 例⼦中我使⽤了role对权限进⾏管理，先将操作的权限赋role,然后将role赋给dbuser(和直接赋权给dbuser⼀样).（1）层层赋权测试postgres=# cYou are now connected to database "postgres" as user "gpadmin".postgres=# create schema shm4;CREATE SCHEMApostgres=# create table shm4.t(id int,m varchar(10));CREATE TABLE#使⽤dbuser⽤户进⾏查询res=> cYou are now connected to database "postgres" as user "dbuser".#没有权限查询postgres=> select * from shm4.t;ERROR: permission denied for schema shm4LINE 1: select * from shm4.t;#先赋权 ，role1为⾓⾊，赋予了⽤户dbuser（前⾯已经建好，这⾥可以忽略）postgres=# grant usage on schema shm4 to role1;GRANTpostgres=# grant select on shm4.t to role1;GRANTpostgres=> cYou are now connected to database "postgres" as user "amr_out".#已经有权限查询postgres=> select * from shm4.t; id | m

----+---(0 rows)（2）AUTHORIZATION测试postgres=# cYou are now connected to database "postgres" as user "gpadmin".#执⾏默认的AUTHORIZATIONpostgres=# create schema shm5 AUTHORIZATION role1;CREATE SCHEMApostgres=# create table shm5.t(id int,m varchar(10));#如果直接查询shm5.t，查询失败postgres=> cYou are now connected to database "postgres" as user "amr_out"postgres=> select * from shm5.t;ERROR: permission denied for relation t#再gpadmin⽤户下对权限进⾏grantpostgres=# cYou are now connected to database "postgres" as user "gpadmin".postgres=# grant select on shm5.t to role1;GRANT#已经有权限查询postgres=> cYou are now connected to database "postgres" as user "amr_out".postgres=> select * from shm5.t; id | m

----+---(0 rows)