UDN-企业互联网技术人气社区

板块导航

浏览  : 1690
回复  : 0

[讨论交流] 简单介绍数据库前端开发中PostgreSQL 逻辑结构和权限体系

[复制链接]
王许柔的头像 楼主
发表于 2016-5-13 15:14:35 | 显示全部楼层 |阅读模式
  逻辑结构

  最上层是实例,实例中允许创建多个数据库,每个数据库中可以创建多个schema,每个schema下面可以创建多个对象。

  对象包括表、物化视图、操作符、索引、视图、序列、函数、... 等等。
2.png

  在数据库中所有的权限都和角色(用户)挂钩,public是一个特殊角色,代表所有人。

  超级用户是有允许任意操作对象的,普通用户只能操作自己创建的对象。

  另外有一些对象是有赋予给public角色默认权限的,所以建好之后,所以人都有这些默认权限。

  权限体系
2.png

  实例级别的权限由pg_hba.conf来控制,例如 :
  1. # TYPE  DATABASE        USER            ADDRESS                 METHOD
  2. # "local" is for Unix domain socket connections only
  3. local   all             all                                     trust
  4. # IPv4 local connections:
  5. host    all             all             127.0.0.1/32            trust
  6. host all postgres 0.0.0.0/0 reject
  7. host all all 0.0.0.0/0 md5
复制代码

  以上配置的解释

  允许任何本地用户无密码连接任何数据库

  不允许postgres用户从任何外部地址连接任何数据库

  允许其他任何用户从外部地址通过密码连接任何数据库

  数据库级别的权限,包括允许连接数据库,允许在数据库中创建schema。

  默认情况下,数据库在创建后,允许public角色连接,即允许任何人连接。

  默认情况下,数据库在创建后,不允许除了超级用户和owner之外的任何人在数据库中创建schema。

  默认情况下,数据库在创建后,会自动创建名为public 的schema,这个schema的all权限已经赋予给public角色,即允许任何人在里面创建对象。

  schema级别的权限,包括允许查看schema中的对象,允许在schema中创建对象。

  默认情况下新建的schema的权限不会赋予给public角色,因此除了超级用户和owner,任何人都没有权限查看schema中的对象或者在schema中新建对象。

  schema使用 , 特别注意
  1. According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else, or a superuser chooses to create objects in it.

  2. schema的owner默认是该schema下的所有对象的owner,但是PostgreSQL又允许用户在别人的schema下创建对象,所以一个对象可能属于两个owner,而且schema 的owner有 drop对象的权限。  
  3. 对于两个owner都有drop的权限,这个我个人认为是一个BUG。  

  4. 所以千万不要把自己的对象创建到别人的schema下面,那很危险。  
