pg（hgdb）权限实验

创建用户的语句为：
CREATE ROLE guest LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意上述Role guest拥有LOGIN的权限，所以叫它用户。


创建角色的语句为：
CREATE ROLE "group" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意这里没有LOGIN权限，所以是角色。


上述角色和用户的创建语句中，都没有赋予超级用户、创建数据库等权限。


怎么样控制登录用户操作数据库对象的权限呢？
答案是让用户成为角色的成员，此时用户即可拥有角色的权限，进一步限制了登录用户操作数据库对象的权限。
如把上述角色group赋予guest用户：
GRANT "group" TO guest;
之后，guest用户就拥有了group角色所拥有的数据库对象权限。
==============================================================================
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 highgo    | Superuser, Create role, Create DB, Replication | {}
 
highgo=# create schema a;
CREATE SCHEMA
highgo=# create schema b;
CREATE SCHEMA


highgo=# create role a login password 'highgo123';
CREATE ROLE
highgo=# create role b login password 'highgo123';
CREATE ROLE
highgo=# alter schema a owner to a;
ALTER SCHEMA


highgo=# alter schema b owner to b;
ALTER SCHEMA


highgo=# create table a.test (id int);
CREATE TABLE
highgo=# insert into a.test values (1);
INSERT 0 1
highgo=# create table b.test (id int);
CREATE TABLE
highgo=# insert into b.test values (2);
INSERT 0 1


highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from test;
错误:  对关系 test 权限不够
highgo=> select * from a.test;
错误:  对关系 test 权限不够
highgo=> select * from b.test;
错误:  对模式 b 权限不够
LINE 1: select * from b.test;
                      ^
 
highgo=# \c
You are now connected to database "highgo" as user "highgo". 
highgo=# grant select on a.test to a;
GRANT


highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from a.test;
 id 
----
  1
(1 row)
highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.test;
错误:  对模式 a 权限不够
LINE 1: select * from a.test;
                      ^
 
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# grant select on b.test to b;
GRANT


highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from b.test;
错误:  对模式 b 权限不够
LINE 1: select * from b.test;
                      ^
highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from b.test;
 id 
----
  2
(1 row)




highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 a         |                                                | {}
 b         |                                                | {}
 highgo    | Superuser, Create role, Create DB, Replication | {}


highgo=> \d
                List of relations
     Schema     |      Name      | Type  | Owner  
----------------+----------------+-------+--------
 a              | test           | table | highgo
 oracle_catalog | dual           | view  | highgo
 public         | my_colors      | table | highgo
 public         | myt            | table | highgo
 public         | pg_buffercache | view  | highgo
(5 rows)




highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 a         |                                                | {}
 b         |                                                | {}
 highgo    | Superuser, Create role, Create DB, Replication | {}


highgo=> \du a
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 a         |            | {}
 
highgo=> \du b
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 b         |            | {}


highgo=> \d
                List of relations
     Schema     |      Name      | Type  | Owner  
----------------+----------------+-------+--------
 b              | test           | table | highgo
 oracle_catalog | dual           | view  | highgo
 public         | my_colors      | table | highgo
 public         | myt            | table | highgo
 public         | pg_buffercache | view  | highgo
(5 rows)
 
 ==============================================================================================
 highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> 
highgo=> 
highgo=> 
highgo=> create table atest (id int);
CREATE TABLE
highgo=> insert into atest values (3);
INSERT 0 1
highgo=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 a         |                                                | {}
 b         |                                                | {}
 highgo    | Superuser, Create role, Create DB, Replication | {}


highgo=> \d
                List of relations
     Schema     |      Name      | Type  | Owner  
----------------+----------------+-------+--------
 a              | atest          | table | a
 a              | test           | table | highgo
 oracle_catalog | dual           | view  | highgo
 public         | my_colors      | table | highgo
 public         | myt            | table | highgo
 public         | pg_buffercache | view  | highgo
(6 rows)
highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> grant select on atest to b;
GRANT
highgo=> grant select on a.atest to b;
GRANT


highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.atest;
错误:  对模式 a 权限不够
LINE 1: select * from a.atest;
                      ^
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# grant select on a.atest to b;
GRANT
highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.atest;
错误:  对模式 a 权限不够
LINE 1: select * from a.atest;
                      ^
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# select * from atest;
错误:  关系 "atest" 不存在 第 15 个字符处
语句:  select * from atest;
错误:  关系 "atest" 不存在
LINE 1: select * from atest;
                      ^              ^
highgo=# select * from a.atest;
 id 
----
  3
(1 row)




highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# 
highgo=# revoke select on a.atest from a;
REVOKE


highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from atest;
错误:  对关系 atest 权限不够




highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# grant select on a.atest to a;
GRANT


highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from atest;
 id 
----
  3
(1 row)




highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# grant select on all tables in schema a to b;
GRANT


highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from b.test;
 id 
----
  2
(1 row)


highgo=> select * from test;
 id 
----
  2
(1 row)


highgo=> select * from a.test;
错误:  对模式 a 权限不够
LINE 1: select * from a.test;
                      ^
highgo=> 




在PostgreSQL中，即使已经将schema中的表和函数的访问权限授予某个角色，在没有此schema的USAGE权限的情况下，此角色依然不能访问此schema种的表或者函数对象。
highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> grant usage on schema a to b;                       *****************************************************
GRANT
highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.test;
 id 
----
  1
(1 row)






highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.test;
 id 
----
  1
(1 row)


highgo=> select * from a.atest;
 id 
----
  3
(1 row)


highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# revoke select on a.atest from b;
REVOKE
或者revoke usage on schema a from b;
REVOKE
highgo=> \c
You are now connected to database "highgo" as user "b".
highgo=> select * from a.atest;
错误:  对关系 atest 权限不够
highgo=>