PostgreSQL（七）权限管理

一、PG的权限说明

1、每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限；

对象所有者可以自己回收、重授权自己的权限，用于防止误操作；

2、在数据库中，所有的权限都和角色挂钩；

3、对超级用户 postgres不做权限检查，其它用户走ACL（Access Control list）；

4、对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL；

二、权限管理层次

1、实例权限：控制部分用户、主机是否允许登录、访问数据库（白名单&黑名单）；

2、schema：PG的一个用户可以有多个schema，但一个schema只能属于一个用户；

3、object：表、视图、索引等对象的增删改查的权限

三、对象权限概述及权限列表

1、权限类别概述

表级对象权限控制；

列级别权限控制；

序列权限控制；

类型域的权限控制（域简单来说就是自定义的带约束的数据类型）；

FDW权限控制；

FS权限控制；

函数权限控制；

\ h grant显示所有可设置的访问权限；

2、对象权限列表与含义

rolename = xxxx --privileges granted to a role
         = XXXX --privileges granted to PUBLIC
              r --SELECT ("read")    (允许从指定表,视图或序列的任何列或列出的特定列进行 SELECT。允许用 COPY TO。允许在UPDATE或 DELETE中引用现有列值。
                                     对于序列,此权限还允许使用currval函数。对于大对象,此权限允许读取对象)
              W --UPDATE ("write")    (允许将新行INSERT到指定的表中。如果列出了特定列,则只能在INSERT命令中为这些列分配(因此其他列将接收默认值)。也允许 COPY FROM。)
              a --INSERT ("append") (允许更新指定表的任何列或列出的特定列,依赖SELECT权限。)
              d --DELETE             (允许删除指定表中的行,依赖SELECT权限)
              D --TRUNCATE            (允许在指定的表上截断数据)
              X --REFERENCES        (允许创建引用指定表或表的指定列的外键约束)
              t --TRIGGER            (允许在指定的表上创建触发器)
              X --EXECUTEU             (允许使用指定的函数或过程以及在函数)
              U --USAGE                (对于schema,允许访问指定模式中包含的对象;对于sequence,允许使用currval和nextval函数。对于类型和域,允许在创建表、函数和其他模式对象时使用类型或域)
              C --CREATE            (对于数据库,允许在数据库中创建新的schema、table、 index)
              c --CONNECT            (允许用户连接到指定的数据库。在连接启动时检查此权限)
              T --TEMPORARY            (TEMP,允许在使用指定数据库时创建临时表)
        arwddxt --ALL PRIVILEGES (for tables,varies for other objects 
              * --grant option for preceding privilege        (一次授予所有可用权限)
          /yyyy --role that granted this privilege

四、权限管理（语法）

1、授权

详细语法可在PG中执行** \h grant **查看；

简易语法与sql示例如下：

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

e.g.:
//授权用户wqdb在数据库wqdb上的一切create权限：
GRANT CREATE ON DATABASE wqdb TO wqdb;
//授权访问模式的权限给用户
GRANT USAGE ON SCHEMA $sch_name TO $role_name;
//授权指定模式下对象的访问权限给用户
GRANT SELECT ON $sch_name.$tab_name TO $role_name;

2、回收权限

详细语法可在PG中执行 **\h revoke **查看；

简易语法与sql示例如下：

//回收单个权限
REVOKE SELECT ON $tab_name FROM $role_name
//回收多个/所有权限
REVOKE SELECT,UPDATE,INSERT ON $tab_name FROM $role_name;
REVOKE ALL ON $tab_name FROM $role_name;
//回收某模式下所有表的查询权限
REVOKE SELECT ON ALL TABLES IN SCHEMA $schema_name FROM $role_name;
//收列权限
REVOKE SELECT(col1),UPDATE(col1) ON $tab_name FROM $role_name;
//回收所有权限
REVOKE ALL ON $tab_name FROM public;

3、易主

（1）转移单个表的属主（超级用户、属主可操作，原属主必须是被授权用户的成员）

ALTER TABLE $tab_name OWNER TO $new_owner_name;

（2）转移当前数据库表的属主（超级用户可操作，如果属主是postgres则不允许，因为其中包含数据字典表，只能用单表易主的方式修改）

REASSIGN OWNED BY $old_role_name TO $new_role_name;

五、小实验

1、实验内容

执行SQL--GRANT **select **ON all tables IN SCHEMA $sch_name TO $role_name;

授权某模式下所有表的查询权限给某个用户。

授权成功后再新建表，此用户能否拥有查看后建的表数据的权限？

答案：不能。

2、实验过程

//1、打开窗口1，超级用户登录，创建实验用户
su - postgres
psql
postgres=# select user;
   user   
----------
 postgres
(1 行记录)
postgres=# create user u1 password 'u1';
CREATE ROLE
postgres=# grant create on database wqdb to u1;
GRANT
postgres=# create user u2 password 'u2';
CREATE ROLE
postgres=# \du
                               角色列表
  角色名称   |                    属性                    |  成员属于  
-------------+--------------------------------------------+------------
 dbcreate    | 建立 DB, 无法登录                          | {}
 pgloader_pg |                                            | {}
 postgres    | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 u1          |                                            | {}
 u2          |                                            | {}

//2、打开窗口2，u1用户登录，创建实验模式u1test与实验表a
psql -U u1 -d wqdb
wqdb=> create schema u1test;
CREATE SCHEMA
wqdb=> create table u1test.a(id int);
CREATE TABLE
wqdb=> insert into u1test.a values (1);
INSERT 0 1
wqdb=> select * from u1test.a;
 id 
----
  1
(1 行记录)

//3、打开窗口3，u2用户登录，查询实验表
psql -U u2 -d wqdb
wqdb=> set search_path="$user", public,u1test;
SET
wqdb=> \d
             关联列表
 架构模式 | 名称 |  类型  | 拥有者 
----------+------+--------+--------
 u1test   | a    | 数据表 | u1
(1 行记录)
wqdb=> select * from u1test.a;
 id 
----
  1
(1 行记录)

//4、回到窗口2，新建表b
wqdb=> create table u1test.b(id int);
CREATE TABLE
wqdb=> insert into u1test.b values (2);
INSERT 0 1
wqdb=> select * from u1test.b;
 id 
----
  2
(1 行记录)

//5、回到窗口3，查看表b
wqdb=> \d
             关联列表
 架构模式 | 名称 |  类型  | 拥有者 
----------+------+--------+--------
 u1test   | a    | 数据表 | u1
 u1test   | b    | 数据表 | u1
(2 行记录)

wqdb=> select * from u1test.b;
错误:  对表 b 权限不够

3、总结

从最后的查询结果来看，授权后再新建的表，使用\d可以看到表存在，但无法查看表数据。

（pg可以实现：授权查看schema下所有表的权限后，也能查看后建的表数据。

具体实现方法等待更新中）