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

本文旨在帮助用户理解PostgreSQL的逻辑结构和权限体系，帮助用户快速的理解和管理数据库的权限。

逻辑结构

最上层是实例，实例中允许创建多个数据库，每个数据库中可以创建多个schema，每个schema下面可以创建多个对象。
对象包括表、物化视图、操作符、索引、视图、序列、函数、... 等等。

在数据库中所有的权限都和角色（用户）挂钩，public是一个特殊角色，代表所有人。
超级用户是有允许任意操作对象的，普通用户只能操作自己创建的对象。
另外有一些对象是有赋予给public角色默认权限的，所以建好之后，所以人都有这些默认权限。

权限体系


实例级别的权限由pg_hba.conf来控制，例如 ：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host all postgres 0.0.0.0/0 reject
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使用 , 特别注意

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.

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

所以千万不要把自己的对象创建到别人的schema下面，那很危险。

对象级别的权限，每种类型的对象权限属性都不一样，具体请参考
http://www.postgresql.org/docs/9.5/static/sql-grant.html
以表为例，可以有SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER这些权限。

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 ]

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

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

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

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

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

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

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

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

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

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

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

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

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

简单介绍一下grant的一些通用选项
WITH ADMIN OPTION表示被赋予权限的用户，拿到对应的权限后，还能将对应的权限赋予给其他人，否则只能自己有这个权限，但是不能再赋予给其他人。

用户

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

public

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

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

以表为例 ：

select relname,relacl from pg_class where relkind='r';

或者执行

SELECT n.nspname as "Schema",
  c.relname as "Name",
  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",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT polname
    || CASE WHEN polcmd != '*' THEN
           E' (' || polcmd || E'):'
       ELSE E':'
       END
    || CASE WHEN polqual IS NOT NULL THEN
           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
       ELSE E''
       END
    || CASE WHEN polwithcheck IS NOT NULL THEN
           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
       ELSE E''
       END    || CASE WHEN polroles <> '{0}' THEN
           E'\n  to: ' || pg_catalog.array_to_string(
               ARRAY(
                   SELECT rolname
                   FROM pg_catalog.pg_roles
                   WHERE oid = ANY (polroles)
                   ORDER BY 1
               ), E', ')
       ELSE E''
       END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n')
    AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

得到权限说明如下

 Schema |      Name       |   Type   |       Access privileges        | Column privileges | Policies
--------+-----------------+----------+--------------------------------+-------------------+----------
 public | sbtest1         | table    | postgres=arwdDxt/postgres     +|                   |
        |                 |          | digoal=arwdDxt*/postgres |                   |
 public | sbtest10        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest10_id_seq | sequence |                                |                   |
 public | sbtest11        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest11_id_seq | sequence |                                |                   |
 public | sbtest12        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest12_id_seq | sequence |                                |                   |

解释一下 Access privileges
rolename=xxx 其中rolename就是被赋予权限的用户名，即权限被赋予给谁了?
=xxx 表示这个权限赋予给了public角色，即所有人
/yyyy 表示是谁赋予的这个权限?
权限的含义如下

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

例子
赋予权限的人是postgres用户， sbtest2表的select权限被赋予给了digoal用户。

postgres=# grant select on sbtest2 to digoal;
GRANT
postgres=# \dp+ sbtest2
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
 public | sbtest2 | table | postgres=arwdDxt/postgres+|                   |
        |         |       | digoal=r/postgres         |                   |
(1 row)

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

revoke select on sbtest2 from digoal;

参考

grant
revoke

更高基本的安全控制

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

行安全策略

SELinux-PostgreSQL

时间： 2024-05-25 07:13:10

PostgreSQL 逻辑结构 和 权限体系 介绍的相关文章

PostgreSQL · 特性分析 · 逻辑结构和权限体系

