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

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

When deploying or managing PostgreSQL applications on Ubuntu 22.04 LTS, encountering "connection authorization failed" errors can be a significant roadblock. This issue typically means that PostgreSQL is refusing a connection attempt because its Host-Based Authentication (HBA) rules, defined in the pg_hba.conf file, do not permit the connection from the originating host, user, or database using the specified authentication method. This guide will walk you through diagnosing and resolving these authorization failures.

Symptom & Error Signature

Users attempting to connect to PostgreSQL will typically receive an error message in their application logs or directly in their terminal client (e.g., psql). The exact phrasing can vary slightly depending on the specific reason for the denial, but it generally points to an authorization issue.

Typical Error Output (from psql):

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

Another Common Error (if an entry exists but auth method fails):

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

Application Log Example (e.g., Node.js with pg module):

Error: connect FATAL: no pg_hba.conf entry for host "10.0.0.5", user "webuser", database "webappdb", no encryption
    at Parser.parseErrorMessage (/path/to/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/path/to/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/path/to/node_modules/pg-protocol/dist/parser.js:101:30)
    at Socket.<anonymous> (/path/to/node_modules/pg-protocol/dist/index.js:7:48)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 122,
  severity: 'FATAL',
  code: '28000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'auth.c',
  line: '542',
  routine: 'ClientAuthentication'
}

Root Cause Analysis

The "connection authorization failed" error stemming from pg_hba.conf typically indicates one of the following underlying issues:

1. Missing or Incorrect pg_hba.conf Entry: The most common cause. PostgreSQL processes pg_hba.conf entries sequentially from top to bottom, stopping at the first matching rule. If no rule explicitly permits a connection from the client's IP address, for the specified user and database, using an acceptable authentication method, the connection is rejected.

   • Incorrect ADDRESS: The client's IP address or subnet is not correctly specified in any rule.
   • Incorrect USER or DATABASE: The connecting user or requested database does not match any entry.
   • Incorrect METHOD: The client is attempting to authenticate with a method (e.g., password) that is not allowed by the matching pg_hba.conf entry (e.g., ident, peer, or trust).

2. listen_addresses Misconfiguration: While pg_hba.conf handles authorization, PostgreSQL must first be configured to listen for connections on the network interface the client is trying to reach. If listen_addresses in postgresql.conf is set to localhost or 127.0.0.1 only, external connections will be refused before pg_hba.conf is even consulted, usually resulting in a "connection refused" error rather than an authorization error. However, it's a critical prerequisite for remote pg_hba.conf issues to manifest.

