Unable to connect to Hive database in postgres via Ambari

[#1]

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

[#2]

@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

[#3]

@Zaili Xu

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

[#4]

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.

[#5]

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.