Pivotal Knowledge Base

Follow

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

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x

Symptom

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

Cause

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
[......]
[......]

Resolution

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.

Comments

  • 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

    -Gurjeet

  • Avatar
    Faisal Ali

    Hi,

    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