How to change PostgreSQL user password?

Question

How do I change the password for PostgreSQL user?

Solution 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';

Solution 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

Solution 3

You can and should have the users's password encrypted:

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

Solution 4

I believe the best way to change the password is simply to use:

\password

in the Postgres console.

Per ALTER USER documentation:

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.

Note: ALTER USER is an alias for ALTER ROLE

Solution 5

To change password using Linux command line, use:

sudo -u <user_name> psql -c "ALTER USER <user_name> PASSWORD '<new_password>';"

View more solutions

Comments

[Saad]

whats the default password for postgres? changed it accidently; possible to reset?

[greg]

This let the clear password in the user's postgresql command history.

[David LeBauer]

(on psql 9.2) if I type in \p, it gives me the password; if I type in \password postgres it gives the password and then warnings \p extra argument "assword" ignored; \p extra argument "postgres" ignored

[RickyA]

@greg: so delete it: rm ~/.psql_history

[equivalent8]

off topic but if anyone looking for "how to change name of user" than do ALTER USER myuser RENAME TO newname; ...for some reason google was pointing me here when I was googling that :)

[Lmwangi]

If you have made the change using \password and you are on the same host as the postgres server, the try specifying that you want your connection to go over an inet instead of unix socket. i.e. use the -h parameter: psql -h 127.0.0.1. Doing this saved me from editing the pg_hba configuration file

[Rescribet]

@RickyA Don't forget to clear the db logs too, those might contain plaintext passwords as well.

[G M]

It doesn't work : user@user-NC10:~$ psql -U postgres psql: FATAL: Peer authentication failed for user "postgres"

[Murtaza Kanchwala]

Ok, Do another method sudo su - postgres psql You will enter the terminal and then change the password there, This is an alternate way for this. Let me know if this works for you or you need a full explanation

[G M]

mm i have tried but I have another error:/usr/bin/psql: line 19: use: command not found /usr/bin/psql: line 21: use: command not found /usr/bin/psql: line 23: use: command not found /usr/bin/psql: line 24: use: command not found /usr/bin/psql: psql: line 26: syntax error near unexpected token $version,' /usr/bin/psql: psql: line 26: my ($version, $cluster, $db, $port, $host);' thanks for your help!

[Boyan]

Why are you using both " and ' quotes? I mean, there's a difference, and in a DML query you have to use ' when dealing with strings, but is there a special reason to use both of them here?

[Boyan]

Using single quote ' for the role name doesn't work, but I am still curious why?

[P Daddy]

The user is an object, not a string. Compare with ALTER TABLE "table_name" or even SELECT * FROM "table_name". You couldn't use single quotes in these contexts with tables, and it's the same with users/roles.

[tforgione]

@RickyA deleting the file doesn't mean that the password would be deleted on the system tough, it could be found through forensics with stuff like photorec, so you would need to shred it, etc...

[dualed]

@DragonRock won't help you on SSDs either or even probably newer versions of ext in general.

[tforgione]

@dualed yeah, you're right. Getting totally rid of a file is kind of complicated. It's way better to never have it saved in the first place !

[samsri]

you also need to restart your postgres service for changes to take effect sudo systemctl restart postgresql.service

[Andi-lo]

Just curious but why is an (now unused) password a problem in the log files? It got changed and thus is useless or am I wrong?

[JMStudios.jrichardson]

where should this pg_hba.conf file go?

[Zachary Scott]

If only for my reference, local all postgres md5 allows local logins for postgres with interactive password entry but better yet is existing host all all 127.0.0.1/32 md5 allows sudo psql --host 127.0.0.1 --username postgres --password to log in interactively for postgres login without pg_hba.conf changes.

[sekmo]

what does the first line do?

[John29]

This keyword doesn't matter for the current version. From postgresql.org/docs/current/static/sql-createrole.html The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility.

[Alphaaa]

@greg @RickyA instead of deleting the whole .psql_history it is sufficient to issue the command with some whitespace before ALTER USER, and it will not be stored in .psql_history. This same handy trick is available in standard shell as well.

[Natim]

please use ENCRYPTED PASSWORD

[Gregory Arenius]

@Alphaaa I think that only works if you have \set HISTCONTROL ignorespace set in the .psqlrc file.

[Alphaaa]

Yes @GregoryArenius, your .psqlrc file should contain \set HISTCONTROL ignorespace or \set HISTCONTROL ignoreboth for my suggestion to work.

[Martin]

This can also be used to change passwords for other users: \password username

[Pedro Cordeiro]

Just remember that this will probably save the db's user password in your command history.

[Archit Kapoor]

I don't think you really need to restart the postgresql service after changing the password. I have been able to reset the password with restarting it. \password is the quickest way. Or else you need the ALTER USER command.

[Rohmer]

@Natim From the docs: The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. But you can send an encrypted password: If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption. postgresql.org/docs/current/static/sql-createrole.html

[András Aszódi]

If you use the \password command in psql then the history will not contain the password. (At least in Version 9.2.24)

[otocan]

This is much better than leaving the password in SQL command history.

[phep]

Beware! @John29 comment is only true from Postgresql 10 and above. For all other versions the ENCRYPTED flag matters.

[TheRealChx101]

@ZacharyScott What are you saying?

[TheRealChx101]

can you use environment variables here? I want to automate this in a deploy script

[Punnerud]

To change the password on the postgres user in Linux: sudo passwd postgres

[levibostian]

If you want to change the password for someone other then postgres user, the \password command accepts the role name as the 1st argument: \password <role_name> postgresql.org/docs/9.0/sql-alterrole.html

[FlyingV]

### Use DOUBLE QUOTE for the user if it contains a period.

[Brian Minton]

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

[Timothy Macharia]

Thanks @BrianMinton for the heads up.

[Peter Krauss]

the unencrypted password is rememberd by psql history or shell... How to use hashed (ex. SHA1) password?

[Peter Krauss]

the unencrypted password is rememberd by psql history or shell... Same for SQL command ENCRYPTED PASSWORD (!). How to use encrypted/hashed (ex. SHA256) password?

[Soyuzbek Orozbek Uulu]

it worked for me using trust instead of peer

[Coliban]

If I do that, it prompts for a password

[AndreKR]

This way (without quotes around the user name) will only work for certain user names.

[Coliban]

It does not find the \password (on Linux)

[Andrew]

@Coliban, you need to be on the psql prompt when you run it.

[Coliban]

@Andy: yes, but psql didnt connected to DB for whatever reason. I´ve installed a new version with new passwords, now it is ok. Thank you

[Dimitar Dimitrov]

This is the correct answer. Thanks a lot, I see I've upvoted it long time ago, so glad I found it again!

[xtian]

@Punnerud, "To change the password on the postgres user in Linux: sudo passwd postgres" Holy Smokes! I was so confoosed. Both Postgres and Linux have the same user. I'm new to postgres and using it with a new Django site. I needed a backup of the DB and picking 5 random tutorials, this was not explained well. LSS this solved the password problem so I could make a backup.

[Max Raskolnikov]

-bash: -u: command not found

[Sep GH]

"To do this we need to edit the pg_hba.conf file." and then doesnt explain what to change there

[CHAVDA MEET]

sorry for the trouble...ok lemme update

[CHAVDA MEET]

Now is that good?

[Brian Burns]

Start the command with a space in the terminal and it should keep it out of your command history.

[Mido]

The double quotes got me as well, thanks for sharing.

[Developer-Person]

this worked for me, i couldn't run the ALTER USER command for some reason.