Alter a user password in PostgreSQL

To alter a user password in Postgres, you set a new password on the role with ALTER USER or ALTER ROLE. ALTER USER is an alias for ALTER ROLE.

ALTER USER myuser WITH PASSWORD 'new-password';

The role name comes first. WITH is optional. PASSWORD replaces the stored password with new-password. The password is a string literal, so its capitals stay capitals. An unquoted role name is folded to lower case, so MyUser and myuser are the same role, while "MyUser" is a different role. psql prints ALTER ROLE when the statement succeeds. This is the same change:

ALTER ROLE myuser WITH PASSWORD 'new-password';

That password is sent to the server in cleartext. It can be written into psql’s command history, and it can be written into the server log.

The \password command

\password is a psql command. It prompts for the new password, encrypts it, and sends an ALTER ROLE statement that already contains the encrypted form. The cleartext password does not appear in the command history, the server log, or elsewhere.

\password myuser

psql prints Enter new password for user "myuser": and then Enter it again:. The history file keeps the line \password myuser. Leave the role name off and \password changes the role you are connected as.

VALID UNTIL

VALID UNTIL sets a time after which the password is no longer valid for password authentication. If you leave it out of ALTER ROLE, the previous expiration stays in place. A password created with no VALID UNTIL does not expire.

ALTER ROLE myuser WITH PASSWORD 'new-password' VALID UNTIL '2026-12-31 23:59:59+00';

This sets the password to new-password and stops password logins after 23:59:59 at offset +00 on 31 December 2026. The deadline applies to the password, not to the role. peer, cert, trust, and other non-password methods do not check it.

infinity means the password does not expire. This clears a deadline without choosing a new password:

ALTER ROLE myuser VALID UNTIL 'infinity';

Who can change a password

A superuser can change any role’s password. You can change your own password with no extra privilege.

ALTER ROLE CURRENT_USER WITH PASSWORD 'new-password';

CURRENT_USER is the role of this session. On PostgreSQL 16 and later, changing a different role’s password takes the CREATEROLE attribute and the ADMIN option on that role. The target also has to be neither a superuser nor a replication role. On PostgreSQL 15 and earlier, CREATEROLE alone can change the password of a role that is not a superuser and not a replication role.

On PostgreSQL 16 and later, a role you are not allowed to change produces this error:

ERROR:  permission denied to alter role
DETAIL:  To change another role's password, the current user must have the CREATEROLE attribute and the ADMIN option on the role.

A name that matches no role produces ERROR: role "myuser" does not exist.

Change a password from the shell

psql -c sends one command string to the server and then exits.

psql -U postgres -d postgres -c "ALTER ROLE myuser WITH PASSWORD 'new-password';"

The new password sits in that command string, so shell history keeps it, and the server still receives it in cleartext. A shell here-document is the same SQL, read from the script instead of from -c:

psql -U postgres -d postgres <<'SQL'
ALTER ROLE myuser WITH PASSWORD 'new-password';
SQL

PGPASSWORD and a password file (.pgpass) are the password you use to log in. PostgreSQL recommends a password file over PGPASSWORD, because on some operating systems other users can read a process’s environment. When you can type the new password yourself, connect with psql and use \password.

When the new password still fails

ALTER ROLE only updates the stored password. The next login uses the first line in pg_hba.conf that matches the connection. trust allows the connection with no password, so the new password is never checked. peer, for a local socket, compares your operating system user name with the database role name and does not read the password.

password_encryption chooses the hash written by the next ALTER ROLE ... PASSWORD. Since PostgreSQL 14 the default is scram-sha-256. SHOW password_encryption; prints the setting for this session. A hash that begins with md5 works with the md5 and password methods in pg_hba.conf. Against a scram-sha-256 line the client gets FATAL: password authentication failed for user "myuser", and the server log says the user does not have a valid SCRAM secret. A line that says md5 uses SCRAM when the stored password is already a SCRAM hash. Changing password_encryption does not rewrite a hash that is already stored. Each role picks up the new algorithm the next time its password is set.

After you edit pg_hba.conf, reload it with pg_ctl reload or:

SELECT pg_reload_conf();

If VALID UNTIL is already in the past, the client sees the same password authentication failed failure. The server log says the user has an expired password. Password login works again after you set a new password or move the deadline to infinity.

PASSWORD NULL removes the stored password. Password authentication then fails for that role.

ALTER ROLE myuser WITH PASSWORD NULL;

If this article was helpful, tweet it!