How To Change the Password of a PostgreSQL User

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';
</code>
Code language: HTML, XML (xml)

In this statement, to change the password of a user:

First, specify the username who you want to change the password.
Second, provide the new password wrapped within single quotes (‘).

For example, the following statement changes the password of the super user to secret123.

ALTER ROLE super WITH PASSWORD 'secret123';
Code language: SQL (Structured Query Language) (sql)

Sometimes, you want to set the password 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;
Code language: SQL (Structured Query Language) (sql)

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';
Code language: SQL (Structured Query Language) (sql)

To verify the result, you can view the detailed information of 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 |
Code language: SQL (Structured Query Language) (sql)

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.

Was this tutorial helpful ?