Troubleshooting PostgreSQL `pg_hba.conf` Connection Authorization Failure on macOS

Resolve 'connection authorization failed' when connecting to PostgreSQL locally on macOS. Configure `pg_hba.conf` for seamless local development.

Introduction

As a developer or systems administrator working on macOS, you've likely encountered the "connection authorization failed" error when attempting to connect to your local PostgreSQL instance. This issue typically prevents your applications (e.g., Rails, Node.js, Python/Django) or even the psql command-line client from establishing a database connection, halting your development workflow.

This comprehensive guide delves into the root causes of this common PostgreSQL connection problem on macOS, focusing specifically on the crucial pg_hba.conf file, and provides a step-by-step resolution to get your local development environment back online.

Symptom & Error Signature

When facing a pg_hba.conf authorization failure, you'll typically see one of the following error messages, whether from the psql command-line utility or your application's logs:

1. From psql command line:

psql -U your_user -d your_database

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  connection authorization failed for user "your_user"

or if connecting via TCP/IP:

psql -h localhost -U your_user -d your_database

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  connection authorization failed for user "your_user"
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  connection authorization failed for user "your_user"

2. From Application Logs (e.g., Ruby on Rails, Node.js):

ActiveRecord::StatementInvalid (PG::ConnectionBad: FATAL:  connection authorization failed for user "your_app_user")

Error: connect ECONNREFUSED 127.0.0.1:5432
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16) {
  name: 'ConnectionRefusedError',
  code: 'ECONNREFUSED',
  sqlState: '28000',
  severity: 'FATAL',
  code: '28000',
  detail: 'connection authorization failed for user "your_app_user"'
}

The key indicator is FATAL: connection authorization failed for user "your_user".

Root Cause Analysis

The "connection authorization failed" error almost invariably points to an issue with PostgreSQL's Host-Based Authentication (HBA) configuration, which is controlled by the pg_hba.conf file. This file dictates which hosts, users, and databases are allowed to connect, and what authentication method they must use.

Here's a breakdown of the underlying reasons:

1. Incorrect pg_hba.conf Entry: The most common cause is that there is no entry in pg_hba.conf that matches the incoming connection attempt's parameters (database, user, IP address/connection type). PostgreSQL processes entries sequentially, taking the first match. If no match is found, or if an existing entry specifies an incorrect authentication method, the connection is rejected.

2. Missing or Misconfigured local Entry: For connections via Unix domain sockets (the default when connecting locally without specifying -h localhost), PostgreSQL uses local entries. If your local entry is too restrictive or missing, psql or applications connecting via socket will fail.

3. Missing or Misconfigured host Entry: For connections via TCP/IP (e.g., psql -h localhost or applications configured to use 127.0.0.1), PostgreSQL uses host entries. These entries require a specific IP address range (e.g., 127.0.0.1/32 for IPv4 localhost or ::1/128 for IPv6 localhost).

4. Incorrect Authentication Method: The pg_hba.conf entry might exist, but the specified authentication method (e.g., md5, scram-sha-256, trust, peer) doesn't match what the client is attempting or what the user's password is configured for. For local development, trust or md5/scram-sha-256 are common.

5. Non-existent User or Database: While less direct, if the user or database you're trying to connect with doesn't exist, pg_hba.conf might allow the connection, but PostgreSQL will then report that the database/user doesn't exist, which can sometimes be confused with HBA issues if not carefully distinguished. This guide focuses on the HBA aspect.

6. pg_hba.conf Not Reloaded: After making changes to pg_hba.conf, PostgreSQL needs to be signaled to reload its configuration. Without this, the changes won't take effect.

On macOS, especially with Homebrew installations, the default pg_hba.conf might be conservative, only allowing peer authentication for local connections or specific md5/scram-sha-256 for host connections, which might not align with your existing system user or how you want to connect during development.

Step-by-Step Resolution

Follow these steps carefully to diagnose and resolve your PostgreSQL pg_hba.conf connection authorization issues on macOS.

1. Locate Your pg_hba.conf File

The first step is to find the exact location of your PostgreSQL configuration files. On macOS, if you installed PostgreSQL via Homebrew, the path will differ slightly between Intel-based Macs and Apple Silicon (M1/M2/etc.) Macs.

You can find the pg_hba.conf file path directly from PostgreSQL:

psql -U postgres -c "SHOW hba_file;"

If the psql -U postgres command itself fails with a connection authorization error, it might mean even the postgres superuser isn't allowed to connect. In this case, you'll need to locate the file manually based on Homebrew's typical installation paths:

• Intel Macs: /usr/local/var/postgres/pg_hba.conf
• Apple Silicon Macs: /opt/homebrew/var/postgres/pg_hba.conf

2. Backup Your pg_hba.conf File

Before making any changes, always create a backup of your configuration file.

# Replace with your actual path found in step 1
cp /usr/local/var/postgres/pg_hba.conf /usr/local/var/postgres/pg_hba.conf.bak

Backing up configuration files is a critical sysadmin practice. It allows you to quickly revert to a working state if your changes cause further issues.

3. Understand pg_hba.conf Entries

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

# Replace with your actual path
nano /usr/local/var/postgres/pg_hba.conf

You'll see lines that look like this:

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

Let's break down the columns:

• TYPE: Specifies the connection type.
  • local: Connections via Unix domain sockets (the default for psql without -h).
  • host: Connections via TCP/IP.
  • hostssl: Encrypted TCP/IP connections.
  • hostnossl: Unencrypted TCP/IP connections.
• DATABASE: Which database(s) this rule applies to. all means all databases.
• USER: Which user(s) this rule applies to. all means all users.
• ADDRESS: For host connections, this is the client's IP address range (e.g., 127.0.0.1/32 for localhost IPv4, ::1/128 for localhost IPv6, 0.0.0.0/0 for any IPv4 address). For local connections, this field is ignored.
• METHOD: The authentication method required.
  • trust: Allow connection without password. (Not recommended for production!)
  • peer: Use the client's operating system user name as the database user name. Common for local connections.
  • md5: Require an MD5-hashed password.
  • scram-sha-256: Require a SCRAM-SHA-256 hashed password (more secure than MD5).
  • password: Send unencrypted password (extremely insecure, avoid!).

4. Edit pg_hba.conf for Local Development

You'll typically need to add or modify entries to allow your local user to connect. We'll cover the most common scenarios.

Scenario A: Simplest Local Access (Unix Socket & TCP/IP – trust method)

This is often used for quick local development setups, allowing connection without a password.

# Append these lines to the END of your pg_hba.conf file
# (or modify existing restrictive lines)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow local (Unix socket) connections for all users to all databases with trust
local   all             all                                     trust

# Allow IPv4 TCP/IP connections from localhost for all users to all databases with trust
host    all             all             127.0.0.1/32            trust

# Allow IPv6 TCP/IP connections from localhost for all users to all databases with trust
host    all             all             ::1/128                 trust

Using trust authentication method should NEVER be used in a production environment or any publicly accessible server. It bypasses password authentication entirely. It is suitable only for secure local development environments where your machine itself is secure.

Scenario B: More Secure Local Access (md5 or scram-sha-256 method)

This method requires a password, which is generally better practice even for local development. Make sure your PostgreSQL user has a password set.

# Append these lines to the END of your pg_hba.conf file
# (or modify existing restrictive lines)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Example for a specific user 'your_user' to 'your_database'
# You can use 'all' for user/database if you prefer
host    your_database   your_user       127.0.0.1/32            scram-sha-256
host    your_database   your_user       ::1/128                 scram-sha-256

# If your 'local' connections (Unix socket) are failing, and you prefer password for them too:
# NOTE: 'peer' is common for 'local' if your OS user matches the DB user.
# If 'peer' fails, you might need 'md5' or 'scram-sha-256' for local too,
# but it's less common to connect via local socket with a password.
# local   all             all                                     scram-sha-256

If you choose md5 or scram-sha-256, ensure the PostgreSQL user has a password set. If not, you'll need to set one:

psql -U postgres
# If connecting as postgres user also fails, you might need to use `initdb` to reset or temporarily change `pg_hba.conf` to `trust` for postgres user.
# Then, once connected:
ALTER USER your_user WITH PASSWORD 'your_strong_password';
q

Replace your_user and your_strong_password accordingly. After setting the password, remember to change your pg_hba.conf back to the desired secure method and reload.

5. Reload PostgreSQL Configuration

After saving your changes to pg_hba.conf, PostgreSQL needs to be informed to reload its configuration without restarting the entire server.

Option A: Using Homebrew Services (Recommended for Homebrew installs)

brew services restart postgresql

Option B: Using pg_ctl (Alternative if brew services is not used or fails)

First, find your PostgreSQL data directory. It's typically /usr/local/var/postgres (Intel) or /opt/homebrew/var/postgres (Apple Silicon).

# Replace with your actual data directory
pg_ctl -D /usr/local/var/postgres reload

If reload doesn't resolve it, a full restart might be necessary:

pg_ctl -D /usr/local/var/postgres restart

6. Test Your Connection

Now, try connecting again using the psql command or your application.

# Test local (Unix socket) connection
psql -U your_user -d your_database

# Test TCP/IP connection
psql -h localhost -U your_user -d your_database

If successful, you should see the psql prompt (e.g., your_database=>). If you're using an application, try running it, and the database connection should now be established without the authorization error.

7. (Optional) Create Missing User or Database

If, after fixing pg_hba.conf, you encounter errors like FATAL: database "your_database" does not exist or FATAL: role "your_user" does not exist, you need to create them.

Connect as the postgres superuser (which should now be possible after pg_hba.conf changes):

psql -U postgres

Then, execute the following SQL commands:

-- Create a new user (role)
CREATE USER your_user WITH PASSWORD 'your_password';

-- Create a new database owned by the user
CREATE DATABASE your_database OWNER your_user;

-- Grant all privileges on the database to the user
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

q

8. (Optional) Check PostgreSQL Log Files

If the issue persists, the PostgreSQL log files can provide more detailed information about why the connection was rejected.

PostgreSQL logs for Homebrew installations are usually found in:

• Intel Macs: /usr/local/var/log/postgres.log
• Apple Silicon Macs: /opt/homebrew/var/log/postgres.log

You can tail the log file to watch for new errors as you try to connect:

tail -f /usr/local/var/log/postgres.log

Look for lines containing FATAL: or LOG: entries related to connection attempts and authentication. This can often provide clues that are more specific than the client-side error.