PostgreSQL Authentication Failed: pg_hba.conf Configuration

# PostgreSQL Authentication Failed: pg_hba.conf Configuration

You've got PostgreSQL running, but every connection attempt ends with:

FATAL:  password authentication failed for user "postgres"
FATAL:  no pg_hba.conf entry for host "192.168.1.50", user "appuser", database "mydb", SSL off

These errors stem from PostgreSQL's Host-Based Authentication (HBA) system. Understanding pg_hba.conf is essential for database administrators.

Understanding pg_hba.conf

PostgreSQL uses pg_hba.conf to determine how clients authenticate. The file is processed top-to-bottom, and the first matching rule wins.

Find your configuration file:

sudo -u postgres psql -c "SHOW hba_file;"

View the current configuration:

sudo cat $(sudo -u postgres psql -t -c "SHOW hba_file;" | tr -d ' ')

Common Authentication Errors

Error: "password authentication failed for user"

Full error: `` FATAL: password authentication failed for user "postgres"

This means the user exists but the password is wrong, or the authentication method doesn't support password auth.

Diagnosis: ```bash # Check which authentication method applies sudo -u postgres psql -c "SELECT pg_hba_file_rules(line_number, type, database, user_name, auth_method);"

# Or view the file directly sudo cat /etc/postgresql/16/main/pg_hba.conf ```

Solutions:

1. 1.Reset the password:
2. 2.```sql
3. 3.-- Connect as superuser first
4. 4.ALTER USER postgres WITH PASSWORD 'new_secure_password';
5. 5.`
6. 6.**If using peer authentication, connect differently:**
7. 7.```bash
8. 8.# peer auth uses OS username - must match PostgreSQL username
9. 9.sudo -u postgres psql

# Or change to password auth in pg_hba.conf # Change this line: local all postgres peer # To: local all postgres scram-sha-256 ```

Error: "no pg_hba.conf entry for host"

Full error: `` FATAL: no pg_hba.conf entry for host "192.168.1.50", user "appuser", database "mydb", SSL off

No rule in pg_hba.conf matches this connection.

Solution: Add an entry for this host/user/database combination:

# TYPE  DATABASE    USER        ADDRESS         METHOD
host    mydb        appuser     192.168.1.0/24  scram-sha-256

Error: "Peer authentication failed for user"

Full error: `` FATAL: Peer authentication failed for user "postgres"

Peer authentication requires the OS username to match the PostgreSQL username.

Diagnosis: ```bash # Check current OS user whoami

# PostgreSQL peer auth fails if: # OS user "ubuntu" tries to connect as PostgreSQL user "postgres" ```

Solutions:

1. 1.Connect as matching user:
2. 2.```bash
3. 3.sudo -u postgres psql
4. 4.`
5. 5.Change authentication method:
6. 6.```conf
7. 7.# pg_hba.conf - change peer to scram-sha-256
8. 8.local all all scram-sha-256
9. 9.`
10. 10.Create matching PostgreSQL user:
11. 11.```sql
12. 12.CREATE USER ubuntu WITH SUPERUSER;
13. 13.-- Now "sudo -u ubuntu psql -U ubuntu" works with peer auth
14. 14.`

Authentication Methods Explained

Fixing Common Scenarios

Scenario 1: Allow Remote Connections

Problem: Remote applications cannot connect.

Step 1: Check if PostgreSQL listens on external interfaces:

sudo -u postgres psql -c "SHOW listen_addresses;"

If it's localhost, change postgresql.conf:

listen_addresses = '*'

Step 2: Add remote host to pg_hba.conf:

```conf # Allow connections from specific subnet host all all 10.0.0.0/8 scram-sha-256

# Or from specific IP host myapp app 192.168.1.100/32 scram-sha-256 ```

Step 3: Reload configuration:

sudo systemctl reload postgresql

Scenario 2: Application Connection Fails After Upgrade

Problem: After PostgreSQL 14+ upgrade, authentication fails.

Cause: PostgreSQL 14+ uses scram-sha-256 by default; older versions used md5.

Diagnosis: ``bash # Check password encryption setting sudo -u postgres psql -c "SHOW password_encryption;"

Solution 1: Update user passwords with new encryption:

ALTER USER appuser WITH PASSWORD 'newpassword';
-- Or set explicitly
SET password_encryption = 'scram-sha-256';
ALTER USER appuser WITH PASSWORD 'newpassword';

Solution 2: Allow both methods temporarily:

# pg_hba.conf
host    all    all    127.0.0.1/32    md5
host    all    all    192.168.1.0/24  scram-sha-256

Scenario 3: SSL Connection Required

Error: `` FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL on

Solution: Add SSL entries in pg_hba.conf:

```conf # SSL connections hostssl all all 0.0.0.0/0 scram-sha-256

# Or require SSL for specific database hostssl sensitive_db all 192.168.1.0/24 scram-sha-256 ```

Enable SSL in postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

Testing Configuration Changes

After editing pg_hba.conf, test without restarting:

```bash # Validate syntax (PostgreSQL 16+) sudo -u postgres psql -c "SELECT pg_hba_file_rules.line_number, pg_hba_file_rules.error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

# Reload configuration sudo systemctl reload postgresql

# Or via SQL sudo -u postgres psql -c "SELECT pg_reload_conf();" ```

Test authentication from a client machine:

```bash # Test connection with verbose output psql -h 192.168.1.100 -U appuser -d mydb -v ON_ERROR_STOP=1 -c "SELECT 'connected' AS status;"

# Debug auth issues (requires logging enabled) # In postgresql.conf: log_connections = on log_disconnections = on ```

Debugging with PostgreSQL Logs

Enable detailed auth logging:

# postgresql.conf
log_connections = on
log_disconnections = on
log_statement = 'all'  # For debugging only

Check logs for authentication attempts:

```bash # Ubuntu/Debian tail -f /var/log/postgresql/postgresql-16-main.log

# RHEL/CentOS tail -f /var/lib/pgsql/data/log/postgresql-*.log

# macOS Homebrew tail -f /usr/local/var/log/postgres.log ```

Security Best Practices

1. 1.**Use scram-sha-256** for password authentication, never md5 or trust in production.
2. 2.Limit by IP: Use specific IP ranges, not 0.0.0.0/0:

```conf # Bad - allows entire internet host all all 0.0.0.0/0 scram-sha-256

# Good - specific subnet host all all 10.10.0.0/16 scram-sha-256 ```

1. 1.Order matters: Put specific rules first:

```conf # Specific rules first local replication replicator peer host replication replicator 192.168.1.10/32 scram-sha-256

# Then general rules local all all peer host all all 127.0.0.1/32 scram-sha-256 ```

1. 1.Separate database access:

```conf # App only accesses specific database host appdb appuser 10.0.0.0/8 scram-sha-256

# Admin can access all host all admin 10.0.0.10/32 scram-sha-256 ```

Verification Checklist

After making changes, verify:

```bash # 1. Check configuration syntax sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules LIMIT 5;"

# 2. Reload configuration sudo systemctl reload postgresql

# 3. Test connection from each client type psql -h localhost -U appuser -d mydb -c "SELECT current_user;"

# 4. Verify in logs sudo tail -f /var/log/postgresql/*.log | grep -i auth ```