How to Change PostgreSQL User Password: Complete Guide

To change a PostgreSQL user password, use the ALTER USER or ALTER ROLE command with the WITH PASSWORD clause in the psql interface. The basic syntax is ALTER USER username WITH PASSWORD 'new_password'; and you must have appropriate privileges to modify other users’ passwords. You can also set password expiration dates using the VALID UNTIL clause.

Contents

• Basic Password Change Methods
• Authentication Methods and Considerations
• Troubleshooting Common Issues
• Advanced Password Management
• Automated Password Changes

Basic Password Change Methods

Using ALTER USER Command

The most straightforward method to change a PostgreSQL user password is using the ALTER USER command in the psql interface. This command has been part of PostgreSQL for many versions and remains the primary method for password modification.

ALTER USER username WITH PASSWORD 'new_password';

Example: To change the password for the postgres user:

ALTER USER postgres WITH PASSWORD 'securepassword123';

Using ALTER ROLE Command

PostgreSQL treats USER and ROLE interchangeably in most contexts, so you can also use ALTER ROLE which is functionally equivalent:

ALTER ROLE username WITH PASSWORD 'new_password';

This method is particularly useful when working with newer PostgreSQL versions that emphasize role-based access control.

Setting Password Expiration

You can set passwords to expire at specific dates using the VALID UNTIL clause:

ALTER USER username WITH PASSWORD 'new_password' VALID UNTIL '2025-12-31 23:59:59';

After this timestamp, the password will no longer be valid, and the user will need to reset it.

Authentication Methods and Considerations

Understanding PostgreSQL Authentication

PostgreSQL uses several authentication methods, and the method you use to connect affects how password changes work:

• peer authentication: Uses the operating system user identity
• md5: Uses MD5-hashed passwords (legacy method)
• scram-sha-256: Uses modern SCRAM-SHA-256 hashing (current default)
• trust: Allows connections without password (for testing only)

Current Security Recommendations

PostgreSQL 18 and newer versions are phasing out the less secure MD5 method in favor of SCRAM-SHA-256. According to recent documentation, if you try using MD5 with CREATE ROLE or ALTER ROLE, you’ll get a warning controlled by the md5_password_warnings setting.

For production systems, you should choose strong methods like scram-sha-256 or md5. The trust method should be avoided except during initial setup or testing.

Configuring Authentication

To ensure password changes work properly, you may need to configure authentication in your pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256

Troubleshooting Common Issues

Peer Authentication Failed

One common issue is “FATAL: Peer authentication failed for user”. This typically occurs when the PostgreSQL server uses peer authentication but the OS user doesn’t match the database user.

Solution: Switch to the postgres user first:

sudo su - postgres
psql

Password Authentication Failed

If you experience “password authentication failed” errors:

1. Verify the password was changed correctly using psql -U username
2. Check your pg_hba.conf file for the correct authentication method
3. Ensure you’re using the right connection parameters

Connection Issues

Sometimes you need to edit pg_hba.conf to permit access from your client machine’s IP address using appropriate authentication methods. This is particularly important when connecting from remote systems.

Advanced Password Management

Multiple User Password Management

When managing multiple PostgreSQL users, you can automate password changes using scripts. This is particularly useful for DevOps environments where you need to maintain consistent security policies across multiple databases.

Here’s a basic example in Bash for automated password management:

#!/bin/bash
# Usage: ./change_postgres_password.sh username new_password
psql -U postgres -c "ALTER USER $1 WITH PASSWORD '$2';"

Security Best Practices

• Use strong passwords with a minimum length of 12 characters
• Include uppercase, lowercase, numbers, and special characters
• Regularly rotate passwords (consider setting expiration dates)
• Document password changes in your security logs
• Avoid using the same password across different environments

Automated Password Changes

Script-Based Password Changes

For organizations managing many PostgreSQL instances, automated password changes become essential. The following demonstrates a more robust approach:

#!/bin/bash
# Secure PostgreSQL password change script

# Configuration
DB_HOST="localhost"
DB_PORT="5432"
ADMIN_USER="postgres"
LOG_FILE="/var/log/postgresql_password_changes.log"

# Function to log changes
log_change() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
}

# Change password function
change_password() {
    local username=$1
    local new_password=$2
    
    # Escape single quotes in password
    local escaped_password=$(echo "$new_password" | sed "s/'/''/g")
    
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -c "ALTER USER $username WITH PASSWORD '$escaped_password';"
    
    if [ $? -eq 0 ]; then
        log_change "Successfully changed password for user: $username"
        return 0
    else
        log_change "Failed to change password for user: $username"
        return 1
    fi
}

# Example usage
# change_password "app_user" "SecurePass123!"

Integration with Configuration Management

For enterprise environments, consider integrating password changes with configuration management tools like Ansible, Puppet, or Chef. This ensures consistent password policies across all your PostgreSQL instances while maintaining security through encrypted credential storage.

Sources

1. PostgreSQL: Documentation: 18: ALTER USER
2. How To Change PostgreSQL User Password (3 Methods)
3. Setting and Updating User Passwords in PostgreSQL - GeeksforGeeks
4. PostgreSQL: Change a user password - TechOnTheNet
5. How to Change the Password of a User in PostgreSQL — CommandPrompt Inc.
6. PostgreSQL 18’s Cool New Security Trick: OAuth Made Easy
7. How to Log Into PostgreSQL: Step-by-Step Login Methods and Solutions?
8. PostgreSQL ‘password authentication failed’ Fix Guide

Conclusion

Changing PostgreSQL user passwords is a straightforward process using the ALTER USER or ALTER ROLE commands, but it’s important to consider authentication methods and security best practices. Always use strong authentication methods like scram-sha-256 for production systems, and regularly rotate passwords to maintain security. If you encounter peer authentication issues, switching to the postgres user first typically resolves the problem. For managing multiple users or instances, consider implementing automated scripts while ensuring proper logging and security measures are in place.