Linux – Password authentication failure in PostgreSQL 9.1 from remote client

linuxpostgresql

I run PostgreSQL 9.1.9 in Debian Squeeze server (using backports). The contents of pg_hba.conf file is:

local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host            all     all             192.168.1.237/32        md5

I have set the password for postgres user with:

alter user postgres with password 'pass';

I try to make a connection from PgAdmin, but I cannot make it work. It keeps throwing out the authentication failed error. My client is the one with the IP 192.168.1.237 that's shown in the file above. If I change the authentication method to trust and reload postgresql, I connect just fine. The error from the server logs is:

FATAL:  password authentication failed for user "postgres"

What's wrong with my server configuration?

Best Answer

Since you mention pgAdmin, if you used it to modify the postgres account, you may suffer from its password expiration's date bug mentioned on dba.se in:

PostgreSQL user can not connect to server after changing password

Basically the workaround is:

ALTER USER postgres VALID UNTIL 'infinity';
Related Topic