用好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赋予给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
