用好PostgreSQL role membership来管理继承组权限

背景

在数据库中，如果你想把A用户创建的对象权限赋予给B用户，或者其他用户。

通常我们会对需要赋权的对象使用grant的语法来赋权。

但是这种方法比较繁琐，因为需要对每个对象，每一组需要的权限进行赋权。

如果你需要整个A用户所有对象的权限，以及它将来创建的对象的所有权限，有没有好的方法呢？

没错，你一定会想到role来管理。

role membership & inherit

例子

数据库有一个a 用户，创建了一些对象，需求是把a 创建的对象，自动赋予给b 用户。

创建a用户
postgres=# create role a login;
CREATE ROLE

创建b用户
postgres=# create role b login;
CREATE ROLE

把a赋予给b
postgres=# grant a to b;
GRANT ROLE

查看membership，可以看到b是a的member
postgres=# \du+ a
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 a         |            | {}        | 

postgres=# \du+ b
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 b         |            | {a}       | 

使用a连接数据库，创建一张表
postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table r1(id int);
CREATE TABLE

使用b连接数据库，可以直接使用这张表
postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> insert into r1 values (1);
INSERT 0 1
postgres=> \d+ r1
                          Table "public.r1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 

postgres=> \dt+ r1
                    List of relations
 Schema | Name | Type  | Owner |    Size    | Description 
--------+------+-------+-------+------------+-------------
 public | r1   | table | a     | 8192 bytes | 
(1 row)

如果b用户为noinherit的，那么需要set role才能切换到对应的role.

将b设置为noinherit
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter role b noinherit;
ALTER ROLE

那么b不会自动继承a这个角色，需要显示的set role a;
postgres=# \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> insert into r1 values (1);
ERROR:  permission denied for relation r1

postgres=> set role a;
SET
postgres=> insert into r1 values (1);
INSERT 0 1

另一种加入member的方法是在创建角色时加入，可以一次指定多个.

postgres=# create role c  in role a login;
CREATE ROLE
postgres=# \du+ c
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 c         |            | {a}       | 


d用户登陆后，会自动继承a,b的权限
postgres=# create role d  in role a,b login;
CREATE ROLE
postgres=# \du+ d
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 d         |            | {a,b}     | 

WITH ADMIN OPTION

与SQL标准一样，加了WITH ADMIN OPTION 则允许被授予的用户继续将权限授予给其他人。

postgres=# grant a to b with admin option;
GRANT ROLE

postgres=# \c postgres b
You are now connected to database "postgres" as user "b".

postgres=> grant a to digoal;
GRANT ROLE

postgres=> \c postgres c
You are now connected to database "postgres" as user "c".

postgres=> grant a to digoal;
ERROR:  must have admin option on role "a"

default privilege

使用角色继承来管理有些时候还是不能满足业务需求，因为业务可能只是想把少量的权限给其他用户，而不是所有权限。

例如，使用角色继承的方法是比较危险的，被授予权限的用户，可以删除对象。

postgres=# alter role b inherit;
ALTER ROLE
postgres=# \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> \dt+ r1
                    List of relations
 Schema | Name | Type  | Owner |    Size    | Description 
--------+------+-------+-------+------------+-------------
 public | r1   | table | a     | 8192 bytes | 
(1 row)

postgres=> drop table r1;
DROP TABLE

如果只想要a用户将来创建的所有表的查询权限，怎么做呢？

Command:     ALTER DEFAULT PRIVILEGES
Description: define default access privileges
Syntax:
ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

例子

postgres=# alter default privileges for role a grant select on tables to b;
ALTER DEFAULT PRIVILEGES

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table r1(id int);
CREATE TABLE
postgres=> insert into r1 values (1);
INSERT 0 1
postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select * from r1;
 id 
----
  1
(1 row)

参考

https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

https://www.postgresql.org/docs/9.6/static/sql-createrole.html

https://www.postgresql.org/docs/9.6/static/sql-grant.html

digoal’s 大量PostgreSQL文章入口