Configuring Access Control in PostgreSQL's pg_hba.conf File

Helped a friend solve an Auth issue on postgres and decided to write about it.

PostgreSQL, a powerful open-source relational database management system, offers robust access control mechanisms through its pg_hba.conf (Host-Based Authentication) file. This file dictates how clients can connect to the PostgreSQL server by defining authentication rules based on hostnames, IP addresses, and authentication methods. Be sure also to check on the incredible graph database Apache AGE built just on top of it which you can highly use it to create relationships and get incredible insights.

Introduction
In PostgreSQL, secure access to the database is crucial. The pg_hba.conf file plays a pivotal role in controlling who can connect to the server and how they authenticate themselves.

Understanding pg_hba.conf
The pg_hba.conf file resides within the PostgreSQL data directory and consists of a series of lines, each specifying a connection type, database, user, IP address, authentication method, and other details.

Example Entry:

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host    all       all   10.1.5.21/32  md5

TYPE: Specifies the connection type (e.g., local, host, hostssl, hostnossl).
DATABASE and USER: Determines the database and user to which the rule applies.
CIDR-ADDRESS: Denotes the IP address or range in CIDR notation.
METHOD: Indicates the authentication method (trust, md5, password, reject, etc.).
Implementation: Allowing Specific Hosts
Scenario:
We have a PostgreSQL server at IP address 10.0.5.19 and want to allow a specific host (10.1.5.21) to connect securely.

Steps:
Open pg_hba.conf:
The file is located within the postgres files.

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

Add Entry for Host 10.1.5.21:

host    all    all    10.1.5.21/32    md5

Explanation:

host: Specifies the connection type.
all all: Allows access to all databases and users.
10.1.5.21/32: Grants access to a single IP address.
md5: Specifies the password-based authentication method.
Reload PostgreSQL Configuration:

sudo systemctl reload postgresql

Conclusion
Configuring access control in PostgreSQL through pg_hba.conf is fundamental for securing database connections. By defining specific rules, administrators can control which hosts are permitted to access the server and enforce authentication methods, thereby enhancing security.

In conclusion, pg_hba.conf effectively ensures a secure and controlled environment for PostgreSQL database access.