尝试使用 SSL 通配符服务器证书且无客户端证书连接到 PostgreSQL 9.6 时出错

Question 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 从客户端角度有效。

Answer

中间证书必须在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 从客户端角度有效。

Question 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，这样就可以建立有效的连接。

Answer