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

rvillanueva — Fri, 16 Sep 2022 14:32:56 GMT

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_postgresql_for_ranger.html) 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_with_postgresql.html

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?

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

jsensharma — Fri, 20 Sep 2019 22:38:50 GMT

@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.

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

Shelton — Sat, 21 Sep 2019 17:53:22 GMT

@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 🙂

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

rvillanueva — Wed, 25 Sep 2019 00:59:54 GMT

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_postgresql_for_ranger.html

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-postgres.html

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

dinhhoanglong — Wed, 25 Sep 2019 02:50:23 GMT

OK.

tested and successful

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

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

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

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

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

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