HDP Ambari installation throws "org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host..." when testing connections
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?
