Question: How do you configure a firewall for PostgreSQL replication?

Answer

Configuring a firewall for PostgreSQL replication is crucial to ensure secure and reliable data synchronization between the primary and replica servers. Here's a comprehensive guide:

Firewall Configuration

1. Identify Required Ports

   • PostgreSQL typically listens on port 5432.
   • Replication connections also use this port unless configured otherwise.

2. Allowing Traffic

   • For the simplest setup, ensure your firewall allows inbound and outbound traffic on the PostgreSQL port (default 5432) between the primary and replica servers.

Example: Using iptables

# Allow incoming connections on port 5432 from the replica server IP
iptables -A INPUT -p tcp -s <Replica-Server-IP> --dport 5432 -j ACCEPT

# Allow outgoing connections to the primary server on port 5432
iptables -A OUTPUT -p tcp -d <Primary-Server-IP> --sport 5432 -j ACCEPT

Replace <Replica-Server-IP> with the IP address of your replica server and <Primary-Server-IP> with the IP address of your primary server.

PostgreSQL Configuration

After configuring the firewall, ensure PostgreSQL is set up for replication:

1. On the primary server, edit postgresql.conf:

   listen_addresses = '*'
   wal_level = logical  # or 'replica' depending on your needs
   max_wal_senders = 5  # Adjust according to the number of replicas

2. Configure pg_hba.conf to allow replication connections:

   # On the primary server, allow the replica to connect
   host    replication     all     <Replica-Server-IP>/32      md5

3. Restart the PostgreSQL service for changes to take effect.

Security Considerations

• Always restrict access to what's necessary. Avoid using broad network ranges in your rules.
• Use strong authentication methods for your database and replication connections.
• Regularly update your firewall and PostgreSQL software to protect against known vulnerabilities.

This configuration ensures that only your designated replica can communicate with the primary server over the required port, maintaining both security and the integrity of your data replication processes.