CentOS PostgreSQL Network Configuration Guide

This guide provides step-by-step instructions for configuring network access to PostgreSQL on CentOS, covering essential steps for allowing remote connections while emphasizing security best practices.

1. Install PostgreSQL (If Not Already Installed)

Before configuring network settings, ensure PostgreSQL is installed on your CentOS system. Use the following commands to install the latest version from the official repository:

sudo yum update -y
sudo yum install -y postgresql-server postgresql-contrib

Initialize the database cluster (replace version with your installed PostgreSQL version, e.g., 15):

sudo /usr/pgsql/version/bin/postgresql-setup initdb

Start the PostgreSQL service and enable it to start on boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql

2. Configure postgresql.conf for Network Listening

The postgresql.conf file controls PostgreSQL’s network behavior. Modify it to allow incoming connections:

sudo vi /var/lib/pgsql/data/postgresql.conf

• Set listen_addresses: Change this parameter to allow connections from specific IPs or all IPs. For production, restrict to trusted IPs (e.g., 192.168.1.100) instead of * (all IPs).

  listen_addresses = '*'  # Allow all IPs (not recommended for production)
  # OR
  listen_addresses = '192.168.1.100,localhost'  # Restrict to specific IPs
  

• Optional: Change the Port: If you want to use a non-default port (e.g., 5433), modify the port parameter:

  port = 5433
  

Save and exit the editor.

3. Configure pg_hba.conf for Client Authentication

The pg_hba.conf file defines which clients can connect and the authentication method. Edit it to allow remote connections:

sudo vi /var/lib/pgsql/data/pg_hba.conf

Add rules at the end of the file to permit remote access. For example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5  # Allow all IPs (use with caution)
# OR
host    all             all             192.168.1.0/24          md5  # Restrict to a subnet
host    all             all             ::1/128                 md5  # Allow IPv6 localhost

• Explanation:
  • host: Enables TCP/IP connections.
  • all: Applies to all databases and users.
  • 0.0.0.0/0: Allows all IPv4 addresses (replace with a subnet like 192.168.1.0/24 for security).
  • md5: Requires password-based authentication (use scram-sha-256 for stronger encryption in newer PostgreSQL versions). Save and exit.

4. Restart PostgreSQL to Apply Changes

After modifying the configuration files, restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

5. Configure Firewall Rules

Allow PostgreSQL’s default port (5432, or your custom port) through the firewall to permit external access:

# For firewalld (default on CentOS 7/8)
sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp
sudo firewall-cmd --reload

# For iptables (older systems)
sudo iptables -I INPUT -p tcp --dport 5432 -j ACCEPT
sudo service iptables save
sudo service iptables restart

6. Verify Connectivity

Test the connection from a remote machine using psql (replace placeholders with your server’s IP, username, and database name):

psql -h your_server_ip -U your_username -d your_database

Enter the password when prompted. If the connection succeeds, the configuration is correct.

7. Security Considerations

• Restrict listen_addresses: Avoid using * in production. Limit to specific IPs or subnets to reduce exposure.
• Use Strong Authentication: Replace md5 with scram-sha-256 (PostgreSQL 10+) for better password security.
• Enable SSL: Encrypt traffic between clients and the server by setting ssl = on in postgresql.conf and configuring SSL certificates.
• Limit User Privileges: Grant minimal permissions to remote users (e.g., REVOKE CREATE ON DATABASE your_database FROM PUBLIC;).

Optional: Additional Optimizations

• Adjust Connection Limits: Increase max_connections in postgresql.conf if you expect many concurrent connections (default is 100).
• Use a Connection Pooler: Tools like PgBouncer improve performance by reusing database connections.
• Monitor Performance: Use tools like pg_stat_activity and netstat to monitor network usage and identify bottlenecks.

By following these steps, you can securely configure PostgreSQL on CentOS to allow network access while minimizing risks. Always test configurations in a non-production environment before applying them to live systems.