When dealing with PostgreSQL authentication failures for specific users while others work fine, we're typically looking at one of these core issues:

-- Common error pattern seen in logs
FATAL: password authentication failed for user "readonly_user"
DETAIL: Connection matched pg_hba.conf line 104: "host db_name readonly_user 10.0.0.0/8 md5"

First, verify the authentication flow by checking these components:

1. User existence in PostgreSQL: SELECT usename FROM pg_user WHERE usename = 'XXXX';
2. Password validity: ALTER USER XXXX WITH PASSWORD 'new_password';
3. pg_hba.conf entry format:

   # Correct format for password authentication
   host database_name user_name ip_address/prefix authentication_method
   

When encountering role dependency issues preventing user deletion:

-- First identify dependencies
SELECT pg_describe_object(classid, objid, objsubid) as object,
       pg_get_userbyname(a.rolname) as grantor
FROM pg_depend d
JOIN pg_authid a ON a.oid = d.refobjid
WHERE d.refclassid = 'pg_authid'::regclass
AND d.deptype = 'i'
AND a.rolname = 'XXXX';

-- Then reassign or revoke privileges
REASSIGN OWNED BY XXXX TO postgres;
DROP OWNED BY XXXX;

PostgreSQL stores passwords differently based on authentication method:

-- Check password encryption status
SELECT rolname, rolpassword LIKE 'md5%' as is_md5_encrypted
FROM pg_authid
WHERE rolname = 'XXXX';

-- Force password re-encryption
ALTER USER XXXX ENCRYPTED PASSWORD 'new_password';

Systematic connection testing approach:

# Test local connection bypassing pg_hba.conf
psql -h localhost -U XXXX -d postgres

# Verify password prompt behavior
# If it works locally but not remotely, focus on:
# 1. pg_hba.conf entries
# 2. Network-level restrictions
# 3. Password synchronization across cluster nodes

For persistent cases, enable deeper logging:

# In postgresql.conf
log_statement = 'all'
log_connections = on
log_disconnections = on
log_hostname = on
log_line_prefix = '%m [%p] %q%u@%d '

Then monitor authentication attempts with:

tail -f /var/log/postgresql/postgresql-9.3-main.log | grep "FATAL.*XXXX"

When all else fails, a complete user reset may be necessary:

-- Create backup user with identical privileges
CREATE USER XXXX_backup WITH PASSWORD 'temp_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXXX_backup;

-- Reassign dependencies
REASSIGN OWNED BY XXXX TO XXXX_backup;

-- Now safely drop the problem user
DROP USER XXXX;

-- Recreate with proper authentication
CREATE USER XXXX WITH PASSWORD 'strong_password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
ALTER USER XXXX SET search_path = public;
GRANT CONNECT ON DATABASE db_name TO XXXX;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXXX;

---

When encountering the error FATAL: password authentication failed for user "XXXX" in PostgreSQL 9.3, it typically indicates one of several potential issues with your authentication setup. The log entry showing the matched pg_hba.conf line confirms the server is receiving the connection attempt but rejecting the credentials.

First, verify the pg_hba.conf configuration. The line mentioned in your logs:

host DATABASENAME XXXX 10.0.0.0/8 md5

This shows md5 authentication is required. While you've tried resetting the password, let's ensure proper password assignment:

ALTER ROLE XXXX WITH PASSWORD 'new_password';

Check how the password is stored in pg_shadow:

SELECT usename, passwd FROM pg_shadow WHERE usename = 'XXXX';

If the password field starts with 'md5', it's properly hashed. If it's plaintext or empty, that explains the authentication failure.

The error preventing user deletion indicates existing privileges. To safely reassign these:

REASSIGN OWNED BY XXXX TO postgres;
DROP OWNED BY XXXX;

Now you can drop and recreate the user:

DROP ROLE XXXX;
CREATE ROLE XXXX WITH LOGIN PASSWORD 'secure_password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

If issues persist, test different auth methods in pg_hba.conf:

# Try these variations one at a time
host DATABASENAME XXXX 10.0.0.0/8 md5
host DATABASENAME XXXX 10.0.0.0/8 scram-sha-256
host DATABASENAME XXXX 10.0.0.0/8 password

For a read-only user, use specific grants instead of ALL:

GRANT CONNECT ON DATABASE DATABASENAME TO XXXX;
GRANT USAGE ON SCHEMA public TO XXXX;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXXX;

Your \ddp output shows default read privileges. To modify these:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO XXXX;

After making changes, always reload the configuration:

SELECT pg_reload_conf();

Then test the connection from a client machine using psql with the -h parameter to ensure network-level authentication is working.