PostgreSQL 是一个安全的数据库，我们希望保持这种状态。因此，考虑使用 SSL 来加密客户端和服务器之间的连接是有道理的。这篇文章将帮助您正确设置 PostgreSQL 的 SSL 身份验证，并希望了解一些背景信息，使您的数据库更安全。
使用 PostgreSQL 进行 SSL 认证
在这篇文章的最后，您应该能够以最简单的方式配置 PostgreSQL 并处理安全的客户端服务器连接。
为 OpenSSL 配置 PostgreSQL
设置 OpenSSL 要做的第一件事是更改 postgresql.conf。有几个与加密相关的参数：
ssl = on
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off
一旦可能ssl = on，服务器将协商 SSL 连接。其余参数定义密钥文件的位置、密码的强度等。请注意，开启 SSL 不需要重新启动数据库。可以通过简单的重新加载来设置变量。但是，您仍然需要重新启动，否则 PostgreSQL 将不接受 SSL 连接。对于某些用户来说，这是一个经常导致问题的重要点：
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# SHOW ssl;
ssl
-----
on
(1 row)
该SHOW命令是确保设置确实已更改的简单方法。从技术上讲，此阶段不需要 pg_reload_conf()。无论如何都要稍后重新启动。我们只是重新加载以显示对变量的影响。
在下一步中，我们必须调整 pg_hba.conf 以确保 PostgreSQL 以安全的方式处理我们的连接：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
hostssl all all 10.0.3.0/24 scram-sha-256``
然后重新启动数据库实例以确保启用了 SSL。
您接下来要做的是创建证书。为了简单起见，我们将在这里简单地创建自签名证书。但是，当然也可以用其他证书进行。下面是它的工作原理：
[postgres@node1 data]$ openssl req -new -x509 -days 365 \
-nodes -text -out server.crt \
-keyout server.key \
-subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.......+++++
....................................................+++++
writing new private key to 'server.key'
-----
该证书的有效期为 365 天。接下来我们必须设置权限以确保可以使用证书。如果这些权限太宽松，服务器将不接受证书：
[postgres@node1 data]$ chmod og-rwx server.key
自签名证书很好。但是，要创建客户端可以验证其身份和来源的服务器证书，请首先创建证书签名请求和公钥/私钥文件：
[postgres@node1 data]$ openssl req -new -nodes -text \
-out root.csr \
-keyout root.key \
-subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.................................+++++
....................+++++
writing new private key to 'root.key'
-----
同样，我们必须确保这些权限完全正确：
[postgres@node1 data]$ chmod og-rwx root.key
然后我们签署请求。要使用 OpenSSL 做到这一点，我们首先必须找出可以找到 openssl.cnf 的位置。我们已经看到它并不总是在同一个地方——所以请确保您使用正确的路径：
[postgres@node1 data]$ find / -name openssl.cnf \
2> /dev/null
/etc/pki/tls/openssl.cnf
我们在签署请求时使用此路径：
[postgres@node1 data]$ openssl x509 -req -in root.csr \
-text \
-days 3650 \
-extfile /etc/pki/tls/openssl.cnf \
-extensions v3_ca \
-signkey root.key -out root.crt
Signature ok
subject=CN = cybertec-postgresql.com
Getting Private key
让我们使用新的根权限创建证书：
[postgres@node1 data]$ openssl req -new -nodes -text \
-out server.csr \
-keyout server.key \
-subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.....................+++++
...........................+++++
writing new private key to 'server.key'
[postgres@node1 data]$ chmod og-rwx server.key
[postgres@node1 data]$ openssl x509 -req \
-in server.csr -text -days 365 \
-CA root.crt -CAkey root.key -CAcreateserial \
-out server.crt
Signature ok
subject=CN = cybertec-postgresql.com
Getting CA Private Key
server.crt并且server.key应该按照 postgresql.conf 中的配置存储在服务器上的数据目录中。
但是还有更多：root.crt应该存储在客户端上，以便客户端可以验证服务器的证书是否由证书颁发机构签名。root.key应离线存储以用于创建未来的证书。
需要以下文件：
检查您的设置
现在所有证书都已就位，是时候重新启动服务器了：
[root@node1 ~]# systemctl restart postgresql-13
如果不重新启动，连接将失败并显示错误消息 (“psql: error: FATAL: no pg_hba.conf entry for host “10.0.3.200”, user “postgres”, database “test”, SSL off”).
但是，重新启动后，该过程应按预期工作：
[root@node1 ~]# su - postgres
[postgres@node1 ~]$ psql test -h 10.0.3.200
Password for user postgres:
psql (13.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
test=#
psql 表示连接已加密。要确定连接是否确实加密，我们需要检查 pg_stat_ssl 的内容：
postgres=# \d pg_stat_ssl
View "pg_catalog.pg_stat_ssl"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
pid | integer | | |
ssl | boolean | | |
version | text | | |
cipher | text | | |
bits | integer | | |
compression | boolean | | |
client_dn | text | | |
client_serial | numeric | | |
issuer_dn | text | | |
让我们查询系统视图并查看它包含的内容：
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_stat_ssl;
-[ RECORD 1 ]-+-----------------------
pid | 16378
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
compression | f
client_dn |
client_serial |
issuer_dn |
连接已成功加密。如果“ssl = true”，那么我们就成功了。
PostgreSQL 支持不同级别的 SSL
两个 SSL 设置不一定相同。有多种级别可让您控制所需的安全和保护级别。下表概述了 PostgreSQL 支持的 SSL 模式：
开销实际上取决于您使用的模式。首先我们来看看一般的机制：
现在的主要问题是：如何指定要使用的模式？答案是：它必须作为连接字符串的一部分隐藏，如下例所示：
[postgres@node1 data]$ psql "dbname=test host=10.0.3.200 user=postgres password=1234 sslmode=verify-ca"
psql: error: root certificate file "/var/lib/pgsql/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
在这种情况下，verify-ca 不起作用，因为这样做必须将 root.* 文件复制到客户端，并且证书必须是允许正确验证目标服务器的证书。
加密你的整个服务器：PostgreSQL TDE
到目前为止，您已经学习了如何加密客户端和服务器之间的连接。但是，有时需要加密整个服务器，包括存储。PostgreSQL TDE 正是这样做的：
PostgreSQL的透明数据加密
要了解更多信息，请查看我们关于PostgreSQL TDE的网站。我们提供完全加密的堆栈来帮助您实现最大的安全性。PostgreSQL TDE 可免费使用（开源）。
您可能还感兴趣的东西……
物化视图是包括 PostgreSQL 在内的大多数数据库的重要特性。它们可以帮助加速大型计算，或者至少可以缓存它们。
如果您想确保您的物化视图是最新的，并且如果您想立即阅读有关 PostgreSQL 的更多信息，请查看我们关于pg_timetable的博客，它向您展示了如何在 PostgreSQL 中安排作业。为什么 pg_timetable 如此有用？我们的调度程序确保相同的作业不会重叠，但如果相同的作业已经在运行，则不会再次执行。在长时间作业的情况下，使用调度程序非常重要——特别是如果你想使用物化视图。
作者：汉斯-于尔根·舍尼格
Hans-Jürgen Schönig 自 90 年代以来就有使用 PostgreSQL 的经验。他是 CYBERTEC 的首席执行官兼技术负责人，CYBERTEC 是该领域的市场领导者之一，自 2000 年以来已为全球无数客户提供服务。
文章来源：https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/
