org.postgresql.util.PSQLException：致命：没有主机的 pg_hba.conf 条目

org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host

提问by Silpa Bhatraju

I am trying to connect to PostgreSQL database which is in remote location using Spring JDBC template. I am getting

我正在尝试使用 Spring JDBC 模板连接到位于远程位置的 PostgreSQL 数据库。我正进入（状态

org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "139.126.243.71", user "guest", database "masterdb", SSL off error

org.postgresql.util.PSQLException：致命：没有主机“139.126.243.71”的 pg_hba.conf 条目，用户“guest”，数据库“masterdb”，SSL 关闭错误

I don't have access to pg_hba.conf file of the remote location.

我无权访问远程位置的 pg_hba.conf 文件。

This is the configuration I gave in my spring servlet.xml

这是我在spring servlet.xml中给出的配置

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://100.64.35.52":5432/masterdb"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</bean>

Can we solve the issue by giving any properties?

我们可以通过提供任何属性来解决问题吗？

回答by Amit

It seems that DB server does not allow SSL off connection, You will have to enable it. Change URL from jdbc:postgresql://100.64.35.52":5432/masterdbto jdbc:postgresql://100.64.35.52":5432/masterdb?sslmode=require

似乎数据库服务器不允许 SSL 关闭连接，您必须启用它。将网址从 更改jdbc:postgresql://100.64.35.52":5432/masterdb为 jdbc:postgresql://100.64.35.52":5432/masterdb?sslmode=require

Check mode details about ssl mode at http://www.postgresql.org/docs/9.1/static/libpq-ssl.html

在http://www.postgresql.org/docs/9.1/static/libpq-ssl.html检查有关 ssl 模式的模式详细信息

回答by Parisa Taherian

You must apply below changes to connect:

您必须应用以下更改才能连接：

Navigate to the the following location C:\Program Files (maybe x86)\PostgreSQL\(your version)\data

导航到以下位置 C:\Program Files (maybe x86)\PostgreSQL\(your version)\data

postgresql.conffile:

postgresql.conf文件：

check the listen_addressesbe = *( by default its localhost in some postgres versions) if it isn't you must change it to *. It's used to listen on all interfaces.

检查 listen_addressesbe = *（在某些 postgres 版本中默认为 localhost），如果不是，则必须将其更改为*. 它用于侦听所有接口。

pg_hba.conffile:

pg_hba.conf文件：

add a new row :

添加新行：

host all all 0.0.0.0/0 md5

( use of above row is better) or

（使用上面的行更好）或

host guest masterdb 139.126.243.71 md5

And finally it's very important that the ssl mode is to be on. For this add below command in the end of your url:

最后，开启 ssl 模式非常重要。为此，在您的 url 末尾添加以下命令：

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

In Terms of Bean as

就 Bean 而言

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://100.64.35.52":5432/masterdb?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</bean>

回答by Sami Badawi

Combined settings

组合设置

I tried the suggestions given by @craig-ringer

我尝试了@craig-ringer 给出的建议

jdbc:postgresql://100.64.35.52":5432/masterdb?ssl=true

and given by @amit

并由@amit 提供

jdbc:postgresql://100.64.35.52":5432/masterdb?sslmode=require

neither worked. But when I combined them to:

都没有工作。但是当我将它们组合成：

jdbc:postgresql://100.64.35.52":5432/masterdb?ssl=true&sslmode=require

It worked.

有效。

回答by KeyMaker00

Error explanation

错误说明

The file pg_hba.conf(host-based authentication configuration file) is used to control the client authentication. This file is located in the database cluster's data directory.

该文件pg_hba.conf（基于主机的认证配置文件）用于控制客户端认证。该文件位于数据库集群的数据目录中。

The error java.sql.SQLException: FATAL: no pg_hba.conf entry for host "<your-host-ip>", user "<postgres-user>", database "<db-name>", SSL offmeans that Postgres accepts SSL connections but your are trying to connect without SSL.

