ERROR: "FATAL: no pg_hba.conf entry for host "[local]", user "etl_user", database "etl_user", SSL off" while accessing psql for HAWQ

[#1]

While accessing psql for HAWQ, the command fails with the following error:

[etl_user@hdpmaster2 ~]$ source /usr/local/hawq/greenplum_path.sh 
[etl_user@hdpmaster2 ~]$ psql 
psql: FATAL: no pg_hba.conf entry for host "[local]", user "etl_user", database "etl_user", SSL off

This issue occurs when the etl_user is missing under pg_hba.conf and Postgres database.

To resolve the issue, create a new user from postgres by performing the following steps:

1. Login as gpadmin, sudo su - gpadmin.
2. Create new role and database:
   CREATE USER <user_name> SUPERUSER;
   CREATE DATABASE <user_name> WITH OWNER <database_name>;
3. Edit pg_hba.conf under Master data directory and make the following changes:
   local all <user_name> trust
   host all <user_name> 0.0.0.0/0 trust
   host all <user_name> ::/0 trust
4. Restart HAWQ cluster.

[#2]

Ack! No, don't add "trust". And "host all <user_name> 0.0.0.0/0 trust" means that the user_name you picked can log into the database without a password. Plus, you made the user a superuper. That is bad idea. Instead, add this to the end of the pg_hba.conf file on the master.

host all all 0.0.0.0/0 md5

This means it will use an encrypted password.

When you create your user, use this:

create user <user_name> identified by 'your_password';

or, if you already created your user:

alter user <user_name> identified by 'new_password';

And you don't have to restart the cluster.

hawq stop -u

That will update the cluster with the new pg_hba.conf file.

[#3]

1) For item 2, you don't need superuser to own a database.
2) For item 3, you just open the whole world to access your hawq cluster (all users from all ip to access all dbs) with superuser role. According to the error, you were trying to login "etl_users" from local socket to access "etl_users" db in hawq master, but hawq master didn't find any match entry in pg_hba. You can either specify $PGHOST to master IP (this way, psql will try to access hawq from tcp), or create local entry with md5/password auth method. For example, "local etl_user etl_user md5".

3) For item 4, you don't need to restart hawq cluster. "hawq stop --reload" should work in this case.