Alright, let’s get our hands dirty with the single most common point of failure when someone new tries to connect to a PostgreSQL database: the pg_hba.conf file. Think of it as the bouncer at the club of your data. It has a list (the guest list) and a set of very strict rules about who gets in, how they can prove their identity, and which door they have to use. If your connection is getting rejected, nine times out of ten, this bouncer is the one folding his arms and shaking his head. Let’s learn how to talk to him.

This file, which stands for “Host-Based Authentication,” is the absolute gatekeeper. The server consults it for every single connection attempt, from top to bottom, and uses the first matching rule to decide the fate of the connection. Get this wrong, and you’ll be staring at “FATAL: no pg_hba.conf entry for host…” errors until you develop a nervous twitch.

The Anatomy of a Rule

Each line in pg_hba.conf is a rule with seven fields. Don’t panic; it’s simpler than it looks. The format is:

connection_type database user IP_address/mask authentication_method [auth_options]

Let’s break down each field with a classic example:

# TYPE  DATABASE        USER        ADDRESS         METHOD
host    all             all         192.168.1.0/24  scram-sha-256
  • connection_type: host means a TCP/IP connection. hostssl means it must be SSL-encrypted (use this!). local is for local socket connections.
  • database: Which databases this rule applies to. all means… all of them. You can specify a name like sales_db or use commas, but please, for the love of all that is good, never use all in production unless you enjoy living dangerously.
  • user: Which database users this applies to. Again, all is a thing, and again, it’s a spectacularly bad idea for most rules. Specify a user or a group (with +).
  • ADDRESS: For TCP/IP rules, this is the client’s IP address and mask. 192.168.1.0/24 means any IP from 192.168.1.1 to 192.168.1.254. samenet matches any IP on the server’s own subnet. 0.0.0.0/0 means… every IPv4 address on the planet. Tread carefully.
  • METHOD: This is the important part—how the user proves they are who they say they are. scram-sha-256 is the modern, secure password method. md5 is the old, broken one we’re all trying to get away from. peer is brilliant for local connections on Linux (it uses the OS user). trust is the method where we just give up and let anyone in, no questions asked. It’s the authentication equivalent of leaving your house keys under the doormat with a sign that says “KEYS UNDER HERE.” I will judge you if I see this on a server with a public IP.

Why Order Matters Desperately

Remember I said the bouncer reads the list from top to bottom? This is the most common pitfall. Imagine these two rules, in this order:

# RULE 1
host    all             all         0.0.0.0/0       trust

# RULE 2
host    all             all         192.168.1.50/32 scram-sha-256

You’d think the specific rule for IP 192.168.1.50 would apply, right? Nope. A connection from 192.168.1.50 matches the first rule (0.0.0.0/0 means “everyone”), so the server uses trust and lets them in without a password. The second rule is never even read for that IP. The more specific rule must always come before the general one. This is non-negotiable.

Crafting Sane and Secure Rules

Let’s build a sensible pg_hba.conf for a hypothetical web server. We’ll be paranoid, which is a good trait in a DBA.

# Allow the "postgres" user to connect locally via socket without a password (standard for maintenance)
local   all             postgres                      peer

# Allow all local apps connecting via socket to use peer authentication (matching OS user to DB user)
local   all             all                           peer

# Our web app server at 192.168.1.100 can connect to the "app_db" database as user "app_user" using a secure password.
hostssl app_db          app_user      192.168.1.100/32 scram-sha-256

# A specific administrator's workstation can connect to all databases (but still needs a password)
hostssl all             admin_user    192.168.1.50/32  scram-sha-256

# REJECT EVERYTHING ELSE. This is our "default deny" safety net.
hostssl all             all            0.0.0.0/0       reject

See how that works? The specific allows come first, and the blanket reject at the end catches any connection attempt that doesn’t have an explicit invite. This is how you sleep well at night.

Reloading vs. Restarting

You’ve edited the file. Now what? You don’t need to restart the entire PostgreSQL server (which would drop everyone’s connections and be generally obnoxious). You just need to tell the main server process to reload the file. It does this gracefully, without breaking existing connections.

# The easy, universal way:
pg_ctl reload -D /path/to/your/data/directory

# Or if you have systemd:
sudo systemctl reload postgresql

# Or from inside a PostgreSQL session if you're feeling fancy:
SELECT pg_reload_conf();

The changes take effect immediately for new connection attempts. Existing connections are completely unaffected.

The designers actually got this part right. It’s a smooth, well-considered operation. The pg_hba.conf syntax itself, however, is a bit arcane. It feels like it’s from a simpler time, and it has the rough edges to prove it. But once you get the hang of it, you have absolute control over your database’s front door. Now go configure it. And for heaven’s sake, don’t use trust.