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
Now connect to Postgres using SQL Shell or pgAdmin:
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
Similar Posts:
- how to install PostgreSQL 12 on Ubuntu / Debian 10,11
- How to change Porstgresql default password
- How to create Dblink between Oracle & PostgreSQL
- postresql: move data directory to another location
- how to restore postgreSQL database with template using pg_dump