3. Firewall Blocking Port 5432: A firewall (e.g., UFW on Ubuntu, AWS Security Groups, Google Cloud Firewall Rules) can block TCP port 5432 (PostgreSQL's default port), preventing the connection from ever reaching the PostgreSQL server. Like listen_addresses, this usually results in "connection refused" but is an essential check.

4. Incorrect User Password: If a pg_hba.conf entry does match, but the authentication method is md5 or scram-sha-256, and the provided password is wrong, PostgreSQL will return a "password authentication failed" error. While distinct from "no pg_hba.conf entry", it's a common authorization problem.

Step-by-Step Resolution

Follow these steps to diagnose and resolve pg_hba.conf connection authorization failures.

1. Verify PostgreSQL Service Status

Ensure the PostgreSQL service is running correctly on your Ubuntu server.

sudo systemctl status postgresql

You should see output indicating active (exited) for older versions or active (running) for modern Systemd setups, typically ending with a success message. If it's not running, start it:

sudo systemctl start postgresql

2. Locate PostgreSQL Configuration Files

The pg_hba.conf and postgresql.conf files are crucial. Their exact location can vary slightly depending on the PostgreSQL version and installation method, but on Ubuntu 22.04, it's typically within /etc/postgresql/{VERSION}/main/.

You can find their paths by connecting locally (if possible) 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/14/main/postgresql.conf
(1 row)

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

For the remainder of this guide, we'll assume the path /etc/postgresql/14/main/. Adjust {VERSION} as necessary for your installation.

3. Adjust postgresql.conf listen_addresses

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

1. Open postgresql.conf for editing:

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

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

   • To listen on all available network interfaces (for development or specific environments where security is handled by firewalls), set:

     listen_addresses = '*'
     

   • To listen on localhost and a specific IP address (e.g., your application server's internal IP):

     listen_addresses = 'localhost,192.168.1.100'
     

3. Save the file (Ctrl+O, then Enter, then Ctrl+X).

4. Restart PostgreSQL to apply changes:

   sudo systemctl restart postgresql
   

4. Configure pg_hba.conf for Authorization (The Core Fix)

This is the most critical step. You'll need to add or modify entries in pg_hba.conf to allow your client connections.

The pg_hba.conf file is read from top to bottom. The first matching rule is used. Place more specific rules before more general ones.

1. Open pg_hba.conf for editing:

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

2. Understand the pg_hba.conf format:

   Each line has the following fields: TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

   • TYPE:
     • local: For Unix-domain socket connections (local to the server).
     • host: For TCP/IP connections.
     • hostssl: For TCP/IP connections that require SSL.
     • hostnossl: For TCP/IP connections that specifically reject SSL.
   • DATABASE:
     • all: Applies to all databases.
     • Specific database name (e.g., my_app_db).
     • sameuser: Connect if the requested database has the same name as the requested user.
     • samerole: Connect if the requested database has the same name as an existing role that the requested user is a member of.
   • USER:
     • all: Applies to all users.
     • Specific user name (e.g., my_app_user).
     • +groupname: Applies to members of the specified group.
   • ADDRESS:
     • For local connections: This field is not present.
     • For host connections: The client IP address or network range in CIDR format (e.g., 192.168.1.100/32 for a single IP, 192.168.1.0/24 for a subnet, or 0.0.0.0/0 for all IPv4 addresses).
   • METHOD: The authentication method.
     • scram-sha-256: (Recommended for new setups) Secure password-based authentication. Requires users to have passwords set.
     • md5: (Legacy password-based) Less secure than SCRAM. Requires users to have passwords set.
     • trust: Allows anyone to connect without a password (DANGEROUS for remote access!).
     • peer: For local connections, uses the operating system's username.
     • ident: For host connections, relies on an Ident server on the client.
     • Other methods exist (e.g., gssapi, ssi, cert), but scram-sha-256 or md5 are most common for web applications.
   • OPTIONS: Additional method-specific options (e.g., map=mymap).

3. Add a new entry for your application client.

   • Example 1: Allow local connections (Unix socket for psql or local apps) This is often present by default.

     local   all             all                                     peer
     

   • Example 2: Allow remote connections from a specific IP using SCRAM-SHA-256 (Recommended) Replace 192.168.1.100 with your client's IP address, my_app_user with your database user, and my_app_db with your database name.

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

   • Example 3: Allow remote connections from a specific subnet using MD5 If you have multiple application servers in a subnet, or older clients.

     # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host    my_app_db       my_app_user     192.168.1.0/24          md5
     

   • Example 4: Allow all remote connections from anywhere using SCRAM-SHA-256 (Use with extreme caution!) This is generally not recommended for production environments without robust firewalling.

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

     Using 0.0.0.0/0 with any password-based method (md5, scram-sha-256) is a significant security risk if your database is publicly accessible and not protected by a strong firewall. Avoid trust with 0.0.0.0/0 in production entirely, as it allows any connection without a password. Always prefer specific IP addresses or subnets.

4. Save the pg_hba.conf file (Ctrl+O, then Enter, then Ctrl+X).

5. Restart PostgreSQL Service

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

sudo systemctl restart postgresql

Verify it restarted successfully:

sudo systemctl status postgresql

6. Verify Database User and Password

If your pg_hba.conf entry uses md5 or scram-sha-256, the connecting user must exist and have a password set.

1. Connect to PostgreSQL as the postgres superuser locally:

   sudo -u postgres psql
   

2. Check if your user exists (replace my_app_user):

   du;
   

   If the user doesn't exist, create it:

   CREATE USER my_app_user WITH PASSWORD 'your_strong_password_here';
   

   Always use strong, unique passwords for database users.

3. If the user exists but you need to set or reset their password:

   ALTER USER my_app_user WITH PASSWORD 'your_strong_password_here';
   

4. Grant necessary permissions to your user on the database (replace my_app_db):

   GRANT ALL PRIVILEGES ON DATABASE my_app_db TO my_app_user;
   -- Or more granular permissions for production
   -- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO my_app_user;
   -- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO my_app_user;
   

5. Exit psql:

   q
   

7. Firewall Check (Optional but Recommended)

Ensure your server's firewall (e.g., UFW on Ubuntu) is allowing connections to PostgreSQL's default port (5432).

1. Check UFW status:

   sudo ufw status
   

2. If UFW is active and not explicitly allowing port 5432 from your client's IP, add a rule:

   sudo ufw allow from 192.168.1.100 to any port 5432 comment 'Allow PostgreSQL from App Server'
   

   Or, if you must allow from anywhere (use with caution, combine with pg_hba.conf strictness):

   sudo ufw allow 5432/tcp comment 'Allow PostgreSQL from anywhere (restrict with pg_hba.conf)'
   

   Opening port 5432 to any (or 0.0.0.0/0) in your firewall is a security risk. Always restrict access to known, trusted IP addresses or networks when possible.

8. Test Connection

From your client machine or application server, attempt to connect again:

psql -h YOUR_POSTGRES_SERVER_IP -U my_app_user -d my_app_db

You should now be prompted for the password and then connect successfully. If the error persists, double-check each step, paying close attention to typos in IP addresses, usernames, and database names in pg_hba.conf. Check the PostgreSQL logs (/var/log/postgresql/postgresql-14-main.log or similar) for more detailed error messages.