This error occurs when PostgreSQL receives a connection attempt but cannot find a matching rule in the pg_hba.conf configuration file. It typically happens when connecting from an IP address, hostname, or with a user/database combination that isn't explicitly allowed. Add the missing entry to pg_hba.conf and reload the database to fix it.
PostgreSQL uses a Host-Based Authentication (HBA) configuration file called pg_hba.conf to control client access. When a client attempts to connect, PostgreSQL checks pg_hba.conf sequentially for a matching rule based on connection type (local socket, TCP/IP, SSL), client IP address, database name, username, and authentication method. If no matching rule is found, the connection is rejected with this error. This is a security feature that prevents unauthorized access, but it requires explicit configuration to allow legitimate connections.
Determine which IP address the client is connecting from. The error message usually includes this information. For example:
FATAL: no pg_hba.conf entry for host "192.168.1.100", user "myuser", database "mydb", SSL offThis tells you:
- Client IP: 192.168.1.100
- User: myuser
- Database: mydb
- Connection type: TCP/IP without SSL (host)
If connecting from localhost/127.0.0.1, the error typically indicates a 'local' socket or 'host' with loopback IP.
Find where pg_hba.conf is stored on your PostgreSQL server:
Linux/Unix:
# Typical locations
/etc/postgresql/VERSION/main/pg_hba.conf
/var/lib/postgresql/VERSION/main/pg_hba.conf
/usr/local/var/postgres/pg_hba.conf (macOS)Windows:
C:\Program Files\PostgreSQL\VERSION\data\pg_hba.confFind via psql (if you have local access):
sudo -u postgres psql -c "SHOW hba_file;"This command shows the exact path PostgreSQL is using.
Open pg_hba.conf in a text editor and examine existing rules. The format is:
TYPE DATABASE USER CIDR-ADDRESS METHODExample entries:
# Local connections (Unix socket)
local all all peer
# IPv4 localhost
host all all 127.0.0.1/32 scram-sha-256
# IPv6 localhost
host all all ::1/128 scram-sha-256
# Remote IPv4 connections
host all all 192.168.1.0/24 md5
# Reject all other connections
host all all 0.0.0.0/0 rejectNote that rules are evaluated in order—the first matching rule wins. Rules that appear early must be more specific than later rules.
Based on the error message, add a new rule for the client IP. Place it BEFORE any 'reject' rules:
For the example error (192.168.1.100, user myuser, database mydb):
# Allow myuser from 192.168.1.100
host mydb myuser 192.168.1.100/32 scram-sha-256
# Or allow all users from that network
host all all 192.168.1.0/24 scram-sha-256Choose the authentication method:
- scram-sha-256 - Most secure (default in PostgreSQL 13+)
- md5 - Older but widely compatible
- trust - No password required (use only for trusted networks)
- reject - Deny the connection
CIDR notation:
- 192.168.1.100/32 - Single IP address
- 192.168.1.0/24 - Network (192.168.1.0 to 192.168.1.255)
- 0.0.0.0/0 - All IPv4 addresses (not recommended in production)
Apply the pg_hba.conf changes by reloading the configuration:
Reload (no downtime):
# Linux with systemd
sudo systemctl reload postgresql
# Or with pg_ctl
pg_ctl reload -D /path/to/data/directory
# Or from psql (as superuser)
SELECT pg_reload_conf();If reload fails or doesn't work, restart PostgreSQL:
# Linux with systemd
sudo systemctl restart postgresql
# Or with pg_ctl
pg_ctl restart -D /path/to/data/directory
# macOS with Homebrew
brew services restart postgresql
# Docker
docker restart postgres_container_nameReload is preferred because it doesn't drop existing connections.
Verify the connection works from the client IP:
From the client machine:
psql -h postgres-server-ip -U myuser -d mydbFrom the PostgreSQL server (if connecting locally):
psql -h 127.0.0.1 -U myuser -d mydbIn application connection strings:
postgresql://myuser:password@postgres-host:5432/mydbIf the error persists, check PostgreSQL logs for more details:
# Linux
sudo tail -50 /var/log/postgresql/postgresql.log
# Or from psql
SELECT pg_current_logfile();
SELECT * FROM pg_read_file('postmaster.log', 0, 100000);CIDR Matching: PostgreSQL matches IP addresses against pg_hba.conf rules using CIDR notation. A /32 suffix matches a single IPv4 address, while /24 matches a network of 256 addresses. IPv6 addresses use /128 for a single address and /64 for larger blocks. Rule Order Matters: pg_hba.conf is evaluated top-to-bottom. Place specific rules (single IPs, specific users) before general rules. A 'reject' rule near the top will block all subsequent rules for matching connections. SSL/TLS: Use 'hostssl' instead of 'host' to require encrypted connections, or 'hostnossl' for unencrypted only. Many production databases require SSL. Replication: If setting up replication, use the 'replication' database name: host replication all 192.168.1.100/32 scram-sha-256. Cloud Platforms: AWS RDS, Google Cloud SQL, and Azure Database for PostgreSQL usually manage pg_hba.conf automatically. If self-hosted in containers or VMs, ensure the CIDR block matches your network topology. Peer Authentication: The 'peer' method (common for local connections) requires the OS username to match the PostgreSQL username—it doesn't use passwords. Testing Configuration: PostgreSQL 13+ provides the pg_hba_file_rules view to see how rules are being interpreted: SELECT * FROM pg_hba_file_rules;
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL