In this tutorial we will learn How to set user postgres password in postgresql 9.1 and force to prompt for password. When you freshly install Postgresql and after using command psql it will allow you to login into postgresql server without prompting or asking for password.

Here we will first of all reset the password of user called postgres which is admin of Posgresql server.
Later we will edit the file called pg_hba.conf .
I worked in some old version of Postgresql (version 8.x) but in version 9.1, I found the path of pg_hba.conf is changed (In version 8.x it was located in /var/… )

Details:
Practical has been performed in following environment
Operating System : Ubuntu 12.10 (But steps can be followed in Debian,CentOS and Red Hat also)
Arch: i386

To reset the password of user postgres,follow the given below steps

Step 1: Login with user root or become superuser (root)

sudo su -

or 

su -

Step 2: Install Postgresql server

==== In Debian or Ubuntu ===
apt-get install postgresql

=== In Red Hat and CentOS ===
yum install postgresql

Step 3: Now switch to user postgres (You must be login with root)

root@tuxworld:~# su -l postgres

Step 4 Now login into postgresql server.Now run the below command and reset the password

Password reset syntax : ALTER USER postgres WITH ENCRYPTED PASSWORD 'passwd';
Note: in above syntax, replace the word passwd and give your desired password

postgres@tuxworld:~$ 
postgres@tuxworld:~$ psql
psql (9.1.9)
Type "help" for help.

postgres=# 
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'passwd';
ALTER ROLE
postgres=# q

Step 5: Now exit from user postgres login

postgres@tuxworld:~$exit

Step 6: After exit from postgres user login, now you are back to root’s terminal

Take the backup of pg_hba.conf file . (so that if anything goes wrong we will revert back to old settings)

cp -p /etc/postgresql/9.1/main/pg_hba.conf /etc/postgresql/9.1/main/pg_hba.conf.orig 

Step 7: Now edit /etc/postgresql/9.1/main/pg_hba.conf file.use your favorite editor(vi,vim,nano etc.)

Go to the bottom of file.
comment line local all postgres peer

And new line
local all postgres md5

See my system’s /etc/postgresql/9.1/main/pg_hba.conf file, below given is for reference

vim /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5

Save and exit from /etc/postgresql/9.1/main/pg_hba.conf file

Step 8: Now restart the posgresql service.

/etc/init.d/postgresql restart

Reference:

root@tuxworld:/etc/postgresql/9.1/main# /etc/init.d/postgresql restart
* Restarting PostgreSQL 9.1 database server [ OK ]
root@tuxworld:/etc/postgresql/9.1/main#

Step 9: now login into postgresql server and notice this time it will ask you login password

(a)Switch to user postgres su -l postgres
(b)Run the command psql
(c)give password of postgres

Reference:

root@tuxworld:/etc/postgresql/9.1/main# su -l postgres
postgres@tuxworld:~$ 
postgres@tuxworld:~$ psql
Password: 
psql (9.1.9)
Type "help" for help.

postgres=# 
postgres=# q
postgres@tuxworld:~$

Reset the forgotten user called postgres password

If you forgot the password of user called postgres,in this case revert back to old settings of pg_hba.conf file and restart the postgresql service

Comment the line local all postgres md5
And uncomment the line local all postgres peer


vim /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer
#local   all             postgres                                md5

root@tuxworld:/etc/postgresql/9.1/main# /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server                                                                    [ OK ] 
root@tuxworld:/etc/postgresql/9.1/main# 

Now check the login

root@tuxworld:~# su -l postgres
postgres@tuxworld:~$ 
postgres@tuxworld:~$ psql
psql (9.1.9)
Type "help" for help.

postgres=# q
postgres@tuxworld:~$