DEV Community

Theoklitos Bampouris
Theoklitos Bampouris

Posted on

How to reset your forgotten password in PostgreSQL

#postgres

The moment you realize the problem

It's a good practice even for your local DB server to provide a password for each user. PostgreSQL's default user (who is also an admin) is postgres.

A standard method to get access to our DB is using psql command via terminal (I'm running Manjaro Linux). So, we type:

psql -U postgres
Enter fullscreen mode Exit fullscreen mode

We enter our password and we get the message

Password Authentication Failed

Don't Panic!

First of all, we must find the "PostgreSQL Client Authentication Configuration File", which has the name pg_hba.conf. In Manjaro, it lives in path /var/lib/postgres/data/pg_hba.conf. Be careful that it is required to have root permissions.

sudo nano /var/lib/postgres/data/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

PostgreSQL Client Authentication Configuration File

We change the md5 authentication in "local" connection to trust

local   all             all                                     trust
Enter fullscreen mode Exit fullscreen mode

and we restart our server

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Now PostgreSQL doesn't ask a password and we are allowed to connent with user postgres

Trusted Connection

Next step is to reset the password

Reset Password

We exit psql, we turn back pg_hba.conf to it's previous state (md5 authentication) and we restart the server. We are able to connent using our new password for user postgres.

Successful Authentication

Originally published at https://www.codingnotebook.eu/postgresql-reset-password/

Top comments (0)