2009-06-21

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 !!!

7 comments:

SoGua said...

so late still post ar. so hardworking la. hehe

Gurjeet Singh said...

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.

Unknown said...

It does not work for me.... if I modify the line, server does not start. If I remove "sameuser" part, server start but when using psql -U postgres, password is requested.

I have postgreSQL 9.1

Regards,
Jaime

Chris Tyler said...

I have 9.1, and I tried replacing 'md5 sameuser' with 'peer' and it worked.

Chris Tyler said...

I also have 9.1. It worked after I replaced 'md5 sameuser' with 'peer'

Negrabarba said...

It doesn't work with

local all postgres trust sameuser

But it worked with

local all postgres trust

Thanks for the tip

Unknown said...

Thanks for the info.
Note that with Postgresql 9.2 "trust sameuser" is not accepted anymore, instead use only "trust".