1 说明

公司某SaaS平台需要进行等保评测，要求pg数据库必须使用ssl链接。

整个ssl方案的调整包括pg数据库端的证书调整和使用pg数据库的服务的调整，如下的操作中以Java服务为例进行说明。

如下的操作中pg的运行用户是postgres，参考本方案的时候根据实际情况修改即可。

• 服务连接pg数据库的用户是pg
• 运行pg库的用户是postgres

2 操作步骤

2.1 数据库端调整

2.1.1 生成服务端证书

生成证书key文件

[postgres@localhost ~]$ cd /data/app/postgresql/
[postgres@localhost postgresql]$ ls
bin  include  lib  lib.bak  share
[postgres@localhost postgresql]$ mkdir certs
[postgres@localhost postgresql]$ cd certs/
[postgres@localhost certs]$ openssl genrsa -des3 -out server.key 2048
Generating RSA private key, 2048 bit long modulus
......................+++
.......................................+++
e is 65537 (0x10001)
Enter pass phrase for server.key: certpass
Verifying - Enter pass phrase for server.key:  certpass

移除密码

[postgres@localhost certs]$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key: certpass
writing RSA key

生成证书

[postgres@localhost certs]$ openssl req -new -key server.key -days 36500 -out server.crt -x509
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) []:Hebei
Locality Name (eg, city) [Default City]:langfang
Organization Name (eg, company) [Default Company Ltd]:ltd
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:localhost
Email Address []:

生成根证书 由于没有公证机构提供，只能使用自签名证书，因此可以将服务器证书作为根证书

$ cp server.crt root.crt

2.1.2 配置证书

pg配置文件postgresql.conf增加ssl整数配置项

ssl=on
ssl_ca_file='/data/app/postgresql/certs/root.crt'
ssl_key_file='/data/app/postgresql/certs/server.key'
ssl_cert_file='/data/app/postgresql/certs/server.crt'

更改pg库连接授权 配置文件为pg_hba.conf
如下这行添加到最上边

hostssl all             pg      all                     cert

证书文件权限调整

[postgres@localhost postgresql-12.8]$ cd /data/app/postgresql/certs
[postgres@localhost certs]$ ls
root.crt  server.crt  server.key
[postgres@localhost certs]$ chmod 0600 ./*

重启数据库

bash /data/app/scripts/postgresql restart

2.1.3 生成客户端证书

客户端需要三个文件: root.crt(根证书)、postgresql.crt(客户端证书)、postgresql.key(客户端私钥)

生成客户端私钥

[postgres@localhost ~]$ cd /data/app/postgresql/certs/
[postgres@localhost certs]$ openssl genrsa -des3 -out postgresql.key 2048
Generating RSA private key, 2048 bit long modulus
....................................+++
..+++
e is 65537 (0x10001)
Enter pass phrase for postgresql.key: certpass
Verifying - Enter pass phrase for postgresql.key: certpass

移出密码

[postgres@localhost certs]$ openssl rsa -in postgresql.key -out postgresql.key
Enter pass phrase for postgresql.key: certpass
writing RSA key

生成客户端csr

[postgres@localhost certs]$ openssl req -new -key postgresql.key -out postgresql.csr
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]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:pg
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

生成客户端证书

[postgres@localhost certs]$ openssl x509 -req -days 36500 -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=pg
Getting CA Private Key

2.2 使用pg库的服务端调整

在Java - jdbc中，客户端密钥必须是用pk8的格式，需要转换一下。

openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in postgresql.key -out postgresql.pk8

将证书文件导入到服务目录下 假如是服务目录下的certs目录
/usr/local/service_name/certs

修改jdbc链接

ssl=true&sslmode=verify-ca&sslcert=/usr/local/service_name/certs/postgresql.crt&sslkey=/usr/local/service_name/certs/postgresql.pk8&sslrootcert=/usr/local/service_name/certs/root.crt

重启java服务

3 避坑指南

3.1 证书核验通不过的情况

问题描述

按照上述操作后，如果pg没有配置session_exec，应该是没问题的。
如果配置了session_exec，参考文档等保: pg配置session_exec
那么由于证书核验不通过，会锁定链接用户，导致应用程序无法正常使用pg数据库。

具体的核验命令有两个

命令一 验证客户端证书是否与根证书匹配
openssl verify -CAfile root.crt -purpose sslclient postgresql.crt

命令二 验证证书是否可以用于登录

psql是不能在命令行直接以这种方式使用证书的: psql --sslmode=verify-full --sslcert=
psql使用证书连接的命令行方式如下 等同于上述的–sslmode的参数模式

psql 'host=10.21.0.173 port=5432 dbname=postgres user=postgres sslmode=verify-full sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'

命令一的正常返回为

postgresql.crt: OK

命令二的正常返回为: 直接进入pg数据库

修复措施

在2.1的步骤中 生成服务端证书的时候 Common Name字段必须设置为数据库的ip地址，必须是ip地址，vip不可以。
在2.1的步骤中，生成客户端证书的时候 Common Name字段必须设置为连接数据库的用户名，否则会使用服务所在主机的主机名连接。

注意上述两个点后 重新生成证书并配置即可

被这个问题困扰了很久 最后通过这样的方式解决的 实测没问题 不会再锁定用户