一.角色权限继承

       在create role时通过指定in role和inherit可以继承权限，这是没问题的，但是系统权限（见postgresql权限（一））是无法继承的，只能通过alter命令进行修改。lihao=# create role role1 superuser createdb createrole login password '123456';CREATE ROLElihao=# grant select on table t1 to role1;GRANTlihao=# create user user1 in role role1 inherit;CREATE ROLElihao=> \du+                                    List of roles Role name |                   Attributes                   | Member of | Description -----------+------------------------------------------------+-----------+------------- lihao     | Superuser, Create role, Create DB, Replication | {}        |  role1     | Superuser, Create role, Create DB, Replication | {}        |  user1     |                                                                           | {role1}   | lihao=# \c lihao user1You are now connected to database "lihao" as user "user1".lihao=> create database db1;ERROR:  permission denied to create databaselihao=> create role role1;ERROR:  permission denied to create rolelihao=> select * from t1; id ----(0 rows)

lihao=> \c lihao role1

You are now connected to database "lihao" as user "role1".

lihao=# alter user user1 createdb;

ALTER ROLElihao=# \c lihao user1You are now connected to database "lihao" as user "user1".lihao=> create database test;CREATE DATABASE

二.角色默认权限修改

      这里使用的是ALTER DEFAUT PRIVILEGES命令。可以用这条命令来修改修改的是“将来”你或你所在角色组要创建对象的权限，对已有对象的权限不会有影响。这种修改在当前库或指定模式下是全局性的，目前只能修改表（包括外部表），序列，函数，类型（包括域）。

//验证组成员可以修改所在角色组的default权限

lihao=# create database db1 owner "lihao";CREATE DATABASElihao=# \c db1 lihaoYou are now connected to database "db1" as user "lihao".db1=# create role role1 login password 'oracle';CREATE ROLE//使用NOINHERIT创建用户，否则user1默认继承role1的select权限db1=# create user user1 in role role1 noinherit password 'oracle';CREATE ROLEdb1=# create user user2 in role role1;CREATE ROLEdb1=# \c db1 role1You are now connected to database "db1" as user "role1".db1=> create table t1 (id int);CREATE TABLEdb1=> \c db1 user1You are now connected to database "db1" as user "user1".db1=> select * from t1;ERROR:  permission denied for relation t1db1=> alter default privileges for role role1 in schema public grant select on tables to user1;ALTER DEFAULT PRIVILEGESdb1=> \c db1 role1You are now connected to database "db1" as user "role1".db1=> create table t2 (id int);CREATE TABLEdb1=> \c db1 user1You are now connected to database "db1" as user "user1".db1=> select * from t2; id ----(0 rows)db1=> select * from t1;ERROR:  permission denied for relation t1

db1=> alter default privileges for user user2  in schema public grant select on tables to user1;

ERROR:  must be member of role "user2

//证明这条修改只能在当前库下生效

db1=> \c lihao role1You are now connected to database "lihao" as user "role1".lihao=> create table t3 (id int);CREATE TABLElihao=> \c lihao user1You are now connected to database "lihao" as user "user1".lihao=> select * from t3;ERROR:  permission denied for relation t3

注意：

       通过读文档大家可以看到alter default privileges***revoke**操作，然后在实验这条命令，比如此时有多个角色成员，却只想要回收掉角色成员user1在角色role1”将来要“创建对象上的select权限，会发现命令提示成功，却没有生效，这是因为之前所说的，revoke操作只能回收grant赋予的权限，对于角色成员所继承的角色权限不能在角色成员上revoke掉，只能通过对角色进行revoke。