Everything flows and changes, and sometimes is forgotten over time. What to do if you forget the password of the postgres user?

Step 1: Locate the “pg_hba.config” File

Default path:

/etc/postgresql/{postgresql version}/main/pg_hba.conf.

If the server comes in a separate package with its own structure, then the file is located together with the data directory, for example:

/opt/postgresql/11/data/pg_hba.conf

Example:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

host    all             all             0.0.0.0/0               md5
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Resetting the local connections to “trust” will allow you to log into Postgres without providing the superuser password.

Edited example

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

host    all             all             0.0.0.0/0               trust
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Then restart postgresql server

# systemctl restart postgresql

Step 5: Reset the Password

Now execute the “ALTER USER” or “ALTER ROLE” command with the “PASSWORD” attribute to reset the password for the “postgres” user:

ALTER USER postgres WITH PASSWORD 'my_modified_password';

Then return the original file instead of the edited one. And it remains to restart the database server once more