PostgreSQL – Reset Password For Postgres

When working with PostgreSQL databases, we may occasionally forget the PostgreSQL administrator password or need to change it. In such cases, it’s crucial to know the correct process to reset the password. Resetting the PostgreSQL password is essential for ensuring the security of our database system while restoring access.

In this guide, we will provide a detailed, step-by-step process to reset PostgreSQL user password, modify the authentication method in the pg_hba.conf file, and restore our system to its default authentication setup. Follow these steps to update PostgreSQL password securely and get back to managing our database with ease.

Understanding the PostgreSQL Authentication System

PostgreSQL uses the pg_hba.conf file to manage host-based authentication, determining how users can connect to the database system. The pg_hba.conf file is typically located in the data directory of our PostgreSQL installation (for example, C:\Program Files\PostgreSQL\12\data on Windows). The hba in pg_hba.conf stands for host-based authentication, which allows us to control user access and authentication methods.

When the password is forgotten, we can modify the authentication method to allow login without a password. Here’s how we can reset the password for the postgres user and return to the correct configuration.

Step-by-Step Process to Reset the PostgreSQL User Password

Follow the below steps to reset a password for the postgres user:

Step 1: Backup the pg_hba.conf File

Before making any changes, it’s a best practice to create a backup of the pg_hba.conf file. This ensures that we can restore the original file later. We can either copy the file to another directory or simply rename it for backup purposes. For example, we can rename it as pg_hba.conf.bk.

Step 2: Modify the pg_hba.conf File for Passwordless Login

Now, we need to modify the pg_hba.conf file to allow connections without requiring a password. This step temporarily changes the authentication method from md5 (password authentication) to trust (passwordless authentication). Locate the following section in the pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 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.

host    replication     all             127.0.0.1/32            trust

host    replication     all             ::1/128                 trust

Step 3: Restart PostgreSQL Server

After modifying the authentication method, the PostgreSQL server needs to be restarted to apply the changes. On a Windows machine, we can restart the PostgreSQL service from the Services panel. Alternatively, we can restart the server directly using the following command in the Windows terminal:

pg_ctl -D "C:\Program Files\PostgreSQL\12\data" restart

The “C:\Program Files\PostgreSQL\12\data” is the data directory. Ensure that we replace "C:\Program Files\PostgreSQL\12\data" with the correct path to our PostgreSQL data directory.

Step 4: Connect to PostgreSQL Database Without Password

Finally connect to the PostgreSQL database server using any tool such as psql or pgAdmin(In pgAdmin, press ok while it prompts us to enter the password without entering anything in the field):

psql -U postgres

At this stage, we will not be asked for any authentication.

Step 5: Change the PostgreSQL Password

Once connected to the PostgreSQL database, we can set a new password for the postgres user. Use the following SQL command.

ALTER USER postgres WITH PASSWORD 'new_password';

Replace 'new_password' with the new password we wish to set. We should see an output confirming the password update, as shown below:

Step 6: Restore the pg_hba.conf File

Now restart the PostgreSQL database server. At this stage, we can connect to the PostgreSQL database server with the new password. After resetting the PostgreSQL database password, it’s crucial to revert the authentication method back to md5 in the pg_hba.conf file for security purposes. Modify the file to look like this:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

Then, reload the PostgreSQL configuration to apply the changes:

sudo -u postgres pg_ctl reload

Conclusion

Resetting the PostgreSQL user password is a straightforward process that involves modifying the pg_hba.conf file and temporarily allowing passwordless login. By following these steps, we can regain access to our PostgreSQL database, set a new password for the postgres user, and return our system to a secure state by restoring the original authentication settings. This process ensures that our PostgreSQL password reset is both secure and effective, allowing us to manage our database confidently.

FAQs

How to reset Postgres user password?

To reset the Postgres user password, modify the pg_hba.conf file to allow passwordless login by changing the authentication method to trust. Then, connect to the PostgreSQL database using psql and execute ALTER USER postgres WITH PASSWORD 'new_password'; to set the new password.

How to reset role password in psql?

In psql, you can reset a role’s password by connecting to the database and running the command: ALTER USER role_name WITH PASSWORD 'new_password';, replacing role_name with the actual role and new_password with the desired password.

How to reset Postgres?

To reset Postgres, you can either restart the PostgreSQL service or, if you want to reset the database itself, use the pg_resetxlog utility. However, resetting the database should be done cautiously, as it may cause data loss

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-administration