David Taing
teeang's blog

Follow

teeang's blog

Follow

Resetting PostgresSQL Password on Ubuntu 22.0.4 LTS

David Taing's photo
David Taing
·Nov 21, 2022·

2 min read

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

Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails

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)
 
Share this