I've installed PostgreSQL 9.1 and pgadmin3 on Ubuntu Server 13.10.
I configured postgresql.conf with: listen_addresses = '*'
also I configured ph_hba.conf by changed peer connections to md5
Plus I reset the password of postgres by: sudo password postgres
then restarted the service with sudo /etc/init.d/postgresql restart
after that I tried to connect to the default PostgreSQL template database:
sudo -u postgres psql template1
but login failed with this error message:
psql: FATAL: password authentication failed for user "postgres"
then I tried to login from the pgadmin, which gave me the same error.
I've read here that it might be a password expiry dates bug
PostgreSQL user can not connect to server after changing password
but I couldn't solve it coz I cannot login with psql. Does anyone now how to resolve this issue?
EDIT
ph_hba file:
Screenshot:
Best Answer
You are confusing the password for the unix user "postgres" with the database password for the database user "postgres". These are not the same.
You've locked yourself out, because you enabled
md5
authentication for database userpostgres
without setting a password for the database userpostgres
.Add a new line to the top of
pg_hba.conf
:then restart/reload PostgreSQL and:
From the resulting prompt:
then remove the line you added to
pg_hba.conf
and restart Pg again. You can now use the password you set above to connect to PostgreSQL as thepostgres
user.To learn more, read the "client authentication" chapter of the user manual and the docs on
pg_hba.conf
.