Search code examples
postgresqlgreenplum

Can't connect to greenplum with error message : "no pg_hba.conf entry"

I'm using gp 4.3.7.2, and I followed the manual http://120.52.72.46/gpdb.docs.pivotal.io/c3pr90ntcsf0/gs/43/pdf/GPDB43xGettingStarted.pdf

I can connect to server with admin user, but when I try to use another user, I get the error message 

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "gpuser", database "rdw", SSL off

I've already done some research on stackoverflow. I modified the pg_hba.conf, add an entry

host  all  all 0.0.0.0/0 md5

and then reload the config file with gpstop -u and SELECT pg_reload_conf();, even tried to restart the gp server. But I still get the same error.

I also tried other entries like 

host    all             all             127.0.0.1/32            md5

this didn't work too.

Please tell me how to fix this.

edit:

I reinstalled the database in single node mode and followed exactly the manual mentioned before. I create two users : user1, user2 and grant role users to these two users. What I executed: In command line:

$ createuser -P user1
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

In psql:

=# CREATE USER user2 WITH PASSWORD 'changeme' CREATEDB NOSUPERUSER;    
=# CREATE ROLE users;
=# GRANT users TO user1, user2;

Here is my whole pg_hba.conf, which is located at /data/gp/server/master/gpsne-1/pg_hba.conf. I add the last 3 lines.

local    all         hadoop         ident
host     all         hadoop         127.0.0.1/28    trust
host     all         hadoop         10.10.10.32/32       trust
host     all         hadoop         ::1/128       trust
host     all         hadoop         fe80::d6ae:52ff:feb9:87c2/128       trust
local    replication hadoop         ident
host     replication hadoop         samenet       trust
local tutorial +users md5
local tutorial +users 127.0.0.1/28 md5
hostssl tutorial +users samenet md5

But I still get the same error.

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "user1", database "tutorial", SSL off

When I installed the database, I got a warning message, not sure if it is related.

20160302:10:44:51:023434 gpinitsystem:bobcat04:hadoop-[WARN]:-Host 127.0.0.1 is assigned as localhost in /etc/hosts
20160302:10:44:51:023434 gpinitsystem:bobcat04:hadoop-[WARN]:-This will cause segment->master communication failures
20160302:10:44:51:023434 gpinitsystem:bobcat04:hadoop-[WARN]:-Remove 127.0.0.1 from local host line in /etc/hosts
Solution

  • You have a mistake in the 2nd last line:

    local tutorial +users 127.0.0.1/28 md5

    There should not be an IP address listed for local connections.

    Did you run gpstop -u to reload after making the changes? I suspect the reload failed due to the invalid line.

    Remove the line and run gpstop -u again and try to connect again.