select * from depesz;

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"

Now what?

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.