PostgreSQL - how to reset postgres password
Last updated: Jul 09, 2022
Colleagues hello to all.
In today's article, we will talk about how to reset the password for the main postgres account in the PostgreSQL DBMS. There are times when you accidentally forget or lose the password for the postgres account, but this does not matter, it can be reset very easily.
- Changing the database connection port.
- Editing the connection file pg_hba.conf.
- Set a new password for the postgres account.
- We return everything as it was.
1. Change the database connection port.
Just in case, for security purposes, before editing the pg_hba.conf connection file, we will change the connection port from 5432 to 5433. To do this, we need to find the PORT parameter in the postgresql.conf file and change it.
port = 5433
After changing the port, be sure to restart the database.
2. Editing the connection file pg_hba.conf.
The main file in the PostgreSQL DBMS that is responsible for accessing databases is called pg_hba.conf. You need to find it and add it to it:
local all postgres trust
With this line we indicate that the local user of the server can connect to the database without entering the password from the postgres account.
After the entered line, we need to restart the database or run a query in the console that will reread the pg_hba.conf file and apply all the changes.
sql> SELECT pg_reload_conf();
3. Setting a new password for the postgres account.
Now we can set a new password for the postgres account, to do this, connect to the psql console and run the command:
sql> ALTER USER postgres WITH PASSWORD 'Qwerty123';
4. We return everything as it was.
After we changed the password, do not forget to return everything back, namely the port that we changed before the robots.
Be sure to also remove the connection string that we inserted in the pg_hba.conf file, otherwise it will turn out that we have set a new password for the postgres account, but still access to the database will be available without entering a password.
Thank you all, I hope that my article helped you in some way.