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