Recently I upgraded to the latest Postgres version. I had a number of pain points doing this including not being able to start the DBs because the version upgrade was too big – had to export from old version and import to new. But one of the problems was that for some reason my password didn’t work.

One of the more important config files in Postgres is pg_hba.conf. It is the one that controls access. In my case the uncommented bit looks like:

   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

   # "local" is for Unix domain socket connections only
   local   all         all                               password
   # IPv4 local connections:
   host    all         all         192.168.0.0/16          md5
   host    all         all         127.0.0.1/32            md5

Running on Fedora 18 this is located in:

/var/lib/pgsql/data/pg_hba.conf

To reset the password you need to change the first line from “password” to “trust” this means that it will log you in with the Unix account “postgres” rather than the Postgres account “root”. So change the file as below:

#local   all         all                               password
local   all         all                               trust

You will need to restart Postgres for it to be effective !

The following commands should reset the password:

su – postgres
psql -U postgres
ALTER USER postgres WITH PASSWORD 'mySuperSecretpassword';
\q

Obviously the above assumes you are running Postgres under the account “postgres”. Note that “/q” is the command to exit psql.

Obviously afterwards you need to undo the change to “pg_hba.conf” and restart PosgreSQL