Restricting Access by IP and Configuring pg_hba.conf

pg_hba.conf is a PostgreSQL config file made for managing connections and authentication. HBA stands for Host-Based Authentication. This file acts like a network "gatekeeper" that decides:

• Who’s allowed to connect to the server (IP addresses, role, database).
• What authentication method is required for the connection.

If you compare it to real life, pg_hba.conf is like a security guard at the door. He decides who gets in and who doesn’t, checking not just your ID (password), but also your address (IP address).

Structure of the pg_hba.conf File

The pg_hba.conf file is made up of lines, where each line describes an access rule. The structure of a line looks like this:

<connection type> <database> <user> <address> <authentication method>

Let’s break down the components:

Connection type: defines how the client will connect.

• local: connect via Unix sockets (for local users on a Linux server).
• host: connect via TCP/IP.
• hostssl: connect via TCP/IP, but only using SSL.
• hostnossl: connect via TCP/IP without SSL.

Database: lists the databases that are allowed. You can specify a specific database, several databases separated by commas, or use keywords:

• all: Allow access to all databases.

User: specifies which users are allowed to connect.

• You can specify a specific username or use all to allow everyone.

Address: specifies the client’s IP address or a range of addresses.

• For IPv4, use x.x.x.x or x.x.x.x/y (where /y is the subnet mask, like /24).
• For IPv6, use ::/y.
• The keyword all means allow all IP addresses.

Authentication method: tells which authentication method is used.

Examples:

• trust: allow connection without a password (not safe, only for testing).
• md5: use a password (hashed).
• scram-sha-256: more secure authentication using SHA-256.
• reject: deny access.

Examples of Lines in pg_hba.conf

The file setup can be flexible. Here are a few examples:

1. Allow local connections via Unix socket

   local   all             all                                     trust
   

   All users are allowed to connect to all databases on the local server without a password.

2. Allow connections from a single IP address

   host    my_database     my_user        192.168.1.100/32         md5
   

   User my_user can connect to my_database only from IP 192.168.1.100 using a password.

3. Allow access to a database from an entire subnet

   host    my_database     all            192.168.1.0/24           scram-sha-256
   

   Any user from subnet 192.168.1.0/24 can connect to my_database, but only with SHA-256 authentication.

4. Deny connections from a specific subnet

   host    all             all            192.168.2.0/24           reject
   

   Connections from subnet 192.168.2.0/24 are totally denied.

Setting Up Access by IP Address

Now that we’ve figured out the file structure, let’s see how to manage connections.

Let’s try to restrict access to the server from certain IP addresses. Say we have a PostgreSQL server and we want to allow access only from localhost (127.0.0.1) and from the office subnet (192.168.10.0/24). For that, we’ll add these lines to pg_hba.conf:

# Local access
host    all             all            127.0.0.1/32             trust

# Office access
host    all             all            192.168.10.0/24          md5

# Deny everything else
host    all             all            0.0.0.0/0                reject

Here, the rule 0.0.0.0/0 means "all IP addresses". We’re explicitly denying access from anywhere except the addresses we listed.

Setting Up Access for Remote Users

If your PostgreSQL server is running in the cloud or on a remote server, you might want to allow connections only for certain external IPs. For example:

# Access for admin from home
host    all             admin_user     203.0.113.10/32          md5

In this example, only user admin_user from IP 203.0.113.10 can connect.

Reloading the Configuration

After you make changes to pg_hba.conf, PostgreSQL needs to apply them. Use this command:

sudo systemctl reload postgresql

Reloading is safe and won’t crash your server.

If you forgot where pg_hba.conf is, you can find its path via SQL:

SHOW hba_file;

Common Mistakes When Working with pg_hba.conf

Working with pg_hba.conf is pretty simple, but newbie admins sometimes mess up. For example:

1. Forgot to reload the server. All changes in pg_hba.conf only take effect after you reload the config.
2. Conflicting rules. PostgreSQL processes rules from top to bottom. As soon as a rule matches, the rest are ignored. More general rules should go lower.
3. Wrong subnet mask. For example, if you put /0, you open access to everyone, which can be a serious security hole.

Real-World Usage Examples

Testing an app on a local server.

Allow access only from localhost:

local   all   all   trust

Working with remote clients.

Allow access for a client from a specific IP:

host    all   client_user   203.0.113.42/32   scram-sha-256

Restricting access on a public network.

Deny connections from the Internet (but allow the office):

host    all   all   0.0.0.0/0   reject
host    all   all   192.168.10.0/24   md5

At this point, you should already have a good idea of how to use pg_hba.conf to restrict access to PostgreSQL. This file is one of the key tools for keeping your database secure. Make sure your settings are logical, tested, and match your business needs. Don’t we all want to live in a world where our data is safe from hackers? Well, unless you’re the hacker yourself :)