开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 批量权限 管理方法

简介:
+关注继续查看

关于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的用户有帮助。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
js包管理工具yarn
Yarn是类似npm的另一种javascript包管理工具。 它的目的是解决使用 npm 时面临的少数问题,即: 1. 安装的时候无法保证速度/一致性 2. 安全问题,因为 npm 安装时允许运行代码
24 0
RH358管理DNS和DNS服务器--使用BIND 9配置授权名称服务器
RH358管理DNS和DNS服务器--使用BIND 9配置授权名称服务器
65 0
使用APICloud AVM框架开发人事档案管理助手app实战
由于人事档案具有涉密性,所以本应用没有使用后台服务,全部功能都在APP本地实现。开发工具采用 APICloud Studio3,基于VSCode的(PS:比基于Atom的autio2好用太多)。
54 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 22 章 管理数据库_22.3. 模板数据库
22.3. 模板数据库 CREATE DATABASE实际上通过拷贝一个已有数据库进行工作。默认情况下,它拷贝名为template1的标准系统数据库。所以该数据库是创建新数据库的“模板”。 如果你为template1数据库增加对象,这些对象将被拷贝到后续创建的用户数据库中。
1122 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 22 章 管理数据库
第 22 章 管理数据库 目录 22.1. 概述 22.2. 创建一个数据库 22.3. 模板数据库 22.4. 数据库配置 22.5. 销毁一个数据库 22.6. 表空间 每个正在运行的PostgreSQL服务器实例都管理着一个或多个数据库。
993 0
PostgreSQL服务器管理:数据库角色
PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组,这取决于角色被怎样设置。角色可以拥有数据库对象(例如,表和函数)并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。
1458 0
基于jsp+servlet图书管理系统之后台用户信息修改操作
上一篇的博客写的是查询操作,且附有源码和数据库,这篇博客写的是修改操作,附有从头至尾写的代码(详细的注释)和数据库!   此次修改操作的源码和数据库:http://download.csdn.net/detail/biexiansheng/9732691  为了方便理解和说明,先写一下执行的流程和步奏,详细代码可以下载连接。
942 0
使用 Trello 管理自己与团队的工作
本文转载http://iprogramming.diandian.com/?tag=%E6%95%8F%E6%8D%B7%E8%BD%AF%E4%BB%B6%E5%BC%80%E5%8F%91   使用 Trello 管理自己与团队的工作   Trello 是一个敏捷项目管理工具,它可以很好的管理团队和个人的工作,下面我来介绍下如何使用它。
1026 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载