Troubleshooting PostgreSQL pg_hba.conf Connection Authorization Failed on Ubuntu 20.04 LTS

Resolve PostgreSQL 'connection authorization failed' errors on Ubuntu 20.04 LTS by correctly configuring pg_hba.conf for secure and authorized database access.

Resolve PostgreSQL 'connection authorization failed' errors on Ubuntu 20.04 LTS by correctly configuring pg_hba.conf for secure and authorized database access.

Introduction

As an experienced Systems Administrator, you've likely encountered the frustrating "connection authorization failed" error when attempting to connect to your PostgreSQL database. This error is a clear indicator that PostgreSQL, by design, is preventing a client from establishing a connection. It's a security mechanism, not a bug. On Ubuntu 20.04 LTS, this issue almost always stems from an incorrectly configured pg_hba.conf file, which dictates PostgreSQL's client authentication rules. This guide will walk you through diagnosing and resolving these common connection problems.

### Symptom & Error Signature

When PostgreSQL denies a connection due to pg_hba.conf misconfiguration, you'll typically observe one of the following error messages, either directly from the psql client, within your application's logs, or in the PostgreSQL server logs:

From psql client:

psql: error: FATAL:  Peer authentication failed for user "your_username"

psql: error: FATAL:  password authentication failed for user "your_username"

psql: error: FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "your_username", database "your_database", no encryption

From Application Logs (e.g., Python/Django, Node.js, PHP):

django.db.utils.OperationalError: FATAL:  password authentication failed for user "your_username"

org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "fe80::...:b024", user "your_username", database "your_database", no encryption

From PostgreSQL Server Logs (/var/log/postgresql/postgresql-12-main.log or similar):

LOG:  connection received: host=[local] port=XXXXX
FATAL:  Peer authentication failed for user "your_username"

LOG:  connection received: host=192.168.1.100 port=XXXXX
FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "your_username", database "your_database", no encryption

### Root Cause Analysis

The "connection authorization failed" error directly points to a mismatch between the client's connection attempt and the rules defined in PostgreSQL's Host-Based Authentication (HBA) configuration file, pg_hba.conf. This file is PostgreSQL's gatekeeper, specifying who can connect, from where, to which database, as which user, and how they must authenticate.

Here are the primary underlying reasons for this error:

Incorrect Host/IP Address Mapping: The client's source IP address or hostname is not explicitly listed or covered by a network range (CIDR) in any pg_hba.conf rule that permits access.
Mismatched Database or User: The database or user specified in the connection string does not match any allowed entry in pg_hba.conf. Remember all acts as a wildcard.
Incorrect Authentication Method: The method specified in pg_hba.conf (e.g., peer, md5, scram-sha-256, trust, ident) does not align with how the client is trying to authenticate, or the client is not providing the expected credentials (e.g., a password when md5 or scram-sha-256 is required).
- peer: Default for local connections on Unix sockets. Requires the connecting OS user to match the PostgreSQL database user.
- md5/scram-sha-256: Requires a password to be sent by the client, which is then hashed and compared against the stored hash. scram-sha-256 is generally more secure.
- trust: Grants access without any password. Highly insecure for anything other than very specific, isolated local use cases or debugging.
- ident: Relies on an ident server lookup on the client machine. Less common for modern applications.
Order of Rules: pg_hba.conf rules are processed sequentially from top to bottom. The first matching rule determines access. A too-broad reject rule or an insufficiently specific allow rule placed too high or too low can inadvertently block legitimate connections.
listen_addresses Configuration: While not directly pg_hba.conf, if PostgreSQL isn't configured to listen on the correct network interface(s) in postgresql.conf, remote connections won't even reach pg_hba.conf for evaluation. The server simply won't respond.
Configuration Not Reloaded: Any changes to pg_hba.conf or postgresql.conf require PostgreSQL to be reloaded (or restarted) for the new rules to take effect.
PostgreSQL User Does Not Exist: Even with correct pg_hba.conf entries, the connection will fail if the specified PostgreSQL user does not exist in the database.

### Step-by-Step Resolution

Follow these steps meticulously to diagnose and resolve your PostgreSQL connection authorization issues.

1. Locate `pg_hba.conf` and `postgresql.conf`

First, you need to find the correct configuration files for your PostgreSQL instance. On Ubuntu 20.04 LTS, with PostgreSQL 12, they are typically located in /etc/postgresql/12/main/.

You can confirm the paths using psql if you can connect locally (e.g., as the postgres user):

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

This will output something like:

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

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

Alternatively, you can use find:

sudo find /etc/postgresql/ -name "pg_hba.conf"
sudo find /etc/postgresql/ -name "postgresql.conf"

2. Review PostgreSQL Server Logs

The PostgreSQL server logs are your most valuable diagnostic tool. They will tell you exactly why a connection was rejected.

sudo tail -f /var/log/postgresql/postgresql-12-main.log

While running tail -f, attempt to connect from your client application or psql. Observe the logs for specific FATAL errors, noting the client IP address, database, and user mentioned. This information is critical for crafting the correct pg_hba.conf rule.

3. Verify `listen_addresses` in `postgresql.conf` (For Remote Connections)

If you're trying to connect remotely (from a different server or even a Docker container), PostgreSQL must be configured to listen on the appropriate network interface. By default, it often listens only on localhost.

Open postgresql.conf for editing:

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

Find the listen_addresses directive.

