Database connection by a user requiring a password fails with Error "psql: FATAL: password authentication failed for user"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

A user is created on the database and the pg_hba.conf. The user's credentials are updated to allow the user to connect to the database only using a password.

Snippet from pg_hba.conf file:

local    gpperfmon         gpmon         md5
host     all         gpmon         127.0.0.1/28    md5

Reloading the configuration using gpstop -u and attempting to connect to the database will result in an error message before being prompted to enter a password:

[gpadmin@mdw ~]$  psql -d gpperfmon -U gpmon -c 'select * from system_now;'
psql: FATAL:  password authentication failed for user "gpmon"

To workaround this issue, use the -W along with PSQL to force the password prompt.

[gpadmin@mdw gpseg-1]$  psql -d gpperfmon -U gpmon  -W
Password for user gpmon:

Environment

Cause

The issue is that the environment parameter "PGPASSWORD" is set. This results in the password being auto supplied when the password is 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
[......]
[......]

Resolution

PGPASSWORD behaves the same as the password connection parameter. Using this environment variable is not recommended for security reasons. Mainly, some of the operating systems allow non-root users to view process environment variables via ps. Instead, use the ~/.pgpass file.

Remove the environment parameter and retry the connection.