How to set user password in PostgreSQL

Whether you’re setting up a new database user, resetting a forgotten password, or implementing stronger security policies, understanding how to manage PostgreSQL passwords effectively is crucial for database administrators and developers. In this PostgreSQL tutorial, I will show the steps on how to set the PostgreSQL user password.

How to set user password in PostgreSQL

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

So you need to change or set the password for the default user or any existing user in PostgreSQL. Here, we will explore several approaches to setting passwords for these types of users in PostgreSQL.

Approach 1: Using the ALTER USER Command

Syntax

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, log in using the command below.

psql -U postgres

If the above command asks for a password, press Enter on your keyboard, as the password is none for this user.

After executing the above command, you are taken to the psql prompt, as shown in the picture below.

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

ALTER USER postgres PASSWORD 'postgres@456'

After executing the above query, I obtained the expected output, as shown in the screenshot below.

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

Enter the below command in the psql prompt to exit.

\q

Now you can log in with the user ‘postgres’ using the new password you have set.

When to Use ALTER USER

It is recommended to use the ALTER USER command when:

• You’re working directly with the PostgreSQL database through psql or another SQL client
• You need to script password changes for automation
• You’re working with any version of PostgreSQL (it’s universally supported)

Read Also: How to Set Up Oracle Database In Archivelog Mode

Approach 2: Using Configuration File

PostgreSQL has an access policy configuration file called pg_hba.conf, which is used for client authentication. Sometimes, you may not have access to your password, or perhaps you have forgotten the password for a specific user in PostgreSQL.

You have one option to set the password through the pg_hba.conf file.

To set user password in PostgreSQL, follow the steps below.

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

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

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

4. 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, you are taken to the psql prompt without being prompted for a password.

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

ALTER USER postgres PASSWORD '@postgres222';

If you see the message ‘ALTER ROLE’ as indicated by the green arrow in the above picture, it means the password for the user ‘postgres’ has been updated. Then, exit the psql prompt using the command below.

\q

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

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

Approach 3: Using the createuser Utility with the Password Option

For creating new users with passwords in one step, PostgreSQL provides a createuser utility. I frequently use this approach when setting up new databases as it streamlines the user creation process.

createuser --pwprompt --interactive username

The --pwprompt flag will prompt you to enter a password, while --interactive allowing you to specify other user attributes interactively.

When to Use createuser

This method is ideal when:

• You’re setting up a new database user from the command line
• You want to create a user and set their password in a single operation
• You prefer not to type the password directly in the command (for security reasons)

Approach 4: Using psql Meta-Commands

When working interactively with the PostgreSQL command-line client (psql), you can use meta-commands to manage user passwords.

Connect to PostgreSQL using psql:

psql -U postgres

Then set a user’s password using the \password Meta-command:

postgres=# \password username

After executing the above command, I obtained the expected output, as shown in the screenshot below.

Or to change your password:

postgres=# \password

After executing the above query, I got the expected output as shown below.

This method will prompt you to enter and confirm the password, without showing the characters you type.

When to Use psql Meta-Commands

You can use this approach when:

• You’re already working in a psql interactive session
• You want to avoid exposing the password in the command history
• You’re changing your password and don’t have superuser privileges

Approach 5: Using the SQL GRANT Command for Role-Based Management

In more complex PostgreSQL environments, I often implement role-based access control, where passwords are set at the role level. This approach scales well for organizations with a large number of users.

First, create a role with a password:

CREATE ROLE analyst_role WITH PASSWORD 'secure_role_password' LOGIN;

After executing the above query, the role was created successfully as per the screenshot below.

Then grant this role to users:

GRANT analyst_role TO postgres;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

This way, when these users connect to PostgreSQL, they use the role’s password rather than individual passwords.

When to Use Role-Based Password Management

It is recommended when:

• You have many users with similar access patterns
• You want to centralize password management
• You’re implementing a comprehensive RBAC (Role-Based Access Control) strategy

Conclusion

Properly managing PostgreSQL user passwords is a fundamental aspect of database security. By understanding the various approaches available, as mentioned in this article—from SQL commands to authentication configuration—you can implement a robust password management strategy that strikes a balance between security and operational needs.

Below are the recommendations

1. Implementing a password policy that enforces complexity requirements
2. Regularly rotating passwords for sensitive accounts
3. Auditing password changes for compliance purposes
4. Using different passwords across development, testing, and production environments

By following the approaches outlined in this article, you’ll be able to manage PostgreSQL user passwords.

You may also like:

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