用好PostgreSQL role membership来管理继承组权限 原作者:digoal/德哥 创作时间:2016-11-15 17:30:17+08 |
doudou586 发布于2016-11-15 16:30:17 评论: 1 浏览: 30280 顶: 5164 踩: 5355 |
在数据库中,如果你想把A用户创建的对象权限赋予给B用户,或者其他用户。
通常我们会对需要赋权的对象使用grant的语法来赋权。
但是这种方法比较繁琐,因为需要对每个对象,每一组需要的权限进行赋权。
如果你需要整个A用户所有对象的权限,以及它将来创建的对象的所有权限,有没有好的方法呢?
没错,你一定会想到role来管理。
例子
数据库有一个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} |
与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"
使用角色继承来管理有些时候还是不能满足业务需求,因为业务可能只是想把少量的权限给其他用户,而不是所有权限。
例如,使用角色继承的方法是比较危险的,被授予权限的用户,可以删除对象。
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)