How to Set PostgreSQL User Password

In this PostgreSQL tutorial, I will show the step to how to set the PostgreSQL user password. Also, I will show you how to set the PostgreSQL user password using a configuration file.

I will let you know about the command to set the password for any user in PostgreSQL.

How to Set PostgreSQL User Password

In PostgreSQL, a user called ‘postgres’ is the default user, and the password for this user isn’t set when the first time you install PostgreSQL. But a user without a password means your data isn’t secure anymore and anyone can access your data.

So you need to change or set the password for the default user or any existing user in PostgreSQL. So here we will take some of the examples to understand how to set the password for these kinds of users in PostgreSQL.

The syntax is given below.

ALTER USER username PASSWORD 'user_new_password';

• ALTER USER: The command to modify the user attributes. Here the password attribute will be modified.
• username: The user whose password you want to set.
• PASSWORD: It is the input option or keyword to specify the new password for the user.
• user_new_password: This is the user’s new password that you want to set.

To change the password of the default user ‘postgres’ in PostgreSQL. First, you need to log in, so use the below command.

psql -U postgres

If the above command asks for a password, just hit enter from your keyboard because the password is none for this user.

After executing the above command, it takes you to the psql prompt that is shown in the below picture.

Now use the below command to set the password for the default user ‘postgres’.

ALTER USER postgres PASSWORD 'postgres@456'

After executing the above command, a message appears ‘ALTER ROLE’ which means the password for the user ‘postgres’ updated.

Enter the below command in the psql prompt to exit.

\q

Now you can log in with user ‘postgres’ with a new password that you have set.

Read Also: How to Setup Oracle Database In Archivelog Mode

How to Set PostgreSQL User Password using Configuration File

PostgreSQL has an access policy configuration file called pg_hba.conf and this file is used for client authentication. Sometimes you don’t have access to your password or maybe you forgot the password for the particular user in PostgreSQL.

So you have one option to set the password through this file pg_hba.conf. Follow the below steps to set the password for the user.

First, find the file pg_hba.conf and your system wherever PostgreSQL is installed, On windows you can find it at ‘C:\Program Files\PostgreSQL\14\data’ as shown in the below picture.

Open the file and change the method to ‘trust’ as shown in the below picture.

Then restart the PostgreSQL server from the services as shown in the below picture.

Open the command prompt and log in with the default user ‘postgres’ to set the password using the below command.

psql -U postgres

After executing the above command, it takes you to the psql prompt without asking for any password.

Then run the below command to set the password for the user ‘postgres’.

ALTER USER postgres PASSWORD '@postgres222';

If you see the message ‘ALTER ROLE’ as indicated as a green arrow in the above picture that means the password for the user ‘postgres’ is updated. Then exit the psql prompt using the below command.

\q

Now again open the file pg_hba.conf and the method to ‘md5’ as shown in the below picture.

Again Restart the PostgreSQL server from the services of Windows. After restarting the server you can log in with the new password that you have set for the user ‘postgres’.

Conclusion

In this PostgreSQL tutorial, you have learned how to set the password for the user in PostgreSQL, then covered the two ways to set the password, one using psql prompt and the other through pg_hba.conf file.

You may also like:

• How to create user in PostgreSQL
• PostgreSQL list users – Helpful Guide