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