关于PostgreSQL的逻辑架构和权限体系，可以参考
https://yq.aliyun.com/articles/41210
本文将给大家介绍一下如何批量管理表，视图，物化视图的权限。
以及如何管理默认权限，批量赋予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的用户有帮助。