postgreSQL角色权限控制

电玩女神 2022-08-18 03:23 253阅读 0赞

1.postgres的逻辑结构：

> databases->database->schema->object 其中schema就像操作系统的目录一样。

2.创建schema

3.在schema下创建表test\_20150710

> cd03=> \\c
> 
> You are now connected to database "cd03" as user "cd03".
> 
> cd03=> create table jayzhou.test\_20150710(id varchar(10));
> 
> ERROR:  permission denied for schema jayzhou
> 
> 注意：这里失败的原因是cd03的用户没有权限在schema下面做任何操作，因为你不是超级用户，也没有schema：jayzhou的使用权限。（就像操作系统一样，jayzhou是jay建立的文件夹，除了系统管理员用户和jay是没有办法进入的，除非授予了权限）
> 
> cd03=> \\c cd03 jay
> 
> You are now connected to database "cd03" as user "jay".
> 
> cd03=> create table jayzhou.test\_20150710(id varchar(10));
> 
> CREATE TABLE
> 
> cd03=> set search\_path to jayzhou;
> 
> SET
> 
> cd03=> \\dt
> 
>              List of relations
> 
> Schema  |     Name      | Type  |  Owner  
> 
> \---------+---------------+-------+----------
> 
> jayzhou | jay111        | table | postgres
> 
> jayzhou | test111       | table | jay
> 
> jayzhou | test222       | table | jay
> 
> jayzhou | test\_20150710 | table | jay
> 
> (4 rows)

4.对schema授权

如果要把schema：jayzhou下的表的权限授予没有进入该schema权限的用户，首先要对该用户授予该schema的使用权限。

cd03=> grant usage on schema jayzhou to cd03;

GRANT

5.对表授权

> cd03=> \\c cd03 cd03
> 
> You are now connected to database "cd03" as user "cd03".
> 
> cd03=> select \* from jayzhou.test\_20150710 ;
> 
> ERROR:  permission denied for relation test\_20150710
> 
> cd03=> \\c cd03 jay
> 
> You are now connected to database "cd03" as user "jay".
> 
> cd03=> grant select on jayzhou.test\_20150710 to cd03;
> 
> GRANT
> 
> cd03=> \\c cd03 cd03
> 
> You are now connected to database "cd03" as user "cd03".
> 
> cd03=> select \* from jayzhou.test\_20150710 ;id
> 
> \----
> 
> (0 rows)