PostgreSQL 11数据库管理员指南第六章：访问权限

本章中将函数如下小节：

PostgreSQL超级用户
回收用户的数据表访问权限
授权用户访问数据表
授权用户访问指定列
授权用户访问指定行
新建用户
临时阻止用户连接
删除用户但保留其数据
检查所有用户是否有安全的密码
将有限的超级用户权限授予具体用户
审计数据库访问权限
保持知晓哪个用户处于登录状态
集成轻量目录访问协议（LDAP）
使用SSL进行连接
使用SSL证书进行授权
映射外部用户名到数据库角色
加密敏感数据

引言

首先在数据库服务端设置权限规则。PostgreSQL允许我们使用pg_hba.conf文件根据尝试连接的主机来控制访问。如果需要可以指定SSL证书或者在网络安全时跳过这一步。可以指定使用256位密钥的SCRAM认证以及许多其它机制。

接着，为访问数据设置角色和权限。数据库多用于存储数据，在如何使用上存在一些限制。有些记录或表仅能由某些用户查看，甚至那些对所有人可见的数据表也可以对哪些人可插入数据或修改已有数据进行限制。所有这些由权限系统进行管理，其中用户被授予不同表或不同数据库对象（如模式或函数）的权限。

不将这些权限直接授权用户是一种良好实践，而是应当使用中间多用户来汇总权限集。然后，不是将权限授予实际用户，把整个角色授予需要这些权限的用户。例如，办事员多用户可心在user_account表中插入数据及更新已有数据，但可能对transaction_history表仅拥有插入数据的权限。

对权限控制的认真筛选可使用行级权限（RLS）功能来进行管理，它允许对所选择的表定义策略。

数据库权限的另一个方面涉及对数据库访问的管理：确保仅有相应的人可以访问该数据库；一个用户无法看到另一个用户所做的事（除非是管理员或审计人员）；并决定用户可以或不可心将角色授权传递给其他人。

应当考虑使用pgaudit来审计管理员的操作。

典型用户角色

最小化生产服务器设置包含至少两种类型用户，分别为管理员和终端用户，其中管理员可以进行任意操作（他们是超级用户），而终端用户仅能进行少量操作，通常仅是修改少数几张表的数据及读取一些表。

让普通用户创建或修改数据库对象定义不是一个好想法，这表示他们不应对任何模式包含PUBLIC在内拥有CREATE权限。

对于不同类型的终端用户可以有多个角色，如分析员，他们可以从单个表或视图中查询数据，或者一些脚本维护用户无法查看任何数据，仅能执行少数几个函数。

此外，还可以存在管理员角色，他们可以授权或收回其他用户的角色，但不应进行其它操作。

PostgreSQL超级用户

本小节中，我们将学习如何将权限授予用户让其成为超级用户。

PostgreSQL超级用户是一个除登录以外超越所有权限检查的用户。这是非常危险的权限，不应随意使用。很多云数据库不允许授予这一级别的权限。对这类用户加以严格控制是正常之举。

如何实现…

按照如下步骤来为任意用户添加或删除超级用户权限：

在使用SUPERUSER属性集创建用户时其会成为超级用户：

CREATE USER username SUPERUSER;

1

CREATE USER username SUPERUSER;
可以通过使用如下命令删除SUPERUSER属性来剥夺用户的超级用户状态：

ALTER USER username NOSUPERUSER;

1

ALTER USER username NOSUPERUSER;
可使用如下命令来恢复用户的超级用户状态：

ALTER USER username SUPERUSER;

1

ALTER USER username SUPERUSER;
在CREATE USER命令中既未使用SUPERUSER 也未使用 NOSUPERUSER时，那么默认创建的不是超级用户。

运行原理…

PostgreSQL中的一些操作权限默认不可用，需要具体地对用户授权。他们必须由具有特殊属性集的特殊用户来执行。前述的命令可对用户设置及重置该属性。

扩展知识…

PostgreSQL系统自带至少有一个超级用户。最常见的是这个超级用户为postgres，但有时会使用与拥有数据库目录及具有运行PostgreSQL服务权限的系统用户相同的名称。

其它类超级用户属性

除SUPERUSER以外，有两个更小的属性 – CREATEDB 和 CREATEUSER – 它授权用户一部分超级用户的保留权限，具体的有新建数据库及用户。参见将有限的超级用户权限授予具体用户一节获取更多相关知识。

属性永不进行继承

稍后你将学习如何将一个角色授予其它用户 – 角色继承 – 以如何将权限通过中间组角色进行授权。这些对属性都无效 – 执行超级用户独有的操作，你必须要是超级用户才行。

参见其它

同时查看本章中的保持知晓哪个用户处于登录状态一节。

ℹ️注意：以下所有小节都假定使用的是非超级用户，除非显式地说明它们应用于或需要超级用户。

回收用户的数据表访问权限

本节回答以下问题：如何保证用户X无法访问数据表 Y？

准备工作

当前用户必须是超级用户，或者数据表的拥有者，亦或对该表具有GRANT权限的用户。

同时还应记住是无法从超级用户身上回收权限的。

如何实现…

要对用户user2回收数据表table1的所有权限，应运行如下SQL命令：

REVOKE ALL ON table1 FROM user2;

1	REVOKE ALL ON table1 FROM user2;

但如果user2被授予另一个具有table1权限的角色时，如role3，那么这条命令就不够了；还应选择如下的一种选项：

指定用户 – 即从user2身上回收role3
指定角色 – 即从role3身上回收table1的权限

两种选项都不够完美，因为存在一些副作用。前者将回收所有与role3相关联的权限，而不仅仅是对table1的权限；后者是会回收授予过role3的所有用户对table1的权限，而不仅仅是user2。

通常最好避免影响其它有效用户，所以选择第一种。以下是一个示例。

通过添加 \z table1使用psql显示至少被授予过table1某一权限的角色列表。例如，可获取如下输出（一个有关列权限被从右侧删除列权限的额外的列，因为在这里并不相关）：

                   Access privileges
 Schema |  Name  | Type  |     Access privileges     | ...
--------+--------+-------+---------------------------+ ...
 public | table1 | table | postgres=arwdDxt/postgres+| ...
        |        |       | role3=r/postgres         +| ...
        |        |       | role5=a/postgres          | ...
(1 row)

Access privileges

Schema | Name | Type | Access privileges | ...

--------+--------+-------+---------------------------+ ...

public | table1 | table | postgres=arwdDxt/postgres+| ...

| | | role3=r/postgres +| ...

| | | role5=a/postgres | ...

(1 row)

然后，通过输入\du user2查看用户user2是否为这些角色的成员：

           List of roles
 Role name | Attributes |   Member of  
-----------+------------+---------------
 user2     |            | {role3, role4}

List of roles

Role name | Attributes | Member of

-----------+------------+---------------

user2 | | {role3, role4}

在前一步中，我们注意到role3 由postgres 用户授予了SELECT 权限（r表示读取），因此应进行回收，如下：

REVOKE role3 FROM user2;

1	REVOKE role3 FROM user2;

