Ssl

嘗試使用 SSL 萬用字元伺服器證書和無客戶端證書連接到 PostgreSQL 9.6 時出錯

  • August 19, 2019

我在 Amazon Linux 上有一個 PostgreSQL 9.6.11 數據庫,自 2012 年 1 月以來已配置了 2048 位 SSL 萬用字元伺服器證書和基於密碼(無客戶端證書)的遠端連接。在最近的證書升級後(Comodo,現在是 Sectigo) ,我無法再通過 SSL 與該數據庫建立遠端 psql 或 JDBC 連接。

我的目標是能夠通過 psql 和 JDBC 遠端連接到這個 PostgreSQL 數據庫。

從伺服器密鑰(自從我有遠端訪問工作以來它沒有改變)開始,我試圖涵蓋整個系列的步驟,以驗證我的密鑰、證書、防火牆和數據庫設置是否正確。

我一定錯過了什麼,因為我無法通過 psql 或 JDBC 遠端連接。

我錯過了什麼可能導致這些遠端連接失敗?

故障排除步驟


作為使用者postgres

# cd /var/lib/pgsql96/data

postgresql.conf

我曾嘗試限制密碼集以嘗試強制所有 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

pg_hba.conf

該文件設置為僅允許本地主機的公共 IP 地址和我正在測試的遠端主機。我不想要求客戶端證書,只需要使用所需密碼進行加密。

hostssl       all     all     11.222.11.222/32      password # localhost
hostssl       all     all     34.84.31.82/32        password # remote host

我通過ssltest檢查了認證路徑,發現有兩個可用路徑(路徑 #1路徑 #2):

SSLTest 證書路徑

PostgreSQL 9.6 Secure TCP/IP Connections with SSL的文件中:

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

使用sslmode requireverify-full(使用 FQDN)從localhost通過 SSL 成功連接到數據庫。

# 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

規範

未知的_ca

  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)。請注意,這與預設為非驗證 SSL 連接的 libpq 不同。

上述失敗(以及使用 指定不存在的根證書時的成功sslmode=require)似乎表明驗證證書的 CA 存在一些問題。

**注意:**我使用此答案中提供的腳本生成新證書並再次執行上述測試,結果相同。

在 Windows 上,預設值root.crtroot.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-caorsslmode=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 密鑰庫 ( FALSE! )

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客戶端上存在 a,它必須包含伺服器允許的每個驗證路徑的所有正確 CRL。
  • 如果root.crl提供了 a,但一個或多個根 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,這會導致工作連接。

中間證書必須在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://dev@dbhost.yourdomain.com:5432/dev?ssl=true&sslmode=verify-full&sslrootcert=/tmp/pg-ssl/root.crt"'

確保重新啟動伺服器並複製root.crtpsql可以驗證伺服器身份的客戶端。出於測試目的/etc/hosts,必須修改客戶端上的文件以使 CN 從客戶端角度有效。

引用自:https://serverfault.com/questions/979282