How to change PostgreSQL user password?
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';
To change the the postgres user's password follow this steps
- Login into the psql:
$ sudo -u postgres psql
- 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