Recovering from a lost PostgreSQL password.

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.

9 thoughts on “Recovering from a lost PostgreSQL password.”

  1. Handy info. It should probably be called, “Recovering *from a* lost PostgreSQL password,” though, as I didn’t see a part about actually recovering a password 🙂

  3. Trying to install the one clicker Windows install at http://www.enterprisedb.com/products/pgdownload.do#windows. It asks for a password for the install but I have no idea what it might be. I’ve never installed before so I’m not even sure the pg_hba.conf file would be on my machine.

    Can you give any more suggestions that would help a dummy like me?

    Regards,

    Mark

  4. @Mark:
    sorry – never used pg on windows, and never used enterprisedb version of pg. try some enterprisedb support list/group perhaps?

  5. I don’t think it’s necessary to actually stop and restart the postmaster to pick up changes to pg_hba.conf; running “/etc/init.d/postgres restart” as you suggest would likely force a stop and restart, depending on your init script setup.

    “pg_ctl reload” should be enough. From the manual:

    “reload mode simply sends the postgres process a SIGHUP
    signal, causing it to reread its configuration files
    (postgresql.conf, pg_hba.conf, etc.). This allows changing
    of configuration-file options that do not require a
    complete restart to take effect.”

  6. @Josh Kupershmidt:
    Technically – you are right. And it’s of course sufficient. But for person that has problems with recovering from password loss – it will be generally easier to restart postgresql (/etc/init.d/postgres restart is pretty much standard), while doing reload is much more platform/distribution dependant.

  7. What if you’ve uninstalled postgre from a windows machine because the partition was full and wanted it installed on another. I went through the postgre uninstall utility but it left a few folders behind. Now when I try to install it on the other partition it still asks for a password. I didn’t do the original install and the guy that did is no longer with the company.

  8. @Matt: no idea. I don’t use windows, so I just don’t know what case you’re talking about. Try asking on pgsql-general mailing list.

Comments are closed.