该错误java.sql.SQLException: FATAL: no pg_hba.conf entry for host "<your-host-ip>", user "<postgres-user>", database "<db-name>", SSL off意味着 Postgres 接受 SSL 连接，但您尝试在没有 SSL 的情况下进行连接。

Secured connection

安全连接

If a certificate is requested from the client during SSL connection startup, it means that the DBA has set the clientcertparameter in pg_hba.conf. Note that if clientcertis not specified (or set to 0), the server will NOT insist that a client certificate be presented.

如果在 SSL 连接启动期间从客户端请求证书，则表示 DBA 已设置了 中的clientcert参数pg_hba.conf。请注意，如果clientcert未指定（或设置为 0），则服务器不会坚持提供客户端证书。

Check if database server is using SSL

检查数据库服务器是否使用 SSL

Before trying to access your SSL enabled server from Java, make sure that you can get to it via psql.

在尝试从 Java 访问启用 SSL 的服务器之前，请确保您可以通过psql.

If you want to connect with a given user at a given port:

如果要在给定端口与给定用户连接：

$ psql -h <server-ip> -p <server-port> -d <db-name> -U <user>

Or if you want to connect at the default port with your current user:

或者，如果您想在默认端口与当前用户连接：

$ psql -h <server-ip>

You should see an such output if you have established a SSL connection:

如果您建立了 SSL 连接，您应该会看到这样的输出：

$ psql -h <server-ip> -d <db-name> -U <user>
psql (11.2, Server 9.6.5)

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Note that the last line contains 'SSL connection'.

请注意，最后一行包含“SSL 连接”。

JDBC connection parameters

JDBC 连接参数

• sslmust be set if the server required a secured connection. Note that both ssl=trueor sslwork but ssl=trueis recommended for the coming future release of Postgres.
• sslfactoryprovides the class name to use as the SSLSocketFactory when establishing a SSL connection.

• ssl如果服务器需要安全连接，则必须设置。请注意，ssl=true或都可以ssl使用，但ssl=true建议用于即将发布的 Postgres 版本。
• sslfactory提供在建立 SSL 连接时用作 SSLSocketFactory 的类名。

The JDBC driver provdies an option to establish a SSL connection without doing any validation (but it's risky!).

JDBC 驱动程序提供了一个选项来建立 SSL 连接而不进行任何验证（但它有风险！）。

A non-validating connection is established via a custom SSLSocketFactoryclass that is provided with the driver. Setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactorywill turn off all SSL validation.

非验证连接是通过SSLSocketFactory驱动程序提供的自定义类建立的。设置连接 URL 参数sslfactory=org.postgresql.ssl.NonValidatingFactory将关闭所有 SSL 验证。

This was my case and why I also got the same error you had. So, to establish the connection I have:

这是我的情况，也是为什么我也遇到了与您相同的错误。因此，要建立连接，我有：

jdbc:postgresql://<ip-address>:<port>/<db-name>?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

References

参考

• Postgres JDBC SSL
• Postgres JDBC NonValidating
• Postgres SSL-TCP

• Postgres JDBC SSL
• Postgres JDBC 非验证
• Postgres SSL-TCP

回答by Ganesh

You must have root access edit pg_hba.conf.

您必须具有 root 访问权限 edit pg_hba.conf。

• goto rootuser
• edit /var/lib/pgsql/12/data/pg_hba.conf

• 转到root用户
• 编辑 /var/lib/pgsql/12/data/pg_hba.conf

host all all 139.126.243.71/32 trust

托管所有所有 139.126.243.71/32 信任

1. Restart Postgres [service postgresql-12.service restart].

1. 重新启动 Postgres [ service postgresql-12.service restart]。

回答by Florin D

See my answer here, basically had to put user/pwd in url property

在这里看到我的答案，基本上必须将 user/pwd 放在 url 属性中

url="jdbc:postgresql://server:port/mydb?user=fred&password=secret"