Troubleshoot and resolve 'connection authorization failed' errors in PostgreSQL on CentOS Stream and Rocky Linux by correctly configuring pg_hba.conf and related settings.

When working with PostgreSQL on CentOS Stream or Rocky Linux, encountering a "connection authorization failed" error indicates that the database server successfully received your connection request but explicitly denied it based on its access control rules. This guide provides a comprehensive, expert-level approach to diagnose and resolve this common issue, ensuring your applications and users can connect securely.

Symptom & Error Signature

The primary symptom is an inability to connect to your PostgreSQL database, typically from a client application, a command-line psql utility, or another server. You will usually see a FATAL error message.

Typical psql command line error:

$ psql -h your_db_host -U your_db_user -d your_db_name
psql: FATAL:  connection authorization failed for user "your_db_user"
psql: FATAL:  no pg_hba.conf entry for host "your_client_ip", user "your_db_user", database "your_db_name", no encryption

Common application error (e.g., Python with psycopg2):

# Example output from a Python application attempting to connect
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.OperationalError: FATAL:  connection authorization failed for user "web_app_user"
FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "web_app_user", database "webapp_db"

PostgreSQL server log entries (found in /var/lib/pgsql/data/log/postgresql-*.log or journalctl -u postgresql-1X):

202X-XX-XX XX:XX:XX UTC [12345] LOG:  connection received: host=192.168.1.100 port=54321
202X-XX-XX XX:XX:XX UTC [12345] FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "web_app_user", database "webapp_db", no encryption
```

Root Cause Analysis

The "connection authorization failed" error almost exclusively points to an incorrect or missing entry in PostgreSQL's Host-Based Authentication (HBA) configuration file, pg_hba.conf. This file controls which hosts are allowed to connect, which users they can connect as, which databases they can access, and what authentication method is required.

The underlying reasons typically fall into one of these categories:

1. Missing pghba.conf Entry: The most common cause. There is no rule in pghba.conf that matches the incoming connection's parameters (source IP, user, database).
2. Incorrect pg_hba.conf Entry: An existing entry is present, but one or more of its fields (e.g., source IP, user, database, authentication method) do not precisely match the connection attempt.
3. Incorrect Order of Rules: pg_hba.conf rules are processed sequentially from top to bottom. The first rule that matches the connection attempt is used. If a broad, less secure rule appears before a more specific, secure rule, it might inadvertently allow or deny connections in unexpected ways.
4. Incorrect Authentication Method: The pg_hba.conf entry specifies an authentication method (e.g., scram-sha-256, md5, trust, peer, ident) that doesn't match the client's provided credentials or the server's configured user password.
5. * scram-sha-256: The modern, recommended secure password-based authentication.
6. * md5: An older, less secure password-based authentication, still widely used.
7. * trust: Allows anyone to connect without a password (highly insecure for non-local connections).
8. * peer: Used for local connections where the operating system user matches the database user.
9. * ident: Similar to peer, relies on an ident server on the client for authentication.
10. listenaddresses Misconfiguration: While this usually results in "connection refused," if listenaddresses in postgresql.conf is set to localhost or 127.0.0.1 and a remote client tries to connect, the connection will not even reach the pg_hba.conf stage for remote IP addresses. It's essential to ensure PostgreSQL is listening on the correct network interfaces (e.g., * for all, or specific IP addresses).
11. Incorrect Database/User Permissions: Even if pghba.conf allows the connection, the user might not have CONNECT privileges on the requested database or USAGE on specific schemas, leading to application errors after authentication. This is different from the pghba.conf error but often confused.

Step-by-Step Resolution

Follow these steps carefully to diagnose and resolve the pg_hba.conf connection authorization error.

1. Locate pg_hba.conf and postgresql.conf

First, you need to find the correct configuration files. The location can vary slightly depending on the PostgreSQL version and installation method.

# Log in as the postgres user (or use sudo) to execute psql commands
sudo -u postgres psql -c 'SHOW hba_file;'
sudo -u postgres psql -c 'SHOW config_file;'

Common locations on CentOS Stream / Rocky Linux for PostgreSQL 12-16:

• pghba.conf: /var/lib/pgsql/data/pghba.conf (for older versions/manual setup) or /var/lib/pgsql/1X/data/pg_hba.conf (where 1X is your PostgreSQL major version, e.g., 15).
• postgresql.conf: /var/lib/pgsql/data/postgresql.conf or /var/lib/pgsql/1X/data/postgresql.conf.

[!NOTE] On modern CentOS/Rocky systems, PostgreSQL is often installed via dnf, and the data directory is version-specific (e.g., /var/lib/pgsql/15/data).

2. Backup Original Configuration Files

Before making any changes, always back up your configuration files.

PG_VERSION=$(sudo -u postgres psql -t -P format=unaligned -c 'SHOW hba_file;' | cut -d'/' -f5) # Extracts '15' from '/var/lib/pgsql/15/data/pg_hba.conf'

sudo cp ${PGCONFIGDIR}/pghba.conf ${PGCONFIGDIR}/pghba.conf.bak.$(date +%F-%H%M) sudo cp ${PGCONFIGDIR}/postgresql.conf ${PGCONFIGDIR}/postgresql.conf.bak.$(date +%F-%H%M) `