本文旨在帮助用户理解PostgreSQL的逻辑结构和权限体系,帮助用户快速的理解和管理数据库的权限. 逻辑结构 最上层是实例,实例中允许创建多个数据库,每个数据库中可以创建多个schema,每个schema下面可以创建多个对象. 对象包括表.物化视图.操作符.索引.视图.序列.函数.- 等等. 在数据库中所有的权限都和角色(用户)挂钩,public是一个特殊角色,代表所有人. 超级用户是有允许任意操作对象的,普通用户只能操作自己创建的对象. 另外有一些对象是有赋予给public角色默认权限的,所

MaxCompute和DataIDE权限体系介绍

产品关系 MaxCompute是阿里云自主研发的大数据计算引擎.通过MaxCompute,可以使用SQL.MapReduce和Graph的计算模型处理海量数据.产品还提供了机器学习的功能,并包装成机器学习这个产品对外提供服务.用户使用MaxCompute,不需要关心底层的分布式计算细节,就可以轻松地处理海量数据. 但是直接用MaxCompute也有一些不方便之处,比如需要更方便地进行开发代码的管理.进行数据的导入导出.设置任务的定时调度以及上下游依赖.管理任务的运行情况并做好异常报警.需要有简单

【MySql】mysql 的权限体系介绍

mysql 的权限体系大致分为5个层级: 全局层级 全局权限适用于一个给定服务器中的所有数据库.这些权限存储在mysql.user表中.GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限. 数据库层级 数据库权限适用于一个给定数据库中的所有目标.这些权限存储在mysql.db和mysql.host表中.GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限. 表层级 表权限适用于一个给定表中的所有列

Swift中的Access Control权限控制介绍

  这篇文章主要介绍了Swift中的Access Control权限控制介绍,本文讲解了private.internal.public三个关键字的使用,需要的朋友可以参考下 如果您之前没有接触过权限控制,先来听一个小故事: 小明是五道口工业学院的一个大一新生,最近他有点烦恼,因为同屋经常用他的热水壶,好像那是自己家的一样,可是碍于同学情面,又不好意思说.直到有一天,他和学姐小K吐槽. 学姐听了之后,说:大学集体生活里面,大部分东西都是默认室友可以共用的.如果你不想别人拿,我可以帮你做封印,只要打

PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤

链接:http://blog.itpub.net/28602568/viewspace-2077893/ 标题:PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤  作者:lōττéry版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.] PostgreSQL 9.3.1 官方中文手册介绍pg_stat_statements F.28. pg_stat_statements pg_stat_statements模块提供一种跟踪执行统计服务器

理解SQL Server中的权限体系(下)----安全对象和权限

原文:http://www.cnblogs.com/CareySon/archive/2012/04/12/SQL-Security-SecurableAndPermission.html     在开始阅读本文之前,请确保你已经阅读过上一篇文章,文章地址:     理解SQL Server中的权限体系(上)----主体   简介     在上一篇文章中,我对主体的概念做了全面的阐述.本篇文章接着讲述主体所作用的安全对象以及所对应的权限.   理解安全对象(Securable)     安全对象

百度数据仓库体系介绍

百度数据仓库体系介绍 刘立萍   liuliping@baidu.com 2012年11月27日 --我们要解决的大数据问题 --百度数据仓库产品 --百度大数据多维分析/报表引擎产品 temp_12120509416231.pdf

PostgreSQL教程(十二)：角色和权限管理介绍_PostgreSQL

PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.     一.数据库角色:     1. 创建角色:   复制代码 代码如下:     CREATE ROLE role_name;       2. 删除角色:   复制代码 代码如下:     DROP ROLE role_name;         3. 查询角色:

PHP 获取文件权限函数介绍

以下是对PHP中的获取文件权限函数进行了介绍,需要的朋友可以参考下   复制代码 代码如下: /*  * substr 返回字符串的子串  * base_convert  在任意进制之间转换数字  * fileperms  取得文件的权限  */ // 获取权限 function getChmod($filepath){     return substr(base_convert(@fileperms($filepath),10,8),-4); }