Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

HDP Ambari installation throws "org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host..." when testing connections

avatar
Expert Contributor

Attempting to install HDP 3.1.0 via Ambari 2.7.3 using an existing postgresql DB after following the docs here (https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/bk_ambari-installation/content/configuring_pos...) and entering the commands below:

 

----------

dbname=hive

postgres=postgres

user=hive

passwd=hive

echo "CREATE DATABASE $dbname;" | sudo -u $postgres psql -U postgres

echo "CREATE USER $user WITH PASSWORD '$passwd';" | sudo -u $postgres psql -U postgres

echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | sudo -u $postgres psql -U postgres

echo "alter user $postgres superuser;" | sudo -u $postgres psql -U postgres

 

 

dbname=oozie

postgres=postgres

user=oozie

passwd=oozie

echo "CREATE DATABASE $dbname;" | sudo -u $postgres psql -U postgres

echo "CREATE USER $user WITH PASSWORD '$passwd';" | sudo -u $postgres psql -U postgres

echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | sudo -u $postgres psql -U postgres

echo "alter user $postgres superuser;" | sudo -u $postgres psql -U postgres

 

dbname=ranger

postgres=postgres

user=rangeradmin

passwd=ranger

echo "CREATE DATABASE $dbname;" | sudo -u $postgres psql -U postgres

echo "CREATE USER $user WITH PASSWORD '$passwd';" | sudo -u $postgres psql -U postgres

echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | sudo -u $postgres psql -U postgres

echo "alter user $user superuser;" | sudo -u $postgres psql -U postgres

 

 

dbname=rangerkms

postgres=postgres

user=rangerkms

passwd=ranger

echo "CREATE DATABASE $dbname;" | sudo -u $postgres psql -U postgres

echo "CREATE USER $user WITH PASSWORD '$passwd';" | sudo -u $postgres psql -U postgres

echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | sudo -u $postgres psql -U postgres

echo "alter user $user superuser;" | sudo -u $postgres psql -U postgres

 

dbname=superset

postgres=postgres

user=superset

passwd=superset

echo "CREATE DATABASE $dbname;" | sudo -u $postgres psql -U postgres

echo "CREATE USER $user WITH PASSWORD '$passwd';" | sudo -u $postgres psql -U postgres

echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | sudo -u $postgres psql -U postgres

echo "alter user $user superuser;" | sudo -u $postgres psql -U postgres

----------

 

This was done based on the docs here: https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/administering-ambari/content/amb_using_hive_wi...

 

However, when doing the connection tests in the Ambari installation phase that checks the databases for the services to be installed, getting the error

 

Error injecting constructor, java.lang.RuntimeException: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "<some host>", user "<some service user>", database "<some service user>", SSL off

 

for the hive DB and I assume it would be the same for the druid and superset DBs as well if Ambari had provided a "test connection" button for those.

 

My question is: what is the problem here? The docs don't seem to indicate that anything more should be done (unlike with the docs for installing ranger), so what should be done?

 

Currently, my though is to do something like was done for ranger:

 

[root@HW001 ~]# echo "local all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid trust" >> /var/lib/pgsql/data/pg_hba.conf
[root@HW001 ~]# echo "host all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid 0.0.0.0/0 trust" >> /var/lib/pgsql/data/pg_hba.conf
[root@HW001 ~]# echo "host all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid ::/0 trust" >> /var/lib/pgsql/data/pg_hba.conf
[root@HW001 ~]# cat /var/lib/pgsql/data/pg_hba.conf 

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all postgres peer
# IPv4 local connections:
host all postgres 127.0.0.1/32 ident
# IPv6 local connections:
host all postgres ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

local all ambari,mapred md5
host all ambari,mapred 0.0.0.0/0 md5
host all ambari,mapred ::/0 md5
local all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid trust
host all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid 0.0.0.0/0 trust
host all postgres,rangeradmin,rangerlogger,hive,oozie,ranger,rangerkms,superset,druid ::/0 trust

 

but not sure if there is something else I'm missing here or some other things that I should be seeing that I am not. Is this is the correct thing to do or something else?

1 ACCEPTED SOLUTION

avatar
Master Mentor

@rvillanueva 

After making the changes inside the Correct "pg_hba.conf" file have you restarted the Postgres Database?

 

# /etc/init.d/postgresql restart
(OR)
# systemctl start postgresql

 

 

Also what is your Postgres Version like (10.5  / 10.2  / 9.6) ? SupportMatrix: https://supportmatrix.hortonworks.com/

Based on the Postgres Version the "pg_hba.conf" location might be slightly different like:

 

/var/lib/pgsql/9.6/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf 
..etc

 

So please make sure that the changes you are making in the "pg_hba.conf" is actually the correct file.

View solution in original post

4 REPLIES 4

avatar
Master Mentor

@rvillanueva 

After making the changes inside the Correct "pg_hba.conf" file have you restarted the Postgres Database?

 

# /etc/init.d/postgresql restart
(OR)
# systemctl start postgresql

 

 

Also what is your Postgres Version like (10.5  / 10.2  / 9.6) ? SupportMatrix: https://supportmatrix.hortonworks.com/

Based on the Postgres Version the "pg_hba.conf" location might be slightly different like:

 

/var/lib/pgsql/9.6/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf 
..etc

 

So please make sure that the changes you are making in the "pg_hba.conf" is actually the correct file.

avatar
Expert Contributor

Had only done 

 

sudo -u postgres /usr/bin/pg_ctl -D $PGDATA reload

from https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/bk_ambari-installation/content/configuring_pos...

 

So I think restarting the service helped (honestly, did many other things so hard to tell which did the trick). For other finding this, a hint that the service should have been restarted could have been found in the docs here: https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/bk_ambari-installation/content/install-postgre...

 

 

avatar
New Contributor

OK.

tested and successful

 

avatar
Master Mentor

@rvillanueva 

To add on to what @jsensharma  commented it's always a good idea to have separate databases for druid and superset!

In case you get some issues then you have only a component's data in jeopardy 🙂