PostgreSQL

Postgres Reset Password

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:

$ psql -U postgres

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:

ALTER USER username WITH PASSWORD 'new_password';

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

ALTER USER linuxhint WITH PASSWORD 'password';

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:

\q

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.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list