我们还应查看role4。虽然它对table1不拥有权限，但理论上却可能会是其它拥有该表权限的3个角色的成员。执行\du role4 来获取如下输出：

            List of roles
 Role name |  Attributes  | Member of
-----------+--------------+-----------
 role4     | Cannot login | {role5}

List of roles

Role name | Attributes | Member of

-----------+--------------+-----------

role4 | Cannot login | {role5}

我们的猜测获得了证实，user2可以获取对table1的INSERT权限（a 表示追加），首先通过role4，然后借助role5。因此我们需要将这两步分解如下：

REVOKE role4 FROM user2;

1	REVOKE role4 FROM user2;

ℹ️注意：本例很可能不是真的。我通过两个不同的角色成员串联获取了预期外的权限，这让非登录角色，如role4成为了另一个非登录角色即role5的成员。在大多数真实世界的安全中，超级用户会知道这类串联是否存在，因此不应当会存在意外的事；但是，本节旨在保证该用户无法访问该表，也就是说我们不应排除掉可能性很小的选项。

运行原理…

\z命令，以及其同义命令\dp，显示授予表、视图及序列的所有权限。如果Access privileges列为空，表示默认权限，即授予所有者（以及超级用户）所有权限。

\du命令展示授予了角色的属性和角色。

两条命令都接收可选名称或范式来限制其显示。

扩展知识…

这里我们讲解了一些用户和角色管理的良好实践。

数据库创建脚本

对于生产系统，通常保持在数据库创建脚本中包含GRANT 和 REVOKE语句是一个好做法，这样可以保证只有正确的用户集能够访问该表。如果通过手动完成，则很容易遗忘。同时通过这种方式，我们可以确保在开发和测试环境中使用相同角色，这样在部署时就不会发生意外。

以下是从数据库创建脚本中提取的样例：

CREATE TABLE table1( 
... 
); 
GRANT SELECT ON table1 TO webreaders; 
GRANT SELECT, INSERT, UPDATE, DELETE ON table1 TO editors; 
GRANT ALL ON table1 TO admins;

CREATE TABLE table1(

...

);

GRANT SELECT ON table1 TO webreaders;

GRANT SELECT, INSERT, UPDATE, DELETE ON table1 TO editors;

GRANT ALL ON table1 TO admins;

默认搜索路径

在回收权限及授权时使用完全限定名是一种良好实践；否则，可能会不小心操作到其它表上。

可运行如下命令来查看当前数据库的有效搜索路径：

pguser=# show search_path ;
  search_path  
----------------
 "$user",public
(1 row)

pguser=# show search_path ;

search_path

----------------

"$user",public

(1 row)

如果省略模式名，可在PSQL中运行如下命令来查看所受影响的数据表：

pguser=# \d x
     Table "public.x"
 Column | Type | Modifiers
--------+------+-----------

pguser=# \d x

Table "public.x"

Column | Type | Modifiers

--------+------+-----------

响应中的public.x数据表名包含带有模式的完整名称。

视图安全

使用视图来仅暴露部分保密数据表的内容是一种常见技术；但是聪明的攻击者可以通过对视图的访问来利用日志消息显示数据表的其余内容。例如，思考如下示例：

CREATE VIEW for_the_public AS 
  SELECT * FROM reserved_data WHERE importance < 10; 
GRANT SELECT ON for_the_public TO PUBLIC;

CREATE VIEW for_the_public AS

SELECT * FROM reserved_data WHERE importance < 10;

GRANT SELECT ON for_the_public TO PUBLIC;

恶意用户可以定义如下函数:

CREATE FUNCTION f(text) 
RETURNS boolean 
COST 0.00000001 
LANGUAGE plpgsql AS $$ 
BEGIN 
  RAISE INFO '$1: %', $1; 
  RETURN true; 
END; 
$$;

CREATE FUNCTION f(text)

RETURNS boolean

COST 0.00000001

LANGUAGE plpgsql AS $$

BEGIN

RAISE INFO '$1: %', $1;

RETURN true;

END;

$$;

它们可用于从视图中过滤行：

SELECT * FROM for_the_public x WHERE f(x :: text);

1	SELECT * FROM for_the_public x WHERE f(x :: text);

PostgreSQL优化器然后在内部重组查询，扩展视图的定义并组合两个过滤条件为一条WHERE语句。此处的技巧使用COST关键字表明非常非常脆弱，因此优化器会选择首先运行条件。换句话说，该函数会访问数所表中的所有行，如果运行代码的话可以在控制台中查看到相关的INFO行了解具体状况。

这一安全问题可通过使用security_barrier属性来进行规避：

ALTER VIEW for_the_public SET (security_barrier = on);

1	ALTER VIEW for_the_public SET (security_barrier = on);

这表示定义视图的条件总是会先行计算，而不考虑开销。

影响其的性能问题可通过函数的LEAKPROOF属性进行消减。简言之，不能泄漏输出值以外的信息的函数可由超级用户通过LEAKPROOF进行标记，因此策划者将知道在其它视图条件之前运行该函数足够安全。

授权用户访问数据表

用户需要访问数据表来对其执行操作。

准备工作

确保定义了相应的权限，并对PUBLIC角色回收了这些权限：

CREATE GROUP webreaders;
CREATE USER tim;
CREATE USER bob;
REVOKE ALL ON SCHEMA someschema FROM PUBLIC;

CREATE GROUP webreaders;

CREATE USER tim;

CREATE USER bob;

REVOKE ALL ON SCHEMA someschema FROM PUBLIC;

如何实现…

我们应对模式进行授权来保证对数据表的访问。这样对给定模式的访问可用于快速极致地阻止对该模式的任何访问。此外，如果希望允许某些访问，则必须使用按需使用GRANT 和 REVOKE 语句：

GRANT USAGE ON SCHEMA someschema TO webreaders;

1	GRANT USAGE ON SCHEMA someschema TO webreaders;

经常需要将一组数据库对象的相似权限授予一组用户。这时，首先将所有权限授予一个代理角色（也称为权限组），然后像如下这样将组分配给相应的用户：

GRANT SELECT ON someschema.pages TO webreaders; 
GRANT INSERT ON someschema.viewlog TO webreaders; 
GRANT webreaders TO tim, bob;

GRANT SELECT ON someschema.pages TO webreaders;

GRANT INSERT ON someschema.viewlog TO webreaders;

GRANT webreaders TO tim, bob;

现在，tim 和 bob对pages表拥有了SELECT权限，对viewlog表拥有了INSERT权限。你还可以分配给用户后对组角色添加权限。使用如下命令：

GRANT INSERT, UPDATE, DELETE ON someschema.comments TO webreaders;

1	GRANT INSERT, UPDATE, DELETE ON someschema.comments TO webreaders;

在运行该命令后， bob 和 tim就拥有前述的对comments表的所有权限。

这里假定bob 和 tim的角色是通过INHERIT默认设置进行创建的。否则，他们不会自动继承角色的权限，而是需要显式地通过授权角色权限来对授权用户设置角色。

我们可以像如下这样对指定模式中某一类型的所有对象授权：

GRANT SELECT ON ALL TABLES IN SCHEMA someschema TO bob;

1	GRANT SELECT ON ALL TABLES IN SCHEMA someschema TO bob;

