1 說(shuō)明
公司某SaaS平臺(tái)需要進(jìn)行等保評(píng)測(cè)，要求pg數(shù)據(jù)庫(kù)必須使用ssl鏈接。
整個(gè)ssl方案的調(diào)整包括pg數(shù)據(jù)庫(kù)端的證書(shū)調(diào)整和使用pg數(shù)據(jù)庫(kù)的服務(wù)的調(diào)整，如下的操作中以Java服務(wù)為例進(jìn)行說(shuō)明。
如下的操作中pg的運(yùn)行用戶(hù)是postgres，參考本方案的時(shí)候根據(jù)實(shí)際情況修改即可。
- 服務(wù)連接pg數(shù)據(jù)庫(kù)的用戶(hù)是pg
- 運(yùn)行pg庫(kù)的用戶(hù)是postgres
2 操作步驟
2.1 數(shù)據(jù)庫(kù)端調(diào)整
2.1.1 生成服務(wù)端證書(shū)
生成證書(shū)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
生成證書(shū)
[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 []:
生成根證書(shū) 由于沒(méi)有公證機(jī)構(gòu)提供，只能使用自簽名證書(shū)，因此可以將服務(wù)器證書(shū)作為根證書(shū)
$ cp server.crt root.crt
2.1.2 配置證書(shū)
pg配置文件postgresql.conf增加ssl整數(shù)配置項(xiàng)
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庫(kù)連接授權(quán) 配置文件為pg_hba.conf
如下這行添加到最上邊
hostssl all pg all cert
證書(shū)文件權(quán)限調(diào)整
[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 ./*
重啟數(shù)據(jù)庫(kù)
bash /data/app/scripts/postgresql restart
2.1.3 生成客戶(hù)端證書(shū)
客戶(hù)端需要三個(gè)文件: root.crt(根證書(shū))、postgresql.crt(客戶(hù)端證書(shū))、postgresql.key(客戶(hù)端私鑰)
生成客戶(hù)端私鑰
[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
生成客戶(hù)端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 []:
生成客戶(hù)端證書(shū)
[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庫(kù)的服務(wù)端調(diào)整
在Java - jdbc中，客戶(hù)端密鑰必須是用pk8的格式，需要轉(zhuǎn)換一下。
openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in postgresql.key -out postgresql.pk8
將證書(shū)文件導(dǎo)入到服務(wù)目錄下 假如是服務(wù)目錄下的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服務(wù)
3 避坑指南
3.1 證書(shū)核驗(yàn)通不過(guò)的情況
問(wèn)題描述
按照上述操作后，如果pg沒(méi)有配置session_exec，應(yīng)該是沒(méi)問(wèn)題的。
如果配置了session_exec，參考文檔等保: pg配置session_exec
那么由于證書(shū)核驗(yàn)不通過(guò)，會(huì)鎖定鏈接用戶(hù)，導(dǎo)致應(yīng)用程序無(wú)法正常使用pg數(shù)據(jù)庫(kù)。
具體的核驗(yàn)命令有兩個(gè)
命令一 驗(yàn)證客戶(hù)端證書(shū)是否與根證書(shū)匹配openssl verify -CAfile root.crt -purpose sslclient postgresql.crt
命令二 驗(yàn)證證書(shū)是否可以用于登錄
psql是不能在命令行直接以這種方式使用證書(shū)的: psql --sslmode=verify-full --sslcert=
psql使用證書(shū)連接的命令行方式如下 等同于上述的–sslmode的參數(shù)模式
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
命令二的正常返回為: 直接進(jìn)入pg數(shù)據(jù)庫(kù)
修復(fù)措施
在2.1的步驟中 生成服務(wù)端證書(shū)的時(shí)候 Common Name字段必須設(shè)置為數(shù)據(jù)庫(kù)的ip地址，必須是ip地址，vip不可以。
在2.1的步驟中，生成客戶(hù)端證書(shū)的時(shí)候 Common Name字段必須設(shè)置為連接數(shù)據(jù)庫(kù)的用戶(hù)名，否則會(huì)使用服務(wù)所在主機(jī)的主機(jī)名連接。
注意上述兩個(gè)點(diǎn)后 重新生成證書(shū)并配置即可文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-423132.html
被這個(gè)問(wèn)題困擾了很久 最后通過(guò)這樣的方式解決的 實(shí)測(cè)沒(méi)問(wèn)題 不會(huì)再鎖定用戶(hù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-423132.html
到了這里，關(guān)于等保: Postgresql配置ssl鏈接的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
