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

Explorer

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
4 REPLIES 4
Highlighted

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

Highlighted

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?

Highlighted

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

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.

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

Explorer

first for ubuntu :

on the ambari server machine (

nano /etc/postgres/10/main/pg_hba.conf ==> local all postgres peer

                                                                          local all all trust

                                                       host all postgres 127.0.0.1/32 trust

                                                       ipv6 trust

add                                                 local all ambari,mapred md5

                                                       host all ambari,mapred 0.0.0.0/0 md5

                                                       host all ambari,mapred ::/0 md5

                                                       host hive hive 0.0.0.0/0 md5

nano /postgres.conf ==> uncomment listen_addresses = '*'

                                                     and port = 5432

instal jdk-8

wget the jdbc.jar file with in a known directory (https://jdbc.postgresql.org/)

use ambari-server setup --jdbc-db=postgres --jdbc-driver=/path/to/postgres/name of installed file

in postgres : create user with password, create database and grant all privileges ;

in ambari setup :just enter advanced setup (and dont change the default parameters, just press enter

note :

MAKE SURE THAT DATABASE URL IN AMBARI WEB PAGE(STEP 7 :CUSTOMIZE SERVICES) IS TAKING THE NAME OF THE MASTER MACHINE WHERE  AMBARI SERVER AND JDBC DRIVER ARE INSTALLED FOR EXAMPLE :
jdbc:postgresql://master.hadoop.com:5432/hive    (AND THIS IS THE ISSUE IN YOUR CASE HERE )

RESOLVED

THE SAME STEPS FOR CENTOS 7 JUST INSTALL WGET BEFORE

Good luck.

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