Reset Your PostgreSQL Password in Simple Steps
Postgres password reset - Photo Credit | olly
Published on: (Updated on: )
If you cannot remember your PostgreSQL password, you can replace it with the simple steps below:
Step 1: Access Your PostgreSQL Command Line
You need to switch to the 'postgres' user on your machine. How you do this depends on your operating system.
On Linux:
Open a terminal and run:
sudo -i -u postgres
This command switches you to the 'postgres' user.
For Windows:
You might not need to switch users. Just open your command prompt or PowerShell as an admin.
On macOS:
Similar to Linux, open your Terminal and switch to the 'postgres' user if you’ve set a separate user. It might not be necessary depending on how you installed PostgreSQL.
Step 2: Open PostgreSQL Command Line Tool
Once you're in the correct user or the command prompt, access the PostgreSQL command line interface, 'psql', by simply typing:
psql
If you can’t access psql without a password, you’ll need to alter the PostgreSQL instance to allow password-less entry momentarily. This step is detailed in subsequent steps.
Step 3: Update the Method for Localhost Connections
This step involves editing the 'pg_hba.conf' file to allow connections without a password temporarily.
Find your 'pg_hba.conf' file. The location depends on your Operating System and how you installed PostgreSQL. The usual locations are '/etc/postgresql/14/main/pg_hba.conf' on Linux, or within your PostgreSQL installation directory eg, 'C:\Program Files\PostgreSQL\12\data' on Windows.
Open 'pg_hba.conf' in a text editor like nano with root priviledge.
Look for lines similar to:
local all postgres md5
local all all md5
If you want to reset the password as 'postgres' user change the md5 to trust. Otherwise if you are trying to reset the password for a specifi username, change the local all all ending part to trust. You can actually change both to trust if for the purpose of resetting your password but don't forget to change them back once you have changed the necessary password.
Here is an example:
local all all trust
This 'trust' method allows connections without a password.
Save the file and restart your PostgreSQL server. For example, on Linux, you might use:
sudo service postgresql restart
or:
sudo systemctl restart postgresql
Step 4: Reset the Password
Now, access 'psql' again and run the following command to reset your password:
ALTER USER postgres PASSWORD 'newPassword';
You can replace 'postgres' with a username if you are resetting postgres password for a specific user.
Replace 'newPassword' with your new password.
Step 5: Restore Original Authentication Method
Don’t forget to revert the changes you made in 'pg_hba.conf'. Switch the 'trust' back to its original setting (e.g., 'md5' or 'peer'), save the file, and restart the PostgreSQL server.
Note for Hosted PostgreSQL Databases:
The above method would work on local machine or self hosting service like VPS. If you're using a hosted PostgreSQL database (like on AWS RDS, Heroku, or another cloud provider), you might have to follow a different process. Such services usually offer a way to reset the password through web interface. Check their documentation for instructions.
Resetting a forgotten password can get you back up and running with your PostgreSQL databases. Remember to keep passwords secure and follow best practices for password management.