How to change PostgreSQL user password?
PostgresqlChange PasswordPostgresql Problem Overview
How do I change the password for PostgreSQL user?
Postgresql Solutions
Solution 1 - Postgresql
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 - Postgresql
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
Solution 3 - Postgresql
You can and should have the users's password encrypted:
ALTER USER username WITH ENCRYPTED PASSWORD 'password';
Solution 4 - Postgresql
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 - Postgresql
To change password using Linux command line, use:
sudo -u <user_name> psql -c "ALTER USER <user_name> PASSWORD '<new_password>';"
Solution 6 - Postgresql
To Change Password
sudo -u postgres psql
then
\password postgres
now enter New Password and Confirm
then \q
to exit
Solution 7 - Postgresql
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.
Solution 8 - Postgresql
To request a new password for the postgres user (without showing it in the command):
sudo -u postgres psql -c "\password"
Solution 9 - Postgresql
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>;
Solution 10 - Postgresql
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 & granular steps.
Solution 11 - Postgresql
Setting up a password for the Postgres role
$ sudo -u postgres psql
you will get something like are as under:
postgres=#
change password to Postgres for user Postgres
# ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';
you will get something like are as under:
ALTER ROLE
postgres=#
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
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 Postgres so the changes take effect.
$ sudo service postgresql restart
Solution 12 - Postgresql
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
Solution 13 - Postgresql
For my case on Ubuntu 14.04 installed with postgres 10.3. I need to follow the following steps
-
su - postgres
to switch user topostgres
-
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 yourpg_hba.conf
(mine is at/etc/postgresql/10/main/pg_hba.conf
) by making sure you have the following linelocal 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.
Solution 14 - Postgresql
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';
Solution 15 - Postgresql
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.
Solution 16 - Postgresql
change password to postgres for user postgres
# ALTER USER postgres WITH ENCRYPTED PASSWORD '<NEW-PASSWORD>';
Solution 17 - Postgresql
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
Solution 18 - Postgresql
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 <<- 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 ;
"
Solution 19 - Postgresql
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.
Solution 20 - Postgresql
I was on Windows (Server 2019; PG 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:
- backup
pg_hba.conf
topg_hba.orig.conf
e.g. - create
pg_hba.conf
with only this:host all all 127.0.0.1/32 trust
- restart pg (service)
- execute
psql -U postgres -h 127.0.0.1
- enter (in pgctl console)
alter user postgres with password 'SomePass';
- restore
pg_hba.conf
from 1. above
Solution 21 - Postgresql
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
Solution 22 - Postgresql
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/root user):
- Edit the
pg_hba.conf
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 eg.postgresql@13-main
. Alternatively, my bash provided auto-complete after enteringpostgresql@
, 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
.
- The folder of the data directory can be found by inspecting the systemd command line, easily obtained with
- 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 thehost 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.