复制代码

  对象级别的权限,每种类型的对象权限属性都不一样,具体请参考
  http://www.postgresql.org/docs/9.5/static/sql-grant.html

  以表为例,可以有SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER这些权限。
  1. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
  2.     [, ...] | ALL [ PRIVILEGES ] }
  3.     ON { [ TABLE ] table_name [, ...]
  4.          | ALL TABLES IN SCHEMA schema_name [, ...] }
  5.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  6. GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
  7.     [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
  8.     ON [ TABLE ] table_name [, ...]
  9.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  10. GRANT { { USAGE | SELECT | UPDATE }
  11.     [, ...] | ALL [ PRIVILEGES ] }
  12.     ON { SEQUENCE sequence_name [, ...]
  13.          | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
  14.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  15. GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
  16.     ON DATABASE database_name [, ...]
  17.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  18. GRANT { USAGE | ALL [ PRIVILEGES ] }
  19.     ON DOMAIN domain_name [, ...]
  20.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  21. GRANT { USAGE | ALL [ PRIVILEGES ] }
  22.     ON FOREIGN DATA WRAPPER fdw_name [, ...]
  23.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  24. GRANT { USAGE | ALL [ PRIVILEGES ] }
  25.     ON FOREIGN SERVER server_name [, ...]
  26.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  27. GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  28.     ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
  29.          | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
  30.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  31. GRANT { USAGE | ALL [ PRIVILEGES ] }
  32.     ON LANGUAGE lang_name [, ...]
  33.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  34. GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  35.     ON LARGE OBJECT loid [, ...]
  36.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  37. GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
  38.     ON SCHEMA schema_name [, ...]
  39.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  40. GRANT { CREATE | ALL [ PRIVILEGES ] }
  41.     ON TABLESPACE tablespace_name [, ...]
  42.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  43. GRANT { USAGE | ALL [ PRIVILEGES ] }
  44.     ON TYPE type_name [, ...]
  45.     TO role_specification [, ...] [ WITH GRANT OPTION ]

  46. where role_specification can be:

  47.     [ GROUP ] role_name
  48.   | PUBLIC
  49.   | CURRENT_USER
  50.   | SESSION_USER

  51. GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
复制代码

  简单介绍一下grant的一些通用选项

  WITH ADMIN OPTION表示被赋予权限的用户,拿到对应的权限后,还能将对应的权限赋予给其他人,否则只能自己有这个权限,但是不能再赋予给其他人。

  用户

  用户,角色在PostgreSQL是一个概念。

  public

  public角色,代表所有人的意思。

  如何查看和解读一个对象的当前权限状态

  以表为例 :
  1. select relname,relacl from pg_class where relkind='r';
复制代码

  或者执行
  1. SELECT n.nspname as "Schema",
  2.   c.relname as "Name",
  3.   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
  4.   pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  5.   pg_catalog.array_to_string(ARRAY(
  6.     SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
  7.     FROM pg_catalog.pg_attribute a
  8.     WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  9.   ), E'\n') AS "Column privileges",
  10.   pg_catalog.array_to_string(ARRAY(
  11.     SELECT polname
  12.     || CASE WHEN polcmd != '*' THEN
  13.            E' (' || polcmd || E'):'
  14.        ELSE E':'
  15.        END
  16.     || CASE WHEN polqual IS NOT NULL THEN
  17.            E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
  18.        ELSE E''
  19.        END
  20.     || CASE WHEN polwithcheck IS NOT NULL THEN
  21.            E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
  22.        ELSE E''
  23.        END    || CASE WHEN polroles <> '{0}' THEN
  24.            E'\n  to: ' || pg_catalog.array_to_string(
  25.                ARRAY(
  26.                    SELECT rolname
  27.                    FROM pg_catalog.pg_roles
  28.                    WHERE oid = ANY (polroles)
  29.                    ORDER BY 1
  30.                ), E', ')
  31.        ELSE E''
  32.        END
  33.     FROM pg_catalog.pg_policy pol
  34.     WHERE polrelid = c.oid), E'\n')
  35.     AS "Policies"
  36. FROM pg_catalog.pg_class c
  37.      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  38. WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
  39.   AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
  40. ORDER BY 1, 2;
复制代码

  得到权限说明如下
  1. Schema |      Name       |   Type   |       Access privileges        | Column privileges | Policies
  2. --------+-----------------+----------+--------------------------------+-------------------+----------
  3. public | sbtest1         | table    | postgres=arwdDxt/postgres     +|                   |
  4.         |                 |          | digoal=a*r*w*d*D*x*t*/postgres |                   |
  5. public | sbtest10        | table    | postgres=arwdDxt/postgres      |                   |
  6. public | sbtest10_id_seq | sequence |                                |                   |
  7. public | sbtest11        | table    | postgres=arwdDxt/postgres      |                   |
  8. public | sbtest11_id_seq | sequence |                                |                   |
  9. public | sbtest12        | table    | postgres=arwdDxt/postgres      |                   |
  10. public | sbtest12_id_seq | sequence |                                |                   |
复制代码

  解释一下 Access privileges

  rolename=xxx 其中rolename就是被赋予权限的用户名,即权限被赋予给谁了?

  =xxx 表示这个权限赋予给了public角色,即所有人

  /yyyy 表示是谁赋予的这个权限?

  权限的含义如下
  1. rolename=xxxx -- privileges granted to a role
  2.         =xxxx -- privileges granted to PUBLIC

  3.             r -- SELECT ("read")
  4.             w -- UPDATE ("write")
  5.             a -- INSERT ("append")
  6.             d -- DELETE
  7.             D -- TRUNCATE
  8.             x -- REFERENCES
  9.             t -- TRIGGER
  10.             X -- EXECUTE
  11.             U -- USAGE
  12.             C -- CREATE
  13.             c -- CONNECT
  14.             T -- TEMPORARY
  15.       arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
  16.             * -- grant option for preceding privilege

  17.         /yyyy -- role that granted this privilege
复制代码

  例子

  赋予权限的人是postgres用户, sbtest2表的select权限被赋予给了digoal用户。
  1. postgres=# grant select on sbtest2 to digoal;
  2. GRANT
  3. postgres=# \dp+ sbtest2
  4.                                   Access privileges
  5. Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
  6. --------+---------+-------+---------------------------+-------------------+----------
  7. public | sbtest2 | table | postgres=arwdDxt/postgres+|                   |
  8.         |         |       | digoal=r/postgres         |                   |
  9. (1 row)
复制代码

  回收权限一定要针对已有的权限来,如果你发现这里的权限还在,那照着权限回收即可。

  例如
  1. revoke select on sbtest2 from digoal;
复制代码

  参考

  • grant
  • revoke

  更高基本的安全控制

  PostgreSQL还支持凌驾于基本权限体系之上的安全策略,这些安全策略一般在企业级的商业数据库中才有。

  行安全策略

  https://yq.aliyun.com/articles/4271

相关帖子

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部