How to Reset PostgreSQL User Password: Complete Guide
When working with a PostgreSQL database, there are situations when you need to reset the password for the primary user (usually the user named postgres). This may be necessary in case of a lost password, when the database administrator changes, or to improve the security of the system. PostgreSQL provides several ways to reset and change the password for the primary user, each of which is suitable for different scenarios and levels of access to the server.
In this article we will look at the option of resetting the password for the postgres account through the access configuration file called pg_hba.conf.
The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction.
Contents of the article:
- Temporarily changing the connection port to the postgresql database.
- Configuring the pg_hba.conf file.
- Setting a new password.
- Reverse change of connection port to the postgresql database.
1. Temporarily changing the connection port to the postgresql database.
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, you must restart the database.
2. Configuring the pg_hba.conf file.
The main file in the PostgreSQL DBMS that is responsible for access to 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 server user can connect to the database without entering the password for the postgres account.
After entering the line, we need to restart the database or execute 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.
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. Reverse change of connection port to the postgresql database.
After we have changed the password, do not forget to return everything back, namely the port that we changed before the robots.
Also, be sure to delete the connection string that we inserted into the pg_hba.conf file, otherwise it will turn out that we set a new password for the postgres account, but still access to the database will be available without entering a password.
As a result, today we looked at a very quick and easy way to reset the password of the main postgres user in the PostgreSQL database through the pg_hba.conf configuration file.
