by Hafiz Junaid
There are various methods available online for updating or changing a user password in PostgreSQL via the command prompt. This guide demonstrates a straightforward method to update the user password, particularly useful when restoring a client database backup to a local system.
Steps to Reset a Database User Password in PostgreSQL
1. Connect to PostgreSQL
- Open your terminal or command prompt. This can be done manually or using the shortcut key (Ctrl + Alt + T).
- Connect to PostgreSQL using the terminal by running the following command (this example is for Linux):
sudo su postgres //example of linux
Enter your PostgreSQL password when prompted. This will log you in as the PostgreSQL superuser.
Connect to the psql shell by running:
psql //example of linux
Once you execute this command, you will successfully connect to PostgreSQL, and you should see the psql prompt.
2. Run the Query
Switch to the specific database where you need to reset the user password. For example, if your database name is Bazeee, run:
\c Elephant_16C
This command switches you to the Elephant_16C database.
To locate the user whose password needs to be reset, execute the following query to list all users:
select id, login from res_users;
This will list all the users.
Note the login email associated with the user. For example, if the login is admin@numla.com, reset the password with:
update res_users set password = 'admin' where login = 'admin@numla.com'
You can also reset the password by user ID if preferred.
3. View the Results
Verify the password change by rerunning the SELECT query:
select * From res_users_parameter WHERE id=2;
The result should show the user reset updates.
Conclusion
By following these steps, you can easily reset the password for any PostgreSQL user using the terminal. This process helps maintain the security and accessibility of your PostgreSQL databases.
Need more development tips?
Stay tuned to our blog.