Troubleshooting PostgreSQL: FATAL pg_hba.conf Connection Authorization Failed on Alpine Linux

Resolve 'FATAL: no pg_hba.conf entry' errors for PostgreSQL connections on Alpine Linux. This guide covers configuration, network, and service checks.

When attempting to connect to a PostgreSQL database on an Alpine Linux server, you might encounter a "FATAL: no pg_hba.conf entry" error. This highly technical guide will walk you through diagnosing and resolving this common connection authorization issue, ensuring your PostgreSQL server correctly accepts client connections.

Symptom & Error Signature

The most common symptom is a client application, command-line tool (psql), or web service failing to establish a connection to the PostgreSQL database. You will typically see an error message similar to these examples in your client, application logs, or the PostgreSQL server logs:

Client-side (psql command example):

$ psql -h your_db_host -U your_db_user -d your_db_name
psql: error: connection to server at "your_db_host" (XXX.XXX.XXX.XXX), port 5432 failed: FATAL:  no pg_hba.conf entry for host "XXX.XXX.XXX.XXX", user "your_db_user", database "your_db_name", no encryption

Application Log Example (Node.js/Python/PHP using a PostgreSQL driver):

ERROR: Failed to connect to database: FATAL: no pg_hba.conf entry for host "YYY.YYY.YYY.YYY", user "app_user", database "app_db", no encryption

PostgreSQL Server Log Example (found in /var/lib/postgresql/data/log/ or similar):

LOG:  connection received: host=YYY.YYY.YYY.YYY port=ZZZZZ
FATAL:  no pg_hba.conf entry for host "YYY.YYY.YYY.YYY", user "app_user", database "app_db", no encryption

The key phrase here is FATAL: no pg_hba.conf entry for host.

Root Cause Analysis

The pg_hba.conf (Host-Based Authentication) file is PostgreSQL's primary mechanism for controlling client authentication. When you encounter a "no pg_hba.conf entry" error, it means the PostgreSQL server explicitly denied the connection attempt because it couldn't find a matching rule in this file for the incoming connection's parameters.

The underlying reasons typically fall into one or more of the following categories:

1. Missing or Incorrect pg_hba.conf Entry: The most common cause. There isn't an entry in pg_hba.conf that precisely matches the client's connection attempt based on:

   • Connection Type: local (Unix socket), host (TCP/IP for IPv4), hostssl (TCP/IP with SSL), hostnossl (TCP/IP without SSL).
   • Database: The specific database the client is trying to connect to (e.g., your_db_name, all).
   • User: The PostgreSQL user attempting to connect (e.g., your_db_user, all).
   • Client Address: The IP address or network range from which the client is connecting (e.g., 127.0.0.1/32, 0.0.0.0/0, 192.168.1.0/24).
   • Authentication Method: The method requested by the client or configured in the rule (e.g., md5, scram-sha-256, trust, peer).

2. listen_addresses Misconfiguration: The PostgreSQL server is not configured to listen for connections on the IP address the client is trying to reach. By default, PostgreSQL often listens only on localhost (127.0.0.1) for security reasons. If your client is connecting from a remote machine, or even from another Docker container, the server must listen on * or its external IP address. This is configured in postgresql.conf.