3. Understand pg_hba.conf Syntax

Each line in pg_hba.conf defines an access rule. Comments start with #. Blank lines are ignored. A rule typically follows this format:

TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

• TYPE: Specifies the connection type.
• * local: Connections via Unix-domain sockets (local access only).
• * host: Connections via TCP/IP (both IPv4 and IPv6).
• * hostssl: TCP/IP connections only if SSL is used.
• hostnossl: TCP/IP connections only if SSL is not* used.
• DATABASE: Which database(s) this rule applies to. Can be all, a specific database name, or replication (for streaming replication).
• USER: Which user(s) this rule applies to. Can be all, a specific user name, or a group name prefixed with +.
• ADDRESS: The client's IP address range or host.
• * 127.0.0.1/32 or localhost: Only from the local machine (IPv4).
• * ::1/128: Only from the local machine (IPv6).
• * 0.0.0.0/0: All IPv4 addresses (highly insecure for most authentication methods).
• * 192.168.1.0/24: A specific network range.
• * 10.0.0.10/32: A single specific IP address.
• METHOD: The authentication method. scram-sha-256 (recommended), md5, trust, peer, ident, gssapi, ssi.
• OPTIONS: Additional options specific to the authentication method.

4. Edit pg_hba.conf to Allow Connections

Using the information from the error message (client IP, user, database), add or modify an entry in pg_hba.conf. Open the file with your preferred text editor (e.g., vi or nano).

sudo vi ${PG_CONFIG_DIR}/pg_hba.conf

Common Scenarios and Solutions:

Scenario 1: Allow a specific application user from a specific IP address (most common and recommended). Add this line at the end of your pg_hba.conf file, or logically group it with other host entries:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    webapp_db       web_app_user    192.168.1.100/32        scram-sha-256
```
*   Replace `webapp_db` with your database name.
*   Replace `web_app_user` with your database username.
*   Replace `192.168.1.100/32` with the *exact IP address* of the client connecting to PostgreSQL. Use `/32` for a single IPv4 address or `/128` for a single IPv6 address. For a network, use the appropriate CIDR (e.g., `192.168.1.0/24`).

Scenario 2: Allow all users from localhost for a specific database (for local applications/CLI tools).

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    your_db_name    all             127.0.0.1/32            scram-sha-256
host    your_db_name    all             ::1/128                 scram-sha-256

Scenario 3: Allow local connections using peer authentication (recommended for local postgres user). This is typically already present and allows the Linux postgres user to connect to PostgreSQL as the postgres database user via Unix sockets without a password.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

[!WARNING] Avoid using trust for remote connections (host) as it allows anyone to connect without any authentication. Only use trust for local connections in highly controlled environments or for specific, temporary debugging.

host all all 0.0.0.0/0 md5 – This rule is highly insecure as it allows all users from any IP to connect to any database using a password. Only use 0.0.0.0/0 if you have very strict firewall rules in place, and even then, consider restricting it.

5. Verify listen_addresses in postgresql.conf

While pg_hba.conf handles authorization, postgresql.conf determines where PostgreSQL listens for connections. If PostgreSQL isn't listening on the correct network interface, remote connections will result in "connection refused," not "authorization failed." However, it's a common point of confusion.

Open postgresql.conf:

sudo vi ${PG_CONFIG_DIR}/postgresql.conf

Find the listen_addresses parameter and ensure it's configured correctly:

# What IP address(es) to listen on; '*' means all IP interfaces.
# In a production environment, it is best to be explicit.
#listen_addresses = 'localhost'         # (change requires restart)
listen_addresses = '*'                  # Listen on all available interfaces
#listen_addresses = '192.168.1.50,localhost' # Listen on specific IPs and localhost

[!IMPORTANT] Changing listen_addresses requires a restart of the PostgreSQL service, not just a reload.

6. Reload or Restart PostgreSQL

After modifying pghba.conf, you must reload PostgreSQL for the changes to take effect. If you changed listenaddresses in postgresql.conf, a full restart is required.

Reload (for pg_hba.conf changes):

# Get the PostgreSQL service name (e.g., postgresql-15)

sudo systemctl reload ${PG_SERVICE} `

Restart (for postgresql.conf changes or if reload doesn't work):

sudo systemctl restart ${PG_SERVICE}

[!NOTE] systemctl reload is generally preferred as it doesn't drop existing connections. However, if issues persist or if listen_addresses was changed, a restart is necessary.

7. Check Firewall Rules (firewalld)

While less likely to cause an "authorization failed" error (which implies the connection reached PostgreSQL), firewall rules can prevent connections entirely, leading to "connection refused." It's a good practice to verify if you're troubleshooting any connection issue.

PostgreSQL typically listens on port 5432. Ensure this port is open on your CentOS Stream/Rocky Linux server.

# Check current firewall status

If port 5432 is not listed, add it (for public zone, adjust if needed) sudo firewall-cmd –zone=public –add-port=5432/tcp –permanent sudo firewall-cmd –reload `

8. Verify PostgreSQL User and Password

Ensure the database user exists and has the correct password set, matching the authentication method in pg_hba.conf.

# Connect as the postgres superuser

List users and their attributes (look for your user) du

If the user doesn't exist, create it: CREATE USER webappuser WITH PASSWORD 'averystrong_password' VALID UNTIL '2028-01-01';

If the password needs to be set/reset (especially for scram-sha-256): ALTER USER webappuser WITH PASSWORD 'newstrongpassword';

Grant connect privileges to the database (if not already done) GRANT CONNECT ON DATABASE webappdb TO webapp_user;

Quit psql q `

[!IMPORTANT] PostgreSQL 10+ defaults to scram-sha-256 for new password hashes. If your pghba.conf uses md5 and the user password was created more recently, there might be a mismatch. You can explicitly set the password using ALTER USER ... WITH PASSWORD ... and ensure pghba.conf matches.

9. Test the Connection

After making all changes and reloading/restarting PostgreSQL, attempt to connect again from your client or application.

# From the client machine or server itself
psql -h your_db_host -U your_db_user -d your_db_name

If successful, you should be prompted for a password (if using scram-sha-256 or md5) and then connect to the database. If the error persists, carefully review the PostgreSQL logs for the exact FATAL message and re-check each step, paying close attention to IP addresses, user names, database names, and authentication methods in your pg_hba.conf file.