还需要通过GRANT语句对模式本身进行授权。

运行原理…

以上的这些命令首先将模式授权给组角色，然后将某张表的相应查看（SELECT）和修改（INSERT）权限授予角色，最终将该角色的成员分配给两个数据库用户。

扩展知识…

PostgreSQL中获取某一权限不受其它权限的影响。这表示你可以仅拥有写入表的权限，这时可以进行插入而无法查询。它可用于实现邮件队列这样的功能，其中一些用户对另一个用户发送消息，但无法查看其它用户所发送的内容。

此外，可以设置一种情况，可以写入一条记录，但无法修改或删除它。这对于审计日志类型的数据表非常有用，此时会记录所有修改但无法进行更改。

授权用户访问指定列

可以授权用户仅访问数据表的某些列。

准备工作

我们将继续使用前一小节的示例，假定已有一个名为someschema的模式并且它拥有带USAGE权限的名为somerole的角色。新建一张表来授予列级访问权限：

CREATE TABLE someschema.sometable2(col1 int, col2 text);

1	CREATE TABLE someschema.sometable2(col1 int, col2 text);

如何实现…

我们希望授予somerole查看已有数据及插入新数据的权限；还希望提供仅限于col2列的修正已有数据的权限。使用如下的语句：

GRANT SELECT, INSERT ON someschema.sometable2 TO somerole; GRANT UPDATE (col2) ON someschema.sometable2 TO somerole;

1
2

GRANT SELECT, INSERT ON someschema.sometable2 TO somerole;
GRANT UPDATE (col2) ON someschema.sometable2 TO somerole;
假定somerole角色的身份并使用如下命令测试这些权限：

SET ROLE TO somerole; INSERT INTO someschema.sometable2 VALUES (1, 'One'); SELECT * FROM someschema.sometable2 WHERE col1 = 1;

1
2
3

SET ROLE TO somerole;
INSERT INTO someschema.sometable2 VALUES (1, 'One');
SELECT * FROM someschema.sometable2 WHERE col1 = 1;
如我们所料，可以查看新行并查看其内容。现在检查更新单列的权限。先使用我们已授权的第二列：

UPDATE someschema.sometable2 SET col2 = 'The number one';

1

UPDATE someschema.sometable2 SET col2 = 'The number one';
这条命令返回我们所熟悉的输出：

UPDATE 1

1

UPDATE 1
这表示我们能够成功地更新某行中的该列。现在试着更新第一列：

UPDATE someschema.sometable2 SET col1 = 2;

1

UPDATE someschema.sometable2 SET col1 = 2;
这次得到了如下错误消息：

ERROR: permission denied for relation sometable2

1

ERROR: permission denied for relation sometable2

这证实了计划中的仅对第二列授权了更新操作。

运行原理…

扩展了GRANT命令来允许指定一组列，这表示对该字段列表而非整张表进行了授权。

扩展知识…

思考有一张表t，分别有列c1, c2和c3；两种不同方式授权用户u执行如下查询：

SELECT * FROM t;

1	SELECT * FROM t;

第一种是进行表级授权，如下：

GRANT SELECT ON TABLE t TO u;

1	GRANT SELECT ON TABLE t TO u;

另一种是进行列表授权，如下：

GRANT SELECT (c1,c2,c3) ON TABLE t TO u;

1	GRANT SELECT (c1,c2,c3) ON TABLE t TO u;

虽然这两种方法有重复的效果，表级权限与列级权限有明显的不同，这是因为两者的含义也是不同的。对表授权表示将权限授予共当前及未来的所有列，而列级授权要求要显式地表明哪些列，因此不会自动扩展到新列。

PostgreSQL中权限运作的方式表示给定角色会在匹配一种权限允许执行给定的操作。这会导致一个重复区域的混淆。例如，思考如下命令序列：

        GRANT SELECT ON someschema.sometable2 TO somerole; 
        REVOKE SELECT (col1) ON someschema.sometable2 FROM    
        somerole;

GRANT SELECT ON someschema.sometable2 TO somerole;

REVOKE SELECT (col1) ON someschema.sometable2 FROM

somerole;

ℹ️注意

结果有些出乎意料，使用第一条命令进行的表级授权会允许somerole查看该表的所有列。第二条无效，因为它尝试回收的列级权限 (对 col1执行SELECT)一开始从未进行过授权。

授权用户访问指定行

PostgreSQL支持仅将某些行授权给用户。

准备工作

本节中使用了RLS，仅在PostgreSQL 9.5 及之后的版本中可以使用，因此请先查看使用的是否为更老的版本。

对于前面的小节，我们假定已经一个名为someschema的模式以及带有USAGE权限的somerole角色。我们新建一张表来试验等级权限：

CREATE TABLE someschema.sometable3(col1 int, col2 text);

1	CREATE TABLE someschema.sometable3(col1 int, col2 text);

RLS必须对该表进行启用：

ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

1	ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

如何实现…

首先，我们像之前的小节那样授权somerole访问数据表的内容：

GRANT SELECT ON someschema.sometable3 TO somerole;

1	GRANT SELECT ON someschema.sometable3 TO somerole;

假设表的内容如下面的命令所示：

SELECT * FROM someschema.sometable3;
 col1 |   col2   
------+-----------
    1 | One
   -1 | Minus one
(2 rows)

SELECT * FROM someschema.sometable3;

col1 | col2

------+-----------

1 | One

-1 | Minus one

(2 rows)

为授权仅能访问某些行，需要创建一个指定哪些行可供访问的策略。例如，通过这种方式，我们可以强制somerole仅能查询col1中值为正值的行：

CREATE POLICY example1 ON someschema.sometable3
FOR SELECT
TO somerole
USING (col1 > 0);

CREATE POLICY example1 ON someschema.sometable3

FOR SELECT

TO somerole

USING (col1 > 0);

这条命令的效果是无法满足条件的行会被默默地跳过，somerole执行如下命令效果如下：

SELECT * FROM someschema.sometable3;
 col1 |   col2   
------+-----------
    1 | One
(1 row)

SELECT * FROM someschema.sometable3;

col1 | col2

------+-----------

1 | One

(1 row)

如果我们想要对INSERT语句引入一个策略呢？前面的策略显示USING从句指定哪些行受到了影响。并不存在用于指定插入条件的WITH CHECK从句。通常USING从句仅用于已存在的行，而WITH CHECK应用于由所分析语句生成的行。因此，前者可用于SELECT, UPDATE, 和 DELETE，而后者可用于INSERT 和 UPDATE。

运行原理…

RLS策略通过CREATE POLICY 语句对指定表执行创建和删除。RLS策略本身也必须能够显式的对给定表启用，因为默认是关闭的。

在前面的示例中，除了创建RLS策略外我们还需要对表或行进行授权。这是因为RLS不是一个加在一个权限上的另一种权限，而是一种额外的检测。在这个层面，默认关闭是有道理的，因为仅在我们的访问逻辑依赖于行内容时才对表创建策略。

扩展知识…

RLS可因不同的原因产生非常复杂的配置，如下例所示：

