Reset the Postgres User Password

Suppose that you are running PostgreSQL as your database server but you forgot the password of the postgres account. You can't connect on the shell using psql nor using the pgAdmin application. By the way... Yes, I am guilty as charged... been there, done that.

The key thing is to get around the server asking you for a password. You can do that by editing the pg_hba.conf file with the editor of your choice. In my case, running Ubuntu 8.10, I issued the following command at the shell:

sudo emacs /etc/postgresql/8.3/main/pg_hba.conf

In this file you will find the following lines:

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser

You will need to change it to look like this:

# Database administrative login by UNIX sockets
#local   all         postgres                          ident sameuser
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust #ident sameuser

What you have done when editing the pg_hba.conf file as indicated above is to tell the Postgres server that all connections originating at the local machine are trusted. Now you should be able to connect to your server by issuing the following command at the shell prompt:

psql -U postgres

At the Postgres prompt you may now reset the postgres user password by running the following statement:

postgres=# alter user postgres with password '<your password here>';

The postgres account has a new password and you can connect using it. Don't forget to revert back the changes that you made to pg_hba.conf.