How can I change a PostgreSQL user password?

To change a PostgreSQL user’s password, use one of the following methods:

Method 1: Using psql (Interactive & Secure)

1. Connect to PostgreSQL as a superuser (e.g., postgres):

   psql -U postgres -d your_database_name

2. Change the password with the \password meta-command:

   \password username

You’ll be prompted securely to enter the new password twice.
Example output:

   Enter new password for user "username": 
   Enter it again:

Method 2: Using SQL (Direct Command)

Run the ALTER ROLE SQL command to update the password:

ALTER ROLE username WITH PASSWORD 'new_password';

Notes:

• Replace username and new_password with actual values.
• Security Risk: Avoid exposing passwords in command history or logs. Use this method cautiously.

Optional: Specify Encryption Method

PostgreSQL encrypts passwords by default. To explicitly set the method (e.g., SCRAM-SHA-256):

ALTER ROLE username WITH ENCRYPTED PASSWORD 'new_password';

Key Considerations

1. Privileges: You must be a superuser or have CREATEROLE privileges to change another user’s password.
2. Authentication: Ensure pg_hba.conf uses md5 or scram-sha-256 for password-based authentication.
3. Security: Prefer \password in psql to avoid exposing plaintext passwords.

Example Workflow

1. Connect via psql:

   psql -U postgres

2. Change Password:

   \password alice

Follow prompts to set a new password for user alice.

Use \password for interactive security or ALTER ROLE in scripts (with caution).