更新策略可指定我们操作和可进行修改的行
UPDATE 和 DELETE策略在某些情况下要求由相应的SELECT策略授权可见
UPDATE 策略也可应用于 INSERT ... ON CONFLICT DO UPDATE

推荐通过 https://www.postgresql.org/docs/11/static/ddl-rowsecurity.html阅读更详细的信息。

新建用户

本节中我们将展示新建数据库用户的两种方式，一种使用独立的命令行工具，一种使用SQL命令。

准备工作

要新建用户，必须要么是超级用户，要么拥有CREATEROLE 或 CREATEROLE 权限。

如何实现…

通过命令行，可以运行createuser命令：

pguser@hvost:~$ createuser bob

1	pguser@hvost:~$ createuser bob

如果添加--interactive命令行选项，则启用了交互模式，即会询问如下的问题：

不使用--interactive时，以上问题的默认答案都是 no，可以通过-s, -d和 -r 命令行选项进行修改。

在交互模式下，仅在有意义时才询问问题。一种情况是用户是超级用户，没有询问其它问题是因为超级用户不受权限检查的限制。另一种情况是在使用上述选项指定非默认设置时，则不会询问相应的问题。

运行原理…

createuser程序仅是一个对数据库集群执行SQL时的一个很粗浅的封装。它连接postgres数据库，然后为创建用户执行SQL命令。要通过SQL创建相同的用户，可以执行如下命令：

CREATE USER bob;
CREATE USER alice CREATEDB;

1 2	CREATE USER bob; CREATE USER alice CREATEDB;

扩展知识…

可以在psql中查看给定用户的属性，如下：

pguser=# \du alice

1	pguser=# \du alice

这会给出如下输出：

           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 alice     | Create DB  | {}

List of roles

Role name | Attributes | Member of

-----------+------------+-----------

alice | Create DB | {}

CREATE USER 和 CREATE GROUP命令实际上是CREATE ROLE的变体。CREATE USER username; 等价于CREATE ROLE username LOGIN;，而CREATE GROUP groupname;语句等价于CREATE ROLE groupname NOLOGIN;。

临时阻止用户连接

有时，我们需要临时收回用户的连接权限，但并不删除用户或是修改用户的密码。本节展示实现这个功能的方式。

准备工作

要修改其它用户，必须要么是超级用户要么得具有CREATEROLE权限（对于后者仅能修改非超级用户角色）。

如何实现…

按照如下步骤来临时阻止及重新允许用户的登录：

要临时阻止用户登录，运行如下命令：

pguser=# alter user bob nologin; ALTER ROLE

1
2

pguser=# alter user bob nologin;
ALTER ROLE
再次允许用户进行连接，运行如下命令：

pguser=# alter user bob login; ALTER ROLE

1
2

pguser=# alter user bob login;
ALTER ROLE

运行原理…

这会在系统目录中设置标记，告诉PostgreSQL不要让用户进行登录。但不会踢掉处于连接状态的用户。

扩展知识…

还有其它几点。

限制一个用户的并发连接数

将用户的连接数上限设置为0也可以实现相同的效果：

pguser=# alter user bob connection limit 0;
ALTER ROLE

1 2	pguser=# alter user bob connection limit 0; ALTER ROLE

要允许用户bob进行10个并发连接，运行如下命令：

pguser=# alter user bob connection limit 10;
ALTER ROLE

1 2	pguser=# alter user bob connection limit 10; ALTER ROLE

运行如下命令来不对该用户的连接数进行限制：

强制NOLOGIN用户断开连接

为确保所有回收登录权限的用户即时的断开连接，可以超级用户运行如下命令：

SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity a
   JOIN pg_roles r ON a.usename = r.rolname AND NOT rolcanlogin;

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity a

JOIN pg_roles r ON a.usename = r.rolname AND NOT rolcanlogin;

这会通过终止不再允许登录用户此前所打开的后台来断开他们的连接。

删除用户但保留其数据

在尝试删除拥有数据表或其它数据库对象的用户时，会报出如下错误且用户未被删除：

testdb=# drop user bob;
ERROR:  role “bob” cannot be dropped because some objects depend on it
DETAIL:  owner of table bobstable
owner of sequence bobstable_id_seq

testdb=# drop user bob;

ERROR: role “bob” cannot be dropped because some objects depend on it

DETAIL: owner of table bobstable

owner of sequence bobstable_id_seq

本节展示两种解决这一问题的方法。

准备工作

要修改用户，必须得是超级用户或者具有CREATEROLE权限。

如何实现…

解决这一问题的最简单方法是不进行用户删除，而是使用前一小节阻止用户连接的方法：

pguser=# alter user bob nologin;
ALTER ROLE

1 2	pguser=# alter user bob nologin; ALTER ROLE

这样的好处是表的所有者在稍后审计或调试时（为什么有这张表？谁建的？）仍然可供使用

再者，我们可以将已删除用户的权限分配给新用户，使用如下命令：

pguser=# GRANT bob TO bobs_replacement;
GRANT

1 2	pguser=# GRANT bob TO bobs_replacement; GRANT

运行原理…

如前面所说明的，用户通过登录属性集进行实现。本节通过删除该用户的属性来生效，之后仅将他保留为一个角色。

如果一定要删除用户，则必须要为另一个用户分配所有者权限。这时运行如下查询，它是PostgreSQL对标准SQL的一个扩展：

REASSIGN OWNED BY bob TO bobs_replacement;

1	REASSIGN OWNED BY bob TO bobs_replacement;

其功能和字面意思一样，分配所有由bob角色所拥有的数据库对象所有权限给bobs_replacement角色。

但是需要对新老两个角色拥有权限时才能这么做，并且需要对bob所拥有对象的所有数据库进行操作，因为REASSIGN OWNED命令仅对当前数据库生效。

然后就可以删除原有用户bob了。

检查所有用户是否有安全的密码

PostgreSQL没有内置的密码强度检查工具。

我们最好的做法是确保所有的用户密码进行了加密，并且在pg_hba.conf 中不允许明文密码的登录，即对所有用户使用 SCRAM-SHA-256 登录方法，这是在PostgreSQL 10中新增的。任何对早期版本的升级都应将密码加密由md5升级为SCRAM-SHA-256.

对于通过真实或虚拟（VPN）的可信任私有网络连接的客户端应用，只要知道应用所运行的机器不由非信任的个人使用的话就可以使用基于主机的权限限制。对于通过公网的远程连接，添加 SSL 客户端证书会是一种好的做法。

如何实现…

使用如下查询来查看哪些用户没有使用SCRAM加密的密码：

test2=# select usename,passwd from pg_shadow where passwd
not like ‘SCRAM%’ or passwd is null;
 usename  |    passwd   
----------+--------------
 tim      | weakpassword
 asterisk | md5chicken
(2 rows)

test2=# select usename,passwd from pg_shadow where passwd

not like ‘SCRAM%’ or passwd is null;

usename | passwd

----------+--------------

tim | weakpassword

asterisk | md5chicken

(2 rows)

使用如下命令来启用SCRAM-SHA-256 密码加密：

运行原理…

password_encryption参数决定ALTER USER语句对密码进行加密。这应当在postgresql.conf文件中进行全局设置。

