Comprehensive Guide to pg_hba.conf Host Configuration

Configuring pg_hba.conf Entry for Host Connections

The pg_hba.conf file in PostgreSQL controls client authentication by defining who can connect, from where, and using which authentication method. Configuring a proper pg_hba.conf entry for a host is essential for allowing secure and specific connections to your PostgreSQL database.

Syntax:

Each line in pg_hba.conf follows this syntax:

type  database  user  address  auth-method  [auth-options]  

Parameters:

1. type: The type of connection (host for TCP/IP connections).

2. database: The target database(s). Use all for all databases.

3. user: The user(s) allowed to connect. Use all for all users.

4. address: Client IP address or range (e.g., 192.168.1.0/24).

5. auth-method: The authentication method (e.g., md5, scram-sha-256, trust).

6. auth-options (optional): Additional options like passwords or certificates.

Examples

1. Allow Connections from a Specific Host

Code:

host  mydatabase  myuser  192.168.1.100/32  md5

Explanation:

• host: Specifies a TCP/IP connection.
• mydatabase: Restricts the connection to mydatabase.
• myuser: Only the user myuser can connect.
• 192.168.1.100/32: Allows only the host with IP 192.168.1.100.
• md5: Requires the user to provide a password hashed using MD5.

2. Allow All Connections from a Subnet

Code:

host  all  all  10.10.0.0/16  scram-sha-256  

Explanation:

• host: Allows TCP/IP connections.
• all: Applies to all databases and users.
• 10.10.0.0/16: Allows connections from any host in the subnet 10.10.0.0/16.
• scram-sha-256: Ensures secure password hashing.

3. Trust Connections from Localhost

Code:

host  all  all  127.0.0.1/32  trust  

Explanation:

• host: Specifies TCP/IP connection.
• 127.0.0.1/32: Restricts to localhost.
• trust: No password is required. (Use only in development environments!)

Editing the pg_hba.conf File

1. Locate the File: The pg_hba.conf file is typically located in the data directory of your PostgreSQL installation.

 
/var/lib/postgresql//main/pg_hba.conf  

2. Edit the File: Use a text editor to add or modify entries.

sudo nano /var/lib/postgresql/14/main/pg_hba.conf

3. Reload PostgreSQL: After making changes, reload the configuration for the changes to take effect.

 
sudo systemctl reload postgresql  

Advanced Usage

Using CIDR Notation

CIDR (Classless Inter-Domain Routing) allows specifying a range of IPs.

 
host  all  all  192.168.0.0/24  md5  

This entry permits all hosts in the 192.168.0.0 subnet to connect.

Combining SSL with Host Authentication

For secure connections, use the hostssl type.

 
hostssl  mydb  myuser  192.168.1.0/24  cert  

Common Errors and Troubleshooting

1. Connection Refused: Ensure the client IP matches the address parameter in the entry.

2. Authentication Failed: Verify the auth-method and user credentials.

3. File Order Matters: PostgreSQL checks pg_hba.conf entries in order. Ensure specific rules appear before general ones.

Precautions

• Always test new pg_hba.conf entries in a staging environment.
• Avoid using trust authentication in production to prevent unauthorized access.
• Combine restrictive IP ranges with secure authentication methods (e.g., scram-sha-256).

All PostgreSQL Questions, Answers, and Code Snippets Collection.