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.