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:
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