org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
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/masterdb
to 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
You must apply below changes to connect:
Navigate to the the following location C:\Program Files (maybe x86)\PostgreSQL\(your version)\data
postgresql.conf
file:
check the listen_addresses
be = *
( 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.
pg_hba.conf
file:
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=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
In Terms of Bean as
<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>
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.
The error java.sql.SQLException: FATAL: no pg_hba.conf entry for host "<your-host-ip>", user "<postgres-user>", database "<db-name>", SSL off
means that Postgres accepts SSL connections but your are trying to connect without SSL.
Secured connection
If a certificate is requested from the client during SSL connection startup, it means that the DBA has set the clientcert
parameter in pg_hba.conf
. Note that if clientcert
is not specified (or set to 0), the server will NOT insist that a client certificate be presented.
Check if database server is using SSL
Before trying to access your SSL enabled server from Java, make sure that you can get to it via 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:
$ 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'.
JDBC connection parameters
-
ssl
must be set if the server required a secured connection. Note that bothssl=true
orssl
work butssl=true
is recommended for the coming future release of Postgres. -
sslfactory
provides the class name to use as the SSLSocketFactory when establishing a SSL connection.
The JDBC driver provdies an option to establish a SSL connection without doing any validation (but it's risky!).
A non-validating connection is established via a custom SSLSocketFactory
class that is provided with the driver. Setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactory
will turn off all SSL validation.
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
Combined settings
I tried the suggestions given by @craig-ringer
jdbc:postgresql://100.64.35.52":5432/masterdb?ssl=true
and given by @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.
See my answer here, basically had to put user/pwd in url property
url="jdbc:postgresql://server:port/mydb?user=fred&password=secret"