PostgreSQL 批量权限 管理方法

背景

关于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 语法一键创建外部表》

digoal’s 大量PostgreSQL文章入口