PostgreSQL: How to change PostgreSQL user password?
PostgreSQL: How to change PostgreSQL user password?
Accepted Answer
For password less login:
sudo -u user_name psql db_name
To reset the password if you have forgotten:
ALTER USER user_name WITH PASSWORD 'new_password';
Popular Answer
Then type:
$ sudo -u postgres psql
Then:
\password postgres
Then to quit psql
:
\q
If that does not work, reconfigure authentication.
Edit /etc/postgresql/9.1/main/pg_hba.conf
(path will differ) and change:
local all all peer
to:
local all all md5
Then restart the server:
$ sudo service postgresql restart
Read more… Read less…
You can and should have the users's password encrypted:
ALTER USER username WITH ENCRYPTED PASSWORD 'password';
I believe the best way to change the password is simply to use:
\password
in the Postgres console.
Source:
Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client's command history or the server log. psql contains a command \password that can be used to change a role's password without exposing the cleartext password.
from https://www.postgresql.org/docs/9.0/static/sql-alterrole.html.
To change password using Linux command line, use:
sudo -u <user_name> psql -c "ALTER USER <user_name> PASSWORD '<new_password>';"
To Change Password
sudo -u postgres psql
then
\password postgres
now enter New Password and Confirm
then \q
to exit
Go to your Postgresql Config and Edit pg_hba.conf
sudo vim /etc/postgresql/9.3/main/pg_hba.conf
Then Change this Line :
Database administrative login by Unix domain socket
local all postgres md5
to :
Database administrative login by Unix domain socket
local all postgres peer
then Restart the PostgreSQL service via SUDO command then
psql -U postgres
You will be now entered and will See the Postgresql terminal
then enter
\password
and enter the NEW Password for Postgres default user, After Successfully changing the Password again go to the pg_hba.conf and revert the change to "md5"
now you will be logged in as
psql -U postgres
with your new Password.
Let me know if you all find any issue in it.