How To Change User Password in PostgreSQL

Last updated on August 1st, 2024 at 06:14 am

Often database administrators need to change user password in PostgreSQL, for database management and website administration. In fact, it is a best practice to regularly change user passwords to avoid security breaches. In this article, we will look at how to change user password in PostgreSQL. You can use these steps to change postgres password in Ubuntu, Windows and other Linux systems. You can also use it to change default postgres password.

How To Change User Password in PostgreSQL

Here are the steps to change user password in PostgreSQL. PostgreSQL provides ALTER USER and ALTER ROLE statements to change user password in PostgreSQL. They both have similar syntax.

1. Log into PostgreSQL

Open terminal and run the following command to log into PostgreSQL. Replace username and dbname with your username and database name respectively.

$ sudo -u username psql dbname

Also read : How to change user to superuser in PostgreSQL

2. Change User Password Using ALTER USER

We will use the ALTER USER command to change user password. Please note, Here is its syntax.

ALTER USER username WITH PASSWORD 'password';

In the above command, you need to specify the username whose password you want to change, and also the new password for that user.

Run the following ALTER USER command to change user password. Replace testuser and newpassword with

ALTER USER testuser WITH PASSWORD 'newpassword';

Similarly, if you want to change password for postgres user, modify the above command as shown.

ALTER USER postgres WITH PASSWORD 'newpassword';

Also read : How to Copy data from one table to another in SQL

Sometimes you may need to expire a password after a specific date. In such cases, use VALID UNTIL clause with ALTER USER statement. Here’s the above SQL query modified to set password expiration date as December 31, 2024.

ALTER USER testuser WITH PASSWORD 'newpassword'
VALID UNTIL 'December 31, 2024';

3. Change Password with ALTER ROLE

You can also use ALTER ROLE command to change user password in PostgreSQL. Please note, ALTER ROLE command will send your new password to database server, in plain text. Therefore, it will be visible in PostgreSQL server logs. Nevertheless, here is its syntax.

ALTER ROLE username WITH PASSWORD 'password';

Just as in the case of ALTER USER, you need to specify the new password along with username whose password you want to change.

Here is the command to change password of user ‘testuser’.

ALTER USER testuser WITH PASSWORD 'newpassword';

Here too, you can set a password to expire at a specific date with VALID UNTIL clause as shown below. Here is an SQL query to expire password on Dec 31, 2024.

ALTER USER testuser WITH PASSWORD 'newpassword'
VALID UNTIL 'December 31, 2024';

4. Verify Change

You can verify that password expiry timestamp using the \du command followed by username, as shown below.

postgres-# \du super

 Role name |                 Attributes
-----------+---------------------------------------------
 super     | Password valid until 2050-12-31 00:00:00+07

Conclusion

As you can see, it is very easy to change user password in PostgreSQL in irrespective of operating system. It is always a system administration best practice to regularly change database user passwords, or at least set it with an expiry date. You can use either ALTER USER or ALTER ROLE command to change user password, as per your requirement. Please note, both these statements are supported only in PostgreSQL>=12.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!