Let's say you're in situation when you have to connect to PostgreSQL, but you have no idea on what password might be set. But some definitely is, as you get this error message:
=> psql Password: psql: FATAL: password authentication failed for user "depesz"
If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root – solution is easy.
Find your pg_hba.conf file. It might be in many files so try:
- $ locate pg_hba.conf
- find /var/lib/ -type f -name pg_hba.conf
- find /etc -type f -name pg_hba.conf
- find / -type f -name pg_hba.conf
Of course last option is your last resort – it will take a long time.
When you'll find it, it might contain something like this:
=> cat /some/location/pg_hba.conf local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5
There might be more lines like these, there might be comments of blank lines.
Now. Edit the file, and at the beginning of it put:
local all all trust
or (depending on your paranoia):
local all postgres ident
And restart your PostgreSQL (usually something like /etc/init.d/postgres restart).
Afterwards you should be able to connect to Postgres as postgres user without password.
You should note, that if you have choosen the option with “ident" you will be able to connect without password only from shell account named “postgres".
When you'll connect issue alter user command:
=> psql ... # ALTER USER postgres WITH password 'new password, that i will never forget'; ALTER ROLE
After the change, remove this extra-added line from pg_hba.conf, restart Postgres, and that's all. You should have the access back.