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

作者

digoal

日期

2016-11-14

标签

PostgreSQL , role , 角色 , membership , inherit , 权限继承

背景

在数据库中，如果你想把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赋予给bpostgres=# grant a to b;GRANT ROLE查看membership，可以看到b是a的memberpostgres=# \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 aYou are now connected to database "postgres" as user "a".postgres=> create table r1(id int);CREATE TABLE使用b连接数据库，可以直接使用这张表postgres=> \c postgres bYou are now connected to database "postgres" as user "b".postgres=> insert into r1 values (1);INSERT 0 1postgres=> \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设置为noinheritpostgres=> \c postgres postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter role b noinherit;ALTER ROLE那么b不会自动继承a这个角色，需要显示的set role a;postgres=# \c postgres bYou are now connected to database "postgres" as user "b".postgres=> insert into r1 values (1);ERROR:  permission denied for relation r1postgres=> set role a;SETpostgres=> insert into r1 values (1);INSERT 0 1

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

postgres=# create role c  in role a login;CREATE ROLEpostgres=# \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 ROLEpostgres=# \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 ROLEpostgres=# \c postgres bYou are now connected to database "postgres" as user "b".postgres=> grant a to digoal;GRANT ROLEpostgres=> \c postgres cYou 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 ROLEpostgres=# \c postgres bYou 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 PRIVILEGESDescription: define default access privilegesSyntax:ALTER DEFAULT PRIVILEGES    [ FOR { ROLE | USER } target_role [, ...] ]    [ IN SCHEMA schema_name [, ...] ]    abbreviated_grant_or_revokewhere 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 PRIVILEGESpostgres=# \c postgres aYou are now connected to database "postgres" as user "a".postgres=> create table r1(id int);CREATE TABLEpostgres=> insert into r1 values (1);INSERT 0 1postgres=> \c postgres bYou are now connected to database "postgres" as user "b".postgres=> select * from r1; id ----  1(1 row)

参考