Three ways to change your PostgreSQL user password

According to the Identity Theft Resource Center (ITRC), there were at least 2,365 cyberattacks resulting in data compromises in 2023, just shy of 6.5 per day, with more than 353 million victims.

Data breaches have cost companies across industries an average of $4.88 million this year. Credential management is not the whole answer to that, but it is one of the few parts of prevention that sits entirely in your hands.

This guide covers three ways to change a PostgreSQL user password, when it is worth doing, and what to watch for in the two places this process commonly leaks the password you are trying to protect. 

A step-by-step guide to changing your PostgreSQL user password

PostgreSQL is an open-source relational database management system (RDBMS) commonly 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 a wide range of applications, 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 part of keeping that data accessible only to those who should have access. 

Why change PostgreSQL user passwords?

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

• Routine security maintenance. A credential that has not changed since the database was provisioned is the one most likely to have been shared, written down or embedded in a script that nobody owns anymore. 
• Compromised credentials. According to guidelines from the National Institute of Standards and Technology (NIST), organizations must change passwords after a clear security incident. 
• Security policies. Compliance frameworks have traditionally required password changes on a set schedule, often every 60 to 90 days. Current guidance moves away from that. NIST now recommends rotating credentials based on evidence of compromise and screening new passwords against known-breach lists, rather than forcing a change on a fixed calendar. 
• Employee turnover. In many cases, 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 most popular ones are:

Method 1: Using an 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

Depending on how authentication is configured in pg_hba.conf, this may prompt you for 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 steps:

1. Open pgAdmin and log in with your administrative credentials.
2. In the left-hand navigation pane, expand the server you want to work in, then expand Login/Group Roles.
3. Right-click the role whose password you want to change and select Properties.
4. On the Definition tab, enter the new password in the Password field.
5. Click Save to apply the change.

Method 3: Scripting the change with PGPASSWORD

In automated environments, you usually need to run the ALTER USER command without an interactive prompt. The PGPASSWORD environment variable gets you there, but it’s worth being precise about what it actually does. PGPASSWORD authenticates the connection. It supplies your current credential to psql so the script doesn’t stop and wait for input. It does not change the password on the server. The ALTER USER command still does that work.

1. Set PGPASSWORD to the account’s current password, then run the change in the same command.

export PGPASSWORD="currentpassword"
psql -U postgres -c "ALTER USER username WITH PASSWORD 'newpassword';"

2. Clear the variable right after:

unset PGPASSWORD

Don’t add this export to .bashrc, .zshrc or any file that persists across sessions. A password sitting in a shell config is a password sitting in plain text on disk, and other processes on the same machine can often read environment variables straight out of /proc or ps. Scope PGPASSWORD to the single command that needs it, then unset it.

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

Automating password changes removes the manual step where mistakes and delays happen. There are three ways to do it for PostgreSQL:

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 a vault to rotate credentials

The scripted approach above still leaves someone holding a password. A vault removes that step. Delinea Secret Server stores the credential and uses Remote Password Changing to update the account on the target system when the secret expires or on a set schedule, generating the new password. Then it authenticates against the target to confirm that the stored credential still works and flags the secret as out of sync if it does not. 

On the machine side, where pipelines and applications need credentials at runtime rather than a person needing them at a prompt, DevOps Secrets Vault serves them via an API call and can issue dynamic secrets that expire on their own. Either way, the password no longer lives in a dotfile or a script argument, which removes both exposures described earlier. 

Where this leaves you

Changing a PostgreSQL password is a two-minute job. Keeping track of every account that has one, across every environment, is the part that does not scale. The number of users needing access grows, the number of places a credential has been copied grows faster, and manual updates stop being realistic well before anyone admits it. 

That is the case for vaulting rather than rotating by hand. The Delinea Platform holds the credential, rotates it on a schedule you set and injects it into the session so the person connecting never handles it. The rotation question shifts from how often you should change the password to who needs to know it. 

For more on how credential-based attacks run, and how vaulting stops them, take an interactive tour of the Delinea Platform.