Webmasters and website owners use a variety of methods to change the password for PostgreSQL.

As a part of our PostgreSQL Management Services, we help our Customers to fix PostgreSQL related errors regularly.

Let us today discuss the different methods available to change the password for PostgreSQL on Linux.

How to change a password for PostgreSQL on Linux?

Users can easily change the password for PostgreSQL via the command line. There are a variety of options available to perform this task.

Let us now discuss the different options available to perform this task.

Switch to the PostgreSQL User – postgres

If we are working from a default PostgreSQL installation, then PostgreSQL will be configured with the user postgres.

Since we are logged in as root, and we are assuming that root does not have a user for PostgreSQL. Thus the initial step would be to switch to the default PostgreSQL user – postgres.

$ su - postgres

Then attempt a connection to PostgreSQL with psql command below:

$ psql

Add/Change the Password for the PostgreSQL User – postgres

Use the following command to change the password for the current user, which is now postgres:

\password

Enter your new password, and then enter it again to confirm it. Finally quit the PostgreSQL interface:

\q

We can do all the step one in exactly one command:

$ su -c "psql" - postgres

The password of a PostgreSQL user can also be changed using the ALTER ROLE statement as follows:

ALTER ROLE username
WITH PASSWORD 'password';
</code>

Sometimes, we want to set the password valid until a date and time. In this case, we use the VALID UNTIL clause:

ALTER ROLE username
WITH PASSWORD 'new_password'
VALID UNTIL timestamp;

Note that if we omit the VALID UNTIL clause, the password will be valid for all time.

The following statement sets the expiration date for the password of the superuser to December 31 2020:

ALTER ROLE super
VALID UNTIL 'December 31, 2020';

To verify the result, we 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.

[Need any further assistance to change the password in postgresql? – We’re available 24*7]

Conclusion

In short, the password for the postgres user can be changed easily via the command line. Today, we saw how our Support Engineers perform this task.