When your PostgreSQL .pgpass file isn't working as expected during automated backups, you'll typically encounter these symptoms:

• The script still prompts for password despite PGPASSFILE being set
• Permission denied errors when accessing the password file
• Connection failures with "no password supplied" messages

First, let's ensure your .pgpass file is properly formatted and located:

# Correct .pgpass format example
hostname:port:database:username:password
*:*:*:postgres:your_secure_password_here

Key requirements for the file:

• Must be located in the user's home directory by default (~/.pgpass)
• Requires strict 0600 permissions (chmod 600 ~/.pgpass)
• Should contain no trailing whitespace or extra characters

The script should explicitly set the PGPASSFILE variable before calling pg_dump:

#!/bin/bash
# Set the full path to .pgpass
export PGPASSFILE="/full/path/to/.pgpass"

# Verify the variable is set correctly
echo "Using password file at: $PGPASSFILE"

# Perform the backup
pg_dump --username=postgres --format=c --file=/backup/db/db.sqlc database

1. File Permissions Check

ls -la /folder/.pgpass
# Should show -rw------- permissions

2. Environment Verification

env | grep PGPASS
# Should show your PGPASSFILE variable

3. Alternative Connection Testing

psql "postgresql://postgres@localhost/database"
# Should connect without password prompt

Here's a more robust backup script implementation:

#!/bin/bash

# Configuration
BACKUP_DIR="/backups/postgres"
PGPASSFILE="/etc/postgresql/.pgpass"
DB_USER="postgres"
DB_NAME="production_db"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Validate permissions
if [ $(stat -c %a "$PGPASSFILE") -ne 600 ]; then
    echo "ERROR: .pgpass must have 600 permissions"
    exit 1
fi

export PGPASSFILE

# Create backup directory if not exists
mkdir -p "$BACKUP_DIR"

# Execute backup
pg_dump \
    --username="$DB_USER" \
    --format=custom \
    --file="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" \
    "$DB_NAME"

# Verify backup success
if [ $? -eq 0 ]; then
    echo "Backup completed successfully: $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"
else
    echo "Backup failed with error code $?"
fi

SELinux Context Issues: On RHEL/CentOS systems, you might need to adjust the SELinux context:

chcon -t default_t /path/to/.pgpass

Password File Location: Consider these alternative locations:

• /etc/postgresql/.pgpass (system-wide)
• ~/.pgpass (user-specific)
• Custom path set via PGPASSFILE

Special Characters in Password: Escape special characters like :, \, or ':

*:*:*:postgres:my\:complex\ password\\with\#chars

PostgreSQL's .pgpass file should automatically provide credentials when properly configured, but several factors can cause silent failures. The file must have strict permissions (0600) and correct formatting:

# Correct .pgpass format
hostname:port:database:username:password
# Wildcard example for local development
*:*:*:postgres:YourSecurePassword123

From experience, these are the most frequent issues:

# 1. Permission issues
chmod 600 /folder/.pgpass

# 2. Environment variable scope
export PGPASSFILE=/folder/.pgpass && pg_dump [...] 

# 3. Hidden newline characters
echo -n "content" > .pgpass

When basic checks don't work, try these diagnostic steps:

# Verify environment variable propagation
env | grep PGPASS

# Test connection separately
psql "postgresql://postgres@localhost/database" -c "SELECT 1"

# Enable verbose logging
PGDEBUG=1 pg_dump [...] 2>&1 | grep -i password

If .pgpass still fails, consider these alternatives:

# Using connection URI in the command
pg_dump "postgresql://postgres:password@localhost/database" -Fc -f backup.dump

# Using pg_service.conf
echo "[mybackup]
user=postgres
password=YourPassword" >> ~/.pg_service.conf

Never compromise security while troubleshooting:

• Rotate passwords after testing
• Use pgcrypto for sensitive data
• Consider SSH tunneling for remote connections