How to set user password in PostgreSQL

In this Postgresql tutorial, we are going to learn about “Postgresql set user password”, which means changing the password of an existing user in the Postgres database on different environments like Windows, Ubuntu, etc.

We are going to cover the following topics.

  • How to set user password in PostgreSQL in windows OS
  • Set user password in PostgreSQL in ubuntu
  • Change user password using pgadmin in PostgreSQL

In Postgresql, We can change the password of the user using the below syntax.

Syntax:

ALTER USER user_name WITH PASSWORD 'new_password';

Here ALTER USER is a command that changes the attributes of a PostgreSQL user account, user_name is the name of the user whose password is to be altered and new_password is the password that you want to set for the user.

Postgresql set user password windows

We can change password of a Postgresql user account using the command line in windows.

The following are the instructions to change the password of the user.

Open CMD on your computer using CTRL+R and type cmd in the box, then hit Enter from your keyboard.

Enter into psql command prompt as postgres user using below command.

If it asks for a password, then enter the password of the user and remember the password.

psql -U postgres

Now change the password of the current user postgres.

ALTER USER postgres WITH PASSWORD '23456';

Now remember this new password and forget the password that we have remembered before for a user named postgres.

Exit from the psql prompt.

\q -- To exit from psql prompt in Postgresql databast
postgresql set user password windows
PostgreSQL set user password windows

We have successfully change the password of a user named Postgres.

Now log in again with the new password of user Postgres.

psql -U postgres

if it asks for a password, enter the new password that we have set recently.

Postgresql set user password windows
Postgresql set user password windows

Read: How to create a table in PostgreSQL

Postgresql set user password ubuntu

In Ubuntu, we can change the password of the Postgresql user account using the terminal.

Open the terminal using CTRL+ALT+T from your keyboard and log into the Postgres prompt using the below command.

sudo su - postgres

Enter into psql prompt.

psql

Now change the password of the user named postgres.

ALTER USER postgres WITH PASSWORD '23456';

Exit from psql prompt and logout from Postgres prompt.

exit -- To exit from psql and postgres prompt
Postgresql set user password ubuntu
Postgresql set user password ubuntu

From the above output, we see the output “ALTER ROLE”, which means the password changed successfully.

Now again log in with the new password and if it asks for a password, then enter the new password.

sudo -i -u postgres

You will successfully be logged in.

Read: PostgreSQL installation on Linux

Postgresql change user password pgadmin

In Postgresql, we can also change the user password from the pgAdmin application.

The following are the instructions to change passwords in the Postgresql database.

Open pdAdmin, go to Browser section and expand icon > in front of Server then expand the icon > in front of Login/Group Roles.

Postgresql change user password pgadmin
Postgresql change user password pgadmin

Now select postgres user from Login/Group Roles, right-click on that and click on option Properties.

Postgresql change user password pgadmin
Postgresql change user password pgadmin

After clicking on Properties, a Login Role-postgres dialog appears, click on the Definition tab and enter the password then click on the Save button at the bottom right corner.

Postgresql change user password pgadmin
Postgresql change user password pgadmin

After clicking on Save, we have successfully changed the password of a user named postgres. Now, close the pgAdmin application, and log in again with the new password.

You may also like some of our latest articles on PostgreSQL.

So in this tutorial, we have learned about “Postgresql set user password” and changed the password of existing users. We have covered the following topics.

  • How to set user password windows in PostgreSQL
  • Set user password ubuntu in PostgreSQL
  • How to change user password pgadmin in PostgreSQL

Leave a Comment