Postgres' ALTER USER and ALTER ROLE statements are used to change/modify a user's password. To change the user password in Postgres, all you need to do is, use the ALTER USER or ALTER ROLE command and provide the new password within the single quotations.

This blog post will present detailed knowledge about how to change the user password in Postgres. So, let’s begin!

How to Change/Modify the User’s Password in Postgres?

Use one of the below-given syntaxes to change the password of the Postgres user.

Syntax 1:

Use the ALTER ROLE statement with PASSWORD attribute and specify the new password within the single quotation:

ALTER ROLE user_name 
WITH PASSWORD 'modified_password'
VALID UNTIL ‘expiry_date_time’;

Syntax 2:

Use the ALTER USER statement with PASSWORD attribute and specify the modified password within the single quotation:

ALTER USER user_name
WITH PASSWORD 'modified_password'
VALID UNTIL 'expiry_date_time';

In the above-given syntaxes:

- ALTER ROLE and ALTER USER are the Postgres statements that modify the role/user.
- User_name is a user to be modified.
- Specify the modified password of your choice in place of the modified_password.
- VALID UNTIL is optional and used to specify the password validation until a specific date and time.

Note: ALTER ROLE and ALTER USER statements are used to alter the attributes of a user's account. Only superusers can change the privileges and passwords of the user’s account.

Example 1: How to Change User Password Using ALTER USER command?

To get the list of Postgres users, firstly, execute the below-mentioned command:

\du

Suppose we need to change the password of a user named “command_prompt”. To do so, we will execute the ALTER USER Command as follows:

ALTER USER command_prompt
WITH PASSWORD 'cp@54321'
VALID UNTIL '2022-12-31 11:59:59';

In the above snippet, we utilized the ALTER USER command to change the password for the “command_prompt” user. The specified password will be valid until “2022-12-31 11:59:59”:

The ALTER ROLE message in the output proves that the password has been changed successfully. Let’s run the “\du” command followed by the user name to see the user details:

\du command_prompt

The output proves that the password has been changed successfully, and it will be valid until the specified date and time.

Example 2: How to Change User Password Using ALTER ROLE command?

Let’s change the Password of the “command_prompt” user one more time using the ALTER ROLE statement:

ALTER ROLE command_prompt
WITH PASSWORD 'cp12345678'
VALID UNTIL 'infinity';

In the above statement,

- We changed the user’s password via the ALTER ROLE statement.
- Next, we specified “infinity” in the VALID UNTIL clause, so the specified password will never expire:

From the output, it can be seen that the password has been changed successfully. Let’s explore the user’s details via the below command:

\du command_prompt

If you are a superuser in PostgreSQL, you can change a user's password this way.

Conclusion

Use the ALTER USER or ALTER ROLE statement to change/modify the password of a Postgres user. To do so, use the ALTER USER or ALTER ROLE command and provide the new/modified password within the single quotations. Additionally, you can use the VALID UNTIL clause to specify the password’s expiry date and time. In such a case, the password will be valid until the defined date/time. This blog post has explained how to change the user’s password in PostgreSQL via the ALTER ROLE and ALTER USER statements.