postgreSQL角色权限控制

1.postgres的逻辑结构：

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

2.创建schema

create schema jayzhou authorization jay;

cd03=> \dn

      List of schemas

      Name      |  Owner  

----------------+----------

cd03           | cd03

jayzhou        | jay

pgpool_catalog | postgres

public         | postgres

(4 rows)

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)