对数据库的密码加密只是完成了一部分。更大的问题是要保证用户使用不易猜到的密码。password, secret或 test这些肯定是有问题的，大部分常用单词都不是好的密码。

如果无法保证用户会选择复杂密码，可以编写一个封装应用来检查密码强度并确保用户在修改密码时使用复杂密码。contrib模块在有限的情况下进行使用（密码以明文从客户端发送到服务端）。访问http://www.postgresql.org/docs/11/static/passwordcheck.html获取更多相关信息

将有限的超级用户权限授予具体用户

超级用户角色的某些权限可单独授予非超级用户。

要授予角色bob新建数据库的权限，运行如下命令：

ALTER ROLE BOB WITH CREATEDB;

1	ALTER ROLE BOB WITH CREATEDB;

给予角色bob新建用户的权限，运行如下命令：

ALTER ROLE BOB WITH CREATEROLE;

1	ALTER ROLE BOB WITH CREATEROLE;

也可以使用security definer函数授予普通用户那些为超级用户所保留的更细化的控制权限。有些方法还可用于在不同用户间传递部分权限。

准备工作

首先，必须要具有访问数据库的超级用户权限来进行授权。这里，我们假定使用默认的超级用户postgres。

我们将演示两种方式来将一些超级用户独有的功能授予指定的普通用户。

如何实现…

PostgreSQL中普通用户无法将文件中的数据复制到数据表中。只有超级用户有这种权限，如下：

pguser@hvost:~$ psql -U postgres
test2
...
test2=# create table lines(line text);
CREATE TABLE
test2=# copy lines from ‘/home/bob/names.txt’;
COPY 37
test2=# SET ROLE to bob;
SET
test2=> copy lines from ‘/home/bob/names.txt’;
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql’s \copy command also works for anyone.

pguser@hvost:~$ psql -U postgres

test2

...

test2=# create table lines(line text);

CREATE TABLE

test2=# copy lines from ‘/home/bob/names.txt’;

COPY 37

test2=# SET ROLE to bob;

SET

test2=> copy lines from ‘/home/bob/names.txt’;

ERROR: must be superuser to COPY to or from a file

HINT: Anyone can COPY to stdout or from stdin. psql’s \copy command also works for anyone.

为让bob直接从文件中进行拷贝，超级用户可以为bob编写一个特殊封装函数，如下：

create or replace function copy_from(tablename text, filepath text) 
returns void 
security definer 
as 
$$ 
 declare 
 begin 
      execute 'copy ' || quote_ident(tablename) 
                 || ' from ' || quote_literal(filepath) ; 
 end; 
$$ language plpgsql;

create or replace function copy_from(tablename text, filepath text)

returns void

security definer

declare

begin

execute 'copy ' || quote_ident(tablename)

|| ' from ' || quote_literal(filepath) ;

end;

$$ language plpgsql;

通常将这类函数仅限定给指定的用户是一种好的做法：

revoke all on function copy_from( text,  text) from public; 
grant execute on function copy_from( text,  text) to bob;

1 2	revoke all on function copy_from( text, text) from public; grant execute on function copy_from( text, text) to bob;

可能还要验证bob仅能从自己的家目录导入文件。

ℹ️注意

不幸的是，这种方法对恶意攻击者进行提权操作时不是完全的安全。这是因为在函数中执行COPY命令还会导致以postgres用户进行执行，有一些负面效果，如某一INSERT触发器的执行、CHECK约束的计算、函数式索引的计算等等。

换句话说，如果用户希望以超级用户执行某一操作，将该函数放到前述函数中就可以了。

运行原理…

在定义函数中调用了security definer时，PostgreSQL修改会话权限为那些在执行函数时定义了函数的用户。

因此，在bob执行copy_from(tablename, filepath)函数时，bob会被函数运行时提升为超级用户。

这一行为类似于Unix系统中的setuid标记，其中可以拥有一个由任意（具有execute权限的）用户以程序所有者运行的程序。也会带有类似的风险。

扩展知识…

存在其它一些为PostgreSQL超级用户所预留的权限，如设置某些参数。

为开发者编写debugging_info函数

一些控制日志的参数是为超级用户所保留的。

如果希望允许一些开发者设置日志，可以为他们编写函数来进行该操作：

create or replace function debugging_info_on() 
returns void 
security definer 
as 
$$ 
  begin 
   set client_min_messages to 'DEBUG1'; 
   set log_min_messages to 'DEBUG1'; 
   set log_error_verbosity to 'VERBOSE'; 
   set log_min_duration_statement to 0; 
 end; 
$$ language plpgsql; 
revoke all on function debugging_info_on() from public; 
grant execute on function debugging_info_on() to bob;

create or replace function debugging_info_on()

returns void

security definer

begin

set client_min_messages to 'DEBUG1';

set log_min_messages to 'DEBUG1';

set log_error_verbosity to 'VERBOSE';

set log_min_duration_statement to 0;

end;

$$ language plpgsql;

revoke all on function debugging_info_on() from public;

grant execute on function debugging_info_on() to bob;

可能会希望有一个函数，通过将所有涉及的变量设置为DEFAULT来回到默认日志状态：

create or replace function debugging_info_reset() 
returns void 
security definer 
as 
$$ 
  begin 
   set client_min_messages to DEFAULT; 
   set log_min_messages to DEFAULT; 
   set log_error_verbosity to DEFAULT; 
   set log_min_duration_statement to DEFAULT; 
 end; 
$$ language plpgsql;

create or replace function debugging_info_reset()

returns void

security definer

begin

set client_min_messages to DEFAULT;

set log_min_messages to DEFAULT;

set log_error_verbosity to DEFAULT;

set log_min_duration_statement to DEFAULT;

end;

$$ language plpgsql;

在这里无需GRANT 和 REVOKE语句，因此将它们设回为默认值不存在安全风险。除SET xxx to DEFAULT外，还可以使用相同命令的更简短版本，即RESET xxx。

此外，可以简单地结束自己的会话，因为参数仅在当前会话中有效。

审计数据库访问权限

审计数据库权限的话题比你所想象的要大得多，因为它包含着很多的要求。

准备工作

首先，决定你希望进行的操作并查看相应的小节：

SQL语句执行了什么？审计SQL
数据表访问了些什么？审计数据表权限
数据行修改了什么？审计数据修改
数据行查看了些什么？这里未进行讲解，通常数据量很大

只审计SQL会产生最少量的审计日志信息，尤其是如如果仅选择记录DDL的话。更多级别会快速的积攒更多信息，因此在实践中可能会很快决定不这么做。阅读各部分来了解其中的好处与权衡。

审计 SQL

有两种记录SQL的主要方式：

使用PostgreSQL的log_statement参数
使用pgaudit插件的pgaudit.log参数

log_statement参数可设置为如下的一种选项：

ALL：记录所有在顶层执行的SQL语句
MOD：记录所有INSERT, UPDATE, DELETE 及 TRUNCATE语句的SQL
ddl：记录所有针对DDL命令的SQL语句
NONE：不记录任何语句

例如，要记录所有的DDL命令，编辑postgresql.conf文件来进行如下设置：

