Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to connect to Hive database in postgres via Ambari

Highlighted

Unable to connect to Hive database in postgres via Ambari

New Contributor

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
3 REPLIES 3

Re: Unable to connect to Hive database in postgres via Ambari

Super Mentor

@Zaili Xu

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.


Reference:

http://irwinj.blogspot.in/2010/07/no-pghbaconf-entry-for-host.html

Re: Unable to connect to Hive database in postgres via Ambari

Super Mentor

@Zaili Xu

Is this working ? Or are you still facing this issue?

Re: Unable to connect to Hive database in postgres via Ambari

New Contributor

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.

Don't have an account?
Coming from Hortonworks? Activate your account here