PostgreSQL pg_hba.conf Connection Authorization Failed on Windows WSL2 Ubuntu

Fix 'pg_hba.conf connection authorization failed' for PostgreSQL on Windows WSL2 Ubuntu. Configure network access and user permissions for seamless database connectivity.

Introduction

As a seasoned SysAdmin and DevOps engineer, I've seen countless connection issues, and PostgreSQL's pg_hba.conf misconfigurations are a frequent culprit, especially in development environments like Windows Subsystem for Linux 2 (WSL2). When you try to connect to your PostgreSQL database running inside your WSL2 Ubuntu distribution, you might encounter a "connection authorization failed" error. This guide will walk you through diagnosing and resolving this common issue, ensuring your applications or client tools can connect seamlessly.

This problem typically arises because PostgreSQL, by default, is configured for secure local access. When you're trying to connect from your Windows host machine (or even another application within WSL2) to your PostgreSQL server within WSL2, it's often treated as an "external" connection, requiring specific authorization rules that are not present in the default pg_hba.conf.

Symptom & Error Signature

You will typically see this error when attempting to connect to your PostgreSQL server using psql from a terminal (either within WSL2 or from Windows via wsl.exe psql ...) or when an application (e.g., a web application, DBeaver, or VS Code extension) tries to establish a database connection.

Example Error Output from psql:

psql: error: FATAL:  connection authorization failed for host "172.24.123.45", user "myuser", database "mydb"
FATAL:  password authentication failed for user "myuser"

Common variations you might see in application logs or other clients:

• org.postgresql.util.PSQLException: FATAL: connection authorization failed for host "..."
• FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off

The key phrases are "connection authorization failed" or "no pg_hba.conf entry". While "password authentication failed" might also appear, it often stems from the same pg_hba.conf issue if the configured method isn't md5 or scram-sha-256 for your specific host.

Root Cause Analysis

The core of this problem lies within PostgreSQL's Host-Based Authentication (HBA) configuration file, pg_hba.conf. This file dictates:

1. Who can connect (which users).
2. From where they can connect (which IP addresses or ranges).
3. To which databases they can connect.
4. Using what authentication method (e.g., md5 for password, peer for local OS users, trust for no password, etc.).

When PostgreSQL receives a connection attempt, it scans pg_hba.conf from top to bottom, looking for the first entry that matches the incoming connection's parameters (connection type, requested database, user, and client IP address). If no matching entry is found, or if a matching entry's authentication method fails, the "connection authorization failed" error is returned.

Specific reasons for this error in a WSL2 environment include:

• WSL2 Network Dynamics: WSL2 runs on a virtualized network. Its IP address, and the IP address of your Windows host machine (as seen by WSL2), can change each time you restart WSL2 or your computer. Default pg_hba.conf entries usually only allow connections from 127.0.0.1 (localhost) or via Unix domain sockets.
• Incorrect IP Address/Range: The pg_hba.conf file might not contain an entry that correctly specifies the IP address of your client (e.g., your Windows machine or another WSL2 application) attempting to connect.
• Incorrect Authentication Method: Even if the host is allowed, the authentication method specified (peer, ident, trust) might not be suitable for an external connection expecting a password (e.g., md5).
• listen_addresses Restriction: While pg_hba.conf handles authorization, the postgresql.conf file determines which network interfaces PostgreSQL listens on. If listen_addresses is set only to localhost (127.0.0.1), no external connections can even reach the HBA system, leading to a "connection refused" rather than "authorization failed" error, but it's a related network configuration to be aware of. The "authorization failed" error confirms the connection reached the server, but was rejected by pg_hba.conf.

Step-by-Step Resolution

Let's fix this by configuring PostgreSQL to correctly authorize connections from your WSL2 environment and Windows host.

1. Locate PostgreSQL Configuration Files

First, you need to find the exact location of your postgresql.conf and pg_hba.conf files. PostgreSQL versions and installations can vary.

Connect to your WSL2 Ubuntu instance:

wsl

Then, execute these commands as the postgres user to find the paths:

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

You'll likely get output similar to:

          config_file
-------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

          hba_file
----------------------------
 /etc/postgresql/14/main/pg_hba.conf
(1 row)

Replace 14 with your PostgreSQL version in subsequent commands.

2. Identify Your WSL2 Network Information

To correctly configure pg_hba.conf, you need to know the IP address of your WSL2 instance and, more importantly, the IP address of your Windows host machine as seen by WSL2.

Get your WSL2 IP address:

ip addr show eth0 | grep -oP 'inetsK[d.]+'

This will typically output an IP address like 172.24.123.45. This is the IP address of your Ubuntu VM within the WSL2 network.

Get your Windows host IP address (as seen by WSL2):

The Windows host's IP address is usually the nameserver entry in /etc/resolv.conf.

cat /etc/resolv.conf | grep nameserver | awk '{print $2}'