log_statement  =  'ddl'

1	log_statement = 'ddl'

log_statement SQL语句显式地给出了顶层命令。如果使用了任一PL语言的话可通过这一设置在不进行记录的情况下执行SQL语句，通过DO语句或调用包含SQL语句的函数。

修改是否提交了呢？可以在日志文件中记录一些语句但在数据库结构中不可见。PostgreSQL中的大部分DDL命令可以进行回滚，因此日志中仅为由PostgreSQL执行的一组命令，而非实际提交的内容。日志文件非事务的，它保存回滚的命令。可以通过在log_line_prefix 设置中添加%x来在每个日志中显示事务标识符，但在使用层面会存在问题。

谁做的修改？要知道哪个数据库用户进行了DDL修改，必须确保该信息也进行了记录。要这么做，可能需要修改log_line_prefix参数来包含%u格式化字符串。

针对审计DDL推荐的最小化log_line_prefix格式化字符串有%t %u %d，它告诉PostgreSQL在每个日志行的起始记录时间戳、数据库用户和数据库名称。

pgaudit插件提供两级审计日志：会话级和对象级。会话级日志设计用于解决log_statement的问题。pgaudit会记录所有访问，即使这并非由顶层语句执行的，并且它会记录所有动态的SQL。pgaudit.log可设置为不包含或包含以下的任意设置：

READ: SELECT 和 COPY
WRITE: INSERT, UPDATE, DELETE, TRUNCATE 和 COPY
FUNCTION: 函数调用与 DO 代码块
ROLE: GRANT, REVOKE, CREATE/ALTER/DROP ROLE
DDL: 所有没有在ROLE中包含的 DDL
MISC: 其它—DISCARD, FETCH, CHECKPOINT, VACUUM等

例如，要记录所有的DDL命令，编辑postgresql.conf文件来进行如下设置：

pgaudit.log = 'role, ddl'

1	pgaudit.log = 'role, ddl'

应当设置以下参数来减轻过重的日志：

pgaudit.log_catalog = off
pgaudit.log_relation = off
pgaudit.log_statement_once = on

pgaudit.log_catalog = off

pgaudit.log_relation = off

pgaudit.log_statement_once = on

ℹ️注意

pgaudit插件最初由2ndQuadrant的Simon Riggs 和 Abhijit Menon-Sen 编写，它是欧盟AXLE项目的一部分。紧接着的版本由Simon Riggs 和 David Steele 设计用于提供对象级日志。最初的版本被弃用并且不再可用。新版本进行了完全的支持并且由 US DoD适配为一个PostgreSQL审计日志的可选工具。

在postgresql.org仓库中可获取二进制形式的pgaudit。

审计数据表权限

pgaudit可以记录对每张表的访问。因此，如果SQL数据表涉及到了三张表，它会生成三条日志记录，每张表一条。这很重要，因为不然的话你会要尝试并解析SQL来查看出来所涉及到的数据表，不访问模式及search_path设置时这会非常困难。

调整如下设置来让对每张表的审计日志的访问更为容易：

pgaudit.log_relation = on
pgaudit.log_statement_once = off

1 2	pgaudit.log_relation = on pgaudit.log_statement_once = off

如果想要更为精细的审计，pgaudit允许我们控制所审计的数据表。用户无法知晓记录了哪些表、没记录哪些表，因此可以让调查人员在收到了潜在攻击或嫌疑时默默地提升日志级别。

首先，设置由审计人员所使用的角色：

pgaudit.role = ‘investigator’

1	pgaudit.role = ‘investigator’

然后，可通过权限系统定义日志，命令如下：

管理审计日志

log_statement 和 pgaudit 输出审计日志记录到服务端日志中。这是最为灵活的方式，因为日志可以多种方式进行路由来保证安全并与普通日志记录进行分离。

如果你允许日志记录进入普通服务日志中，会发现日志中存在CREATE, ALTER和 DROP的记录：

postgres@hvost:~$ egrep -i “create|alter|drop” \
/var/log/postgresql/postgresql-11-main.log

1 2	postgres@hvost:~$ egrep -i “create\|alter\|drop” \ /var/log/postgresql/postgresql-11-main.log

如果启用了日志轮询，可能还要对老文件使用grep。

如果可用的日志太新而你又没有在其它地方保存更老的日志的话，那就不那么幸运了。

postgresql.conf文件中针对日志轮询的默认设置如下：

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_rotation_size = 10MB

ℹ️注意

日志轮询也可以通过第三方工具实现。例如，Debian 和 Ubuntu发行版中默认行为是根据/etc/logrotate.d/postgresql-common文件中指定的规则使用logrotate工具来压缩或删除老日志文件。

要确保拥有完整的DDL执行历史记录，可能需要设置一个cron任务来提取PostgreSQL主日志中的DDL语句放到单独的DDL审计日志中。还要验证日志不要轮询过快而无法捕获所有DDL语句。
如果使用的是syslog，那么可以使用不同的OS工具来路由审计消息。

此外，可以使用pgaudit分析插件来将数据加载回特殊审计日志数据库。还存在不同的其它选项。

审计数据修改

本节提供收集针对审计的数据表中所包含数据修改的不同方式。

首先你需要进行如下决策：

是需要审计所有修改还是仅部分修改？
需要收集有关修改的哪些信息？仅是数据被修改这一行为吗？
在记录字段或元组的新值时，是否还需要记录老值？
记录哪个用户进行的修改是否足够，或者是否需要记录IP地址及其它连接信息？
这些审计信息应多么安全（防篡改）？例如，是否保持与所审计的数据表相分隔？

可通过触发器从元组中收集的新值（如果需要，还有老值）来收集修改，并将它们保存到审计表中。触发器可添加到任何需要进行追踪的数据表中。

audit_trigger插件提供了一种方便的统一审计触发器，这样无需自行编写。如果记录表中各行的老值及新值，序列化为hstore数据类型值。最新版本及其文档在https://github.com/2ndQuadrant/audit-trigger中均可获取到。

插件创建一个名为audit的模式，其中放置所有审计触发器代码的其它组件，然后我们可以启用对具体表的审计。

作为示例，我们可以通过运行pgbench工具来创建标准的pgbench表：

pgbench -i

1	pgbench -i

然后，我们以超级用户连接到PostgreSQL，并执行如下SQL来对pgbench_account表启用审计：

SELECT  audit.audit_table('pgbench_accounts');

1	SELECT audit.audit_table('pgbench_accounts');

现在，执行一些写入活动来查看是如何被审计的。最简单的方式是再次运行pgbench工具，这次执行一些事务，如下：

pgbench -t 1000

1	pgbench -t 1000

我们期望审计触发器对pgbench_accounts的操作进行记录，因为我们对其启用了审计。为进行验证，再次通过psql 进行连接并执行如下SQL：

cookbook=# SELECT count(*) FROM audit.logged_actions;
count
-------
1000
(1 row)

cookbook=# SELECT count(*) FROM audit.logged_actions;

count

-------

1000

(1 row)

这证实了我们确实记录了1,000条操作。我们通过读取logged_actions表中的行来查看记录的信息。首先，启用扩展模式，因为查询会产生大量的列：

