How to change PostgreSQL user password?

Question

How do I change the password for PostgreSQL user?

Answer 1

To log in without a password:

sudo -u user_name psql db_name 

To reset the password if you have forgotten:

ALTER USER user_name WITH PASSWORD 'new_password'; 

Answer 2

To change the the postgres user's password follow this steps

1. Login into the psql:

$ sudo -u postgres psql 

2. Then in the psql console change the password and quit:

postgres=# \password postgres Enter new password: <new-password> postgres=# \q 

or using a query

ALTER USER postgres PASSWORD '<new-password>'; 

or in one line

sudo -u postgres psql -c "ALTER USER postgres PASSWORD '<new-password>';" 

Note:

If that does not work, reconfigure authentication by editing /etc/postgresql/9.1/main/pg_hba.conf (path will differ) and change:

local   all         all                  peer # change this to md5  ## to  local   all         all                  md5 # like this 

Then restart the server:

$ sudo service postgresql restart