PostgreSQL Authentication Failed: pg_hba.conf Rejected Connection

What's Actually Happening

PostgreSQL uses pg_hba.conf to control client authentication. When authentication fails, the client's connection attempt doesn't match any rule, or the authentication method (password, trust, peer) fails.

The Error You'll See

```bash $ psql -U myuser -d mydb psql: error: FATAL: pg_hba.conf rejects connection for host "192.168.1.100", user "myuser", database "mydb", no encryption

# Or: psql: error: FATAL: password authentication failed for user "myuser"

# Or: psql: error: FATAL: Peer authentication failed for user "postgres" ```

Why This Happens

1. 1.Missing pg_hba.conf entry - No rule allows your connection
2. 2.Wrong authentication method - Method doesn't match client
3. 3.Password wrong - Password mismatch
4. 4.Peer mismatch - OS user doesn't match PostgreSQL user
5. 5.SSL required - Connection not encrypted when required
6. 6.Rule order - Earlier rule rejects before allow rule

Step 1: Check pg_hba.conf Location

```bash # Find pg_hba.conf location sudo -u postgres psql -c "SHOW hba_file;"

# Typical locations: # /etc/postgresql/14/main/pg_hba.conf # /var/lib/pgsql/data/pg_hba.conf ```

Step 2: Review pg_hba.conf Contents

cat /etc/postgresql/14/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

Shows rules:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

Step 3: Understand Authentication Methods

Step 4: Add Rule for Your Connection

Edit pg_hba.conf:

sudo vim /etc/postgresql/14/main/pg_hba.conf

Add entry:

``` # Allow myuser from specific network host mydb myuser 192.168.1.0/24 scram-sha-256

# Allow any user from specific host host all all 192.168.1.100/32 md5

# Allow local connections with password local all all md5 ```

Step 5: Fix Peer Authentication

If getting "Peer authentication failed":

```bash # Option 1: Connect as matching OS user sudo -u postgres psql

# Option 2: Change method from peer to md5 # In pg_hba.conf: local all all md5

# Option 3: Create PostgreSQL user matching OS user sudo -u postgres createuser --superuser $USER ```

Step 6: Fix Password Authentication

If password fails:

```sql -- Set password for user ALTER USER myuser WITH PASSWORD 'newpassword';

-- Use scram-sha-256 (modern, secure) ALTER USER myuser WITH PASSWORD 'newpassword'; -- In pg_hba.conf use: -- host ... scram-sha-256 ```

Step 7: Reload PostgreSQL

After editing pg_hba.conf:

```bash # Reload without restart systemctl reload postgresql

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

Step 8: Check Rule Order

pg_hba.conf rules are evaluated top-to-bottom. First match wins.

# Check order - put specific rules before general
cat /etc/postgresql/14/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

Bad order (reject first):

host    all             all             0.0.0.0/0               reject
host    all             myuser          192.168.1.100/32        md5

Good order (specific first):

host    all             myuser          192.168.1.100/32        md5
host    all             all             0.0.0.0/0               reject

Step 9: Check User Exists

SELECT usename FROM pg_user WHERE usename = 'myuser';

Create user if missing:

CREATE USER myuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Step 10: Enable Logging for Debugging

```bash # In postgresql.conf log_connections = on log_disconnections = on

# Reload systemctl reload postgresql

# Check logs tail -f /var/log/postgresql/postgresql-14-main.log ```

Verify the Fix

```bash # Test connection psql -U myuser -d mydb -h postgres-server-ip

# Should connect without error psql -U myuser -d mydb -c "SELECT current_user;"

# Check authentication method used # In logs, you'll see connection details ```

Prevention Tips

``` # Use scram-sha-256 for password auth (most secure) host all all 0.0.0.0/0 scram-sha-256

# Restrict to specific networks when possible host all all 192.168.1.0/24 scram-sha-256

# Use TLS for encrypted connections hostssl all all 0.0.0.0/0 scram-sha-256 ```