PostgreSQL - How to reset user name "postgres" password

PostgreSQL is an open source RDBMS (Relational DataBase Management System) that is not only feature rich, fast and light but also easy to use. Its documentation available at its website is a superb way of learning as well. Browsing the documentation and you will find that its way of explaining is no "bullshit" approach, short and concise.

After the initial installation, one might forget to set the password after running the initial script to setup the database. After a while, naturally, we all forget the password. Below are the steps to reset the password for user name "postgres" :

1. In pg_hba.conf, insert or change the below line.

from :

local   all         postgres

to

local   all         postgres                          trust sameuser

2. Restart PostgreSQL services in order for Step 1 changes to take effect :

• In Linux,
  

  /etc/init.d/postgresql-8.3 restart

• In FreeBSD,
  

  /usr/local/etc/rc.d/postgres restart

3. Login to PostgreSQL on the local machine with the user name "postgres" to change the password :

e.g.

psql -U postgres

4. At the "postgres=#" prompt, change the user name "postgres" password :

e.g.

ALTER USER postgres with password 'secure-password';

5. Quit PostgreSQL interactive session by executing "\q", to exit

6. Alter the configuration (what we did in Step 1) to disable password-less login from local machine to PostgreSQL by changing the word "trust" to "md5" in pg_hba.conf.

e.g.

from:

local   all         postgres                          trust sameuser

to:

local   all         postgres                          md5 sameuser

7. Restart PostgreSQL to make Step 6 changes take effect by repeating Step 2.

8. Re-login to PostgreSQL using the new password by :

psql -U postgres

Voilla !!!