Recovering/Reseting Password PostgreSQL Server

For those who had encountered error as below,

ubuntu@ip-10-128-73-74:~$ psql -U postgres -h localhost
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

here is how to fix it.

  1. Edit pg_hba.conf and change the following line
    ubuntu@ip-10-128-73-74:~$ sudo nano /etc/postgresql/9.0/main/pg_hba.conf
    # old
    local   all         postgres                         ident
    

    into

    # new
    local   all         postgres                         trust
    
  2. Restart the postgreSQL service

    ubuntu@ip-10-128-73-74:~$ sudo service postgresql restart
  3. Reset your postgres password by executing

    ubuntu@ip-10-128-73-74:~$ psql -U postgres -d template1 -c alter user postgres with password 'yournewpasswd';
    psql: warning: extra command-line argument "user" ignored
    psql: warning: extra command-line argument "postgres" ignored
    psql: warning: extra command-line argument "with" ignored
    psql: warning: extra command-line argument "password" ignored
    psql: warning: extra command-line argument "yournewpasswd" ignored
    ERROR:  syntax error at end of input
    LINE 1: alter
    
  4. ubuntu@ip-10-128-73-74:~$ psql -U postgres -d template1 -c "alter user postgres with password 'yournewpasswd'";
    ALTER ROLE
    
  5. Edit pg_hba.conf and change the following line

    ubuntu@ip-10-128-73-74:~$ sudo nano /etc/postgresql/9.0/main/pg_hba.conf
    # old
    local   all         postgres                         trust
    

    into

    # new
    local   all         postgres                         ident
    
  6. As before, restart the service

    ubuntu@ip-10-128-73-74:~$ sudo service postgresql restart
  7. Finally, check your new passwd

    ubuntu@ip-10-128-73-74:~$ psql -U postgres -h localhost
    Password for user postgres:
    psql (9.0.4)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.
    
    postgres=# \q
    ubuntu@ip-10-128-73-74:~$
    

Posted with WordPress for BlackBerry.

Advertisements