In PostgreSQL, when you have once installed the setup, it will ask you to set a password for the default database, i.e., ‘postgres’. You can also create your own user later in the PostgreSQL and set the password for that user. But what if there is a need to change the password for managing a database or administration duties and there pop up a question in your mind, how and from where can you change the password? There is no need to worry because this article will specifically focus on answering your question with simple and different ways to change user passwords in PostgreSQL. This guide will help you to change user passwords and define each step clearly for your better understanding.
Different Modes to Alter User Password:
You can change user passwords through two different methods in PostgreSQL. In both methods, you can create and set a password and change it as well. These two methods are:
- By using pgAdmin.
- By using psql.
Steps to Change Password Through pgAdmin:
When you open PostgreSQL, a screen that is displayed in front of you something looks like this:
On the left side, a navigation menu can be seen in which “Login/Group Roles” is defined. When clicking on that, a drop-by list appears.
In this list, all the usernames are stored that exist in a database with their defined and privileged roles along with them.
Let’s first create a username and set up a password for that username, and then we will change the password. To create a username, click on “Login/Group Roles” and click on “create” a login or group role. Here, we will create a login role in the database with desired roles.
After clicking on the “Login/Group Roles” this will appear:
In the name field, you can specify any name you want. Then click on the “Definitions” and input the password for your username.
In “Privileges” define your user roles and save your login user data in the end.
Now, you have created a user, and you can simply change the password by clicking on your username and then “Properties” in the side navigation bar like this:
The “Properties” will now open the same screen where you have created the username for your login. Here in the “Passwords” you can enter your new password and save it in the end.
In the “Passwords” field, re-enter your new password, and your password is changed for the username ‘saeed_raza’.
Change Password Through psql:
In SQL shell (psql) you can also change the password using two ways:
- Using ALTER ROLE statements.
- Using meta-commands.
Change Password Using ALTER ROLE Statements:
The ALTER ROLE statements are used to change the passwords of a user in PostgreSQL. Here is the basic syntax for using ALTER Role statements in your database:
In the above statement, mention the username in place of ‘username’ of which you want to alter the password. Then write the new password in place of ‘new_password’ to alter the password. The VALID UNTIL clause is optional; it is used to enter the time period in which you want the password to be functional after the specified date or time the password will be expired.
Below is an illustration of altering the password of a username ‘saeed_raza’ with the new password as ‘data’.
The ALTER ROLE command after the SQL statement ensures that the password is changed in the database.
Let’s see one more example of changing the password which will be valid for the specific period that we will assign:
VALID UNTIL 'March 30, 2022' ;
I have changed the password from ‘data’ to ‘defined’ for the username ‘saeed_raza’ and mentioned the date till the password for this username would be valid, which is ‘March 30, 2022’. The password will be expired until this date, but if you don’t put the VALID UNTIL clause in the statement, the password will be valid for the lifetime.
To ensure that the password would be valid until this date, run the following command to verify:
This command will display all lists of roles that are present in the databases with their attributes and username. The above command will show the following results:
In the above output, you can clearly see that in the Role name ‘saeed_raza’ the password is valid until the date 2022-03-30.
Change Password Using Meta Command:
In the above method, to change the password using ALTER ROLE statements, we have seen that the password is visible to the system, and it will also transfer that password to the server, which can be saved in the psql’s statements history as well. You can change the password by keeping it safe and secure from the server log and its history in this method.
First, when starting the psql you must enter the username of which you want to change the password:
I have entered the username ‘saeed_raza’ because I want to change the password of that user in PostgreSQL. Now, follow this simple syntax that will change the user’s or default PostgreSQL password as well by simply using a meta-command:
Enter new password:
Enter it again:
The password is now changed for the user ‘saeed_raza’ by using this simple meta-command.
In this guide, we have learned how we can change the user’s password through pgAdmin and psql with different ways of psql as well. All of the methods we used in this article were efficient and simple, which you can implement on your system to finally answer your questions of how to change user passwords in PostgreSQL.