Postgres password authentication issue

Question

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

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
– Milen A. Radev, Commented Jan 31, 2014 at 15:47
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
– Craig Ringer, Commented Feb 1, 2014 at 4:35

pomaxa · Accepted Answer · 2014-01-31 16:24:50Z

2

in your pg_hba.conf, modify

local   all             postgres                                md5

to

local   all             postgres                                trust

then you'll be able to login without password

answered Jan 31, 2014 at 16:24

pomaxa

1,74616 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

If you do this, I hope you trust everyone able to access the server the PostgreSQL instance is running on, because you just gave them total access to the PostgreSQL server. I strongly recommend at least using peer authentication.

Collectives™ on Stack Overflow

Postgres password authentication issue

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related