Pivotal Knowledge Base


Database Connection by A User Requiring a Password Fails with "psql: FATAL: password authentication failed for user"


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

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


  • Avatar
    Gurjeet singh

    Hi Faisal,

    Could you please tell me in greenplum on which table history of login failures will be stored ?
    For Example : Login A attempted to connect with my production database with wrong password so there must be some table where history of those attempts will be stored


  • Avatar
    Faisal Ali


    There is no table at the moment that store the information of login failure, but you can use the gptoolkit view "gp_log_database" to retrieve the information ( provided you have the logs in the pg_log directory ) , you can also use this information to create a history table.

    for eg.s

    select * from gp_toolkit.gp_log_database where logseverity='FATAL' and logmessage like '%password%';
Powered by Zendesk