How To Change PostgreSQL User Password (3 Methods)

According to the Identity Theft Resource Center (ITRC), there were at least 2,365 cyberattacks leading to data compromises in 2023—just shy of 6.5 every day. These incidents left over 343 million victims, which is more than 940,000 per day, or nearly 11 per second.

Data breaches have cost companies across industries an average of $4.88 million this year. Luckily, effectively preventing them comes down to simply managing user credentials effectively. In fact, regularly updating user passwords can notably reduce the risk of unauthorized access and data theft. 

Ready to level up your cybersecurity game? Here’s a step-by-step guide on how to change a PostgreSQL user password, why it’s important, and the best practices for securing your database. Read on!

How To Change PostgreSQL User Password: A Step-by-Step Guide

PostgreSQL is a powerful open-source relational database management system (RDBMS) widely used by developers and enterprises worldwide. Known for its rich feature set, it supports advanced data types and complex queries. This makes it an ideal choice for its many applications — ranging from small databases to large-scale enterprise solutions.

Because PostgreSQL often stores sensitive and business-critical data, vigilant security measures are required to prevent major issues down the line. Adequate password management and regular updates are your best allies here. These measures can help you guarantee that your data and your clients’ data are always in the right hands.

Why Change PostgreSQL User Passwords?

You might need to change the password for a PostgreSQL user for several reasons, including:

• Routine security maintenance: An alarming 44% of internet users claim to rarely change their passwords. Regularly rotating credentials, however, is part of good cybersecurity hygiene.
• Compromised credentials: According to guidelines by the National Institute of Standards and Technology (NIST), organizations force password changes if there’s been an evident security incident. This measure can help keep attacks at bay and sensitive information safe.
• Security policies: Organizations often require password changes after a certain period as part of their compliance strategy. Sources often cite doing so every 60 to 90 days, but once a year may suffice to prevent users from recycling old credentials.
• Employee turnover: A recent survey found that one in three ex-employees still have access to company data even when they no longer work there. When an employee leaves the organization, their credentials should be updated or removed to maintain security.

Methods To Change a PostgreSQL User Password

Depending on your preferences and setup, there are several ways to change a PostgreSQL user password. The three more popular ones are:

Method 1: Using SQL command in psql

The most direct way to change a PostgreSQL user’s password involves using the ALTER USER SQL command in the psql command-line tool. 

1. Open the psql command-line interface:

psql -U postgres

This will prompt you to enter the superuser password.

2. Run the ALTER USER command to change the password:

ALTER USER username WITH PASSWORD 'newpassword';

Don't forget to replace "username" with the name of the user whose password you wish to change and "newpassword" with the new password.

3. Confirm that the password has been changed:

Log out and attempt to log back in using the new credentials.

Method 2: Using pgAdmin (GUI approach)

If you prefer a graphical interface, pgAdmin allows you to change a user’s password in a few simple steps:

1. Open pgAdmin and log in with your administrative credentials.
2. In the left-hand navigation pane, expand the Servers section, followed by Databases, and locate your PostgreSQL instance.
3. Right-click on the user whose password you want to change and select Properties.
4. In the Properties tab, find the Password field and enter the new password.
5. Click Save to apply the changes.

Method 3: Changing password via environment variables

PostgreSQL reads password information from environment variables in some environments, especially automated ones. This is useful for scripting or when managing multiple databases.

1. Set the PGPASSWORD environment variable with the new password:

export PGPASSWORD="newpassword"

This will temporarily set the password for the session.

2. Make it permanent:

Add the export command to your shell configuration file (e.g., .bashrc or .zshrc).

Changing the PostgreSQL Superuser Password

Also known as postgres, the PostgreSQL superuser password is similar to any other user’s password. However, you'll need elevated privileges to change it.

1. Log into the psql command-line tool:

psql -U postgres

2. Run the ALTER USER command for the postgres user:

ALTER USER postgres WITH PASSWORD 'newsuperpassword';

After the command runs successfully, the superuser password will be updated.

Automating Password Changes

Automation is gaining massive traction in streamlining IT operations—including password maintenance. It allows you to uphold your security standards and minimize the risks of manual password management. You can leverage this handy resource to secure PostgreSQL in three ways:

1. Using scripts to change passwords

If you manage multiple PostgreSQL users, you can automate password changes using scripts. Here’s a basic example in Bash:

#!/bin/bash 
 psql -U postgres -c "ALTER USER $1 WITH PASSWORD '$2';"

This script takes the username and new password as arguments and automatically updates the PostgreSQL user’s credentials.

2. Integrating with configuration management tools

Consider using configuration management tools like Ansible, Chef, or Puppet to manage PostgreSQL credentials across multiple servers for larger infrastructures. These tools can automate updating passwords and enforce consistent security policies.

3. Using StrongDM for automated credential rotation

StrongDM can automate credential rotation across your entire infrastructure, ensuring that passwords are updated regularly without manual intervention. This significantly reduces the risk of compromised credentials and maintains secure access to your PostgreSQL databases.

Secure PostgreSQL Database Access With StrongDM

Managing user credentials is crucial for database security, and StrongDM simplifies this task. As your organization expands, the number of users needing access to data and systems increases, making manual password updates across multiple environments much more challenging. StrongDM centralizes database access, including PostgreSQL, by providing a single interface to manage credentials, enforce least-privilege policies, and automate password rotation.

StrongDM also eliminates the need for hardcoded credentials by integrating seamlessly with your existing identity providers and infrastructure. With StrongDM, you can ensure that every user has secure, audited access to only the systems they need while automatically updating credentials without manual intervention.

Ready to learn more? Visit our site and book a demo today!