September 18, 2023
Summary: in this tutorial, you will learn how to change the password for a user in PostgreSQL.
To change the password of a PostgreSQL user, you use the ALTER ROLE statement as follows:
ALTER ROLE username
WITH PASSWORD 'password';
In this statement, to change the password of a user:
- First, specify the
usernamewho you want to change the password. - Second, provide the new
passwordwrapped within single quotes (‘).
For example, the following statement changes the password of the super user to secret123.
ALTER ROLE super WITH PASSWORD 'secret123';
Sometimes, you want to set the password to valid until a date and time. In this case, you use the VALID UNTIL clause:
ALTER ROLE username
WITH PASSWORD 'new_password'
VALID UNTIL timestamp;
Note that if you omit the VALID UNTIL clause, the password will be valid for all time.
The following statement sets the expiration date for the password of super user to December 31 2020:
ALTER ROLE super
VALID UNTIL 'December 31, 2020';
To verify the result, you can view the detailed information of the user:
postgres=# \du super;
List of roles
Role name | Attributes | Member of
-----------+---------------------------------------------+-----------
super | Superuser, Cannot login +| {}
| Password valid until 2020-12-31 00:00:00+07 |
Note that using the ALTER ROLE statement will transfer the password to the server in cleartext. In addition, the cleartext password may be logged in the psql’s command history or the server log.
In this tutorial, you have learned how to change the password of a PostgreSQL user using the ALTER ROLE statement.