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 !!!
2 Comments
so late still post ar. so hardworking la. hehe
In Steps 2 and 7, for the pg_hba.conf to take effect, you can just use the comman ‘reload’. Instead of restarting the Postgres server, it just signals the server to re-read the config files.