如何通过 SSL 连接 PostgreSQL 数据库？

Question

提问by Valter Silva

I have created my own certificate and configured postgresql.conffile:

我已经创建了自己的证书和配置postgresql.conf文件：

...
#authentication_timeout = 1min          # 1s-600s
ssl = true                              # (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                        # (change requires restart)
#ssl_prefer_server_ciphers = on         # (change requires restart)
#ssl_ecdh_curve = 'prime256v1'          # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/company/database/certificate'           # (change requires restart)
ssl_key_file = '/etc/ssl/certs/company/database/key'            # (change requires restart)
ssl_ca_file = '/usr/share/ca-certificates/company/ca/certificate'                        # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)
#password_encryption = on
#db_user_namespace = off
#row_security = on
...

Then, I allow my server to connect with my database, pg_hba.conf:

然后，我允许我的服务器连接到我的数据库，pg_hba.conf：

...
hostssl    postgres             postgres             XXX.XXX.XXX.XXX/0            md5
...

So, I can connect to it via psqlcommand line:

所以，我可以通过psql命令行连接到它：

psql (9.5.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

But, when I try to open a connection with the database via my java application, even when I provide the truststore with my database certificate included, I keep getting no connection with it:

但是，当我尝试通过我的 java 应用程序打开与数据库的连接时，即使我提供了包含数据库证书的信任库，我也一直没有与它建立连接：

mvn package -Djavax.net.ssl.trustStore=/opt/app/truststore -Djavax.net.ssl.trustStorePassword=changeit

Exception:

例外：

2016-05-23 16:28:32,900 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@75fa1be3 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.>
2016-05-23 16:28:32,900 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2be057bf -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: >
java.lang.NullPointerException
    at org.postgresql.Driver.parseURL(Driver.java:532)
    at org.postgresql.Driver.acceptsURL(Driver.java:431)
    at java.sql.DriverManager.getDriver(DriverManager.java:299)
    at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:285)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:161)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:161)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:147)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPoolPooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:202)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access0(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2016-05-23 16:28:32,901 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@75fa1be3 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.>

Via psqleverything seems to be working fine, not with my application. Any suggestion ?

通过psql一切似乎工作正常，而不是我的应用程序。有什么建议吗？

EDIT:

编辑：

My props.propertiesfile:

我的props.properties文件：

uatDb.user=postgres
uatDb.password=password
uatDb.driverClass=org.postgresql.Driver
uatDb.jdbcUrl=jdbc:postgresql://<server_name>:1234/uat?ssl=true
uatDb.port=5443
uatDb.name=uat
uatDb.host=<server_name>

Answer 1

回答by D V Santhosh Kiran

url=jdbc:postgresql://<host_url_or_ip>:<port>/<db_name>?currentSchema=<schema_name>&sslmode=verify-ca&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory

Note: if schema_nameis public, it is not required. But port even if is default i.e. 5432, you have to provide it.

注意：如果schema_name是public，则不需要。但是端口即使是默认的，即 5432，你也必须提供它。

For sslmode values ref: https://jdbc.postgresql.org/documentation/head/ssl-client.htmlset sslfactory=org.postgresql.ssl.DefaultJavaSSLFactoryto enable validation.

对于 sslmode 值参考：https://jdbc.postgresql.org/documentation/head/ssl-client.html 设置sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory为启用验证。

For non-validating ssl connection, you can use sslfactory=org.postgresql.ssl.NonValidatingFactory

对于非验证 ssl 连接，您可以使用 sslfactory=org.postgresql.ssl.NonValidatingFactory

But remember, once you enable SSL validation, it may require a root CA certificate.

但请记住，一旦启用 SSL 验证，它可能需要根 CA 证书。

You have various options as follows (may not be exhaustive. but these worked for me.)

您有以下各种选择（可能并不详尽。但这些对我有用。）

You can place it in its default place i.e ~/Postgres/root.crt OR
Set PGSSLROOTCERTenv variable to its path OR
import into a truststore and pass it path as: -Djavax.net.ssl.trustStore=[trust_store_path] -Djavax.net.ssl.trustStorePassword=[trust_store_password]. If you are using default truststore i.e. JRE's cacertsthese two env variables are not required.

你可以把它放在它的默认位置，即 ~/Postgres/root.crt 或
将PGSSLROOTCERTenv 变量设置为其路径或
导入信任库并将其路径传递为： -Djavax.net.ssl.trustStore=[trust_store_path] -Djavax.net.ssl.trustStorePassword=[trust_store_password]。如果您使用默认信任库，即 JRE，cacerts则不需要这两个环境变量。

Ref:

参考：

https://jdbc.postgresql.org/documentation/head/ssl-client.html

https://www.postgresql.org/docs/9.0/libpq-ssl.html

Answer 2

回答by Dexter

Need a keystore too I believe. Since ssl=true should require a private key for your self-signed cert. I also would add "nonValidating SSLFactory" setting

我相信也需要一个密钥库。由于 ssl=true 应该需要您的自签名证书的私钥。我还会添加“nonValidating SSLFactory”设置

?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Also your port setting for server_name:1234does not match 5432.

此外，您的端口设置server_name:1234不匹配5432。

"parseUrl"is a null error, maybe there's a variable somewhere in the JDBC url that is incorrectly set.

"parseUrl"是一个空错误，也许 JDBC url 中某处有一个变量设置不正确。

Answer 3

回答by Orion

If you don't need to verify database client, just comment out ssl_ca_filein postgresql.conf.

如果不需要验证数据库客户端，只需ssl_ca_file在postgresql.conf.

ssl_ca_file- trusted certificate authorities, checks that client certificate is signed by a trusted certificate authority.

ssl_ca_file- 受信任的证书颁发机构，检查客户端证书是否由受信任的证书颁发机构签署。

Your database URL should look like this:

您的数据库 URL 应如下所示：

url=jdbc:postgresql://host:5432/db_name?ssl=true&sslmode=require

Refer to PostgreSQL JDBC docsfor more details on sslmode.

有关的更多详细信息，请参阅PostgreSQL JDBC 文档sslmode。

Otherwise, in case you need to verify the database client, you should add sslcert=...and sslkey=...parameters to your database URL.

否则，如果您需要验证数据库客户端，您应该将sslcert=...和sslkey=...参数添加到您的数据库 URL。

如何通过 SSL 连接 PostgreSQL 数据库？

提问by Valter Silva

回答by D V Santhosh Kiran

回答by Dexter

回答by Orion

相关推荐

最近更新

标签

如何通过 SSL 连接 PostgreSQL 数据库？

提问by Valter Silva

回答by D V Santhosh Kiran

回答by Dexter

回答by Orion

相关推荐

PostgreSQL - 或附近的语法错误

postgresql 如何使用 psql \copy 元命令忽略错误

postgresql 与 OR 时的情况

postgresql 错误：必须是数据库的所有者

相关推荐

最近更新

标签