Change a PostgreSQL User Password

1. Overview

PostgreSQL is a powerful open-source relational database that is widely used for storing and managing data. Just like any other database system, securing user information is critical to maintaining data integrity and preventing unauthorised access. One of the fundamental security practices is to regularly update user passwords. In PostgreSQL, we can change a user’s password using the SQL commands.

In this tutorial, we’ll discuss all the common approaches to updating PostgreSQL user passwords while maintaining security best practices.

2. Using the SQL ALTER USER Command

The most common and recommended way to change a PostgreSQL user’s password is by using the ALTER USER command. This command allows for password modification within an SQL session connected to the database:

$ ALTER USER username WITH PASSWORD 'new_password';

Note that this method requires appropriate privileges, usually the role of a superuser or the ability to alter other roles. Alternatively, we can also use the ALTER ROLE command. Since USER and ROLE are synonyms in PostgreSQL, both commands function identically:

$ ALTER ROLE username WITH PASSWORD 'new_password';

When using this command, the password is stored as a hashed value in PostgreSQL’s system catalog, provided the server is configured with the appropriate authentication method.

3. Using the password Command

PostgreSQL’s interactive terminal, psql, provides the \password command to change a user’s password conveniently. Upon invocation, the command will prompt to enter the new password, followed by another prompt for password confirmation:

$ \password
Enter new password:
Enter it again:

The above command will initiate the password change process for the current user. Alternatively, we can also specify the username as shown below:

$ \password USERNAME

This method has a built-in prompting mechanism that avoids displaying passwords in plaintext on the screen or in logs.

4. Reconfiguring Authentication via pg_hba.conf

If standard password change methods fail due to authentication issues, we may need to adjust connection permissions by modifying PostgreSQL’s pg_hba.conf file. The pg_hba.conf file is commonly located at /etc/postgresql/ directory on Debian-based systems. Within the pg_hba.conf file, authentication methods are defined for different connection types and user/database combinations. The “trust” authentication method allows connections without any password at all, while md5 or scram-sha-256 explicitly require password verification.

To ensure password authentication works correctly, we need to ensure that the method is either md5 or scram-sha-256:

local   all             all                                     md5

If not, then we need to update the authentication method. At last, we’ll reload the PostgreSQL service for the new authentication settings to take effect:

$ sudo systemctl reload postgresql

5. Conclusion

In this article, we learned different ways to update the PostgreSQL user password. The recommended approach is to use ALTER USER or ALTER ROLE for scripting and automation, while the \password command in psql offers an interactive and secure alternative.

By understanding and applying these methods, we can achieve secure and efficient user account management in PostgreSQL.