PostgreSQL: Change passwords

Let’s see how to change a user’s password or your own password in PostgreSQL. We’ll also discuss how to avoid sending the password in clear over a network.

Changing a user password

To change a user’s password we can use ALTER ROLE:

ALTER ROLE helen WITH PASSWORD 'LedZeppelin_vol4';

WITH is optional, so we could just write:

ALTER ROLE helen PASSWORD 'LedZeppelin_vol4';

To use no password for a certain user, specify PASSWORD NULL.

Encrypting the password

It is a good idea to avoid passwords to be sent over a network in clear. So we can provide PostgreSQL an already encrypted password. PostgreSQL will automatically find out that the password is already encrypted.

ALTER ROLE helen PASSWORD 'e3a71c4af3b43149374191c008962ccc';

Starting with PostgreSQL 10, the encryption algorithm can be set with password_encryption. Allowed values are 'scram-sha-256' (default) and 'md5'. In older PostgreSQL versions, passwords could only be encrypted as MD5.

Changing your own password

The above commands work for your own user as well. But if you’re using the psql client, there is a simpler way:

postgres=# \password
Enter new password: 
Enter it again: 

psql will not send an unencrypted password over the network.