To listen only on localhost (for local TCP/IP connections):
```
listen_addresses = 'localhost'
```
To listen on a specific IP address (e.g., your server's private IP):
```
listen_addresses = 'localhost, 192.168.1.10'
```
To listen on all available network interfaces (less secure, but sometimes necessary for testing or complex network setups):
```
listen_addresses = '*'
```

Setting listen_addresses = '*' makes your PostgreSQL server accessible from any network interface. This should only be done in conjunction with strict pg_hba.conf rules and proper firewall (UFW/IPTables) configurations to limit access to trusted IPs.

If you change listen_addresses, you must restart the PostgreSQL service for the changes to take effect.

4. Edit `pg_hba.conf` to Allow Connections

Now, based on the error messages from your logs, you'll modify pg_hba.conf. Open the file:

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

Here are common scenarios and how to configure them:

The general format for a rule is: TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

TYPE: local (Unix domain socket), host (TCP/IP), hostssl (TCP/IP with SSL), hostnossl (TCP/IP without SSL).
DATABASE: Specific database name, all, sameuser, samerole.
USER: Specific user name, all.
ADDRESS: Client IP address or network range in CIDR format (e.g., 192.168.1.100/32 for a single host, 192.168.1.0/24 for a subnet), 0.0.0.0/0 for all IPv4, ::/0 for all IPv6. For local connections, this field is not used.
METHOD: peer, ident, md5, scram-sha-256, trust, reject, etc.

Scenario A: Local Unix Socket Connection (e.g., psql directly on the server)

The default local rule often uses peer authentication. This means your current Linux user must match the PostgreSQL database user you're trying to connect as.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

If your OS user is ubuntu and you want to connect as PostgreSQL user app_user, peer will fail. You'd need to sudo -u postgres psql and then CREATE USER ubuntu; or use an alternative method.

Scenario B: Local TCP/IP Connection (e.g., an application on the same server connecting via 127.0.0.1)

You'll need a host rule for 127.0.0.1. Using md5 or scram-sha-256 is typical.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

This allows any user (all) to connect to any database (all) from localhost using md5 password authentication.

Scenario C: Remote TCP/IP Connection (e.g., an application server at 192.168.1.100)

This is the most common scenario for "no pg_hba.conf entry" errors. You need to add a specific host rule for your client's IP address and desired authentication method.

Rules are processed in order. Place specific rules before more general rules. For example, a rule to reject a specific IP should come before a rule to allow a broader subnet.

Example 1: Allowing a single host with scram-sha-256 (recommended for security)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    my_app_db       app_user        192.168.1.100/32        scram-sha-256

This allows the app_user to connect to my_app_db from 192.168.1.100 using scram-sha-256 password authentication.

Example 2: Allowing an entire subnet with md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          md5

This allows any user to any database from any host in the 192.168.1.0/24 subnet using md5 password authentication.

Example 3: Allowing all connections (highly insecure, for temporary debugging only)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5

Using 0.0.0.0/0 or ::/0 with md5/scram-sha-256 makes your database accessible from anywhere on the internet, assuming your firewall allows it. This is generally discouraged without strict network segmentation and strong password policies. Never use trust with these broad IP ranges in production.

Combined Example of pg_hba.conf entries:

# PostgreSQL Client Authentication Configuration File
# =================================================

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow specific application server for a specific DB and user
host    my_prod_db      prod_user       10.0.0.50/32            scram-sha-256

# Allow development team's subnet for all databases/users
host    all             all             192.168.10.0/24         md5

# Allow local connections via Unix domain socket
local   all             all                                     peer

# Allow local connections via TCP/IP
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

# Explicitly reject a problematic IP address (MUST be above a general allow rule)
# host    all             all             192.168.1.200/32        reject

# DENY ALL OTHER CONNECTIONS (optional, implicit if no other rule matches)
# host    all             all             0.0.0.0/0               reject
# host    all             all             ::/0                    reject

5. Reload PostgreSQL Service

After making any changes to pg_hba.conf, PostgreSQL needs to reload its configuration.

To reload configuration without dropping active connections (preferred for pg_hba.conf changes):

sudo systemctl reload postgresql

If you changed listen_addresses in postgresql.conf (or if reload reports an error), you must restart the service. This will temporarily drop all active connections.

sudo systemctl restart postgresql

Always attempt reload first. If reload fails or the changes (like listen_addresses) specifically require it, then proceed with restart.

6. Test the Connection

Once PostgreSQL has reloaded/restarted, attempt to connect again from your client application or psql command.

From a remote server:

psql -h <POSTGRES_SERVER_IP> -U your_username -d your_database

If the connection is successful, you'll be prompted for a password (if using md5/scram-sha-256) and then enter the psql prompt. Check your application logs for successful database connections.

7. Create/Manage PostgreSQL Users (If applicable)

Even if pg_hba.conf is perfectly configured, a connection will fail if the PostgreSQL user does not exist or has an incorrect password.

Connect to PostgreSQL as the postgres superuser locally:

sudo -u postgres psql

Then, you can:

Create a new user:
```
CREATE USER your_username WITH PASSWORD 'your_strong_password';
```
Replace 'your_strong_password' with a genuinely secure, unique password.

Change a user's password:

ALTER USER your_username WITH PASSWORD 'new_strong_password';

Grant privileges to a user:

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;
-- Or more granular:
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_username;
-- GRANT USAGE, CREATE ON SCHEMA public TO your_username;

List existing users:
```
du
```

Once done, exit psql:

By meticulously following these steps, you should be able to identify and rectify any pg_hba.conf related connection authorization issues on your Ubuntu 20.04 LTS PostgreSQL server.