I am trying to create a cluster which installs hive. I am suing the Ambari embedded postgres db and created database hive. But testing db connection failed in Ambari console with the following error message. I wonder whether I need to add an entry to pg_hba.conf for the newly created hive user or not.
Feb 22, 2018 6:05:59 PM org.postgresql.Driver connect SEVERE: Connection error: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "10.63.6.134", user "hive", database "hive", SSL off at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:475) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195) at org.postgresql.Driver.makeConnection(Driver.java:452) at org.postgresql.Driver.connect(Driver.java:254) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at org.apache.ambari.server.DBConnectionVerification.main(DBConnectionVerification.java:37) ERROR: Unable to connect to the DB. Please check DB connection properties. org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "10.63.6.134", user "hive", database "hive", SSL off
Can you please share your "/var/lib/pgsql/data/pg_hba.conf" file here?
We would like to see if you have following kind of entry at the end of this file?
local all hive,ambari,mapred md5 host all hive,ambari,mapred 0.0.0.0/0 md5 host all hive,ambari,mapred ::/0 md5
Or you can add the following entry there:
host all all 0.0.0.0/0. md5
The restart your Postgres Service and then try again.
In addition to @Zaili Xu workaround, add the IP address of the host (on which PostgreSQL server is running) to the pg_hba.conf file and make sure that the method is set to "trust".
Note: Replace <ip address> with the IP address of your host to allow connections.
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 10.63.6.134/24 trust
# IPv6 local connections: host all all ::1/128 md5
Now, restart postgresql service.