3. Firewall Restrictions: Even if pg_hba.conf and listen_addresses are correct, an active firewall (like iptables on Alpine) might be blocking incoming connections on port 5432 (PostgreSQL's default port).

4. PostgreSQL Service Not Reloaded: After making changes to pg_hba.conf or postgresql.conf, the PostgreSQL service must be reloaded for the changes to take effect.

5. Incorrect File Permissions/Location: While less common, ensuring pg_hba.conf has correct permissions and is in the expected location for the PostgreSQL instance is crucial.

Step-by-Step Resolution

Follow these steps carefully to diagnose and resolve the connection authorization issue on your Alpine Linux system.

1. Locate pg_hba.conf and postgresql.conf

First, you need to find the active configuration files for your PostgreSQL instance. The location can vary depending on how PostgreSQL was installed.

# As the postgres user (or using sudo -u postgres)
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

Typical locations on Alpine Linux after installation via apk add postgresql-server are:

• pg_hba.conf: /var/lib/postgresql/data/pg_hba.conf
• postgresql.conf: /var/lib/postgresql/data/postgresql.conf

Make a backup of pg_hba.conf before making any changes:

sudo cp /var/lib/postgresql/data/pg_hba.conf /var/lib/postgresql/data/pg_hba.conf.bak

2. Verify PostgreSQL Service Status

Ensure the PostgreSQL service is running and accessible locally. Alpine Linux primarily uses OpenRC for service management.

sudo rc-service postgresql status

You should see output indicating postgresql is running. If not, start it:

sudo rc-service postgresql start

Now, try connecting locally as the postgres user to confirm the basic installation is functional:

sudo -u postgres psql
# You should get a psql prompt: postgres=#
# To exit: q

3. Adjust listen_addresses in postgresql.conf

If your client is connecting from a different machine or even a different Docker container, PostgreSQL needs to listen on an IP address other than just localhost.

1. Open postgresql.conf for editing:

   sudo vim /var/lib/postgresql/data/postgresql.conf
   

2. Locate the listen_addresses parameter. Uncomment it if it's commented out (starts with #).

   • To listen on all available network interfaces (most common for remote connections):

     listen_addresses = '*'
     

   • To listen on a specific IP address:

     listen_addresses = '192.168.1.100'
     

   If listen_addresses is set to 'localhost' or '127.0.0.1', remote clients will not be able to connect via TCP/IP, even if pg_hba.conf allows it.

3. Save and close the file.

4. Configure pg_hba.conf for Client Access

This is the most critical step. You need to add an entry (or modify an existing one) that matches your client's connection parameters. pg_hba.conf entries are read sequentially, so the first matching rule is applied.

1. Open pg_hba.conf for editing:

   sudo vim /var/lib/postgresql/data/pg_hba.conf
   

2. Add or modify entries according to your requirements. Here are common scenarios:

   • Connecting from the same machine (local TCP/IP): If your application connects via localhost (127.0.0.1) but not via a Unix socket, you need this:

     # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host    all             all             127.0.0.1/32            scram-sha-256
     

     • all for DATABASE means it applies to all databases.
     • all for USER means it applies to all users.
     • 127.0.0.1/32 specifies the exact localhost IP.
     • scram-sha-256 is a secure password-based authentication method. md5 is also common but less secure.

   • Connecting from any remote IPv4 address (least secure): This allows connections from any IPv4 address. Use with extreme caution and strong passwords.

     # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host    all             all             0.0.0.0/0               scram-sha-256
     

     • 0.0.0.0/0 means any IPv4 address.

   • Connecting from a specific remote IPv4 address or subnet: This is a more secure approach for known client IPs.

     # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host    your_db_name    your_db_user    192.168.1.10/32         scram-sha-256
     host    all             app_user        192.168.1.0/24          md5
     

     • 192.168.1.10/32 for a single host.
     • 192.168.1.0/24 for a subnet (e.g., all IPs from 192.168.1.1 to 192.168.1.254).

   • Using local for Unix socket connections (if you need it explicit): This is for connections originating from the same system via Unix domain sockets. Often present by default.

     # TYPE  DATABASE        USER            ADDRESS                 METHOD
     local   all             all                                     peer
     

     • peer authentication uses the operating system's user name to authenticate.

   Using trust as an authentication method means that anyone who can connect to the PostgreSQL server can connect without a password for the specified database and user. This is highly insecure and should only be used in isolated development environments or for very specific, trusted local connections.

   When adding new rules, consider the order. pg_hba.conf rules are evaluated from top to bottom. The first rule that matches the connection parameters will be used. Place more specific rules above more general rules (e.g., a specific IP rule before a 0.0.0.0/0 rule).

3. Save and close pg_hba.conf.

5. Reload PostgreSQL Service

After modifying postgresql.conf or pg_hba.conf, you must reload the PostgreSQL service for the changes to take effect. Restarting is also an option, but a reload is generally sufficient and less disruptive.

sudo rc-service postgresql reload

If you prefer using pg_ctl directly as the postgres user:

sudo -u postgres pg_ctl reload -D /var/lib/postgresql/data

Check the PostgreSQL server logs (/var/lib/postgresql/data/log/postgresql-YYYY-MM-DD_HHMMSS.log) for messages confirming the configuration reload.

6. Check Firewall Rules (if connecting remotely)

If you're connecting from a remote machine, ensure that your Alpine Linux server's firewall (likely iptables) is configured to allow incoming connections on TCP port 5432.

To temporarily allow port 5432 (for testing, will be reset on reboot):

sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

To make it persistent, you'll need to save your iptables rules. Alpine uses iptables-save and iptables-restore with a service like iptables or nftables.

sudo sh -c 'iptables-save > /etc/iptables/rules.v4' # For IPv4
sudo rc-service iptables save # If iptables service is installed and configured to manage rules

Always be cautious when modifying firewall rules, especially on production servers. Ensure you understand the implications and don't inadvertently expose other services.

7. Test Connection

Finally, attempt to connect to your PostgreSQL database again using your client application or psql command:

psql -h your_db_host -U your_db_user -d your_db_name

Replace your_db_host with the IP address or hostname of your Alpine server, your_db_user with the PostgreSQL username, and your_db_name with the database name. You should now be prompted for a password and then gain access to the psql prompt.

If you continue to face issues, review the PostgreSQL server logs for any new FATAL errors or warnings, which will provide further clues for debugging.