Postgres schema间对象的访问操作权限

schema间对象的访问操作权限

（1）使用超级用户highgo登录数据库，创建测试schema a和b，并创建测试表a.at和b.bt

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a        |                                               | {}

 highgo   | Superuser, Create role, Create DB, Replication | {}

 

highgo=#\du

                             List of roles

 Role name |                   Attributes                   | Member of

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

 a        |                                                | {}

 highgo   | Superuser, Create role, Create DB, Replication | {}

 

highgo=#  select current_user;

 current_user

--------------

 highgo

(1row)

highgo=#create schema a;

CREATESCHEMA

highgo=#create schema b;

CREATESCHEMA

highgo=#create table a.at (id int);

CREATETABLE

highgo=#insert into a.at values (1);

INSERT0 1

highgo=#create table b.bt (id int);

CREATETABLE

highgo=#insert into b.bt values (1);

INSERT0 1

highgo=#select * from a.at;

 id

----

  1

(1row)

highgo=#select * from b.bt;

 id

----

  1

(1row)

 

highgo=>\dn

     List of schemas

      Name     | Owner 

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

 a             | highgo

 b             | highgo

 hgdb_catalog  | highgo

 oracle_catalog | highgo

 public        | highgo

(5rows)

（2）验证使用test查询a.at和b.bt

使用test用户登录数据库，查询a.at和b.bt，会报出无权限

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>select * from a.at;

错误:  对模式 a 权限不够

LINE1: select * from a.at;

                      ^

highgo=>select * from b.bt;

错误:  对模式 b 权限不够

LINE1: select * from b.bt;

使用超级用户登录数据库，将表a.at和b.bt的select权限赋予test

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

highgo=#grant select on a.at to test;

GRANT

highgo=#grant select on b.bt to test;

GRANT

此时，test仍然无法select表a.at和b.bt

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>select current_user;

 current_user

--------------

 test

(1row)

 

highgo=>select * from a.at;

错误:  对模式 a 权限不够

LINE1: select * from a.at;

                      ^

highgo=>select * from b.bt;

错误:  对模式 b 权限不够

LINE1: select * from b.bt;

此时需要将schema a和schema b的usage权限赋予test

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

 

highgo=#grant usage on schema a to test;

GRANT

highgo=#grant usage on schema b to test;

GRANT

 

之后，test可以查询a.at和b.bt的数据

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>select * from a.at;

 id

----

  1

(1row)

 

highgo=>select * from b.bt;

 id

----

  1

(1row)

（3）验证test对a.at和b.bt的增删改操作，均需要对指定对象赋予指定的权限才可以操作

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>update a.at set id=2 where id=1;

错误:  对关系 at 权限不够

highgo=>update b.bt set id=2 where id=1;

错误:  对关系 bt 权限不够

highgo=>

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

highgo=#grant update on a.at to test;

GRANT

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>update a.at set id=2 where id=1;

UPDATE1

highgo=>update b.bt set id=2 where id=1;

错误:  对关系 bt 权限不够

 

highgo=>insert into a.at values(2);

错误:  对关系 at 权限不够

highgo=>insert into b.bt values(2);

错误:  对关系 bt 权限不够

highgo=>

highgo=>delete from a.at;

错误:  对关系 at 权限不够

highgo=>delete from b.bt;

错误:  对关系 bt 权限不够

highgo=>

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

highgo=#grant insert,delete on a.at to test;

GRANT

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>insert into a.at values(2);

INSERT0 1

highgo=>insert into b.bt values(2);

错误:  对关系 bt 权限不够

highgo=>

highgo=>delete from a.at;

DELETE2

highgo=>  delete from b.bt;

错误:  对关系 bt 权限不够

（4）回收test对a.at的所有权限

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>select * from a.at;

 id

----

(0rows)

highgo=>insert into a.at values(1);

INSERT0 1

highgo=>update a.at set id=2;

UPDATE1

highgo=>delete from a.at;

DELETE1

回收test对表a.at的所有权限：

highgo=>\c highgo highgo

Youare now connected to database "highgo" as user "highgo".

highgo=#revoke all on schema a from test;

REVOKE

回收后test无法操作a.at表：

highgo=#\c highgo test

Youare now connected to database "highgo" as user "test".

highgo=>select * from a.at;

错误:  对模式 a 权限不够

LINE1: select * from a.at;

                      ^

highgo=>insert into a.at values(1);

错误:  对模式 a 权限不够

LINE1: insert into a.at values(1);

                    ^

highgo=>update a.at set id=2;

错误:  对模式 a 权限不够

LINE1: update a.at set id=2;

               ^

highgo=>delete from a.at;

错误:  对模式 a 权限不够

LINE1: delete from a.at;