PostgreSQL权限（二）

2019独角兽企业重金招聘Python工程师标准>>>

一.角色权限继承

       在create role时通过指定in role和inherit可以继承权限，这是没问题的，但是系统权限（见postgresql权限（一））是无法继承的，只能通过alter命令进行修改。
lihao=# create role role1 superuser createdb createrole login password '123456';
CREATE ROLE
lihao=# grant select on table t1 to role1;
GRANT
lihao=# create user user1 in role role1 inherit;
CREATE ROLE
lihao=> \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 user1
You are now connected to database "lihao" as user "user1".
lihao=> create database db1;
ERROR:  permission denied to create database
lihao=> create role role1;
ERROR:  permission denied to create role
lihao=> 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 ROLE
lihao=# \c lihao user1
You 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 DATABASE
lihao=# \c db1 lihao
You 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 ROLE
db1=# create user user2 in role role1;
CREATE ROLE
db1=# \c db1 role1
You are now connected to database "db1" as user "role1".
db1=> create table t1 (id int);
CREATE TABLE
db1=> \c db1 user1
You are now connected to database "db1" as user "user1".
db1=> select * from t1;
ERROR:  permission denied for relation t1
db1=> alter default privileges for role role1 in schema public grant select on tables to user1;
ALTER DEFAULT PRIVILEGES
db1=> \c db1 role1
You are now connected to database "db1" as user "role1".
db1=> create table t2 (id int);
CREATE TABLE
db1=> \c db1 user1
You 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 role1
You are now connected to database "lihao" as user "role1".
lihao=> create table t3 (id int);
CREATE TABLE
lihao=> \c lihao user1
You 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。

转载于:https://my.oschina.net/u/1171200/blog/200838