Right, let’s talk about the pg_hba.conf file, the bouncer at the door of your PostgreSQL club. This file, pg_hba.conf (Host-Based Authentication), is where you decide who gets in, how they prove their identity, and which doors they’re allowed to knock on. It’s a simple concept that, through the magic of enterprise IT, often becomes a tangled mess of panic and misconfiguration. I’m here to make sure that doesn’t happen to you.

The first thing you need to internalize is that PostgreSQL reads this file from top to bottom and uses the first matching rule. Not the best match. The first one. This is the most common way people accidentally lock themselves out of their own database. You put a super restrictive rule at the top for 10.0.0.5/32 and a nice, open 0.0.0.0/0 rule right below it, and you’ll wonder why that one host can’t connect. The answer is it matched the restrictive rule first and never got to the permissive one. Order is absolutely everything.

The Anatomy of a Rule

Each line in pg_hba.conf is a rule with seven potential fields. Let’s break one down:

# TYPE  DATABASE        USER            ADDRESS                 METHOD  [OPTIONS]
host    sales_db        sales_app       10.0.1.0/24            md5
  • Type: host means a TCP/IP connection (SSL or non-SSL). hostssl means only SSL-encrypted connections. hostnossl is its weird, insecure cousin you should probably avoid. local is for local socket connections.
  • Database: This isn’t the user’s default database; it’s the database they are trying to connect to. You can use all, a specific name like sales_db, or even a comma-separated list. You can also use sameuser which is a nifty trick that allows a user to connect to a database of the same name.
  • User: Which user is this rule for? all means, well, all users. You can also use a specific username or a group role (prefixed with +).
  • Address: For host/hostssl types, this defines the client’s IP range. It can be a specific IP (192.168.1.100/32), a CIDR mask (192.168.1.0/24), or a hostname (don’t do this, it requires a reverse DNS lookup and will slow everything down). For local types, this field should be blank.
  • Method: This is the fun part—how the user authenticates. This is where the real security decisions are made.

A Tour of Authentication Methods (The Good, The Bad, and The Ugly)

Let’s talk about these methods. Some are heroes, some are deprecated relics, and one (trust) is an open invitation for your data to be stolen.

  • trust: This is the equivalent of having a sign on your datacenter that says “Free Databases, Just Take One.” If the connection matches the rule, the user gets in. No password. Nothing. Useful for local connections in a secure, single-user environment (like your dev machine), but an absolutely catastrophic idea on any network. Just don’t.

  • reject: The bouncer that immediately says “not tonight, pal.” Useful for explicitly blocking a specific user or network before a more general rule. Great for blacklisting.

  • md5: The old standard. It challenges the client to provide an MD5-hashed version of the password. It’s better than nothing, but MD5 is cryptographically broken. You should be moving past this.

  • `scram-sha-256:** This is the new gold standard. It uses a secure challenge-response mechanism (SCRAM) with SHA-256. It’s immune to replay attacks and is the method you should be using for password-based authentication whenever possible. If you’re starting fresh, use this.

  • peer:** This is for local` connections only. It pulls the client’s operating system username and checks it against the database username. It’s fantastic for scripting and local maintenance on a secure machine, as no password is involved. It just trusts the OS.

Here’s a realistic example for a three-tiered environment:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Block a known bad IP first
host    all             all             192.0.2.100/32          reject

# Application servers get to connect to a specific app database with strong auth
hostssl app_production  app_user        10.0.1.0/24            scram-sha-256

# Developers can access all dbs from the trusted office network, but must use strong auth
hostssl all             +dev_roles      203.0.113.0/24         scram-sha-256

# Allow passwordless local connections for the postgres OS user (common for admin)
local   all             postgres                                peer

# The "break glass in case of emergency" rule. COMMENT THIS OUT AFTER USE.
# This allows anyone on the local machine to connect as postgres with a password.
# host    all             postgres        127.0.0.1/32           md5

Best Practices and Gotchas

  1. hostssl is Your Friend: Unless you enjoy packets being snooped on, mandate SSL for any remote connection. Use hostssl instead of host. You’ll need to configure your SSL certificates in postgresql.conf, but that’s a story for another section.

  2. Be Specific, Early: Put your most specific rules (specific IPs, specific users) at the top. Put your general, catch-all rules (like a reject-all) at the very bottom.

  3. The “Oops, I Locked Myself Out” Recovery: If you mess up your pg_hba.conf and can’t connect, don’t panic. Connect via the local socket (which often has a peer or trust rule). On a standard Linux install, this is as easy as becoming the postgres OS user and running psql.

    sudo su - postgres
psql

  4. Reload, Don’t Restart: After editing pg_hba.conf, you don’t need to restart the entire PostgreSQL server. You can just tell it to reload the configuration file. The pg_reload_conf() function will do this, or you can signal the postmaster from the command line:

    # Find the pid of the postmaster process and send it a SIGHUP
pg_ctl reload -D /path/to/your/data/directory
# Or from inside psql:
SELECT pg_reload_conf();

  5. Test Relentlessly: Your authentication setup is your first line of defense. Test connection attempts from every conceivable user and network location before you assume it’s working. Assume you’ve made a mistake, because you probably have. I always do.