I accidentally set password to blank for a PostgreSQL database, how do I change it?

Question

When I set the password to blank, it didn't give any warnings. Now when I try to log in with a blank password, it gives me an error "fe_sendauth: no password supplied". So now I can't log in - any ideas how I can get access to the db again?

Answer 1

This blogpost should answer. Full disclosure: it's my blogpost.

Summary:

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.

Edit your pg_hba.conf 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”.

don't forget to reverse the pg_hba.conf settings when you have reset the password.

Answer 2

It really depends on how your cluster is configured...

If you have another administrator user (postgres?) you can just connect and:

ALTER USER username PASSWORD 'new password';

Otherwise you could (temporarily) allow password-less logins in pg_hba.conf (see http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html - search for the word "trust")