PostgreSQL 批量权限 管理方法

作者

digoal

日期

2016-05-29

标签

PostgreSQL , 批量权限管理 , grant , ALTER DEFAULT PRIVILEGES

背景

关于PostgreSQL的逻辑架构和权限体系，可以参考

《PostgreSQL 逻辑结构 和 权限体系 介绍》

本文将给大家介绍一下如何批量管理表，视图，物化视图的权限。

以及如何管理默认权限，批量赋予schema的权限。

对整个schema的对象进行权限管理

PostgreSQL 从9.0开始就提供了比较方便的对整个schema的指定对象赋权给目标用的语法。

http://www.postgresql.org/docs/9.5/static/sql-grant.html

例子

```
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

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

将schema digoal下的所有表的select,update权限赋予给test用户。

注意

如果digoal.*中包含了非当前用户的表，并且当前用户非超级用户，并且当前用户没有这些表的select,update的with grant option权限。将报错。

换句话说，如果要确保这个赋权操作万无一失，可以选择使用超级用户来执行。

grant select,update on all tables in schema digoal to test;

将schema digoal下的所有表的select,update权限从test用户回收。

revoke select,update on all tables in schema digoal from test;

在对整个schema下的所有对象的权限管理完后， 别忘记了在对象之上，还需要对schema、database、instance进行相应的赋权。

如何设置用户创建的对象的默认权限

另一个问题，如何设置用户新建的对象的默认权限?

在PostgreSQL 9.0以后新加的语法：

http://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html

例如

```
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 ]
```

例子：

将digoal用户未来在public下面创建的表的select,update权限默认赋予给test用户.

postgres=> alter default privileges for role digoal in schema public grant select,update on tables to test; ALTER DEFAULT PRIVILEGES

将test用户未来在public,digoal下面创建的表的select,update权限默认赋予给digoal用户.

postgres=# alter default privileges for role test in schema public,digoal grant select,update on tables to digoal; ALTER DEFAULT PRIVILEGES

查看已经赋予的默认权限

postgres=> \ddp+ Default access privileges Owner | Schema | Type | Access privileges ----------+--------+-------+--------------------------- digoal | public | table | test=rw/digoal test | digoal | table | digoal=rw/test test | public | table | digoal=rw/test

或

```
SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Owner",
n.nspname AS "Schema",
CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' END AS "Type",
pg_catalog.array_to_string(d.defaclacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_default_acl d
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

Owner | Schema | Type | Access privileges
----------+--------+-------+---------------------------
digoal | public | table | test=rw/digoal
postgres | | table | postgres=arwdDxt/postgres+
| | | digoal=arwdDxt/postgres
test | digoal | table | digoal=rw/test
test | public | table | digoal=rw/test
(4 rows)
```

如何定制批量管理权限

将"指定用户" owne 的表、视图、物化视图的"指定权限"赋予给"指定用户"，并排除"指定对象"

这个需求需要写一个函数来完成，如下

```
create or replace function g_or_v
(
g_or_v text, -- 输入 grant or revoke 表示赋予或回收
own name, -- 指定用户 owner
target name, -- 赋予给哪个目标用户 grant privilege to who?
objtyp text, -- 对象类别: 表, 物化视图, 视图 object type 'r', 'v' or 'm', means table,view,materialized view
exp text[], -- 排除哪些对象, 用数组表示, excluded objects
priv text -- 权限列表, privileges, ,splits, like 'select,insert,update'
) returns void as $$
declare
nsp name;
rel name;
sql text;
tmp_nsp name := '';
begin
for nsp,rel in select t2.nspname,t1.relname from pg_class t1,pg_namespace t2 where t1.relkind=objtyp and t1.relnamespace=t2.oid and t1.relowner=(select oid from pg_roles where rolname=own)
loop
if (tmp_nsp = '' or tmp_nsp <> nsp) and lower(g_or_v)='grant' then
-- auto grant schema to target user
sql := 'GRANT usage on schema "'||nsp||'" to '||target;
execute sql;
raise notice '%', sql;
end if;

tmp_nsp := nsp;

if (exp is not null and nsp||'.'||rel = any (exp)) then    
  raise notice '% excluded % .', g_or_v, nsp||'.'||rel;    
else    
  if lower(g_or_v) = 'grant' then    
    sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" to '||target ;    
  elsif lower(g_or_v) = 'revoke' then    
    sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" from '||target ;    
  else    
    raise notice 'you must enter grant or revoke';    
  end if;    
  raise notice '%', sql;    
  execute sql;    
end if;
复制

end loop;
end;
$$ language plpgsql;
```

例子

将digoal用户的所有表(除了'public.test'和'public.abc')的select, update权限赋予给test用户.

```
postgres=# select g_or_v('grant', 'digoal', 'test', 'r', array['public.test', 'public.abc'], 'select, update');
NOTICE: GRANT usage on schema "public" to test
NOTICE: grant select, update on "public"."tb1l" to test
NOTICE: grant select, update on "public"."new" to test
g_or_v

(1 row)

postgres=# \dp+ public.tb1l
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | tb1l | table | test=rw/digoal | |
(1 row)
postgres=# \dp+ public.new
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-----------------------+-------------------+----------
| | | test=rw/digoal | |
(1 row)
```

从 test 用户回收digoal用户的所有表(除了'public.test'和'public.abc')的update权限.

```
postgres=# select g_or_v('revoke', 'digoal', 'test', 'r', array['public.test', 'public.abc'], 'update');
NOTICE: revoke update on "public"."tb1l" from test
NOTICE: revoke update on "public"."new" from test
g_or_v

(1 row)

postgres=# \dp+ public.tb1l
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | tb1l | table | test=r/digoal | |
(1 row)

postgres=# \dp+ public.new
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-----------------------+-------------------+----------
| | | test=r/digoal | |
(1 row)
```

希望这个例子对PostgreSQL的用户有帮助。

do 赋权

如果你的数据库版本太低，还没有支持GRANT/REVOKE IN SCHEMA语法，那么可以使用DO语句来构建一个函数，实现批量的权限赋予。

详见如下

《PostgreSQL permission grant/revoke in schema clause emulate》

其他批量管理

比如批量导入外部表

《PostgreSQL 使用函数生成 外部表DDL(use function generate foreign table DDL in postgresql)》

《PostgreSQL 9.5 使用 import foreign schema 语法一键创建外部表》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.