Question: How do you reset the PostgreSQL server password?

Answer

Resetting a PostgreSQL server password can be essential for regaining access or maintaining security. Here's how to reset the password for the PostgreSQL user:

Step 1: Access the Server

First, log into your server where PostgreSQL is installed. You need root or superuser access.

Step 2: Edit the pg_hba.conf File

Locate the pg_hba.conf file, typically found in /etc/postgresql/{version}/main/ on Ubuntu/Debian systems, or /var/lib/pgsql/{version}/data/ on Red Hat-based systems. Open this file with a text editor like nano or vim.

sudo nano /etc/postgresql/12/main/pg_hba.conf

Step 3: Enable Password-less Local Access

Change the method for local connections temporarily from md5 or peer to trust. This allows local users to connect without a password.

From:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

To:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

Save and exit the editor.

Step 4: Restart PostgreSQL

Restart the PostgreSQL service to apply the changes.

sudo systemctl restart postgresql

Step 5: Reset the Password

Connect to the PostgreSQL command line as the PostgreSQL user.

sudo -u postgres psql

Now, run the SQL command to change the password:

ALTER USER postgres WITH PASSWORD 'newpassword';

Replace 'newpassword' with your desired password.

Step 6: Revert the pg_hba.conf Changes

After resetting the password, don't forget to revert your changes in the pg_hba.conf file back to the original method (md5 or peer).

Step 7: Restart PostgreSQL Again

Restart PostgreSQL one more time to re-enable the normal security settings.

sudo systemctl restart postgresql

With these steps, you've successfully reset the PostgreSQL server password. For security purposes, ensure that the new password is strong and stored securely.