When managing multiple PostgreSQL servers, setting up user passwords manually becomes inefficient. Here's how to automate password configuration for the postgres user using bash scripting.

The traditional method requires interactive sessions:

sudo -u postgres psql
\password postgres

This approach doesn't scale well for multiple servers or automated deployments.

PostgreSQL provides several methods for non-interactive password setting:

Method 1: Using psql with -c flag

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'your_secure_password';"

Method 2: Environment Variable Approach

export PGPASSWORD='temporary_password'
psql -U postgres -h localhost -c "ALTER USER postgres WITH PASSWORD 'new_secure_password';"
unset PGPASSWORD

For production use, consider this complete script example:

#!/bin/bash

POSTGRES_PASSWORD="ComplexP@ssw0rd123!"

# Validate input
if [ -z "$POSTGRES_PASSWORD" ]; then
    echo "Error: Password not set" >&2
    exit 1
fi

# Set PostgreSQL user password
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '$POSTGRES_PASSWORD';" &> /dev/null

# Verify password change
if [ $? -eq 0 ]; then
    echo "PostgreSQL password updated successfully"
else
    echo "Failed to update PostgreSQL password" >&2
    exit 1
fi

When implementing this in scripts:

• Never hardcode passwords in scripts
• Use environment variables or secret management tools
• Ensure script files have restricted permissions
• Consider using password hashes instead of plain text

For automated deployments, you might use:

#!/bin/bash

read -s -p "Enter PostgreSQL password: " pg_password
echo

ansible all -i inventory.ini -m postgresql_user \
  -a "name=postgres password=$pg_password role_attr_flags=SUPERUSER" \
  --become --become-user=postgres

Common issues and solutions:

• Permission denied: Ensure script runs with sudo
• Connection refused: Verify PostgreSQL is running
• Authentication failures: Check pg_hba.conf settings

When working with PostgreSQL in production environments, securely setting the postgres user password across multiple servers becomes crucial. The manual approach using sudo -u postgres psql followed by \password command works fine for single instances but doesn't scale well.

Here's a robust bash script solution that automates this process securely:

#!/bin/bash

NEW_PASSWORD="your_secure_password_here"

# Set PostgreSQL user password non-interactively
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '${NEW_PASSWORD}';"

# Verify password change
sudo -u postgres psql -c "\conninfo"

For better security practices, consider these enhancements:

#!/bin/bash

# Generate random password if not provided
if [ -z "$1" ]; then
  NEW_PASSWORD=$(openssl rand -base64 32)
  echo "Generated password: ${NEW_PASSWORD}"
else
  NEW_PASSWORD="$1"
fi

# Export password to avoid shell history exposure
export PGPASSWORD="${NEW_PASSWORD}"

# Execute password change
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '${NEW_PASSWORD}';"

# Clean up environment variable
unset PGPASSWORD

For multi-server deployment using SSH:

#!/bin/bash

read -s -p "Enter new PostgreSQL password: " NEW_PASSWORD
echo

SERVERS=("server1.example.com" "server2.example.com" "server3.example.com")

for server in "${SERVERS[@]}"; do
  echo "Updating ${server}..."
  ssh admin@${server} "sudo -u postgres psql -c \"ALTER USER postgres WITH PASSWORD '${NEW_PASSWORD}';\""
done

Add proper error handling for production scripts:

#!/bin/bash

set -euo pipefail

LOG_FILE="postgres_password_update.log"
NEW_PASSWORD=${1:-}

if [ -z "${NEW_PASSWORD}" ]; then
  echo "Usage: $0 [password]"
  exit 1
fi

{
  echo "$(date) - Starting password update"
  if ! sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '${NEW_PASSWORD}';"; then
    echo "Failed to update password" >&2
    exit 1
  fi
  echo "$(date) - Password updated successfully"
} >> "${LOG_FILE}" 2>&1