How can I change a PostgreSQL user password?

Question

How do I change the password for a PostgreSQL user?

Ammadis

Confusingly, the (literal) name of the default user is "postgres". What is the default username and password for PostgreSQL?. – Bytom

And "Is "postgres" a default and special user of PostgreSQL?" – Bytom

It's worth noting that "postgres" is the default registered user. The default user used when running psql is the current shell user (echo $USER). To login to psql as "postgres" when the current shell user is not "postgres", we need to run psql as "postgres" with sudo -u postgres psql. Also, note that we can't switch the current shell user to "postgres" since that account is locked by default. – Personification

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

Mirepoix

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

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

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 :) – Henni

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

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? – Driver

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

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. – Mors

@Ormolu 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... – Butz

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

@Kino 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 ! – Butz

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? – Othelia

what does the first line do? – Hunks

@Czarra @Ormolu 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. – Broncobuster

please use ENCRYPTED PASSWORD – Viva

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

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

@Viva 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 – Russon

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

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

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

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

-bash: -u: command not found – Heddie

Answer 2

To change the PostgreSQL user's password, follow these steps:

1. log in into the psql console:

   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 (the 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

Assassinate

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

(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 – Avatar

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 – Silden

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. – Mcclenon

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

@ZacharyScott What are you saying? – Scoutmaster

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

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 – Iridotomy

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

@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. – Pit

I connected using the account I want to change the password for, and then ran \password without anything after. I could change the password successfully and securely. Good answer. – Stephenson

Use only ' (single quote) – Ambrosial

Answer 3

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

Carpet

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

It does not find the \password (on Linux) – Tuchman

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

@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 – Tuchman

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

this worked for me, ALTER USER does not.. thanks! – Cultural

Answer 4

You can and should have the users' password encrypted:

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

Laywoman

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. – Fellers

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

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

@PeterKrauss you might be interested in this Q&A – Zebrawood

Answer 5

To the change password:

 sudo -u postgres psql

Then

\password postgres

Now enter the new password and confirm.

Then \q to exit.

Snooty

If I do that, it prompts for a password – Tuchman

Answer 6

To change the password using the Linux command line, use:

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

Dame

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

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

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

In bash, if HISTCONTROL is set to ignorespace or ignoreboth, then a command line starting with a space is not added to the history. – Rahm

Answer 7

Go to your PostgreSQL configuration and edit file 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 the '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 the PostgreSQL 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.

Durno

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

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 – Durno

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! – Selfrighteous

Answer 8

Setting up a password for the postgres role

sudo -u postgres psql

You will get a prompt like the following:

postgres=#

Change password to PostgreSQL for user postgres

ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';

You will get something as follows:

ALTER ROLE

To do this we need to edit the pg_hba.conf file.

(Feel free to replace nano with an editor of your choice.)

sudo nano /etc/postgresql/9.5/main/pg_hba.conf

Update in the pg_hba.conf file

Look for an uncommented line (a line that doesn’t start with #) that has the contents shown below. The spacing will be slightly different, but the words should be the same.

    local   postgres   postgres   peer

to

    local   postgres   postgres   md5

Now we need to restart PostgreSQL, so the changes take effect

sudo service postgresql restart

Apodal

Answer 9

To request a new password for the postgres user (without showing it in the command):

sudo -u postgres psql -c "\password"

Downy

Answer 10

If you are on Windows.

Open pg_hba.conf file and change from md5 to peer.

Open cmd and type psql postgres postgres.

Then type \password to be prompted for a new password.

Refer to this Medium post for further information & granular steps.

Parshall

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 – Nereus

Thanks @BrianMinton for the heads up. – Parshall

it worked for me using trust instead of peer – Drier

Answer 11

This was the first result on google, when I was looking how to rename a user, so:

ALTER USER <username> WITH PASSWORD '<new_password>';  -- change password
ALTER USER <old_username> RENAME TO <new_username>;    -- rename user

A couple of other commands helpful for user management:

CREATE USER <username> PASSWORD '<password>' IN GROUP <group>;
DROP USER <username>;

Move user to another group

ALTER GROUP <old_group> DROP USER <username>;
ALTER GROUP <new_group> ADD USER <username>;

Autopilot

Answer 12

The configuration that I've got on my server was customized a lot, and I managed to change the 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.

Bracket

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

where should this pg_hba.conf file go? – Timorous

Answer 13

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

Haunted

Answer 14

For my case on Ubuntu 14.04 (Trusty Tahr), installed with PostgreSQL 10.3: I need to follow the following steps

• su - postgres to switch the user to postgres

• psql to enter the PostgreSQL shell

• \password and 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 PostgreSQL service by service postgresql restart

• Now switch to the postgres user and enter the PostgreSQL shell again. It will prompt you for a password.

Gamut

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. – Chadbourne

Answer 15

For Windows using the Command Prompt:

1. Change the directory to the /bin of your PostgreSQL installation folder. Commonly we could use this command:

   C:\Program Files\PostgreSQL\<version>\bin
   

2. Connect to PostgreSQL server as a superuser:

   psql -U postgres
   

3. Run the following command to change the superuser password:

   ALTER USER postgres WITH PASSWORD 'new_password';
   

Caterwaul

Answer 16

TLDR:

On many systems, a user's account often contains a period, or some sort of punctuation (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';

Rationale:

PostgreSQL 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.

Simla

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

Answer 17

You can easily change the password by executing the following command line code:

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

However, it should be noted that your unencrypted password will still be visible in plaintext in the command line history.

It would be best if you also used the ENCRYPTED keyword explicitly if using PostgreSQL version 10 or less.

Amritsar

Answer 18

Changing password of a PostgreSQL User is fairly simple task. After starting Postgres, use the following command.

ALTER ROLE username   
WITH PASSWORD 'password';

Instead of username write the user you want to alter and in '' where password is written, write the new password, you want for the user.

For further understanding, visit following article: How To Change The Password of a PostgreSQL User

Theretofore

Answer 19

This is similar to other answers in syntax, but it should be known that you can also pass the MD5 hash value 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.

1. If you do not have SSL and are modifying remotely you are transmitting the plain text password across the network.
2. 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.
3. If you are not protecting these logs, it’s a problem.
4. If you collect these logs/ETL them and display them where others have access, they could end up seeing this password, etc.
5. If you allow a user to manage their password, they are unknowingly revealing a password to an administrator 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 hash value of the password.

• PostgreSQL, when hashing a password as MD5, salts the password with the user name and then prepends the text "md5" to the resulting hash.

• Example: "md5"+md5(password + username)

• In Bash:

  echo -n "passwordStringUserName" | md5sum | awk '{print "md5"$1}'
  

  Output:

  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 documentation. For older, it changes some, but MD5 is still supported 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

• PostgreSQL password authentication documentation

• Building PostgreSQL password MD5 hash value

Nathalie

Answer 20

And the fully automated way with Bash and expect (in this example we provision a new PostgreSQL administrator with the newly provisioned PostgreSQL password both on OS and PostgreSQL run-time level):

  # The $postgres_usr_pw and the other Bash variables 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 <<- 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' executable 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 ;
 "

Adlei

Answer 21

I was on Windows (Windows Server 2019; PostgreSQL 10), so local type connections (pg_hba.conf: local all all peer) are not supported.

The following should work on Windows and Unix systems alike:

1. backup pg_hba.conf to pg_hba.orig.conf e.g.
2. create pg_hba.conf with only this: host all all 127.0.0.1/32 trust
3. restart pg (service)
4. execute psql -U postgres -h 127.0.0.1
5. enter (in pgctl console) alter user postgres with password 'SomePass';
6. restore pg_hba.conf from 1. above

Electroballistics

Answer 22

Change password to "postgres" for user "postgres":

# ALTER USER postgres WITH ENCRYPTED PASSWORD '<NEW-PASSWORD>';

Audiphone

What is "#"? The prompt from executable "psql"? – Bytom

Answer 23

To interactively change/assign the password of an existing user in the psql shell

\password <username>
# you will be prompted for the password that you want to define

If you leave the username blank, it will prompt you to change the password of the 'postgres' user by default.

Tristich

Answer 24

One hacky way of changing your pgsql password is executing this command in the terminal as a superuser

ALTER USER username WITH PASSWORD 'your password'

You may have to restart your server for this to take effect.

I hope this helps!

Cuneiform

Isn't this basically the same thing that the 11 year old answer with 2300+ upvotes says (not to mention the other half-dozen or so near duplicates)? Regurgitating already existing content adds no value. – Province

Answer 25

In general, just use the pgAdmin UI for doing database-related activity.

If instead you are focusing more in automating database setup for your local development, CI, etc.

For example, you can use a simple combination 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 PostgreSQL database.

(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' "

PostgreSQL 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 PostgreSQL, and it is overall quite trivial to integrate this into your development setup scripts or into automated CI configuration.

Donatus

Answer 26

Check file 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: 19.1. The pg_hba.conf File

Osmometer

Yes, this dependency is seldom mentioned. Is it the default? – Bytom

Answer 27

Most of the answers were mostly correct, but you need to look out for minor things. The problem I had was that I didn't ever set the password of "postgres", so I couldn't log into an SQL command line that allowed me to change passwords. These are the steps that I used successfully (note that most or all commands need sudo or root user):

• Edit the pg_hba.conf file in the data directory of the DB cluster you're trying to connect to.

  • The folder of the data directory can be found by inspecting the systemd command line, easily obtained with systemctl status postgresql@VERSION-DB_CLUSTER. Replace VERSION with your psql version and DB_CLUSTER with the name of your database cluster. This may be main if it was automatically created, so, e.g., postgresql@13-main. Alternatively, my Bash shell provided auto-complete after entering postgresql@, so you could try that or look for the PostgreSQL services in the list of all services (systemctl -a). Once you have the status output, look for the second command line after CGroup, which should be rather long, and start with /usr/lib/postgresql/13/bin/postgres or similar (depending on version, distro, and installation method). You are looking for the directory after -D, for example /var/lib/postgresql/13/main.

• Add the following line: host all all 127.0.0.1/32 trust. This allows for all users on all databases to connect to the database via IPv4 on the local machine unconditionally, without asking for a password.

  This is a temporary fix and don't forget to remove this line again later on. Just to be sure, I commented out the host all all 127.0.0.1/32 md5 (md5 may be replaced by scram-sha-256), which is valid for the same login data, just requiring a password.

• Restart the database service: systemctl restart postgresql@... Again, use the exact service you found earlier.

• Check that the service started properly with systemctl status postgresql@....

• Connect with psql, and very importantly, force psql to not ask for a password. In my experience, it will ask you for a password even though the server doesn't care, and will still reject your login if your password was wrong. This can be accomplished with the -w flag.

  The full command line looks something like this: sudo -u postgres psql -w -h 127.0.0.1 -p 5432. Here, postgres is your user and you may have changed that. 5432 is the port of the cluster-specific server and may be higher if you are running more than one cluster (I have 5434 for example).

• Change the password with the \password special command.

• Remember to remove the password ignore workaround and restart the server to apply the configuration.

Geometrid

Answer 28

Using pgAdmin 4:

Menu Object → Change password...

Mauldin

Answer 29

For those intend to use it in a CI/CD pipeline, an alternative is to use Clint Bugs' one line solution, and assign the password to a global variable:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD '$PGPASSWORD';"

Considering, of course, reading the documentation of the CI/CD tool (I used Semaphore), for the definition of the value of this global variable.

Glyndaglynias

Answer 30

It worked:

1. Put only one entry in pg_hba.conf. host all all ::1/128 trust

2. Make sure that you run cmd from administrator if windows pg_ctl reload -D "C:\Program Files\PostgreSQL\15\Data"

3. start psql and it won't ask for the password and connect.

4. Now reset the password. postgres=# alter user postgres with password 'postgres'; ALTER ROLE

5. Now go to Pgadmin and provide the password. connected...Bingo!

Alcyone