cookbook=# \x on

1	cookbook=# \x on

然后，执行如下命令：

cookbook=# SELECT * FROM audit.logged_actions LIMIT 1;
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------
event_id          | 1
schema_name       | public
table_name        | pgbench_accounts
relid             | 246511
session_user_name | gianni
action_tstamp_tx  | 2017-01-18 19:48:05.626299+01
action_tstamp_stm | 2017-01-18 19:48:05.626446+01
action_tstamp_clk | 2017-01-18 19:48:05.628488+01
transaction_id    | 182578
application_name  | pgbench
client_addr       |
client_port       |
client_query      | UPDATE pgbench_accounts SET abalance = abalance + -758 WHERE aid = 86061;
action            | U
row_data          | "aid"=>"86061", "bid"=>"1", "filler"=>"  ", "abalance"=>"0"
changed_fields    | "abalance"=>"-758"
statement_only    | f

cookbook=# SELECT * FROM audit.logged_actions LIMIT 1;

-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------

event_id | 1

schema_name | public

table_name | pgbench_accounts

relid | 246511

session_user_name | gianni

action_tstamp_tx | 2017-01-18 19:48:05.626299+01

action_tstamp_stm | 2017-01-18 19:48:05.626446+01

action_tstamp_clk | 2017-01-18 19:48:05.628488+01

transaction_id | 182578

application_name | pgbench

client_addr |

client_port |

client_query | UPDATE pgbench_accounts SET abalance = abalance + -758 WHERE aid = 86061;

action | U

row_data | "aid"=>"86061", "bid"=>"1", "filler"=>" ", "abalance"=>"0"

changed_fields | "abalance"=>"-758"

statement_only | f

保持知晓哪个用户处于登录状态

在前面的小节中，我们仅仅是通过记录当前PostgreSQL会话中的用户变量值来记录当前用户角色。

这并不总是表示这一具体用户是实际在会开始时所授权的用户。例如，超级用户可以执行SET ROLE TO ...命令来设置当前角色给任意其它系统中用户或角色。你可能也想到了，非超级用户只能使用他们自身的角色。

我们是可以使用current_user 和 session_user session变量来区分已登录角色和执行所使用的角色的：

postgres=# select current_user, session_user;
current_user | session_user
-------------+--------------
postgres     | postgres 

postgres=# set role to bob;
SET
postgres=> select current_user, session_user;
current_user | session_user
-------------+--------------
bob          | postgres

postgres=# select current_user, session_user;

current_user | session_user

-------------+--------------

postgres | postgres

postgres=# set role to bob;

SET

postgres=> select current_user, session_user;

current_user | session_user

-------------+--------------

bob | postgres

有时，是需要让每个用户以自己的用户名登录并按照具体用例来使用角色的。

准备工作

通过授权必要的权限和选项为不同的任务准备所要求的组角色和访问级别。

如何实现…

步骤如下：

使用NOINHERIT参数创建不具有权限的用户：

postgres=# create user alice noinherit; CREATE ROLE postgres=# create user bob noinherit; CREATE ROLE

1
2
3
4

postgres=# create user alice noinherit;
CREATE ROLE
postgres=# create user bob noinherit;
CREATE ROLE
然后为每组所需要分配的权限创建角色：

postgres=# create group sales; CREATE ROLE postgres=# create group marketing; CREATE ROLE postgres=# grant postgres to marketing; GRANT ROLE

1
2
3
4
5
6

postgres=# create group sales;
CREATE ROLE
postgres=# create group marketing;
CREATE ROLE
postgres=# grant postgres to marketing;
GRANT ROLE
授权每个用户其所需的角色：

postgres=# grant sales to alice; GRANT ROLE postgres=# grant marketing to alice; GRANT ROLE postgres=# grant sales to bob; GRANT ROLE

1
2
3
4
5
6

postgres=# grant sales to alice;
GRANT ROLE
postgres=# grant marketing to alice;
GRANT ROLE
postgres=# grant sales to bob;
GRANT ROLE

在执行完操作之后，用户alice 和 bob在登录后没有权限，但通过执行SET ROLE TO sales他们可以具有销售权限，alice还可以具有超级用户角色。

运行原理…

如果角色或用户是通过NOINHERIT参数创建的，那么用户不会自动获取授予其它角色的权限授权给自己。要通过具体角色声明这些权限，需要设置其角色为其它的角色。

从某种意义上说，这有点类似Unix和Linux系统中的su（set user）命令。也就是说，你（可能）可以拥有成为该用户的权限，但不会自动拥有前述用户的权限。

这一设置可用于获取更佳的审计信息，因为会让你知道真实的用户是谁。如果仅允许每个用户以任务所需的角色进行登录，就没有很好的方式来在之后知道那些在进行$100,000转账时以clerk1真实登录的用户是谁。

扩展知识…

SET ROLE命令有两种运行方式，即可以获取或丢失权限。超级用户可以对系统中定义的任意用户设置其权限。回归到原始的登录角色，只需使用RESET ROLE。

不继承用户属性

并非所有权限都是通过GRANT命令来授予用户的。有些重要权限是通过用户属性（SUPERUSER, CREATEDB 和 CREATEROLE）来授予的，并它不会进行继承。

如果授权了用户超级用户的权限并希望使用这一用户的超级用户权限，需要在要求使用超级用户属性设置内容之前使用SET ROLE To mysuperuserrole。

换句话说，用户属性行为总是好像用户是一个NOINHERIT用户那样。

集成轻量目录访问协议（LDAP）

本节展示如何设置PostgreSQL系统来让其使用LDAP进行认证。

准备工作

确保数据库中的用户名与LDAP服务中的相匹配，因为这一方法适用于已在数据库中定义了的用户的权限检查。

如何实现…

在PostgreSQL授权文件pg_hba.conf中，我们定义一些地址段来使用LDAP作为认证方法，并且对LDAP服务配置这一地址段：

host    all         all         10.10.0.1/16          ldap \
ldapserver=ldap.our.net ldapprefix="cn=" ldapsuffix=", 
   dc=our,dc=net"

host all all 10.10.0.1/16 ldap \

ldapserver=ldap.our.net ldapprefix="cn=" ldapsuffix=",

dc=our,dc=net"

运行原理…

这一设置会让PostgreSQL从配置的LDAP服务端检查密码。

用户权限不是从LDAP服务端进行查询的，而是需要使用 ALTER USER, GRANT和 REVOKE命令在数据库内部进行定义。

扩展知识…

我们已展示PostgreSQL如何使用LDAP服务端来进行密码校验。也可以使用LDAP服务端的其它信息，在接下来的两个示例中会进行展示。

设置客户端来使用 LDAP

如果使用pg_service.conf文件来定义数据库访问参数，可以定义通过在pg_service.conf文件中包含类似如下行来定义对LDAP服务端的一些查询：

ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydb)

1	ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydb)

用户名称映射功能的替代

虽然我们不能对LDAP使用用户名映射功能，但可以在LDAP端实现类似的效果。使用ldapsearchattribute及search + bind 模式来从LDAP服务端获取PostgreSQL的角色名。

参见其它

