Secure SQL Access: IP Whitelisting

TL;DR

Allowing direct SQL connections from the internet is risky. This guide shows you how to restrict access to specific, trusted IPs using your database server’s firewall and configuration settings. We’ll cover common databases like MySQL/MariaDB and PostgreSQL.

Steps

1. Understand the Risks
• Direct SQL connections expose your database to potential attacks (SQL injection, brute-force).
• Without restrictions, anyone could try to connect.
• IP whitelisting is a basic but effective security measure.
2. Identify Trusted IPs
• List all the IP addresses that *need* access to your database. This might include:
• Your application servers’ public IPs
• Developer machines (for testing – use with caution!)
• Jump boxes or bastion hosts
• Document why each IP needs access.
3. MySQL/MariaDB Configuration
• Edit the MySQL configuration file (usually my.cnf or my.ini). Location varies by OS. Common locations:
• Linux: /etc/mysql/my.cnf, /etc/my.cnf, ~/.my.cnf
• Windows: C:ProgramDataMySQLMySQL Server X.Xmy.ini (replace X.X with your version)
• Add a bind-address directive to restrict listening IPs. For example, to allow only connections from 192.168.1.10 and 10.0.0.5:

bind-address = 192.168.1.10

bind-address = 10.0.0.5

• Important: Each IP needs its own bind-address line.
• Restart the MySQL server for changes to take effect. Example commands:
• Linux (systemd): sudo systemctl restart mysql
• Windows: Restart the MySQL service through Services Manager.
4. MySQL/MariaDB Firewall Rules
• Configure your server’s firewall (e.g., iptables, firewalld, Windows Firewall) to allow connections only from the trusted IPs on port 3306 (the default MySQL port).
• Example using iptables:

sudo iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.10 -j ACCEPT

sudo iptables -A INPUT -p tcp --dport 3306 -s 10.0.0.5 -j ACCEPT

sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

• Important: Ensure the rules are ordered correctly – allow trusted IPs *before* dropping all other connections.
• Save your firewall rules to make them persistent across reboots. (e.g., sudo iptables-save > /etc/iptables/rules.v4)
5. PostgreSQL Configuration
• Edit the PostgreSQL configuration file (postgresql.conf). Location varies by OS, but is often in /etc/postgresql//main/ on Linux.
• Modify the listen_addresses directive to specify which IPs PostgreSQL listens on. To listen only on specific IPs:

listen_addresses = '192.168.1.10, 10.0.0.5'

• Edit the pg_hba.conf file to control client authentication based on IP address. Add lines like these:

host   all             all             192.168.1.10/32            md5

host   all             all             10.0.0.5/32               md5

• The /32 specifies a single IP address. Use appropriate CIDR notation for ranges.
• Restart the PostgreSQL server:

sudo systemctl restart postgresql

6. PostgreSQL Firewall Rules
• Configure your firewall to allow connections only from trusted IPs on port 5432 (the default PostgreSQL port). Use similar commands as in the MySQL example, replacing 3306 with 5432.
7. Testing
• From a trusted IP, try connecting to your database using a SQL client (e.g., MySQL Workbench, pgAdmin).
• From an untrusted IP, attempt the same connection – it should be refused.
8. Regular Review
• Periodically review your list of trusted IPs to ensure they are still valid and necessary.
• Remove any IPs that no longer require access.