PostgreSQL 用户及授权管理 06：启用 SSL 及验证
安全在外企中是非常受重视的，一般外企都会要求所有可以使用加密的地方都使用加密。本小节我们启用 PostgreSQL 的 SSL 加密连接并进行抓包验证。
SSL 连接加密
安全套接字层 (SSL) 允许 PostgreSQL 接受加密的网络连接，这意味着每个数据包中的每一条数据都经过加密，因此我们要处理好密钥及合适的证书。
为了启用 SSL 扩展，我们首先需要配置服务器，然后接受传入的 SSL 连接，最后检测及验证客户端以 SSL 模式进行连接。
生成相关的证书
证书分为手工创建及自签名及从证书厂商那里购买证书。手工创建的证书适合在测试环境中使用，如果是在生产环境，那么建议购买付费的证书。证书是有有效期的，一般是免费的证书的有效期大概是 3 个月，付费的证书有效期一般是 1 年。接下来我们就介绍这两种类型的证书。
通过 openssl 的方式创建证书
接下来我们使用 openssl 来生成证书，或者在证书网站上面进行购买专业的证书。
[root@developing ~]# su - lavenliu
# 进入 PGDATA 目录，这里大家的目录可能与我的不一样
# 可以根据实际情况进行修改
[lavenliu@developing ~]$ cd $PGDATA
[lavenliu@developing data]$ mkdir ssl
[lavenliu@developing data]$ cd ssl
[lavenliu@developing ssl]$ openssl req -new -x509 -nodes -out mypg.lavenliu.cn.pem -keyout mypg.lavenliu.cn.key -days 365
.....+.+.........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.+..+....+...+..+......+..........+..+.+......+......+........+.+...............+...+.....+......+.+...+..+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*..+.......+......+...........+....+..+...+....+...+.....+...+..........+......+.....+.........................+...+..+.+...+..+...+..........+..+..................+.+........+...+....+...+...+...+.....+.+......+.....+.......+..+........................+....+.....+.+...........+....+..+............+.......+.........+......+..............+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
...+.........+......+..+.......+...+......+.........+.....+.+..+....+.....+......+...+.+........+.+......+.....+.+..+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.....+.....+.+...+......+.........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.......+...+....+...+..................+.....+.+.....+.+..+......+.+.....+......+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
# 下面会交互式的提示输入一些信息
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Shanghai
Locality Name (eg, city) [Default City]:Shanghai
Organization Name (eg, company) [Default Company Ltd]:Chuanda Group
Organizational Unit Name (eg, section) []:DevOps Team
Common Name (eg, your name or your server's hostname) []:mypg.lavenliu.cn
Email Address []:lcc@lavenliu.cn
通过云厂商申请免费证书
现在很多云厂商都会提供一些免费的 SSL 证书可供使用，有效期有三个月及一年的，非常的方便。如果是在公有云厂商的控制台申请免费的，也是非常快的，通常几分钟就可以完成申请。如：
申请完成，可以下载证书到本地。我这里选择下载 Nginx 格式的。如：
为集群配置 SSL
为了启用 SSL 进行加密，服务器必须拥有私有和公共证书。一旦我们获得了证书，我们唯一需要做的就是将证书和密钥文件导入到我们的 PostgreSQL 服务器中。
假设我们的证书和密钥文件分别命名为 mypg.lavenliu.cn.pem 和 mypg.lavenliu.cn.key，则必须在 postgresql.conf 配置文件中配置以下参数：
ssl = on
# 通过绝对路径配置
ssl_key_file = '/home/lavenliu/.pgenv/pgsql/data/ssl/mypg.lavenliu.cn.key'
ssl_cert_file = '/home/lavenliu/.pgenv/pgsql/data/ssl/mypg.lavenliu.cn.pem'
# 也可以通过相对路径配置，相对位置是 PGDATA 环境变量的值
ssl_key_file = 'ssl/mypg.lavenliu.cn.key'
ssl_cert_file = 'ssl/mypg.lavenliu.cn.pem'
第一行告诉 PostgreSQL 启用 SSL，而其他两行告诉服务器在哪里可以找到建立加密连接所需的文件。当然，这些文件必须能够被运行 PostgreSQL 集群的用户（通常是 postgres 操作系统用户）读取。
启用 SSL 后，我们需要调整 pg_hba.conf 文件以允许基于主机的访问机制处理基于 SSL 的连接。特别是，如果我们不想接受普通连接，则需要将每个主机条目替换为hostssl，例如：
hostssl all lavenliu myunixhost scram-sha-256
hostssl all forum 192.168.56.0/24 scram-sha-256
如果我们想同时接受明文连接和加密连接，可以将 host 方式保留即可。
通过 SSL 连接集群
当连接 PostgreSQL 时，如果基于主机的访问规则有匹配 hostssl 条目，客户端将自动切换到 SSL 连接；否则，它将默认为标准普通连接。
如果 pg_hba.conf 有一个 host 规则行，这意味着它可以接受 SSL 和普通连接。因此，我们需要在启动连接时强制使用 SSL。在 psql 中，这只能通过使用连接字符串并指定 sslmode=require 参数来启用它来实现。服务器如果接受连接，将打印正在使用的 SSL 协议：
[lavenliu@developing ~]$ psql "postgresql://forum@localhost:5432/forumdb?sslmode=require"
psql (16.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
forumdb=>
如果省略 sslmode 参数或使用标准 psql 连接参数，并且 pg_hba.conf 文件具有匹配的 hostssl 行，则连接将转换为 SSL。例如，以下三个连接产生相同的结果（加密连接）：
$ psql -h localhost -U forum forumdb
psql (16.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
forumdb=> \q
$ psql "postgresql://forum@localhost:5432/forumdb"
psql (16.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
forumdb=> \q
$ psql "postgresql://forum@localhost:5432/forumdb?sslmode=require"
psql (16.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
forumdb=>
同样，我们可以通过设置 sslmode=disable 来指定禁用 SSL 连接。如果 pg_hba.conf 中有 hostssl 模式，则连接将被拒绝，而如果 pg_hba.conf 文件中有 host 行，则将作为非加密连接：
$ psql "postgresql://forum@localhost:5432/forumdb?sslmode=disable"
psql (16.2)
Type "help" for help.
forumdb=>
最后，如果我们在连接字符串中使用了 SSL，但 PostgreSQL 服务器又未配置为使用 SSL，那么 PostgreSQL 会输出不支持 SSL 的错误信息：
$ psql "postgresql://forum@localhost:5432/forumdb?sslmode=disable"
psql: error: could not connect to server: server does not support SSL, but SSL was required
附录
PostgreSQL 不支持 SSL 问题解决
如何判断当前的 PostgreSQL 服务是否支持 SSL 呢？可以执行如下命令来验证：
[lavenliu@developing data]$ postgres -c ssl=on
2024-05-02 08:47:45.215 GMT [47322] FATAL: SSL is not supported by this build
由上述输出可以看到，我们当前通过 pgenv 安装的 PostgreSQL 服务是不支持 SSL 的。或者执行如下命令：
[root@developing ~]# ldd `which postgres` |grep ssl
如果没有输出，则表明当前安装的 PostgreSQL 也不支持 SSL。如果支持 SSL，则上述命令会输出如下：
[lavenliu@developing ~]$ ldd /opt/pgsql-16.2/bin/postgres |grep ssl
libssl.so.3 => /lib64/libssl.so.3 (0x00007f7469790000)
针对上述编译的版本不支持 SSL 怎么办？重新编译使其支持 SSL 即可，然后替换一下 postgres 的二进制程序，之前的数据还是可以使用的
[!tip]
通过 YUM 或 APT 安装的 PostgreSQL 则是支持 SSL 的。这里再次强调一下，不建议日常工作中使用编译的方式安装 PostgreSQL。
抓包验证连接是否加密
这里我们通过 tcpdump 来抓包验证一下，开启及没有开启 SSL 的区别。
没开启 SSL 的连接验证
分别打开两个终端，第一个终端里面使用 OS 的管理员账号进行抓包：
[root@developing ~]# tcpdump -nX -i lo port 5432
第二个终端进行数据库的连接及建表操作：
[postgres@developing ~]$ /opt/pgsql-16.2/bin/psql "postgresql://postgres@localhost:5432/forumdb?sslmode=disable"
psql (16.2)
Type "help" for help.
forumdb=# create table if not exists demo(name text);
NOTICE: relation "demo" already exists, skipping
CREATE TABLE
forumdb=# insert into demo values ('Can you see me?');
INSERT 0 1
forumdb=# select * from demo;
name
-----------------
Can you see me?
Can you see me?
Can you see me?
Can you see me?
Can you see me?
Can you see me?
(6 rows)
此时，第一个终端里面将会显示如下内容（只截取了部分内容）：
[root@developing ~]# tcpdump -nX -i lo port 5432
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on lo, link-type EN10MB (Ethernet), snapshot length 262144 bytes
11:55:09.651463 IP6 ::1.44496 > ::1.noadmin: Flags [S], seq 1958056583, win 33280, options [mss 65476,sackOK,TS val 3192031632 ecr 0,nop,wscale 7], length 0
...... 此处省略很多内容
0x0000: 6006 fa3e 0020 0640 0000 0000 0000 0000 `..>...@........
0x0010: 0000 0000 0000 0001 0000 0000 0000 0000 ................
0x0020: 0000 0000 0000 0001 add0 0781 74b5 92db ............t...
0x0030: d9e0 19ce 8010 0101 0028 0000 0101 080a .........(......
0x0040: be42 8994 be42 8994 .B...B..
11:55:16.843364 IP6 ::1.44496 > ::1.noadmin: Flags [P.], seq 84:133, ack 422, win 260, options [nop,nop,TS val 3192038824 ecr 3192031636], length 49
0x0000: 6006 fa3e 0051 0640 0000 0000 0000 0000 `..>.Q.@........
0x0010: 0000 0000 0000 0001 0000 0000 0000 0000 ................
0x0020: 0000 0000 0000 0001 add0 0781 74b5 92db ............t...
0x0030: d9e0 19ce 8018 0104 0059 0000 0101 080a .........Y......
0x0040: be42 a5a8 be42 8994 5100 0000 3063 7265 .B...B..Q...0cre # 这里是我们的建表语句
0x0050: 6174 6520 7461 626c 6520 6966 206e 6f74 ate.table.if.not
0x0060: 2065 7869 7374 7320 6465 6d6f 286e 616d .exists.demo(nam
0x0070: 6520 7465 7874 293b 00 e.text);.
......
......
11:55:34.603291 IP6 ::1.44496 > ::1.noadmin: Flags [P.], seq 133:158, ack 560, win 260, options [nop,nop,TS val 3192056584 ecr 3192038824], length 25
0x0040: be42 eb08 be42 a5a8 5100 0000 1873 656c .B...B..Q....sel # 此处是我们的查询语句
0x0050: 6563 7420 2a20 6672 6f6d 2064 656d 6f3b ect.*.from.demo;
0x0060: 00 .
11:55:34.612985 IP6 ::1.noadmin > ::1.44496: Flags [P.], seq 560:766, ack 158, win 260, options [nop,nop,TS val 3192056593 ecr 3192056584], length 206
0x0070: 0f43 616e 2079 6f75 2073 6565 206d 653f .Can.you.see.me? # 这是查询出的数据
0x0080: 4400 0000 1900 0100 0000 0f43 616e 2079 D..........Can.y
0x0090: 6f75 2073 6565 206d 653f 4400 0000 1900 ou.see.me?D.....
0x00a0: 0100 0000 0f43 616e 2079 6f75 2073 6565 .....Can.you.see
0x00b0: 206d 653f 4400 0000 1900 0100 0000 0f43 .me?D..........C
0x00c0: 616e 2079 6f75 2073 6565 206d 653f 4400 an.you.see.me?D.
0x00d0: 0000 1900 0100 0000 0f43 616e 2079 6f75 .........Can.you
0x00e0: 2073 6565 206d 653f 4400 0000 1900 0100 .see.me?D.......
0x00f0: 0000 0f43 616e 2079 6f75 2073 6565 206d ...Can.you.see.m
0x0100: 653f 4300 0000 0d53 454c 4543 5420 3600 e?C....SELECT.6.
0x0110: 5a00 0000 0549 Z....I
从上面的抓包可以看到都是明文信息，这对安全要求严格的企业里面是不允许这样明文通讯的。
已开启 SSL 的连接验证
分别打开两个终端，第一个终端里面使用 OS 的管理员账号进行抓包：
[root@developing ~]# tcpdump -nX -i lo port 5432
第二个终端进行数据库的连接及建表操作：
[postgres@developing ~]$ psql "postgresql://forum@localhost:5432/forumdb?sslmode=require"
Password for user forum:
psql (16.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
forumdb=> create table if not exists demo(name text);
forumdb=> insert into demo values ('Can you see me?');
INSERT 0 1
forumdb=> select * from demo;
name
-----------------
Can you see me?
(1 rows)
此时，第一个终端里面将会显示如下内容（只截取了部分内容）：
11:15:39.716066 IP 192.168.26.164.58914 > 192.168.26.164.noadmin: Flags [P.], seq 428722790:428722817, ack 442865350, win 512, options [nop,nop,TS val 2232602189 ecr 2232530665], length 27
0x0000: 4500 004f 2e23 4000 4006 55ed c0a8 1aa4 E..O.#@.@.U.....
0x0010: c0a8 1aa4 e622 0781 198d ca66 1a65 96c6 .....".....f.e..
0x0020: 8018 0200 b6da 0000 0101 080a 8512 ce4d ...............M
0x0030: 8511 b6e9 1703 0300 1689 269b d9de 66b3 ..........&...f.
0x0040: 1e25 ac6a dc3d 2f21 48d0 a346 3430 97 .%.j.=/!H..F40.
11:15:39.716099 IP 192.168.26.164.58914 > 192.168.26.164.noadmin: Flags [P.], seq 27:51, ack 1, win 512, options [nop,nop,TS val 2232602189 ecr 2232530665], length 24
0x0000: 4500 004c 2e24 4000 4006 55ef c0a8 1aa4 E..L.$@.@.U.....
0x0010: c0a8 1aa4 e622 0781 198d ca81 1a65 96c6 .....".......e..
0x0020: 8018 0200 b6d7 0000 0101 080a 8512 ce4d ...............M
0x0030: 8511 b6e9 1703 0300 13d0 5baf ec18 278d ..........[...'.
0x0040: 2c4f 45c6 11a2 b909 e125 c543 ,OE......%.C
11:15:39.716193 IP 192.168.26.164.58914 > 192.168.26.164.noadmin: Flags [F.], seq 51, ack 1, win 512, options [nop,nop,TS val 2232602189 ecr 2232530665], length 0
0x0000: 4500 0034 2e25 4000 4006 5606 c0a8 1aa4 E..4.%@.@.V.....
0x0010: c0a8 1aa4 e622 0781 198d ca99 1a65 96c6 .....".......e..
0x0020: 8011 0200 b6bf 0000 0101 080a 8512 ce4d ...............M
0x0030: 8511 b6e9 ....
。。。。。。省略很多输出
11:15:41.422926 IP 192.168.26.164.noadmin > 192.168.26.164.56030: Flags [P.], seq 2:101, ack 311, win 512, options [nop,nop,TS val 2232603896 ecr 2232603896], length 99
0x0000: 4500 0097 25c1 4000 4006 5e07 c0a8 1aa4 E...%.@.@.^.....
0x0010: c0a8 1aa4 0781 dade 94d8 74b2 b503 ba93 ..........t.....
0x0020: 8018 0200 b722 0000 0101 080a 8512 d4f8 ....."..........
0x0030: 8512 d4f8 1603 0300 5802 0000 5403 03cf ........X...T...
0x0040: 21ad 74e5 9a61 11be 1d8c 021e 65b8 91c2 !.t..a......e...
0x0050: a211 167a bb8c 5e07 9e09 e2c8 a833 9c20 ...z..^......3..
0x0060: 08e2 4768 d975 7b24 7855 635e 9790 458f ..Gh.u{$xUc^..E.
0x0070: b388 db86 9f44 f04d 81c1 ed87 bf26 d68c .....D.M.....&..
0x0080: 1302 0000 0c00 2b00 0203 0400 3300 0200 ......+.....3...
0x0090: 1714 0303 0001 01 .......
11:15:41.423139 IP 192.168.26.164.56030 > 192.168.26.164.noadmin: Flags [P.], seq 311:652, ack 101, win 512, options [nop,nop,TS val 2232603896 ecr 2232603896], length 341
0x0000: 4500 0189 27cd 4000 4006 5b09 c0a8 1aa4 E...'.@.@.[.....
0x0010: c0a8 1aa4 dade 0781 b503 ba93 94d8 7515 ..............u.
由上面的输出可以看到，打印的内容已经加密了。
总结
最后，如果配置得当，服务器可以通过 SSL 处理网络连接，从而加密所有网络流量和数据。如果在外企工作的话，那么他们对安全要求是非常严格的，在他们那里：安全第一，业务第二。在国内的企业可能要求的就没那么高了。这一节里面介绍的内容在国内企业用的虽然不多，但是这节的内容非常重要，推荐大家掌握。虽然国内企业不那么重视安全，但我们却不能不重视。