PostgreSQL 用户及授权管理 04：授予及回收权限

PostgreSQL 是一个坚如磐石的数据库，它非常注重安全性，提供了非常丰富的基础设施来处理权限、特权和安全策略。在前面的章节中以我们介绍的基本概念为基础，重新审视角色概念，特别关注授予角色的安全性和权限（角色可以是用户，也可以是用户组）。我们将学习如何配置角色的各个方面以细致管理安全性，从连接到访问数据库中的数据。

授予及回收权限

在前面的小节中我们了解到，角色与权限集合相关联，这些权限通过 GRANT 语句授予，并通过 REVOKE 语句回收，权限以 ACL 的形式存储在内部。

本小节将深入探讨 GRANT 和 REVOKE 语句，以更好地帮助我们了解如何针对不同的数据库对象而它们进行不同的授权。

GRANT 语句的语法如下：

GRANT <permission, permission, ...> ON <database-object> TO <role>;

我们可以把需要的权限列出来，以逗号为分割，然后指定数据库。还可以使用 WITH GRANT OPTION 子句扩展 GRANT 语句，这将使得目标角色能够将其收到的相同权限授予另一个角色。

REVOKE 语句的语法如下：

REVOKE <permission, permission, ..> ON <database-object> FROM <role>;

在处理权限管理时，可以使用一个名为 PUBLIC 的特殊角色。它不是一个具体的角色，而是一个表示“所有可用角色”的标记。换言之，如果向 PUBLIC 授予权限，则会隐式地将此权限授予所有可用角色。

但是，“所有可用角色”是什么意思？它意味着所有现有和未来的角色。PUBLIC 角色表示在管理权限时和将来将存在于系统中的任何角色。

根据上述内容，为了防止任何用户访问我们的数据，我们应该始终从特殊的公共角色中删除所有权限，然后有选择性地授予特定角色所需的权限。也就是按需授予指定的权限。

表相关的权限

我们已经看到了与数据库表相关的主要权限。它们指的是可以针对表对象运行的主要语句，例如 SELECT、INSERT、UPDATE、DELETE 和 TRUNCATE。此外，可以使用特殊关键字 TRIGGER 和 REFERENCES 在表中创建触发器和外键。

当然，关键字 ALL 则包含了所有权限。例如，为了向 forum_stats 角色组提供读取、更新和插入数据到 categories 表中的权限，而无需授予执行其他操作的权限，我们可以在连接 forum 用户后执行以下操作：

$ psql -U forum forumdb

forumdb=> REVOKE ALL ON forum.categories FROM forum_stats;
REVOKE

forumdb=> GRANT SELECT, INSERT, UPDATE ON forum.categories TO forum_stats;
GRANT

forumdb=> \dp categories
                                    Access privileges
 Schema |    Name    | Type  |     Access privileges      | Column privileges | Policies 
--------+------------+-------+----------------------------+-------------------+----------
 forum  | categories | table | forum=arwdDxt/forum       +|                   | 
        |            |       | forum_admins=arwdDxt/forum+|                   | 
        |            |       | taoqi=arw/forum           +|                   | 
        |            |       | =d/forum                  +|                   | 
        |            |       | forum_stats=arw/forum      |                   | 
(1 row)

第一个 REVOKE 语句不是强制性的，但这是一个很好的做法。由于我们希望确保该角色恰好具有我们要授予的权限，而不是更多，因此先从角色中删除所有权限可以确保任何之前的 GRANT 授权语句会被回收。

基于列的权限

基于列的授权与前面的授权语法是一样的，我只需要把相关的列写出来即可。

列的授权只能应用于 SELECT、UPDATE、INSERT 和 REFERENCES 权限。

举例如下，考虑一个场景，即 forum_stats 用户组只能通过 gecos 和 username 列与 users 表交互，能够读取这两个列，但只更新第一个。权限可以由用户 forum 分配如下：

forumdb=> SELECT current_role;
 current_role 
--------------
 forum
(1 row)

forumdb=> REVOKE ALL ON forum.users FROM forum_stats;

forumdb=> GRANT SELECT (username, gecos), UPDATE (gecos) ON forum.users TO forum_stats;
GRANT

正如我们前面建议的那样，最好先执行第一个 REVOKE 语句，以确保在我们分配我们想要的角色之前重置角色的权限。然后，我们再授予 SELECT 和 UPDATE 权限。

前面的 GRANT 语句的副作用是，forum_stats 角色不再能够执行比 GRANT 中指定的列列表更多的 SELECT 或 UPDATE。我们验证如下：

$ psql -U taoqi forumdb

-- 执行失败，不是所有的列都可以访问
forumdb=> SELECT * FROM forum.users;
ERROR:  permission denied for table users

-- 执行成功
forumdb=> SELECT gecos, username FROM forum.users;
    gecos     |   username   
--------------+--------------
 LAVEN LIU    | laven_liu
 TAO QI       | taotao_qi
 JAMES LEBRON | james_lebron
 TAO QI 2     | taoqi
 LAVEN LIU 2  | lavenliu
 FORUM LI     | forum
(6 rows)

-- 执行失败，username 列不允许被更新
forumdb=> UPDATE forum.users SET username = upper( username );
ERROR:  permission denied for table users

-- 执行成功
forumdb=> UPDATE forum.users SET gecos = lower( gecos );

forumdb=> SELECT gecos, username FROM forum.users;
    gecos     |   username   
--------------+--------------
 laven liu    | laven_liu
 tao qi       | taotao_qi
 james lebron | james_lebron
 tao qi 2     | taoqi
 laven liu 2  | lavenliu
 forum li     | forum
(6 rows)

现在让我们检查用户表的权限：

forumdb=> \dp forum.users
                                    Access privileges
 Schema | Name  | Type  |     Access privileges      |   Column privileges    | Policies 
--------+-------+-------+----------------------------+------------------------+----------
 forum  | users | table | forum=arwdDxt/forum       +| username:             +| 
        |       |       | forum_admins=arwdDxt/forum |   forum_stats=r/forum +