November 28th, 2008 by depesz | Tags: , , , , | 9 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 9 comments

  2. Nov 29, 2008

    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. Nov 29, 2008

    @David Fetter:
    your wish is my command :)

    Thanks for correction.

  4. # Mark
    Sep 30, 2009

    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

  5. Sep 30, 2009

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

  6. # Josh Kupershmidt
    Feb 25, 2010

    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.”

  7. Feb 25, 2010

    @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.

  8. # Matt
    Mar 5, 2011

    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.

  9. Mar 5, 2011

    @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.

  10. Nov 26, 2012

    Very nice write-up. I certainly appreciate this website. Continue
    the good work!

Leave a comment