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

Question

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

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

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

This is the configuration I gave in my 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?

Answer 1

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

Answer 2

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>