PostgreSQL How to change PostgreSQL user password

Question

How do I change the password for PostgreSQL user

User · Answer

use this    password   enter the new password you want for that user and then confirm it  If you don t remember the password and you want to change it  you can log in as postgres and then use this   ALTER USER  the username  WITH PASSWORD  the new password

User · Answer

check pg hba conf In case the authentication method is  peer   the client s operating system user name password must match the database user name and password  In that case  set the password for Linux user  postgres  and the DB user  postgres  to be the same  see the documentation for details  https   www postgresql org docs 9 1 auth-pg-hba-conf html

User · Answer

For my case on Ubuntu 14 04 installed with postgres 10 3  I need to follow the following steps   su - postgres to switch user to postgres psql to enter postgres shell  password then enter your password  q to quit the shell session Then you switch back to root by executing exit and configure your pg hba conf  mine is at  etc postgresql 10 main pg hba conf  by making sure you have the following line  local all   postgres    md5 Restart your postgres service by service postgresql restart Now switch to postgres user and enter postgres shell again  It will prompt you with password

User · Answer

To request a new password for the postgres user  without showing it in the command    sudo -u postgres psql -c   password

User · Answer

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

User · Answer

In general  just use pg admin UI for doing db related activity   If instead you are focusin more in automating database setup for your local development  or CI etc     For example  you can use a simple combo like this    a  Create a dummy super user via jenkins with a command similar to this   docker exec -t postgres11-instance1 createuser --username postgres --superuser experiment001   this will create a super user called experiment001 in you postgres db    b  Give this user some password by running a NON-Interactive SQL command   docker exec -t postgres11-instance1 psql -U experiment001 -d postgres -c  ALTER USER experiment001 WITH PASSWORD  experiment001      Postgres is probably the best database out there for command line  non-interactive  tooling  Creating users  running SQL  making backup of database etc    In general it is all quite basic with postgres and it is overall quite trivial to integrate this into your development setup scripts or into automated CI configuration

User · Answer

Similar to other answers in syntax but it should be known that you can also pass a md5 of the password so you are not transmitting a plain text password   Here are a few scenarios of unintended consequences of altering a users password in plain text    If you do not have SSL and are modifying remotely you are transmitting the plain text password across the network  If you have your logging configuration set to log DDL Statements log statement   ddl or higher  then your plain text password will show up in your error logs    If you are not protecting these logs its a problem  If you collect these logs ETL them and display them where others have access they could end up seeing this password  etc  If you allow a user to manage their password  they are unknowingly revealing a password to an admin or low level employee tasked with reviewing logs     With that said here is how we can alter a user s password by building an md5 of the password    Postgres when hash a password as md5  salts the password with the user name then prepends the text  md5  to the resulting hash     ex   md5  md5 password   username   In bash           echo -n  passwordStringUserName    md5sum   awk   print  md5  1       md5d6a35858d61d85e4a82ab1fb044aba9d    In PowerShell         PSCredential   Credential   Get-Credential       StringBuilder   New-Object System Text StringBuilder       null    StringBuilder Append  md5          System Security Cryptography HashAlgorithm   Create  md5   ComputeHash  System Text Encoding   ASCII GetBytes   ConvertFrom-SecureStringToPlainText -SecureString  Credential Password     Credential UserName      ForEach-Object            null    StringBuilder Append    ToString  x2                StringBuilder ToString            OUTPUT     md5d6a35858d61d85e4a82ab1fb044aba9d    So finally our ALTER USER command will look like       ALTER USER UserName WITH PASSWORD  md5d6a35858d61d85e4a82ab1fb044aba9d      Relevant Links  Note I will only link to the latest versions of the docs for older it changes some but md5 is still support a ways back   create role    The password is always stored encrypted in the system catalogs  The ENCRYPTED keyword has no effect  but is accepted for backwards compatibility  The method of encryption is determined by the configuration parameter password encryption  If the presented password string is already in MD5-encrypted or SCRAM-encrypted format  then it is stored as-is regardless of password encryption  since the system cannot decrypt the specified encrypted password string  to encrypt it in a different format   This allows reloading of encrypted passwords during dump restore   configuration setting for password encryption postgres password authentication doc building postgres password md5

User · Answer

You can and should have the users s password encrypted   ALTER USER username WITH ENCRYPTED PASSWORD  password

User · Answer

To login 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

User · Answer

TLDR   On many systems  a user s account often contains a period  or some sort of punction  user  john smith  horise johnson    IN these cases a modification will have to be made to the accepted answer above   The change requires the username to be double-quoted   Example   ALTER USER  username lastname  WITH PASSWORD  password      Rational   Postgres is quite picky on when to use a  double quote  and when to use a  single quote    Typically when providing a string you would use a single quote

User · 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

User · Answer

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

User · Answer

If you are on windows  Open pg hba conf file and change from md5 to peer Open cmd  type psql postgres postgres Then type  password to be prompted for a new password  Refer to this medium post for further information  amp  granular steps

User · Answer

and the fully automated way with bash and expect   in this example we provision a new postgres admin with the newly provisioned postgres pw both on OS and postgres run-time level         the  postgres usr pw and the other bash vars MUST be defined      for reference the manual way of doing things automated with expect bellow    echo  copy-paste   postgres usr pw     sudo -u postgres psql -c   password      the OS password could   should be different   sudo -u root echo  postgres  postgres usr pw    sudo chpasswd    expect  lt  lt - EOF EXPECT      set timeout -1      spawn sudo -u postgres psql -c     password       expect  Enter new password         send --   postgres usr pw r       expect  Enter it again         send --   postgres usr pw r       expect eof EOF EXPECT    cd  tmp      at this point the postgres uses the new password   sudo -u postgres PGPASSWORD  postgres usr pw psql       --port  postgres db port --host  postgres db host -c     DO     DECLARE r record       BEGIN         IF NOT EXISTS              SELECT            FROM   pg catalog pg roles            WHERE  rolname      postgres db useradmin    THEN               CREATE ROLE   postgres db useradmin  WITH SUPERUSER CREATEROLE               CREATEDB REPLICATION BYPASSRLS   PASSWORD    postgres db useradmin pw   LOGIN           END IF       END        ALTER ROLE   postgres db useradmin  WITH SUPERUSER CREATEROLE   CREATEDB REPLICATION BYPASSRLS  PASSWORD     postgres db useradmin pw   LOGIN

User · Answer

To change password using Linux command line  use   sudo -u  lt user name gt  psql -c  ALTER USER  lt user name gt  PASSWORD   lt new password gt

User · Answer

This was the first result on google  when I was looking how to rename a user  so   ALTER USER  lt username gt  WITH PASSWORD   lt new password gt     -- change password ALTER USER  lt old username gt  RENAME TO  lt new username gt      -- rename user   A couple of other commands helpful for user management   CREATE USER  lt username gt  PASSWORD   lt password gt   IN GROUP  lt group gt   DROP USER  lt username gt     Move user to another group  ALTER GROUP  lt old group gt  DROP USER  lt username gt   ALTER GROUP  lt new group gt  ADD USER  lt username gt

User · Answer

To Change Password    sudo -u postgres psql   then    password postgres   now enter New Password and Confirm   then  q to exit

User · Answer

Configuration that I ve got on my server was customized a lot and I managed to change password only after I set trust authentication in the pg hba conf file   local   all   all   trust   Don t forget to change this back to password or md5

[postgresql] PostgreSQL: How to change PostgreSQL user password?

Examples related to postgresql

Examples related to change-password