对于服务端设置，包括 search + bind模式，参见 http://www.postgresql.org/docs/11/static/auth-methods.html#AUTH-LDAP
客户端设置相关，访问http://www.postgresql.org/docs/11/static/libpq-ldap.html

使用SSL进行连接

这里，我们将演示如何启用PostgreSQL来使用SSL通过加密对该连接传递的所有数据来保护数据库的连接。使用SSL会让嗅探数据库传输更为困难，包括用户名、密码及其它敏感数据。否则的话，所有内容都会在客户端与数据库之间进行非加密传输，对于在它们之间的某处进行网络监听就是可查看的。另一种对SSL替代方案是通过虚拟私有网络（VPN）进行连接。

使用SSL会让加密连接的数据传输变慢一些，因此如果确定网络是安全的话就可以不进行使用。如果创建很多短连接的话对性能影响会比较大，因为设置一个SSL会很消耗CPU。这时，可能需要运用本地连接池方案，如PgBouncer，客户端对其不使用加密连接，然后配置PgBouncer使用SSL对服务端进行连接。老版本的PgBouncer不支持SSL；该方案是通过stunnel实现的数据库连接通道，在PgBouncer FAQ 中进行了描述https://pgbouncer.github.io/faq.html。

准备工作

对服务端获取或生成SSL服务端密钥与证书对，将它们存储在当前数据库实例的data目录下，命名为server.key 和 server.crt。

ℹ️注意

在某些平台，这没有必要；密钥证书对可能已由安装包生成。例如，在Ubuntu中，PostgreSQL默认设置为支持SSL连接。

如何实现…

在postgresql.conf 中设置ssl = on并重启数据库。

运行原理…

如果设置了ssl = on，那么PostgreSQL对相同的端口（默认为5432）同时监听普通及SSL连接，并通过新连接的第一个字节决定连接的类型。然后，它会在进入的请求要求时继续设置SSL连接。

扩展知识…

可以保留该设置将是否使用SSL交给客户端，或者可以强制使用服务端的SSL。

要让客户端选择，在pg_hba.conf文件中使用如下形式的行：

host database  user  IP-address/IP-mask  auth-method

1	host database user IP-address/IP-mask auth-method

如果希望仅允许SSL客户端连接，使用hostssl关键字来替换掉host。

pg_hba.conf中的内容可使用pg_hba_file_rules视图来进行查看。

以下的pg_hba.conf 配置内容启用来自192.168.1.0/24本地子网的非SSL及SSL连接，但对于来自其它网络访问数据库的用户都要求使用SSL：

获取 SSL 密钥和证书

对于网页服务端，通常应从公认的证书授权机构（CA）处获取证书，因为大部分浏览器对不可识别的 CA 发布的证书会进行警告。他们对用户报出常见危险的警告并要求在对不识别的 CA 所发布证书的服务端进行连接时先予以确认。

而对于数据库服务，通常使用OpenSSL生成证书就足够了。以下证书为服务端生成一个自签署的证书：

openssl  genrsa  2048  >  server.key
openssl  req  -new  -x509  -key  server.key  -out server.crt

1 2	openssl genrsa 2048 > server.key openssl req -new -x509 -key server.key -out server.crt

ℹ️注：访问https://github.com/openssl/openssl/tree/master/doc/HOWTO中OpenSSL的HOWTO页面了解更多有关X.509密钥及证书的知识。

设置使用 SSL 的客户端

客户端应用有关SSL的行为由环境变量PGSSLMODE所控制。它可拥有如下PostgreSQL官方文档中所定义的值：

SSL模式	泄漏监听保护	MITM保护	描述
disabled	No	No	不太在意安全性，不希望有因加密而产生的额外开销。
allow	Maybe	No	不太在意安全性，但如果服务需要可接受加密所带的开销。
prefer	Maybe	No	不太在意安全性，但如果服务端支持可接受加密所带的开销。
require	Yes	No	希望数据进行加密，并接受其开销。相信网络会确保我保持连接到所需的服务端。
verify-ca	Yes	取决于CA的政策	希望数据进行加密，并接受其开销。要由自己来确保连接到所依赖的服务端。
verify-full	Yes	Yes	希望数据进行加密，并接受其开销。要由自己来确保连接到所依赖的服务端，并是服务端是自己所指定的。

上表中的MITM表示中间人（man-in-the-middle）攻击，即有人伪装成你的服务端，可能是通过操纵 DNS 记录或IP路由表，但实际上仅仅是监测并转发流量。

在进行SSL连接时要实现这种攻击，攻击者必须获取到客户端认为是有效的证书。

检查服务端认证信息

最后两种SSL模式允许我们通过椎服务端所呈现的 SSL 证书来合理地确保进行对话的服务端确实是自己的。

为启用这一有用的安全我，在客户端中必须存在如下文件。在Unix系统中，文件位于客户端家目录下的~/.postgresql子文件夹中。在Windows中位于%APPDATA%\postgresql\：

文件	内容	作用
root.crt	一个或多个可信任 CA的证书	PostgreSQL验证证书由可信任CA所签发
root.crl	CA所撤销的证书	不得在列表中的服务端证书

客户端仅需root.crt文件来验证服务端证书。它可以包含多个对所需比对服务端证书的根证书。

使用SSL证书进行授权

本节展示如何设置PostgreSQL系统来要求客户端来进行连接前提供有效的X.509 证书。

这可用于额外的安全层，使用双层认证，客户端必须同时有有效证书来建立SSL连接并且要知道数据库用户的密码。它还可用于单验证方法，此时PostgreSQL服务端会先使用客户端出示的证书来验证其连接，然后从该证书中获取用户名。

准备工作

获取或生成根证书及用于连接客户端的客户端证书。

如何实现…

为进行测试或建立单个可信任用户，可以使用自签署证书。

openssl  genrsa  2048  >  client.key
openssl  req  -new  -x509  -key  server.key  -out  client.crt

1 2	openssl genrsa 2048 > client.key openssl req -new -x509 -key server.key -out client.crt

在服务端的pg_hba.conf文件中使用hostssl方法设置一行并将clientcert参数置为1：

hostssl  all    all    0.0.0.0/0         md5  clientcert=1

1	hostssl all all 0.0.0.0/0 md5 clientcert=1

将root.crt文件中的客户端根证书放到服务端数据目录中（$PGDATA/root.crt）。该文件中可包含多个可信任的根证书。

如果使用中央证书授权机构，可能还有一个证书撤销列表，会放在root.crl文件中并定期更新。

在客户端，将客户端私钥和证书放到~/.postgresql/postgresql.key 和 ~/.postgresql/postgresql.crt中。通过运行如下命令来确保该私钥文件对外不可读取或对用户组不可读取：

chmod 0600 ~/.postgresql/postgresql.key

1	chmod 0600 ~/.postgresql/postgresql.key

在Windows客户端中，相应的文件为%APPDATA%\postgresql\postgresql.key 和 %APPDATA%\postgresql\postgresql.crt。不进行权限检查，因为这个位置被视作是安全的。

如果客户端证书不是由根CA签发，而是来自中间CA，那么所有中间CA证书到根证书也都要放到postgresql.crt文件中。