This will output an IP address like 172.24.123.1. This is your Windows host machine's IP address from WSL2's perspective. It's often the gateway for the WSL2 virtual network.

These IP addresses are dynamic and can change after a WSL2 restart (wsl --shutdown from PowerShell) or a system reboot. For consistent access, you might allow a broader IP range in pg_hba.conf.

3. Edit pg_hba.conf to Allow Connections

Now, let's modify the pg_hba.conf file to permit connections.

Open the pg_hba.conf file using your preferred text editor (e.g., nano or vim). Remember to use sudo for elevated privileges.

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

Scroll to the end of the file. You'll see existing entries, typically for local connections.

Understanding pg_hba.conf entries:

Each line follows the format: TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

• TYPE: local (Unix-domain socket), host (TCP/IP connection).
• DATABASE: all, sameuser, samerole, specific database name.
• USER: all, specific user name.
• ADDRESS: IP address or IP range (CIDR format, e.g., 192.168.1.0/24). 0.0.0.0/0 means "any IP address" (use with extreme caution).
• METHOD: md5, scram-sha-256 (password based), trust (no password), peer, ident.

Add the following lines to the end of your pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow connections from the entire WSL2 subnet (e.g., 172.16.0.0/12)
# This is generally safe for WSL2 environments and more resilient to IP changes.
host    all             all             172.16.0.0/12           md5

# Allow connections from your specific Windows Host IP (from Step 2)
# Replace <Windows_Host_IP_from_Step_2> with the actual IP (e.g., 172.24.123.1)
host    all             all             <Windows_Host_IP_from_Step_2>/32    md5

# Allow connections from within the WSL2 instance itself (if using 127.0.0.1 internally)
host    all             all             127.0.0.1/32            md5

• Security Risk with trust: Avoid using trust as an authentication method for any remote or even internal connections unless you fully understand the security implications. It means "connect without a password."
• Security Risk with 0.0.0.0/0: Using 0.0.0.0/0 (any IP address) for remote connections is a major security vulnerability as it allows anyone from anywhere to attempt a connection. Only use this in isolated, non-production development environments if absolutely necessary, and always with a strong password method like md5 or scram-sha-256. The 172.16.0.0/12 range is generally safe for WSL2 as it's an internal virtual network.

Save the file and exit the editor (e.g., Ctrl+X, then Y, then Enter for nano).

4. Verify postgresql.conf for Network Listening

While the "authorization failed" error points to pg_hba.conf, it's good practice to ensure postgresql.conf allows connections on all interfaces. If listen_addresses is only set to localhost, external connections won't even reach pg_hba.conf.

Open postgresql.conf:

sudo nano /etc/postgresql/14/main/postgresql.conf

Look for the listen_addresses directive. Uncomment it if it's commented out (remove the # at the beginning of the line).

Set it to * to listen on all available network interfaces:

listen_addresses = '*'          # what IP address(es) to listen on;
                                # comma-separated list of addresses;
                                # defaults to 'localhost'; use '*' for all
                                # (change requires restart)

Save the file and exit.

5. Restart PostgreSQL Service

For changes to pg_hba.conf and postgresql.conf to take effect, you must restart the PostgreSQL service.

sudo systemctl restart postgresql

To verify the service is running without errors:

sudo systemctl status postgresql

It should show active (exited) or active (running). If you see errors, review your configuration files for typos.

6. Create or Verify PostgreSQL User and Database

Ensure the user and database you are trying to connect with actually exist and have the correct password.

Connect as the postgres superuser:

sudo -u postgres psql

To create a new user (if needed):

CREATE USER myuser WITH PASSWORD 'very_strong_password';

To create a new database (if needed and owned by your user):

CREATE DATABASE mydatabase OWNER myuser;

To set/change a password for an existing user:

ALTER USER myuser WITH PASSWORD 'new_strong_password';

Replace myuser, mydatabase, and very_strong_password with your desired values.

Exit psql:

q

7. Test Connectivity

Now, test if you can connect.

From within your WSL2 instance:

psql -h 127.0.0.1 -U myuser -d mydatabase

If you configured listen_addresses = '*' and allowed the WSL2 IP range, you could also try connecting to the WSL2 IP:

psql -h <Your_WSL2_IP_from_Step_2> -U myuser -d mydatabase

You should be prompted for your password and then connect successfully to the psql prompt.

From your Windows Host (e.g., using DBeaver, VS Code, or other tools):

Use the following connection parameters:

• Host/Server: Your WSL2 IP address (from Step 2, e.g., 172.24.123.45)
• Port: 5432 (default PostgreSQL port)
• Database: mydatabase
• User: myuser
• Password: your_users_password

If you are still experiencing issues, double-check your pg_hba.conf entries for typos, ensure the IP addresses match, and confirm that the PostgreSQL service was restarted. Sometimes, it helps to restart the entire WSL2 instance (wsl --shutdown from PowerShell, then restart your Ubuntu distro).