Access Control in PostgreSQL with pg_hba.conf: Restrict by IP and User

PostgreSQL’s pg_hba.conf is one of the most powerful — and underutilized — access control tools in modern database operations.

It determines:

• Who can connect

• From where

• Using what authentication method

Let’s explore how to use it effectively to harden your PostgreSQL environment.

---

IP-Based Restrictions

Allow only internal subnet access:

host    all     all     192.168.0.0/24     md5

→ Blocks any attempt outside your private network.

---

👤 User-Based Access Control

Allow myuser to connect only locally:

local   all     myuser                    peer
host    all     myuser    0.0.0.0/0       reject

→ Ensures the account is only usable on the server machine.

---

Real-World Best Practices

• Always reload after changes:

pg_ctl reload
# or
systemctl reload postgresql

• Order matters. Rules are read top-down — first match wins.

• Avoid leaving trust in production, especially after testing phases.

---

Final Thought

pg_hba.conf is not just a config file —
it's your last line of defense.

Make sure it's not an afterthought.
Treat it like your PostgreSQL firewall.
Because that’s exactly what it is.