Password authentication failure in PostgreSQL 9.1 from remote client

Question

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?

Answer 1

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