作者
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
https://www.postgresql.org/docs/9.6/static/sql-grant.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
