Troubleshooting PostgreSQL pg_hba.conf Connection Authorization Failed on Debian 12 Bookworm

Resolve PostgreSQL `pg_hba.conf` connection authorization errors on Debian 12 Bookworm. Configure client authentication for secure database access.

When attempting to connect to a PostgreSQL database on your Debian 12 server, you might encounter a "connection authorization failed" error. This issue typically prevents your applications, database clients, or even psql from establishing a session, indicating a misconfiguration in PostgreSQL's client authentication settings. This guide will walk you through diagnosing and resolving pg_hba.conf related connection failures.

Symptom & Error Signature

The most common symptom is an immediate connection refusal with a fatal error message. You might see this in your terminal when using psql, or within your application logs.

psql Client Error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  "no pg_hba.conf entry for host "[local or client_IP]" user "youruser" database "yourdatabase""
psql: error: connection to server at "your_server_ip" (your_server_ip), port 5432 failed: FATAL:  "password authentication failed for user "youruser""

PostgreSQL Server Log Entry (e.g., /var/log/postgresql/postgresql-15-main.log):

2023-10-27 10:30:45.123 UTC [1234] LOG:  "connection received: host=[client_IP] port=12345"
2023-10-27 10:30:45.123 UTC [1234] FATAL:  "no pg_hba.conf entry for host "[client_IP]" user "youruser" database "yourdatabase""
2023-10-27 10:30:45.123 UTC [1234] DETAIL:  "Host "[client_IP]", user "youruser", database "yourdatabase", no authentication method specified."

Or, if the entry exists but the method is wrong:

2023-10-27 10:35:10.567 UTC [5678] FATAL:  "password authentication failed for user "youruser""
2023-10-27 10:35:10.567 UTC [5678] DETAIL:  "User "youruser" has no password assigned."

Root Cause Analysis

The "connection authorization failed" error primarily points to an issue with PostgreSQL's Host-Based Authentication (HBA) configuration, defined in the pg_hba.conf file. This file dictates which clients are allowed to connect to which databases as which users, and what authentication method they must use.

The root causes typically fall into one of these categories:

1. Missing pg_hba.conf Entry: There is no rule in pg_hba.conf that matches the incoming connection's parameters (connection type, client IP address, target database, and connecting user).
2. Incorrect pg_hba.conf Entry: An entry exists, but one or more of its parameters are incorrect:
   • IP Address/Range: The client's IP address is not covered by the ADDRESS field.
   • Database Name: The requested database name does not match the DATABASE field.
   • User Name: The connecting user does not match the USER field.
   • Authentication Method: The specified METHOD (e.g., md5, scram-sha-256, peer, ident) does not match the client's attempt or the user's configuration (e.g., user lacks a password for password-based methods).
3. PostgreSQL Not Listening on Correct Interface: While not directly a pg_hba.conf error, if listen_addresses in postgresql.conf is set to localhost (127.0.0.1) and you're trying to connect remotely, the connection won't even reach the HBA check.
4. Firewall Blocking Connection: Similar to listen_addresses, if a firewall (like UFW or iptables) is blocking port 5432, the connection will be refused before PostgreSQL can even process it. However, a pg_hba.conf error specifically means the connection reached PostgreSQL, but was rejected by its internal authorization rules.

Step-by-Step Resolution

Follow these steps to diagnose and correct the pg_hba.conf configuration on your Debian 12 server.

1. Identify PostgreSQL Version and Data Directory

First, determine your PostgreSQL version and its data directory. This is crucial for locating the correct configuration files. On Debian, PostgreSQL instances are managed via systemd services like postgresql@15-main.

# Check installed PostgreSQL client version
dpkg -l | grep postgresql-client

# Determine the PostgreSQL data directory (important for pg_hba.conf path)
sudo -u postgres psql -c "SHOW data_directory;"

The output for SHOW data_directory; will typically be /var/lib/postgresql/15/main. Based on this, your pg_hba.conf and postgresql.conf files will usually be located in /etc/postgresql/15/main/.

2. Backup pg_hba.conf

Always create a backup of critical configuration files before making any modifications. This allows for easy rollback if an error occurs.

sudo cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak

3. Edit pg_hba.conf

Open pg_hba.conf using your preferred text editor (e.g., nano or vim):

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

The pg_hba.conf file uses the following format for each rule:

TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

Let's break down common scenarios and add appropriate entries.

• TYPE:
  • local: For Unix-domain socket connections (local connections only).
  • host: For TCP/IP connections (both IPv4 and IPv6).
  • hostssl: For TCP/IP connections that use SSL.
  • hostnossl: For TCP/IP connections that do not use SSL.
• DATABASE:
  • all: Applies to all databases.
  • samename: Applies to databases with the same name as the connecting user.
  • A specific database name (e.g., mydatabase).
  • replication: For replication connections.
• USER:
  • all: Applies to all users.
  • A specific user name (e.g., myuser).
  • +groupname: Applies to all users in a specific group.
