如何通过 SSL 连接 PostgreSQL 数据库? [英] How to connect with PostgreSQL database over SSL?
问题描述
我已经创建了自己的证书并配置了 postgresql.conf
文件:
I have created my own certificate and configured postgresql.conf
file:
...
#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
...
然后,我允许我的服务器连接到我的数据库,pg_hba.conf
:
Then, I allow my server to connect with my database, pg_hba.conf
:
...
hostssl postgres postgres XXX.XXX.XXX.XXX/0 md5
...
所以,我可以通过 psql
命令行连接到它:
So, I can connect to it via psql
command line:
psql (9.5.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
但是,当我尝试通过我的 java 应用程序打开与数据库的连接时,即使我提供包含数据库证书的信任库,我仍然无法与它建立连接:
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:
mvn package -Djavax.net.ssl.trustStore=/opt/app/truststore -Djavax.net.ssl.trustStorePassword=changeit
异常:
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.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.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.access$700(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.>
通过 psql
一切似乎都运行良好,而不是我的应用程序.有什么建议吗?
Via psql
everything seems to be working fine, not with my application.
Any suggestion ?
我的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>
推荐答案
url=jdbc:postgresql://<host_url_or_ip>:<port>/<db_name>?currentSchema=<schema_name>&sslmode=verify-ca&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
注意:如果schema_name
是public
,则不需要.但是端口即使是默认的,即 5432,你也必须提供它.
Note: if schema_name
is public
, it is not required. But port even if is default i.e. 5432, you have to provide it.
对于 sslmode 值参考:https://jdbc.postgresql.org/documentation/head/ssl-client.html设置 sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
以启用验证.
For sslmode values ref: https://jdbc.postgresql.org/documentation/head/ssl-client.html
set sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
to enable validation.
对于非验证 ssl 连接,您可以使用 sslfactory=org.postgresql.ssl.NonValidatingFactory
For non-validating ssl connection, you can use sslfactory=org.postgresql.ssl.NonValidatingFactory
但请记住,一旦您启用 SSL 验证,它可能需要根 CA 证书.
But remember, once you enable SSL validation, it may require a root CA certificate.
您有以下多种选择(可能并不详尽.但这些对我有用.)
You have various options as follows (may not be exhaustive. but these worked for me.)
- 您可以将其放在默认位置,即 ~/Postgres/root.crt 或
- 将
PGSSLROOTCERT
环境变量设置为其路径 OR - 导入信任库并将其路径传递为:
-Djavax.net.ssl.trustStore=[trust_store_path] -Djavax.net.ssl.trustStorePassword=[trust_store_password]
.如果您使用默认信任库,即 JRE 的cacerts
,则不需要这两个环境变量.
- You can place it in its default place i.e ~/Postgres/root.crt OR
- Set
PGSSLROOTCERT
env 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'scacerts
these two env variables are not required.
参考:
https://jdbc.postgresql.org/documentation/head/ssl-客户端.html
https://www.postgresql.org/docs/9.0/libpq-ssl.html
这篇关于如何通过 SSL 连接 PostgreSQL 数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!