How to retrieve or change PostgreSQL password?


Forgetting the database superuser password is a serious problem that limits your actions.
Sometimes it happens that we don’t receive such a password
when we take over the duties of another person.
In such a situation, you need to change the password,
but to do it you need to make some changes to the base configuration.
First of all, you should make PostgresQL accept the connection from the computer where you are logged in.
If the connection is made from the same computer on which the SQL engine is installed, such connections are made by default policy.
However, if you are connecting from another computer, first connect to the computer on which the postgres DATA directory is located
and create a backup copy of the pg_hba.conf file.
now, edit the configuration file.
Depending on the addresses used, add an entry for IPv4 or IPv6.
As described in the line above the configuration, we should enter a new entry
host all postgres our IP address trust
which means we allow postgres user to connect from address to any database,
TRUST authentication method enables connection without a password.
Of course, you can add information about specific databases available for specific users,
but in the case of superusers such restrictions are not necessary.
In the default entry for a local address we must add a hash at the beginning of the line
(this does not apply when we connect to postgres from outside).
Now, the configuration you have entered should be saved
and you should try to connect again using PGadmin.
Select the postgres database and open a new query window.
Perform the query
ALTER USER postgres with password
After completing the query, stop the postgres service and restore the configuration from before the modification.
Then start the postgres service again and check the new password.
Thanks for listening and check our channel for more IT clips.

Was this article helpful?

Related Articles