How to Safely Change a Role Password in PostgreSQL

You can change a PostgreSQL password with one SQL statement. The part people get wrong is how they type it. Done the common way, the new password can end up in plain text in your shell history or in the server logs.

This guide covers the safe way, the quick way, how to reset a forgotten postgres password, and what else to update after the change.

TL;DR

Inside psql, run:

\password app_user

Type the new password twice. The password itself never shows up in your history or in the server logs.

Roles, Users, and Passwords

In PostgreSQL, a “user” is just a role with the LOGIN attribute. ALTER USER and ALTER ROLE do the same thing, so this guide uses ALTER ROLE.

The server never stores your password in plain text. It stores a hash. Since PostgreSQL 14, that hash is SCRAM-SHA-256 by default. Older installs may still use MD5.

Method 1: Use \password in psql (Recommended)

Connect with an account that’s allowed to change the password. That’s a superuser, the role itself, or a role with the right CREATEROLE privileges:

sudo -u postgres psql

Then run:

postgres=# \password app_user
Enter new password for user "app_user":
Enter it again:

If you don’t give a name, \password changes the password for the role you’re logged in as.

Why this is the best option: psql hashes the password on your machine before sending it. The server only ever receives the hash. Nothing readable lands in ~/.psql_history, and nothing readable lands in the PostgreSQL log.

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

Method 2: ALTER ROLE … PASSWORD

This is the one you’ll see in most tutorials:

ALTER ROLE app_user WITH PASSWORD 'N3w-Str0ng-Pass';

It works, but the password travels in the SQL text. That means:

• psql saves it in ~/.psql_history.
• If log_statement is set to ddl or all, the server writes it to the log.
• If the statement fails, for example because of a typo, the server logs the failed statement, password included.

If you have to use this form, say in a script, turn off history for the session first:

\set HISTFILE /dev/null

If the password contains a single quote, double it:

ALTER ROLE app_user WITH PASSWORD 'it''s-a-secret';

Method 3: Change Your Own Password

You don’t need admin rights to change your own password. Log in as yourself and run:

\password

Or:

ALTER ROLE CURRENT_USER WITH PASSWORD 'N3w-Str0ng-Pass';

Set an Expiration Date

Want a password that stops working on a certain date? Use VALID UNTIL:

ALTER ROLE contractor VALID UNTIL '2026-12-31';

After that date, password logins fail for that role. To remove the expiration:

ALTER ROLE contractor VALID UNTIL 'infinity';

One detail that trips people up: VALID UNTIL only applies to password logins. A role that logs in with peer or certificate authentication isn’t affected.

Remove a Password Completely

ALTER ROLE app_user PASSWORD NULL;

Password logins will now always fail for this role. Other methods, like peer on the local socket, still work if pg_hba.conf allows them.

Check Which Roles Have Passwords

Regular users can see roles and expiration dates:

SELECT rolname, rolcanlogin, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin;

A superuser can also see which hash type each password uses:

SELECT rolname, left(rolpassword, 14) AS hash_prefix
FROM pg_authid
WHERE rolcanlogin;

Example output:

 rolname  |  hash_prefix
----------+----------------
 postgres | SCRAM-SHA-256$
 app_user | md5a3c1e5f0b2
 report   |

Here, app_user is still using an MD5 hash, and report has no password at all.

Reset a Forgotten postgres Password

On Ubuntu, Debian, RHEL, and most Linux packages, the local postgres account can log in over the Unix socket without a password. This uses peer authentication. So usually you can just do:

sudo -u postgres psql -c '\password postgres'

If that fails because peer was replaced with md5 or scram-sha-256, you’ll need to edit pg_hba.conf temporarily.

1. Find the file:

sudo -u postgres psql -c 'SHOW hba_file;'

If you can’t log in at all, check the usual locations: /etc/postgresql/<version>/main/pg_hba.conf on Ubuntu and Debian, or /var/lib/pgsql/<version>/data/pg_hba.conf on RHEL.

2. Change the local line for postgres to peer:

local   all   postgres   peer

3. Reload PostgreSQL. This doesn’t need a restart:

sudo systemctl reload postgresql

4. Set the new password:

sudo -u postgres psql -c '\password postgres'

5. Put pg_hba.conf back the way it was and reload again.

Some guides tell you to use trust here. That works, but it lets any local user log in as postgres without a password. peer only lets the OS user postgres in, which is much safer even for a few minutes.

Moving from MD5 to SCRAM-SHA-256

If the check above showed md5 hashes, now is a good time to upgrade them. MD5 is weak, and PostgreSQL 18 deprecates MD5 passwords.

1. Make sure new passwords are hashed with SCRAM:

SHOW password_encryption;

If it says md5, change it in postgresql.conf:

password_encryption = scram-sha-256

Then reload.

2. Set each password again with \password. The new hash will be SCRAM.
3. Once every role has a SCRAM hash, change md5 to scram-sha-256 in pg_hba.conf and reload.

Do step 3 last. With the md5 method in pg_hba.conf, a role that already has a SCRAM password still logs in fine, because PostgreSQL uses SCRAM for it automatically. If you switch pg_hba.conf first, any role still on MD5 gets locked out.

Test the New Password

Force a TCP connection so PostgreSQL actually asks for the password:

psql -h 127.0.0.1 -U app_user -d mydb

Without -h, psql connects over the Unix socket. If that socket uses peer, it logs you in without checking the password, so the test proves nothing.

What Else Needs the New Password

Changing the password doesn’t disconnect anyone. Existing sessions keep running. The new password only matters for new connections. Before your apps reconnect, update every place that stores the old one:

• Application config: environment variables, .env files, Kubernetes Secrets, and similar.
• ~/.pgpass: each line looks like host:port:database:user:password, and the file must have chmod 600.
• PgBouncer: if it uses auth_file (userlist.txt), update the entry there. If it uses auth_query, there’s nothing to change.
• Replication and backups: standby primary_conninfo, backup scripts, and monitoring agents.

A simple way to rotate a password without downtime is to create a second login role, move the app over to it, and then change or drop the old one.

Common Errors

FATAL: password authentication failed for user "app_user" The password is wrong, it has expired (VALID UNTIL), or the role has no password set.

ERROR: permission denied to alter role You’re not a superuser and don’t have the right to manage that role. On PostgreSQL 16 and newer, CREATEROLE alone isn’t enough. You also need ADMIN OPTION on the target role.

Login works without a password even after the change pg_hba.conf is using trust or peer for that connection. Check which line matches your connection.

Summary

• Use \password in psql. It’s the safest way.
• ALTER ROLE ... PASSWORD works, but it can leak the password into history and logs.
• Test with psql -h 127.0.0.1 so the password really gets checked.
• Update apps, .pgpass, and PgBouncer before they need to reconnect.
• If you still have MD5 hashes, move to SCRAM-SHA-256.