• ADDRESS:
  • 127.0.0.1/32: IPv4 loopback (localhost only).
  • ::1/128: IPv6 loopback.
  • 192.168.1.0/24: An IPv4 subnet.
  • 10.0.0.100/32: A specific IPv4 address.
  • 0.0.0.0/0: All IPv4 addresses (highly insecure for remote access).
  • ::/0: All IPv6 addresses (highly insecure for remote access).
• METHOD:
  • peer: (local only) Authenticate using operating system credentials.
  • ident: (local only) Authenticate using an Ident server (rarely used now).
  • md5: Password authentication using MD5 hashing.
  • scram-sha-256: Stronger, recommended password authentication.
  • trust: No authentication required (highly insecure).
  • reject: Explicitly deny connections.

Common Scenarios and Recommended Entries:

• Allow local connections (Unix socket for psql, local applications): This is usually present and essential.

  local   all     all                                     peer
  

• Allow local connections (IPv4 loopback, e.g., 127.0.0.1): For applications connecting via TCP/IP on the same host. scram-sha-256 is recommended.

  host    all     all     127.0.0.1/32            scram-sha-256
  

• Allow remote connections from a specific IP address: Replace CLIENT_IP_ADDRESS with the exact IP of your client machine or application server.

  host    yourdatabase    youruser    CLIENT_IP_ADDRESS/32    scram-sha-256
  

  Example: host myappdb appuser 203.0.113.42/32 scram-sha-256

• Allow remote connections from a specific subnet: Replace NETWORK_ADDRESS/CIDR with your client network (e.g., 192.168.1.0/24).

  host    yourdatabase    youruser    NETWORK_ADDRESS/CIDR    scram-sha-256
  

  Example: host myappdb appuser 192.168.1.0/24 scram-sha-256

• Allow connections from Docker containers (often use a bridge network): The Docker bridge network often uses 172.17.0.0/16 by default. Verify your Docker network range.

  host    all     all     172.17.0.0/16           scram-sha-256
  

• Always place your specific rules before any generic reject rules or overly permissive trust rules that might override them.
• scram-sha-256 is the most secure password authentication method. Ensure your PostgreSQL users have passwords set: sudo -u postgres psql -c "ALTER USER youruser WITH PASSWORD 'your_strong_password';"
• Avoid using trust for anything other than specific, highly controlled local administrative tasks.
• Using 0.0.0.0/0 or ::/0 with scram-sha-256 or md5 allows password-based access from any IP address. This is less secure than restricting to specific IPs/subnets but necessary for some cloud deployments where client IPs are dynamic (though a VPN or cloud security groups are preferred).

4. Verify listen_addresses in postgresql.conf

If you intend to connect remotely, PostgreSQL must be configured to listen on network interfaces other than just localhost.

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

Find the listen_addresses parameter.

• To listen only on localhost: listen_addresses = 'localhost' (default)
• To listen on specific IP addresses (e.g., localhost and one public IP): listen_addresses = 'localhost,192.0.2.10'
• To listen on all available network interfaces (useful for servers, but often coupled with strict pg_hba.conf and firewall rules): listen_addresses = '*'

If you change listen_addresses, a full PostgreSQL restart is required, not just a reload.

5. Reload/Restart PostgreSQL

After modifying pg_hba.conf, you need to tell PostgreSQL to reload its configuration. If you changed postgresql.conf (specifically listen_addresses), a full restart is necessary.

• For pg_hba.conf changes only (preferred):

  sudo systemctl reload postgresql@15-main
  

• For postgresql.conf changes (e.g., listen_addresses) or if a reload doesn't suffice:

  sudo systemctl restart postgresql@15-main
  

• Check service status:

  sudo systemctl status postgresql@15-main
  

6. Test the Connection

Now, try connecting again using your psql client or application.

• Local connection (Unix socket):

  psql -U youruser -d yourdatabase
  

• Local connection (TCP/IP):

  psql -U youruser -d yourdatabase -h localhost
  

• Remote connection:

  psql -U youruser -d yourdatabase -h your_postgres_server_ip
  

If the connection is successful, you're good to go! If not, review the PostgreSQL logs (/var/log/postgresql/postgresql-15-main.log) for more specific errors.

7. Check Firewall Rules (If still facing issues)

If you've verified pg_hba.conf and listen_addresses, but remote connections still fail (especially if the connection hangs or times out before giving a pg_hba.conf error), your server's firewall might be blocking port 5432.

Using UFW (Uncomplicated Firewall):

# Check UFW status
sudo ufw status verbose

# Allow incoming TCP connections on port 5432 from a specific IP
sudo ufw allow from CLIENT_IP_ADDRESS to any port 5432 proto tcp

# Or allow from a specific subnet
sudo ufw allow from NETWORK_ADDRESS/CIDR to any port 5432 proto tcp

# Or allow from anywhere (use with caution, combine with strict pg_hba.conf)
sudo ufw allow 5432/tcp

# Reload UFW to apply changes
sudo ufw reload

After adjusting firewall rules, try testing the connection again.