recover / reset lost posgres password

I am a long time MySQL user, so Postgres administration is unfamiliar to me.  I did used Postgres a long time ago… when I first started my web hosting company, but back then (early 90’s), Postgres was young and not suited for our needs.  Discovered MySQL and never looked back.

Yeah, yeah, stop with the religious war already.  I believe that using the right tool for the job is more important than anything else.

Back to the problem at hand.  As a new, incoming admin, there is a lot of things I have to pick up on-the-fly.  There is a lot of historical knowledge that I do not have at new place.  Such as Postgres, which *work* uses and now I have to admin.  It seem that no one know the postgres user password, or don’t want to share ;->

I really don’t care for politics, just want to do my work!  So things break and I need access to system tables to fix it.  Can’t login to Postgres as postgres, yes, I have root and can su postgres, but still can not login via psql.

E.g. psql -h localhost -U postgress -d template1  ask me for password and I don’t know postgres user password

Searching (googling) found a lot of people asking for help, etc. but no specific way to solve the problem.  At least for a newbie Postgres admin like me.

Finally, someone pointed to pg_hba.conf…. reading the manual, backward, forward, sideways, etc…. and finally, finally, figured it out!

Here it is to save time for others.

Edit pg_hba.conf (usually in /var/lib/psql/data directory, at least on RH/CentOS/Fedora).

Add this line at top, first ACL match wins, so don’t worry about the rest there.

host all postgres 127.0.0.1/32 trust

Then at command line, run pg_ctl reload

Now you can psql -h localhost -d template1 -U postgres and login without password.  Change the password to something you know.

alter user postgres with encrypted password ‘newpassword’;

Exit and comment out the line you added to pg_hba.conf, then reload config via

pg_ctl reload

Tada!  All fixed.