How to Change a PostgreSQL user Password?

How to change a user Password in PostgreSQL?

Changing a user's password in PostgreSQL is straightforward and can be done using the ALTER USER or ALTER ROLE command. Here’s a guide on how to change a PostgreSQL user's password using SQL commands and psql.

1. Using ALTER USER Command

The ALTER USER command allows you to modify attributes of a PostgreSQL user, including setting a new password.

Syntax:

ALTER USER username WITH PASSWORD 'new_password';

Example Code:

-- Change password for a specific user
ALTER USER my_user               -- Specify the username you want to modify
WITH PASSWORD 'new_password123';  -- Set the new password (replace with actual password)

Explanation:

• ALTER USER my_user: Specifies the user whose password you want to change (my_user is the username in this example).
• WITH PASSWORD 'new_password123': Sets the new password for the user. Ensure the password is enclosed in single quotes and replace it with a secure password.

2. Using ALTER ROLE Command

ALTER ROLE is similar to ALTER USER and can also be used to set a new password for a user.

Syntax:

ALTER ROLE username WITH PASSWORD 'new_password';

Example Code:

-- Change password using ALTER ROLE
ALTER ROLE my_user               -- Specify the username for password change
WITH PASSWORD 'new_password123';  -- Set the new password (replace with actual password)

Explanation:

• ALTER ROLE my_user: Specifies the role (or user) whose password you want to change.
• WITH PASSWORD 'new_password123': Sets a new password for the specified user.

3. Using psql Command-Line Interface

If you are using the PostgreSQL psql command-line tool, you can run either ALTER USER or ALTER ROLE commands directly.

Example Code:

-- Access the psql command-line interface
psql -U postgres -d your_database
-- Change the user password inside psql
ALTER USER my_user WITH PASSWORD 'new_password123';

Explanation:

• psql -U postgres -d your_database: Connect to the PostgreSQL database using psql. Replace your_database with the name of the database.
• ALTER USER my_user WITH PASSWORD 'new_password123': Change the password for the specified user directly within psql.

Important Notes

• Security: Avoid using weak passwords. Choose a secure and unique password for each user.
• Superuser Access: Only a superuser or the user themselves can change their password.
• Password Storage: Passwords are encrypted in PostgreSQL but ensure passwords are not exposed in logs or scripts.

All PostgreSQL Questions, Answers, and Code Snippets Collection.