How to change password of PostgreSQL

Are you trying to change the password of PostgreSQL? We can help you with it.

Here at Bobcares, we have seen several such PostgreSQL related queries as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the process of changing the PostgreSQL password.

A few facts about PostgreSQL

PostgreSQL is one of the most popular free and open-source relational database management systems.

It is mainly used as the primary data store or data warehouse for many webs, mobile, geospatial, and analytics applications.

Moreover, it supports many leading programming languages that include Python, Java, Perl, .Net, C/C++, and so on.

How we change the password of PostgreSQL

Here are the steps our Support Engineers provide to our customers to change the password.

Note: Make sure that you have root access to the server.

1. First, switch to PostgreSQL user – postgres

In case, if you are working using a default PostgreSQL installation, then PostgreSQL will be configured with the default user postgres.

Since we logged in as a root user, we assume that the root doesn’t have a user for PostgreSQL. So we switch to PostgreSQL user by running the below command.

$ su – postgresCopy Code

Then we connect to the PostgreSQL by running the below command.

$ psqlCopy Code

You will be asked for a password now. Enter it.

2. Add/Change the PostgreSQL password

Here is the command we use to change the password of the current user.

\passwordCopy Code

Now enter the new password and hit enter again to confirm it as shown below.

Enter new password:
Enter it again:

After changing the password, you can quit it by entering

\qCopy Code

You can also do all the steps in a single command as below.

$ su -c “psql” – postgresCopy Code

Also, the password of the PostgreSQL can be changed using the ALTER ROLE statement as below.

ALTER ROLE username
WITH PASSWORD ‘password’;
</code>Copy Code

In the above statement, first, specify the username of which you want to change the password. Next, provide the new password within a single quote. For example, the following statement changes the password of the superuser to secret12345.

ALTER ROLE super WITH PASSWORD ‘secret12345’;Copy Code

In some cases, you would wish to keep the password valid until a certain date and time. In such cases, you can use VALID UNTIL clause:

ALTER ROLE username
WITH PASSWORD ‘new_password’
VALID UNTIL timestamp;Copy Code

Note: If you omit the VALID UNTIL clause, then the password will be valid forever.

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’;Copy Code

[Need any further assistance with PostgreSQL queries? – We are here to help you.]

Conclusion

Today, we saw how our Support Engineers change the password of PostgreSQL using different methods.