As a database administrator, you will often encounter such instances where you need to reset the passwords for various users in the database. As a result, the password reset is a prevalent task in databases and other applications.

In this tutorial, we will explore how to reset the passwords for users with the PostgreSQL server. It is good to remember that the database users in this case refers to the users with access to the database engine. Therefore, this does not include the user data for any given application.

Method 1: Using the PSQL Utility

The most common way of resetting a user password in PostgreSQL is by logging in as the superuser (Postgres) using the PSQL utility.

Launch a new terminal session and run the following command to access the PostgreSQL database as the superuser:

The given command prompts you for the superuser password as defined during server setup.

Once logged in to the PostgreSQL command-line interface, you can use the ALTER USER command to reset the password of a specific username.

The command syntax is as follows:

For example, suppose we wish to reset the password for the username called “linuxhint”. We can run the query as follows:

You should enclose the password into the single quotes, mainly if the password contains special characters.

Once the command is executed successfully, you can log out of the PSQL interface using the following command:

Method 2: Using the PgAdmin Graphical Interface

You can also reset the password of a given database user using the pgAdmin graphical interface.

You can follow the steps that are outlined here to learn how to reset a user password using the pgAdmin:

a. Launch the pgAdmin utility and log in with the correct credentials.

b. Connect to the target PostgreSQL server using the superuser credentials.

c. In the Object Explorer pane on the left-hand side, expand the “Servers” group and navigate to the target database.

d. Expand the “Login/Group Roles” node to find the user whose password you wish to reset. In this case, we wish to reset the password for the “linuxhint” user.

e. Right-click on the selected user and choose the “Properties” from the context menu.

f. In the “Properties” window, navigate to the “Definition” tab.

g. Provide the new password in the “Password” field for the defined user.

h. Finally, click “Save” to save the changes.

This should set the newly provided password for the target user.

Method 3: Editing the PostgreSQL Password Configuration File

If you have a forgotten the password for the superuser account, you can reset the password by editing the PostgreSQL password configuration file.

Start by locating the data directory for your PostgreSQL installation. The path for this directory varies depending on your operating system and installed PostgreSQL version.
In the data directory, locate the pg_hba.conf file and edit it with your text editor of choice.

Modify all local connections from md5 to trust. This tells PostgreSQL to trust all incoming connections from the local machine without a password.

Once completed, you need to restart the PostgreSQL server for the changes to take effect. You can then log in to the PostgreSQL server using the Postgres user without a password.

Conclusion

We explored the various methods and techniques that we can use to reset a user password in PostgreSQL.