PostgreSQL Authentication: How pg_hba.conf Controls Database Access
Before a user can connect to PostgreSQL, the server must authenticate them. That decision is controlled by a single configuration file: pg_hba.conf. Understanding this file is essential for managing database access.
What is pg_hba.conf?
pg_hba.conf stands for “PostgreSQL Host-Based Authentication configuration.” It’s a text file that tells PostgreSQL:
- Who can connect (which user)
- Where they’re connecting from (localhost, an IP address, a network)
- How they must authenticate (password, trust, certificate, etc.)
Think of it as the security guard at the database door. Before you get in, the guard checks this file to see if you’re allowed.
Where is pg_hba.conf Located?
The location depends on your installation:
Windows:
C:\Program Files\PostgreSQL\15\data\pg_hba.conf
macOS (Homebrew):
/usr/local/var/postgres/pg_hba.conf
Linux (Debian/Ubuntu):
/etc/postgresql/15/main/pg_hba.conf
Linux (CentOS/Fedora):
/var/lib/pgsql/15/data/pg_hba.conf
To find your instance’s location, connect to PostgreSQL and run:
SHOW hba_file;
Output:
hba_file
-------------------------------------------
/usr/local/var/postgres/pg_hba.conf
Understanding pg_hba.conf Format
Here’s a typical pg_hba.conf file with explanations:
# PostgreSQL Client Authentication Configuration
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 0.0.0.0/0 md5
Let’s break down each column:
1. TYPE: Connection Method
local: Unix domain socket (connections from the same machine)host: TCP/IP connectionhostssl: TCP/IP with SSL encryption requiredhostnossl: TCP/IP without SSL
# Allow local connections (same machine, no network)
local all all trust
# Allow network connections
host all all 192.168.1.0/24 md5
2. DATABASE: Which Databases
all: All databasespostgres: Only the postgres databasemydb: Only the mydb databasedb1,db2,db3: Multiple specific databases
# All databases
local all all trust
# Only the learning_db database
local learning_db all trust
# Only postgres and template1
local postgres,template1 all trust
3. USER: Which Users
all: All userspostgres: Only the postgres userjohn: Only the user named john+developers: Any user in the developers role (the + prefix means “role”)
# All users
local all all trust
# Only superuser
local all postgres trust
# Only users in the 'developers' role
local all +developers trust
4. ADDRESS: Connection Source
Only used for host, hostssl, and hostnossl (not local).
127.0.0.1/32: Only localhost (IPv4)::1/128: Only localhost (IPv6)0.0.0.0/0: Any IP address (unrestricted)192.168.1.0/24: Any IP in the 192.168.1.x range192.168.1.100: Single specific IP address
# Only localhost
host all all 127.0.0.1/32 md5
# Any IP address
host all all 0.0.0.0/0 md5
# Specific IP range (office network)
host all all 203.0.113.0/24 md5
5. METHOD: Authentication Type
This is the critical part—how the user must authenticate:
trust: No authentication required. If you match TYPE/DATABASE/USER/ADDRESS, you’re automatically allowed in.reject: Explicitly deny the connection.md5: User must provide password (encrypted with MD5). Less secure than scram-sha-256.scram-sha-256: User must provide password (encrypted with SCRAM-SHA-256). More secure than md5.password: User must provide password in plain text. Never use in production!peer: Use operating system’s user authentication (Linux/Unix only).ident: Use ident server (rarely used now).ldap: Use LDAP server for authentication.pam: Use Pluggable Authentication Modules.
# No password required
local all all trust
# Password required (modern, secure)
host all all 0.0.0.0/0 scram-sha-256
# Password required (legacy)
host all all 0.0.0.0/0 md5
Common pg_hba.conf Configurations
For Development (Local Machine Only)
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
This allows local connections without a password (convenient for development).
For Production (External Network)
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256
This requires passwords for all connections and only allows specific networks.
For Team Development (Trusted Network)
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all 192.168.1.0/24 scram-sha-256
Local connections don’t need passwords, but remote connections do.
Deny Specific User Access
# TYPE DATABASE USER ADDRESS METHOD
host all guest 0.0.0.0/0 reject
host all all 0.0.0.0/0 scram-sha-256
The first line explicitly denies access from the guest user. PostgreSQL reads rules from top-to-bottom and stops at the first match.
Modifying pg_hba.conf
Step 1: Find Your pg_hba.conf
Connect to PostgreSQL:
psql -U postgres
Find the location:
SHOW hba_file;
Step 2: Edit the File
On Windows:
Open pg_hba.conf with Notepad or your text editor.
On Mac/Linux:
sudo nano /path/to/pg_hba.conf
Or use your editor:
sudo vim /path/to/pg_hba.conf
Step 3: Make Your Changes
Add a new line or modify existing ones. Remember:
- TYPE | DATABASE | USER | ADDRESS | METHOD
- Comments start with
#
Example: Allow specific user from specific network:
# Allow the 'analyst' user from 203.0.113.0/24 network with password
host all analyst 203.0.113.0/24 scram-sha-256
Step 4: Reload Configuration
After modifying, you must tell PostgreSQL to reload the configuration file. Don’t restart the server—just reload:
# From command-line
pg_ctl reload
Or from within PostgreSQL (requires superuser):
SELECT pg_reload_conf();
You should see:
pg_reload_conf
----------------
t
(1 row)
The t means true—the configuration was reloaded successfully.
Real-World Example: Setup for a Small Team
Let’s set up a PostgreSQL server for a three-person development team:
Scenario:
- Local development environment (office IP: 203.0.113.0/24)
- Remote developer (VPN IP: 198.51.100.5)
- Production-ready security
pg_hba.conf:
# Local connections (same machine, no password)
local all all trust
# Local network connections (office, password required)
host all all 203.0.113.0/24 scram-sha-256
# Remote connections (VPN developer, password required)
host all all 198.51.100.5/32 scram-sha-256
# Reject everything else
host all all 0.0.0.0/0 reject
host all all ::/0 reject
This configuration:
- Allows local connections without password (convenient for testing)
- Allows office network with password (secure enough for dev)
- Allows specific remote IP with password (the VPN developer)
- Rejects all other connections (no accidental public access)
Understanding Authentication Methods in Detail
TRUST Method (No Security)
local all all trust
Anyone who connects is automatically allowed in. No password required.
When to use: Development environment, completely isolated machine.
When NOT to use: Anything with multiple users or network access. Your database is wide open!
SCRAM-SHA-256 Method (Modern & Secure)
host all all 0.0.0.0/0 scram-sha-256
User provides a password. PostgreSQL uses SCRAM-SHA-256 (Salted Challenge Response Authentication Mechanism) to encrypt it.
When to use: Production, any multi-user environment, any network access.
How it works: The user’s password isn’t sent over the network. Instead, a challenge-response process proves the user knows the password without transmitting it.
MD5 Method (Legacy)
host all all 0.0.0.0/0 md5
User provides a password. PostgreSQL encrypts it with MD5.
When to use: Never in new installations. MD5 is considered cryptographically broken.
Why avoid: Older, less secure than SCRAM-SHA-256. If you’re running PostgreSQL 10+, use scram-sha-256.
PEER Method (Unix/Linux Only)
local all all peer
PostgreSQL delegates authentication to the operating system. If you’re logged in as user john in the OS, you can connect as the PostgreSQL user john without a password.
When to use: Local administration on Linux/Unix servers.
Not available: Windows doesn’t support this method.
Troubleshooting Connection Issues
Error: “FATAL: no pg_hba.conf entry for host…”
Problem: Your connection doesn’t match any pg_hba.conf rule.
Solution:
-
Find your exact connection parameters:
- What IP address are you connecting from?
- What username are you using?
- What database are you connecting to?
-
Add a matching rule to pg_hba.conf:
host target_db target_user your_ip/32 scram-sha-256 -
Reload the configuration:
SELECT pg_reload_conf();
Error: “FATAL: password authentication failed”
Problem: The password is wrong or pg_hba.conf requires a password but you’re not providing one.
Solution:
- Verify the password is correct
- Make sure pg_hba.conf specifies a password authentication method (md5, scram-sha-256)
- If using psql, add the
-Wflag to force password prompt:psql -U john -h localhost -W
Error: “connection refused”
Problem: PostgreSQL server isn’t running or listening on the specified port.
Solution:
- Check if PostgreSQL is running
- Verify the port in postgresql.conf (default is 5432)
- Make sure firewall isn’t blocking the port
Common Errors & Solutions
I Modified pg_hba.conf but Changes Don’t Take Effect
Problem: Changes made but not reloaded.
Solution:
SELECT pg_reload_conf();
Or restart PostgreSQL:
- Windows: Stop and start the service
- Mac:
brew services restart postgresql@15 - Linux:
sudo systemctl restart postgresql
Everyone Can Connect but the Superuser
Problem: Your pg_hba.conf is rejecting the postgres user.
Solution: Add a specific rule for postgres before the general rule:
# Allow postgres user from localhost
host all postgres 127.0.0.1/32 trust
# All other users require password
host all all 0.0.0.0/0 scram-sha-256
Remember: PostgreSQL reads top-to-bottom and stops at first match.
Performance Tips
Use the Least Restrictive Rule That’s Secure
- Don’t use
trustfor network connections (security risk) - Don’t use
passwordwith plain text (sends password unencrypted) - Use
scram-sha-256for production (modern, secure, still fast)
Order Your Rules Efficiently Put most common rules first. PostgreSQL checks each rule in order:
# Good: Specific rules first, general rules last
host learning_db analyst 203.0.113.0/24 scram-sha-256
host all all 203.0.113.0/24 scram-sha-256
host all all 0.0.0.0/0 reject
FAQ
Q: Is trust method secure?
A: No. Anyone who can connect to your PostgreSQL port can access your data without a password. Only use it in isolated development environments.
Q: What’s the difference between md5 and scram-sha-256?
A: SCRAM-SHA-256 is newer and more secure. MD5 is cryptographically broken. Use SCRAM-SHA-256 for any new installation.
Q: How do I know if my pg_hba.conf changes took effect? A: Try connecting. If your test connection works, the change took effect. Or check the PostgreSQL logs:
tail -f /var/log/postgresql/postgresql.log
Q: Can I allow connections from anywhere with 0.0.0.0/0?
A: Technically yes, but it’s a security risk. You’re exposing your database to the entire internet. Only use if you have strong password requirements and firewall rules.
Q: Why do I need pg_hba.conf when I can use roles and permissions? A: They serve different purposes:
pg_hba.conf: Can you connect at all?- Roles/Permissions: Once connected, what can you do?
Both are necessary for complete security.
Next Steps
You now understand how PostgreSQL authenticates connections. You’re ready to start writing SQL queries!
Head to the PostgreSQL SELECT Statement Complete Guide to learn how to query data.
For more advanced permission management, see PostgreSQL Users & Roles.
To learn about the role system in more detail, check the Creating Users & Roles guide.