Resetting PostgresSQL Password on Ubuntu 22.0.4 LTS
You want to navigate to the pg_hba.conf file located at /etc/postgresql/<postgres_version>/main/pg_hba.conf. This file stores the configurations responsible for client authentication.
Opening the file
# opens the file in gedit
$ sudo gedit /etc/postgresql/<postgres_version>/main/pg_hba.conf
# or if you prefer an in terminal editor
$ sudo editor /etc/postgresql/<postgres_version>/main/pg_hba.conf
Editing the file
local all postgres peer
Scroll to the bottom of pg_hba.conf and change peer to trust for postgres user.
local all postgres trust
So it should look like this. Trust will allow you to use the postgres shell without a password.
After you save the file
# restart the postgres service
sudo service postgresql restart
Changing the password
# log into the postgres shell
$ psql -U postgres
# terminal will show this when you have successfully logged in
postgres=#
# run SQL command to update password
$ ALTER USER postgres with password 'YOUR_PASSWORD';
# exit after you are done
$ Exit
Checking if the password has been changed via CLI
# log into the postgres shell
$ psql -U postgres
# enter password when prompted
# terminal will show this when you have successfully logged in
postgres=#
Changing the pg_hba.conf permissions again
# opens the file in gedit or you can use 'sudo editor <path>'
$ sudo gedit /etc/postgresql/<postgres_version>/main/pg_hba.conf
Editing the file
local all postgres trust
Scroll to the bottom of pg_hba.conf and change trust to md5 for the postgres user.
local all postgres md5
After you save the file
# restart the postgres service
sudo service postgresql restart
Sources
Comments
trust - anyone who can connect to the server is authorized to access the database
peer - use client's operating system user name as database user name to access it.
md5 - password-base authentication
History
- 21st November 2022 - Forgot my PostgreSQL password and couldn’t log in. (Ubuntu 22.0.4 LTS)