Postgres password authentication issue

3
votes

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:

local   all             postgres                                md5
local   all             all                                     md5
local   all             all                                     trust

host    all             all             127.0.0.1/32            md5
hostssl all             all             192.168.0.0/16          trust
host    all             all             192.168.0.0/16          trust

host    all             all             ::1/128                 md5
1
postgresqlubuntu
Can you try: sudo -u postgres psql -U postgres -W template1 - Houari
Gave me the same error message - Shadin
You have changed the system user postgres's password, but you haven't changed the DB user postgres's password, which in case of md5 authentication, is the important one. And also in this case you don't need to start psql under system user postgres. Please post your pg_hba.conf here if you still need help! - Milen A. Radev
Updated my post. thanks in advance - Shadin
You cloned this post here: askubuntu.com/q/413585/75715 . Thanks for wasting everyone's time. My answer on that post explains what mistake caused this problem, and what to do to fix it. - Craig Ringer

1 Answers

2
votes

in your pg_hba.conf, modify 

local   all             postgres                                md5

to

local   all             postgres                                trust

then you'll be able to login without password