Pivotal Greenplum Database (GPDB) 4.3.x
A user was created on the database and the pg_hba.conf and was updated to allow the connection to the database only via the password.
Snippet from pg_hba.conf file
local gpperfmon gpmon md5 host all gpmon 127.0.0.1/28 md5
Reloaded the configuration using "gpstop -u" and now attempting to connect to the database result in the error message even before prompting to enter a password.
[gpadmin@mdw ~]$ psql -d gpperfmon -U gpmon -c 'select * from system_now;' psql: FATAL: password authentication failed for user "gpmon"
If you use the "-W" along with psql to force the password prompt, it works
[gpadmin@mdw gpseg-1]$ psql -d gpperfmon -U gpmon -W Password for user gpmon:
The issue is due to the environment parameter "PGPASSWORD" was set, which resulted into auto supplying the password when it was requested.
# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export PGPASSWORD=xxxx [......] [......]
PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some of the operating systems allow non-root users to see process environment variables via ps, instead, consider using the ~/.pgpass file
Remove the environment parameter and retry the connection again.