![尝试使用 SSL 通配符服务器证书且无客户端证书连接到 PostgreSQL 9.6 时出错](https://linux22.com/image/741487/%E5%B0%9D%E8%AF%95%E4%BD%BF%E7%94%A8%20SSL%20%E9%80%9A%E9%85%8D%E7%AC%A6%E6%9C%8D%E5%8A%A1%E5%99%A8%E8%AF%81%E4%B9%A6%E4%B8%94%E6%97%A0%E5%AE%A2%E6%88%B7%E7%AB%AF%E8%AF%81%E4%B9%A6%E8%BF%9E%E6%8E%A5%E5%88%B0%20PostgreSQL%209.6%20%E6%97%B6%E5%87%BA%E9%94%99.png)
我在 Amazon Linux 上有一个 PostgreSQL 9.6.11 数据库,自 2012 年 1 月以来就配置了 2048 位 SSL 通配符服务器证书和基于密码(无客户端证书)的远程连接。在最近的证书升级(Comodo,现为 Sectigo)之后,我无法再通过 SSL 建立与该数据库的远程 psql 或 JDBC 连接。
我的目标是能够通过 psql 和 JDBC 远程连接到这个 PostgreSQL 数据库。
从服务器密钥开始(自从我进行远程访问以来它就没有改变过),我试图涵盖一系列步骤来验证我的密钥、证书、防火墙和数据库是否设置正确。
我肯定错过了什么,因为我无法通过 psql 或 JDBC 进行远程连接。
我错过了什么可能导致这些远程连接失败?
故障排除步骤
作为用户PostgreSQL 的:
# cd /var/lib/pgsql96/data
PostgreSQL 配置文件
我曾尝试限制密码集,以尝试强制所有 SSL 连接使用 TLSv1.2。由于客户端行为没有差异,我注释掉了 ssl_ciphers 和 ssl_prefer_server_ciphers 以允许使用默认值。
ssl = on
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1'
#ssl_prefer_server_ciphers = on
ssl_cert_file = 'server.crt' # wildcard cert plus intermediate certs
ssl_key_file = 'server.key' # private key
#ssl_ca_file = 'root.crt' # commented out - do not require client certs
#ssl_crl_file = 'root.crl' # commented out - no client certificates
配置文件
此文件设置为仅允许本地主机和我正在测试的远程主机的公共 IP 地址。我不想要求客户端证书,只想使用必需的密码进行加密。
hostssl all all 11.222.11.222/32 password # localhost
hostssl all all 34.84.31.82/32 password # remote host
我通过以下方式检查了认证路径安全测试发现有两条路径可用(路径 #1和路径 #2):
从文档中PostgreSQL 9.6 使用 SSL 建立安全 TCP/IP 连接:
server.crt 中的第一个证书必须是服务器的证书,因为它必须与服务器的私钥匹配。“中间”证书颁发机构的证书也可以附加到文件中。这样做可以避免在客户端上存储中间证书的必要性,假设根证书和中间证书是使用 v3_ca 扩展创建的。这可以更轻松地使中间证书过期。
无需将根证书添加到 server.crt。相反,客户端必须拥有服务器证书链的根证书。
组装并验证路径 #1 的证书链
# ls -l
-rw------- 1 postgres postgres 2313 Aug 15 00:26 1_wildcard_server.crt
-rw------- 1 postgres postgres 2167 Aug 15 00:27 2_intermediate_sectigo.crt
-rw------- 1 postgres postgres 2094 Aug 15 00:27 3_root_usertrust-selfsigned.crt
我检查了路径 1 中每个证书的指纹以确认其身份:
# openssl x509 -in 1_wildcard_server.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=8C:69:06:8E:81:31:30:6E:DA:DD:C2:1C:38:83:73:67:97:3D:DB:37:78:B8:49:D7:7E:32:A8:3F:1F:8B:08:AB
# openssl x509 -in 2_intermediate_sectigo.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=7F:A4:FF:68:EC:04:A9:9D:75:28:D5:08:5F:94:90:7F:4D:1D:D1:C5:38:1B:AC:DC:83:2E:D5:C9:60:21:46:76
# openssl x509 -in 3_root_usertrust-selfsigned.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=E7:93:C9:B0:2F:D8:AA:13:E2:1C:31:22:8A:CC:B0:81:19:64:3B:74:9C:89:89:64:B1:74:6D:46:C3:D4:CB:D2
并且还检查了证书的文本版本,以确认中间证书和根证书具有 v3_ca 扩展名(通配符服务器证书没有此扩展名):
# openssl x509 -in 1_wildcard_server.crt -text
...
X509v3 Basic Constraints: critical
CA:FALSE
...
# openssl x509 -in 2_intermediate_sectigo.crt -text
...
X509v3 Basic Constraints: critical
CA:TRUE
...
# openssl x509 -in 3_root_usertrust-selfsigned.crt -text
...
X509v3 Basic Constraints: critical
CA:TRUE
...
使用 OpenSSL 验证路径 #1 的证书链:
# openssl verify -verbose -CAfile 3_root_usertrust-selfsigned.crt -untrusted 2_intermediate_sectigo.crt 1_wildcard_server.crt
1_wildcard_server.crt: OK
为路径#1创建捆绑服务器和中间证书:
# cat 1_wildcard_server.crt > server.crt
# cat 2_intermediate_sectigo.crt >> server.crt
为路径 #1 创建捆绑的中间证书和根证书(带有 v3_ca 扩展的证书)(尽管这仅在需要客户端证书时才需要):
# cat 2_intermediate_sectigo.crt > root.crt
# cat 3_root_usertrust-selfsigned.crt >> root.crt
组装并验证路径 #2 的证书链
# ls -l
-rw------- 1 postgres postgres 2313 Aug 15 00:26 1_wildcard_server.crt
-rw------- 1 postgres postgres 2167 Aug 15 00:27 2_intermediate_sectigo.crt
-rw------- 1 postgres postgres 1956 Aug 15 00:35 3_intermediate_usertrust.crt
-rw------- 1 postgres postgres 1521 Aug 15 00:27 4_root_addtrustroot-selfsigned.crt
我检查了路径#2中每个证书的指纹以确认其身份:
# openssl x509 -in 1_wildcard_server.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=8C:69:06:8E:81:31:30:6E:DA:DD:C2:1C:38:83:73:67:97:3D:DB:37:78:B8:49:D7:7E:32:A8:3F:1F:8B:08:AB
# openssl x509 -in 2_intermediate_sectigo.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=7F:A4:FF:68:EC:04:A9:9D:75:28:D5:08:5F:94:90:7F:4D:1D:D1:C5:38:1B:AC:DC:83:2E:D5:C9:60:21:46:76
# openssl x509 -in 3_intermediate_usertrust.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=1A:51:74:98:0A:29:4A:52:8A:11:07:26:D5:85:56:50:26:6C:48:D9:88:3B:EA:69:2B:67:B6:D7:26:DA:98:C5
# openssl x509 -in 4_root_addtrustroot-selfsigned.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=68:7F:A4:51:38:22:78:FF:F0:C8:B1:1F:8D:43:D5:76:67:1C:6E:B2:BC:EA:B4:13:FB:83:D9:65:D0:6D:2F:F2
并且还检查了证书的文本版本,以确认中间证书和根证书具有 v3_ca 扩展名(通配符服务器证书没有此扩展名):
# openssl x509 -in 1_wildcard_server.crt -text
...
X509v3 Basic Constraints: critical
CA:FALSE
...
# openssl x509 -in 2_intermediate_sectigo.crt -text
...
X509v3 Basic Constraints: critical
CA:TRUE
...
# openssl x509 -in 3_intermediate_usertrust.crt -text
...
X509v3 Basic Constraints: critical
CA:TRUE
...
# openssl x509 -in 4_root_addtrustroot-selfsigned.crt -text
...
X509v3 Basic Constraints: critical
CA:TRUE
...
使用 OpenSSL 验证路径 #2 的证书链(来自本文):
# openssl verify -verbose -CAfile 4_root_addtrustroot-selfsigned.crt -untrusted 3_intermediate_usertrust.crt 2_intermediate_sectigo.crt
2_intermediate_sectigo.crt: OK
为路径#2创建捆绑服务器和中间证书:
# cat 1_wildcard_server.crt > server.crt
# cat 2_intermediate_sectigo.crt >> server.crt
# cat 3_intermediate_usertrust.crt >> server.crt
为路径 #2 创建捆绑的中间证书和根证书(带有 v3_ca 扩展的证书)(尽管这仅在需要客户端证书时才需要):
# cat 2_intermediate_sectigo.crt > root.crt
# cat 3_intermediate_usertrust.crt >> root.crt
# cat 4_root_addtrustroot-selfsigned.crt >> root.crt
已验证密钥和证书权限(如这个问题)。
# ls -l *.key *.crt *.crl-bash-4.2$ ls -l *.key *.crt *.crl
-rw-r----- 1 postgres postgres 963 Aug 14 21:12 root.crl
-rw-r--r-- 1 postgres postgres 1521 Aug 15 01:27 root.crt
-rw-r--r-- 1 postgres postgres 6436 Aug 15 01:27 server.crt
-rw------- 1 postgres postgres 1679 May 28 19:33 server.key
确认服务器密钥正确后,接下来来自 Comodo 的说明。
# openssl version
OpenSSL 1.0.2k-fips 26 Jan 2017
# openssl rsa -check -noout -in server.key
RSA key ok
确认证书和私钥模数相同。
# openssl rsa -modulus -noout -in server.key
Modulus=[REDACTED]
# openssl x509 -modulus -noout -in server.crt
Modulus=[REDACTED]
测试 CRL 并验证颁发者:
# openssl crl -in root.crl -text
Certificate Revocation List (CRL):
Version 2 (0x1)
Signature Algorithm: sha1WithRSAEncryption
Issuer: /C=SE/O=AddTrust AB/OU=AddTrust External TTP Network/CN=AddTrust External CA Root
Last Update: May 28 00:12:38 2019 GMT
Next Update: Jun 1 00:12:38 2019 GMT
CRL extensions:
X509v3 Authority Key Identifier:
keyid:AD:BD:98:7A:34:B4:26:F7:FA:C4:26:54:EF:03:BD:E0:24:CB:54:1A
X509v3 CRL Number:
5275
Revoked Certificates:
Serial Number: 537B76564F297F14DC6943E922AD2C79
Revocation Date: Dec 14 15:58:30 2015 GMT
Serial Number: 46EAF096054CC5E3FA65EA6E9F42C664
Revocation Date: Dec 14 15:58:30 2015 GMT
Serial Number: 3ACDAB9C759886BCAF74E5DF81A9F4E8
Revocation Date: Dec 14 15:58:30 2015 GMT
Serial Number: 79174AA9141736FE15A7CA9F2CFF4588
Revocation Date: Apr 30 20:03:54 2018 GMT
Serial Number: 74C18753F7EEB4EA238D8416B5AC7646
Revocation Date: Oct 9 09:11:57 2018 GMT
Signature Algorithm: sha1WithRSAEncryption
38:3a:7d:3e:ee:be:48:e7:93:c3:91:0a:c3:47:46:11:87:83:
[TRIMMED]
5f:16:1a:38
-----BEGIN X509 CRL-----
MIICnTCCAYUCAQEwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UEBhMCU0UxFDASBgNV
[TRIMMED]
iEx7Li7fLtVPxbIU4aqaKU+15QEE37eJWRccBnuhqJqEDM+ML+k67Hj1yeLaXxYa
OA==
-----END X509 CRL-----
已启动 PostgreSQL 服务。
# service postgresql96 start
Starting postgresql96 service: [ OK ]
已验证日志文件中没有错误。
# cat ../pgstartup.log
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
# cat pg_log/postgresql-Wed.log
LOG: database system was shut down at 2019-08-14 15:01:03 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
已成功通过 SSL 连接到数据库本地主机和ssl模式 要求与完全验证(使用 FQDN)。
# psql "postgresql://mydbuser@localhost:5432/mydb?ssl=true&sslmode=require"
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mydb=> \q
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
Password: ********
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mydb=> \q
使用以下组合通过 Java 远程检索并直观验证 SSL 证书链此方法,此方法,此方法和此方法。这确认防火墙上该主机的端口 5432 已打开,并且可以通过 JDBC 检索证书和链。
Supported Protocols: 5
SSLv2Hello
SSLv3
TLSv1
TLSv1.1
TLSv1.2
Enabled Protocols: 3
TLSv1
TLSv1.1
TLSv1.2
Enabled Cipher suites: 43
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
TLS_RSA_WITH_AES_256_CBC_SHA256
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384
TLS_DHE_RSA_WITH_AES_256_CBC_SHA256
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
TLS_RSA_WITH_AES_256_CBC_SHA
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA
TLS_DHE_RSA_WITH_AES_256_CBC_SHA
TLS_DHE_DSS_WITH_AES_256_CBC_SHA
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
TLS_RSA_WITH_AES_128_CBC_SHA256
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256
TLS_DHE_RSA_WITH_AES_128_CBC_SHA256
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_DSS_WITH_AES_128_CBC_SHA
TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLS_RSA_WITH_AES_256_GCM_SHA384
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384
TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
TLS_RSA_WITH_AES_128_GCM_SHA256
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256
TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256
TLS_EMPTY_RENEGOTIATION_INFO_SCSV
Cert 1 (active):
Thumbprint SHA1 : B5:2D:43:A8:0F:C6:C3:39:1F:2D:BB:9C:30:A5:4B:8D:DF:5F:9B:F8
Fingerprint SHA256: 8c69068e8131306edaddc21c38837367973ddb3778b849d77e32a83f1f8b08ab
Subject: CN=*.[REDACTED].org,OU=PositiveSSL Wildcard,OU=Domain Control Validated
Issuer: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
Cert 2 (active):
Thumbprint SHA1 : 33:E4:E8:08:07:20:4C:2B:61:82:A3:A1:4B:59:1A:CD:25:B5:F0:DB
Fingerprint SHA256: 7fa4ff68ec04a99d7528d5085f94907f4d1dd1c5381bacdc832ed5c960214676
Subject: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
Issuer: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
Cert 3 (active):
Thumbprint SHA1 : 33:E4:E8:08:07:20:4C:2B:61:82:A3:A1:4B:59:1A:CD:25:B5:F0:DB
Fingerprint SHA256: 7fa4ff68ec04a99d7528d5085f94907f4d1dd1c5381bacdc832ed5c960214676
Subject: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
Issuer: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
Cert 4 (active):
Thumbprint SHA1 : EA:B0:40:68:9A:0D:80:5B:5D:6F:D6:54:FC:16:8C:FF:00:B7:8B:E3
Fingerprint SHA256: 1a5174980a294a528a110726d5855650266c48d9883bea692b67b6d726da98c5
Subject: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
Issuer: CN=AddTrust External CA Root,OU=AddTrust External TTP Network,O=AddTrust AB,C=SE
我确认上述根证书(AddTrust External CA Root)均位于默认 Java 密钥库中这里推荐(并且还确认它们也默认位于 Windows 密钥库中):
C:\Windows\System32>"C:\Program Files\Java\jdk1.8.0_212\jre\bin\keytool.exe" -keystore "C:\Program Files\Java\jdk1.8.0_212\jre\lib\security\cacerts" -storepass
changeit -list
Keystore type: jks
Keystore provider: SUN
Your keystore contains 95 entries
....
usertrustrsaca [jdk], Aug 25, 2016, trustedCertEntry,
Certificate fingerprint (SHA1): 2B:8F:1B:57:33:0D:BB:A2:D0:7A:6C:51:F7:0E:E9:0D:DA:B9:AD:8E
....
addtrustexternalca [jdk], Aug 25, 2016, trustedCertEntry,
Certificate fingerprint (SHA1): 02:FA:F3:E2:91:43:54:68:60:78:57:69:4D:F5:E4:5B:68:85:18:68
....
尝试通过 openssl 从远程主机连接到 PostgreSQL(例如这个问题)。
# openssl version
OpenSSL 1.1.0h 27 Mar 2018
# openssl s_client -connect [REDACTED].org:5432 -state -msg -showcerts -debug
CONNECTED(00000003)
SSL_connect:before/connect initialization
write to 0x2070760 [0x20fe520] (289 bytes => 289 (0x121))
0000 - 16 03 01 01 1c 01 00 01-18 03 03 0c 53 44 0c a3 ............SD..
[TRIMMED]
0110 - 03 01 03 02 03 03 02 01-02 02 02 03 00 0f 00 01 ................
0120 - 01 .
>>> TLS 1.2 [length 0005]
16 03 01 01 1c
>>> TLS 1.2 Handshake [length 011c], ClientHello
01 00 01 18 03 03 0c 53 44 0c a3 e2 21 36 f2 b0
[TRIMMED]
01 05 02 05 03 04 01 04 02 04 03 03 01 03 02 03
03 02 01 02 02 02 03 00 0f 00 01 01
SSL_connect:SSLv2/v3 write client hello A
read from 0x2070760 [0x2103a80] (7 bytes => 0 (0x0))
139650021263184:error:140790E5:SSL routines:ssl23_write:ssl handshake failure:s23_lib.c:177:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 289 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol : TLSv1.2
Cipher : 0000
Session-ID:
Session-ID-ctx:
Master-Key:
Key-Arg : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
Start Time: 1565797370
Timeout : 300 (sec)
Verify return code: 0 (ok)
---
# tail pg_log/postgresql-Wed.log
LOG: invalid length of startup packet
尝试通过 Windows psql 客户端 (9.6.5) 进行远程连接。
C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require"
psql: SSL error: certificate verify failed
由于某种原因,psql 客户端发送 TLSv1 警报“未知 ca”:
# tail pg_log/postgresql-Wed.log
LOG: could not accept SSL connection: tlsv1 [alert][15] unknown ca
此外,如果我指定根证书:
C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert=root.crt"
psql: SSL error: certificate verify failed
或者如果我将该参数留空:
C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert="
psql: SSL error: certificate verify failed
但是,如果我指定一个不存在的文件为该参数,我能够成功连接(使用 sslmode=require):
C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert=x"
Password:
psql (9.6.5, server 9.6.11)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q
来自规格:
未知的
A valid certificate chain or partial chain was received, but the certificate was not accepted because the CA certificate could not be located or couldn't be matched with a known, trusted CA. This message is always fatal.
尝试通过 Java 客户端连接postgresql-42.2.5.jar。
javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1946)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:316)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:310)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1639)
at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:223)
at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1037)
at sun.security.ssl.Handshaker.process_record(Handshaker.java:965)
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1064)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379)
at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:40)
... 36 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:397)
at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:302)
at sun.security.validator.Validator.validate(Validator.java:262)
at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:330)
at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:237)
at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:132)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1621)
... 44 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:392)
... 50 more
由于某种原因,Java 客户端正在生成 SSLv3警报,“证书未知”,即使它不是已启用的协议之一:
# tail pg_log/postgresql-Wed.log
LOG: could not accept SSL connection: sslv3 alert certificate unknown
来自规格:
证书未知
Some other (unspecified) issue arose in processing the certificate, rendering it unacceptable.
这PostgreSQL JDBC 驱动程序文档表示 Java 客户端(默认情况下)将尝试使用verify-full
对于服务器证书,这可能是此处产生的错误与 psql 客户端产生的错误不同的原因:
最简单的是 ssl=true,将其传递给驱动程序将导致驱动程序验证 SSL 证书并验证主机名(与 相同
verify-full
)。请注意,这与 libpq 不同,后者默认为非验证 SSL 连接。
上述失败(以及使用指定不存在的根证书时的成功sslmode=require
)似乎表明在验证证书的 CA 时存在一些问题。
笔记:我使用了这个答案生成新的证书并再次执行上述测试,结果相同。
答案1
中间证书必须在server.crt
我不确定是否需要将其添加到root.crt
。请参阅PostgreSQL 文档
编辑:
我刚刚创建了一个脚本来生成设置 SSL 所需的全部内容,并进行全面验证。您可以运行它并确认它是否有效吗?
#!/bin/bash
rm -rf /tmp/pg-ssl
mkdir -p /tmp/pg-ssl
openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
openssl req -new -nodes -text -out intermediate.csr -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.key
openssl x509 -req -in intermediate.csr -text -days 1825 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -CA root.crt -CAkey root.key -CAcreateserial -out intermediate.crt
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server.crt
cat server.crt intermediate.crt > bundle.crt
echo "ssl = true"
echo "ssl_cert_file = '/tmp/pg-ssl/bundle.crt'"
echo "ssl_key_file = '/tmp/pg-ssl/server.key'"
echo "add server ip in hosts file <IP> dbhost.yourdomain.com"
echo "copy root.crt to client"
echo 'connect with psql "postgresql://[email protected]:5432/dev?ssl=true&sslmode=verify-full&sslrootcert=/tmp/pg-ssl/root.crt"'
确保重新启动服务器并复制root.crt
到可以验证服务器身份的客户端psql
。出于测试目的,/etc/hosts
必须修改客户端上的文件以使 CN 从客户端角度有效。
答案2
在 Windows 上,默认root.crt
和root.crl
存储在%APPDATA%\postgresql
(此主题为我指明了正确的方向)。
当我删除这些文件时,我能够通过 psql 成功连接到远程服务器,而无需使用任何 ssl 参数(默认使用自动协商 ssl sslmode=require
):
C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb"
Password:
psql (9.6.5, server 9.6.11)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q
正如预期的那样,当我尝试强制sslmode=verify-ca
或时sslmode=verify-full
,psql 连接失败:
C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-ca"
psql: root certificate file "C:\Users\[USERNAME]\AppData\Roaming/postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: root certificate file "C:\Users\[USERNAME]\AppData\Roaming/postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
此外,当我尝试通过 JDBC 连接时,出现相同的错误(因为 JDBC 默认为sslmode=verify-full
):
org.postgresql.util.PSQLException: Could not open SSL root certificate file C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt.
at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:120)
at org.postgresql.core.SocketFactoryFactory.getSslSocketFactory(SocketFactoryFactory.java:61)
at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:33)
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:435)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:94)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:454)
at org.postgresql.Driver.connect(Driver.java:256)
...
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
Caused by: java.io.FileNotFoundException: C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt (The system cannot find the file specified)
at java.io.FileInputStream.open0(Native Method)
at java.io.FileInputStream.open(FileInputStream.java:195)
at java.io.FileInputStream.<init>(FileInputStream.java:138)
at java.io.FileInputStream.<init>(FileInputStream.java:93)
at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:117)
... 38 more
当我仅将顶级根证书(或路径#1或路径#2的两个顶级根证书)放在中时C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt
,我能够成功连接Java(使用通配符证书没有问题verify-full
!):
Connecting with URL: jdbc:postgresql://[REDACTED].org:5432/mydb
PostgreSQL JDBC Driver 42.2.5
Trying to establish a protocol version 3 connection to [REDACTED].org:5432
converting regular socket connection to ssl
Canonical host name for [REDACTED].org is [REDACTED].org
Server name validation pass for [REDACTED].org, subjectAltName *.[REDACTED].org
同样,当我在 Linux psql 客户端上执行相同操作时:
# cat certs/path_1/3_root_usertrust-selfsigned.crt > ~/.postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
Password: ********
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q
作为健全性检查,如果只有路径 #1 的证书在 server.crt 中,但我尝试verify-full
使用路径 #2 的根:
# cat certs/path_2/4_root_addtrustroot-selfsigned.crt > .postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed
然后我还附加了路径#1的根证书:
# cat certs/path_1/3_root_usertrust-selfsigned.crt >> .postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
Password: ********
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q
我的问题中的误解是:
- Windows 上的 PostgreSQL 使用 Windows 证书存储区 (错误的!)
- JDBC PostgreSQL 驱动程序使用默认的 Java 密钥库 (错误的!)
事实是,如果C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt
存在,则没有任何消息表明该文件在何处(我甚至没有注意到该文件夹)。
为了添加额外的 CRL,我需要下载并从 DER 转换为 PEM:
wget http://crl.usertrust.com/AddTrustExternalCARoot.crl
openssl crl -inform DER -in AddTrustExternalCARoot.crl -outform PEM -out AddTrustExternalCARoot_CRL.pem
wget http://crl.usertrust.com/USERTrustRSACertificationAuthority.crl
openssl crl -inform DER -in USERTrustRSACertificationAuthority.crl -outform PEM -out USERTrustRSACertificationAuthority_CRL.pem
cat USERTrustRSACertificationAuthority_CRL.pem AddTrustExternalCARoot_CRL.pem > root.crl
但后来我发现,如果我将其root.crl
(中级证书的 CRL)复制到~/.postgresql
,我的客户端连接就会失败,并出现与开始时相同的错误:
# cp ../data/root.crl ~/.postgresql
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed
我最终无法让 CRL 适用于远程连接,因此我删除了root.crl
客户端以简化配置。现在,我已成功verify-full
连接 psql 和 Java。有关 CRL 的详细内容,请参阅此相关问题。
我学到了什么:
- PostgreSQL JDBC 驱动程序不需要 中的中间证书
C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt
,并且将接受 的路径 #1 或路径 #2 的根证书verify-full
。 - 缺少
C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crl
(或~/.postgresql/root.crl
在 Linux 上)是可以的 - 如果
root.crl
客户端上存在必须包含服务器允许的每个验证路径的所有正确的 CRL。 - 如果
root.crl
提供了,但一个或多个根 CA 没有关联的 CRL 分发点,则连接可能会失败并显示一条certificate verify failed
消息。
我发现(就我而言)两个根 CA 证书都没有与之关联的 CRL,这可能会触发OpenSSL 错误:
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed
确认此错误的等效 openssl 命令是:
# openssl verify -crl_check -CAfile root.crt -CRLfile root.crl server.crt
server.crt: OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.[REDACTED].org
error 3 at 0 depth lookup:unable to get certificate CRL
如果上述错误确实是导致此错误的原因,那么我能够使用root.crl
我之前的 Comodo 证书的原因是根 CA 证书具有 CRL 分发点,因此从未触发此错误。短期内,我的解决方法是简单地删除root.crl
,这样就可以建立有效的连接。