Restricting MySQL and PostgreSQL Database Access to Local IPs

Securing your database against unauthorized external connections is one of the most critical responsibilities for any systems administrator. If your database instance listens on a public interface without strict firewall rules and host-based access controls, you invite brute-force attacks, data breaches, and unauthorized enumeration. By default, many database engines bind to all network interfaces, exposing your services to the entire internet if network boundary controls fail.

To safely manage remote connections, you need to know your administrative client's public routing address before applying perimeter rules. You can quickly check your current egress endpoint using the What Is My IP tool on XiaTools to identify the exact public IP address your team uses for database administration. This ensures you only permit traffic from trusted administrative networks while blocking all other external scan attempts.

Understanding Database Network Exposure

Database servers like MySQL and PostgreSQL traditionally listen on standard ports—3306 and 5432 respectively. When cloud servers or virtual private servers (VPS) are provisioned, these ports are sometimes exposed to 0.0.0.0/0 (the entire internet) or bound to public network interfaces by default.

Exposing a database directly to the public internet without an intermediate VPN, SSH tunnel, or strict IP whitelisting is a major security risk. While using a VPN or SSH bastion host is the gold standard, many teams require direct application-to-database connections or explicit remote database management. When direct public connection is unavoidable, restricting access at both the operating system firewall layer and the database grant/privilege layer is mandatory.

The Defense-in-Depth Approach

Securing public database access requires three distinct layers of defense:

1. Network Firewall / Security Groups: Drops unauthorized packets before they reach the database daemon.
2. Database Configuration (Bind Address): Instructs the database engine to listen only on specific interfaces or localhost.
3. User Access Grants: Restricts specific database user accounts to connect only from explicitly defined source IP addresses.

Restricting MySQL Database Access

To restrict MySQL or MariaDB access to a specific public IP address, you must adjust the configuration file, apply firewall rules, and update user account privileges.

Step 1: Configure the Bind Address

Open your MySQL configuration file, typically located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf. Find the bind-address directive.

[mysqld]
# Bind to a specific public/private interface IP or localhost
bind-address = 198.51.100.45

If you want the database to listen on local interfaces only, set it to 127.0.0.1. If it must accept connections from a specific public IP via a public interface, set it to the server's public IP address or use 0.0.0.0 combined with strict firewall rules.

Restart the MySQL service to apply changes:

sudo systemctl restart mysql

Step 2: Restrict User Grants to a Specific IP

In MySQL, user accounts are defined as username@host. To restrict a user so they can only connect from an authorized public IP (for example, 203.0.113.50), use the following SQL commands:

-- Create a user restricted to a specific public IP
CREATE USER 'app_admin'@'203.0.113.50' IDENTIFIED BY 'StrongPassword123!';

-- Grant necessary privileges
GRANT SELECT, INSERT, UPDATE ON production_db.* TO 'app_admin'@'203.0.113.50';

-- Apply changes
FLUSH PRIVILEGES;

If an existing user needs their allowed IP address updated, run:

RENAME USER 'app_admin'@'%' TO 'app_admin'@'203.0.113.50';
FLUSH PRIVILEGES;

Step 3: Apply Firewall Rules (UFW / iptables)

Never rely solely on database user grants. Use your operating system firewall to drop unwanted traffic on port 3306.

# Allow a specific public IP address
sudo ufw allow from 203.0.113.50 to any port 3306 proto tcp

# Deny all other traffic to port 3306
sudo ufw deny 3306/proto tcp
sudo ufw reload

If you are using cloud infrastructure (AWS, GCP, Azure), navigate to your cloud console, locate your Virtual Private Cloud (VPC) Security Groups or Firewall Rules, and modify the inbound rules for port 3306 to allow only your administrative public IP range instead of 0.0.0.0/0.

Restricting PostgreSQL Database Access

PostgreSQL handles network access restrictions primarily through two configuration files: postgresql.conf and pg_hba.conf.

Step 1: Configure Listen Addresses

Open /etc/postgresql/{version}/main/postgresql.conf and locate the listen_addresses parameter.

listen_addresses = 'localhost,198.51.100.45'

Setting this to '*' tells PostgreSQL to listen on all available interfaces, which is safe as long as your pg_hba.conf file strictly controls authentication.

Step 2: Configure Client Authentication in pg_hba.conf

Open /etc/postgresql/{version}/main/pg_hba.conf. This file controls which hosts are allowed to connect, which database users they can connect as, and what authentication method is required.

Add a specific rule for your trusted public IP address at the top of the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    production_db   app_user        203.0.113.50/32         scram-sha-256

Ensure that any broad rules like host all all 0.0.0.0/0 md5 are removed or commented out to prevent unauthorized public access.

Reload PostgreSQL to apply the configuration changes:

sudo systemctl restart postgresql

Step 3: Configure Cloud Security Groups for PostgreSQL

Just like MySQL, ensure your cloud provider's firewall or security group permits inbound traffic on port 5432 exclusively from your authorized public IP address.

Comparing Access Restriction Methods

Method	Security Level	Complexity	Best Use Case
SSH Tunnel / Port Forwarding	Very High	Medium	Administrative tasks and direct management
IP Whitelisting (Firewall + DB)	High	Low	Application servers with static public IPs
VPN Gateway (WireGuard/OpenVPN)	Very High	High	Distributed teams and dynamic remote workers
Public IP with Password Only	Critical Risk	Very Low	Never recommended for production

Common Mistakes and How to Fix Them

1. Using Wildcard Hosts (% or 0.0.0.0/0)

• The Mistake: Creating MySQL users with @'%' or setting PostgreSQL pg_hba.conf to allow 0.0.0.0/0 for convenience.
• The Fix: Replace wildcards with specific static public IP addresses or utilize a secure SSH tunnel for remote administration.

2. Forgetting Cloud Provider Firewalls

• The Mistake: You update UFW on your Ubuntu server, but forget to update AWS Security Groups or Google Cloud Firewall rules, leaving ports open at the hypervisor level.
• The Fix: Always audit your cloud provider console firewall settings alongside your local OS firewall rules.

3. Dynamic IP Address Lockouts

• The Mistake: Whitelisting a home office public IP address that changes dynamically via your Internet Service Provider (ISP), resulting in sudden connection drops.
• The Fix: Use a static IP address, connect via a corporate VPN with a dedicated egress IP, or update your firewall automation scripts regularly.

Quick Checklist for Secure Database Access

• Identify your authorized administrative public IP address.
• Bind the database service to specific interfaces rather than 0.0.0.0 where possible.
• Configure OS firewall or cloud security groups to restrict ports 3306/5432.
• Update database user grants and authentication files (pg_hba.conf) to target specific IPs.
• Test connections from both an authorized IP and an unauthorized IP to verify restrictions.