Firewall Configuration and IP Filtering for Database Security

Firewall rules and IP filtering are essential security layers that restrict database access to authorized IP addresses only. This post covers configuring firewalls at multiple levels to protect your MariaDB database.

🎯 Defense in Depth

Implement firewall rules at multiple layers:

┌─────────────────────────────────────┐
│   Application Firewall (MariaDB)     │
│   (User + IP restrictions)            │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│   Operating System Firewall          │
│   (iptables/ufw/firewalld)           │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│   Network Firewall (Cloud/Infra)     │
│   (Security Groups/Network ACLs)     │
└─────────────────────────────────────┘

🔥 Operating System Firewall

Ubuntu/Debian: UFW (Uncomplicated Firewall)

Install and enable:

sudo apt update
sudo apt install ufw
sudo ufw enable

Allow SSH (important - do this first!):

sudo ufw allow 22/tcp

Allow MariaDB from specific IPs:

# Allow from single IP
sudo ufw allow from 192.168.1.100 to any port 3306

# Allow from subnet
sudo ufw allow from 192.168.1.0/24 to any port 3306

# Allow from multiple IPs
sudo ufw allow from 192.168.1.100 to any port 3306
sudo ufw allow from 10.0.0.50 to any port 3306

View rules:

sudo ufw status numbered

Delete rule:

sudo ufw delete <rule-number>

CentOS/RHEL: firewalld

Install and start:

sudo yum install firewalld
sudo systemctl enable firewalld
sudo systemctl start firewalld

Allow MariaDB from specific IPs:

# Allow from single IP
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.100" port protocol="tcp" port="3306" accept'

# Allow from subnet
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept'

# Reload firewall
sudo firewall-cmd --reload

List rules:

sudo firewall-cmd --list-rich-rules

iptables (Advanced)

Direct iptables rules:

# Allow from specific IP
sudo iptables -A INPUT -p tcp -s 192.168.1.100 --dport 3306 -j ACCEPT

# Allow from subnet
sudo iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT

# Deny all other connections to port 3306
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

# Save rules (Ubuntu/Debian)
sudo iptables-save > /etc/iptables/rules.v4

# Save rules (CentOS/RHEL)
sudo service iptables save

🗄️ MariaDB Built-in Firewall

MariaDB includes a firewall plugin for application-level filtering.

Install Firewall Plugin

-- Check if plugin is available
SHOW PLUGINS LIKE '%firewall%';

-- Install plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
INSTALL PLUGIN firewall SONAME 'firewall.so';

Configure Firewall Rules

Create firewall users table:

CREATE TABLE IF NOT EXISTS mysql.firewall_users (
  USERHOST VARCHAR(80) PRIMARY KEY,
  RULE TEXT
);

CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist (
  USERHOST VARCHAR(80),
  RULE TEXT,
  PRIMARY KEY (USERHOST, RULE)
);

Add allowed IPs:

-- Allow user from specific IP
INSERT INTO mysql.firewall_users VALUES
  ('[email protected]', 'ALLOW');

-- Allow user from subnet
INSERT INTO mysql.firewall_users VALUES
  ('[email protected].%', 'ALLOW');

-- Allow user from multiple IPs
INSERT INTO mysql.firewall_users VALUES
  ('[email protected]', 'ALLOW');
INSERT INTO mysql.firewall_users VALUES
  ('[email protected]', 'ALLOW');

Reload firewall:

FLUSH FIREWALL;

Query-Based Firewall Rules

Whitelist specific queries:

-- Allow SELECT queries
INSERT INTO mysql.firewall_whitelist VALUES
  ('[email protected]', 'SELECT');

-- Allow specific table access
INSERT INTO mysql.firewall_whitelist VALUES
  ('[email protected]', 'SELECT customers');
INSERT INTO mysql.firewall_whitelist VALUES
  ('[email protected]', 'SELECT products');

☁️ Cloud Provider Firewalls

AWS Security Groups

Create security group:

aws ec2 create-security-group \
  --group-name mariadb-sg \
  --description "MariaDB Security Group"

Add inbound rules:

# Allow from specific IP
aws ec2 authorize-security-group-ingress \
  --group-id sg-12345678 \
  --protocol tcp \
  --port 3306 \
  --cidr 192.168.1.100/32

# Allow from subnet
aws ec2 authorize-security-group-ingress \
  --group-id sg-12345678 \
  --protocol tcp \
  --port 3306 \
  --cidr 192.168.1.0/24

Via Terraform:

resource "aws_security_group" "mariadb" {
  name        = "mariadb-sg"
  description = "Security group for MariaDB"

  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["192.168.1.100/32"]
    description = "Allow from office IP"
  }

  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["10.0.0.0/24"]
    description = "Allow from VPC subnet"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

Google Cloud: Firewall Rules

# Create firewall rule
gcloud compute firewall-rules create allow-mariadb \
  --allow tcp:3306 \
  --source-ranges 192.168.1.100/32 \
  --target-tags mariadb-server \
  --description "Allow MariaDB from office IP"

Azure: Network Security Groups

# Create NSG rule
az network nsg rule create \
  --resource-group myResourceGroup \
  --nsg-name mariadb-nsg \
  --name allow-mariadb \
  --priority 1000 \
  --source-address-prefixes 192.168.1.100 \
  --destination-port-ranges 3306 \
  --access Allow \
  --protocol Tcp

🎯 Filess.io Firewall Configuration

On Filess.io Dedicated Runtime, configure firewall via console or API.

Via Console

1. Navigate to your database instance
2. Go to "Networking" → "Firewall"
3. Add allowed IP addresses
4. Save configuration

Via API

Add allowed IP:

# Visual example of firewall rule creation
# (Imagine a beautiful UI here)

Via CLI

# Add IP
filess-cli firewall add \
  --database-id abc123 \
  --ip 192.168.1.100 \
  --description "Office IP"

# List rules
filess-cli firewall list --database-id abc123

# Remove IP
filess-cli firewall remove \
  --database-id abc123 \
  --rule-id xyz789

🔄 Dynamic IP Management

Script to Update Firewall Rules

#!/bin/bash
# update-firewall-ip.sh

DB_ID="abc123"
FILESS_TOKEN="your_token"
CURRENT_IP=$(curl -s https://api.ipify.org)

echo "Current IP: $CURRENT_IP"

# Add current IP to firewall
{{ ... }}

echo "Firewall rule added for IP: $CURRENT_IP"

Cron Job for Dynamic IPs

# Add to crontab
crontab -e

# Update firewall every hour
0 * * * * /path/to/update-firewall-ip.sh >> /var/log/firewall-update.log 2>&1

🛡️ Advanced Firewall Strategies

Geo-blocking

Block connections from specific countries:

# Using iptables with ipset
sudo apt install ipset

# Create country block list
sudo ipset create blocked_countries hash:net

# Add country IP ranges (example: block China)
# Download IP ranges and add to ipset
wget http://www.ipdeny.com/ipblocks/data/countries/cn.zone
for i in $(cat cn.zone); do sudo ipset add blocked_countries $i; done

# Block in iptables
sudo iptables -A INPUT -p tcp --dport 3306 -m set --match-set blocked_countries src -j DROP

Rate Limiting

Limit connection attempts:

# Limit to 5 connections per minute per IP
sudo iptables -A INPUT -p tcp --dport 3306 -m state --state NEW \
  -m recent --set --name mariadb
sudo iptables -A INPUT -p tcp --dport 3306 -m state --state NEW \
  -m recent --update --seconds 60 --hitcount 5 --name mariadb -j DROP

Fail2Ban Integration

Automatically ban IPs after failed login attempts:

Install Fail2Ban:

sudo apt install fail2ban

Create MariaDB jail:

# /etc/fail2ban/jail.d/mariadb.conf
[mariadb]
enabled = true
port = 3306
filter = mariadb
logpath = /var/log/mysql/error.log
maxretry = 3
bantime = 3600
findtime = 600

Create filter:

# /etc/fail2ban/filter.d/mariadb.conf
[Definition]
failregex = ^.*Access denied for user '.*'@'<HOST>'.*$
ignoreregex =

Restart Fail2Ban:

sudo systemctl restart fail2ban
sudo fail2ban-client status mariadb

📊 Monitoring Firewall Activity

Log Firewall Blocks

Configure MariaDB to log blocked connections:

-- Enable general query log
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- View blocked connections
SELECT * FROM mysql.general_log 
WHERE command_type = 'Connect' 
AND argument LIKE '%Access denied%';

Firewall Audit Script

#!/bin/bash
# firewall-audit.sh

echo "=== Firewall Rules Audit ==="
echo ""

# OS Firewall
echo "OS Firewall (UFW):"
sudo ufw status numbered
echo ""

# MariaDB Firewall
echo "MariaDB Firewall Users:"
mysql -u root -p -e "SELECT * FROM mysql.firewall_users;"
echo ""

# Recent connection attempts
echo "Recent Connection Attempts:"
sudo tail -n 50 /var/log/mysql/error.log | grep "Access denied"

🧪 Testing Firewall Configuration

Test Script

#!/bin/bash
# test-firewall.sh

ALLOWED_IP="192.168.1.100"
BLOCKED_IP="192.168.1.200"
DB_HOST="your-db-host"
DB_USER="test_user"
DB_PASS="test_password"

echo "Testing firewall configuration..."

# Test 1: Allowed IP should connect
echo "Test 1: Testing allowed IP..."
if mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SELECT 1" 2>/dev/null; then
  echo "✓ Allowed IP can connect"
else
  echo "✗ Allowed IP cannot connect (unexpected)"
fi

# Test 2: Blocked IP should not connect
echo "Test 2: Testing blocked IP..."
# This would need to be run from the blocked IP
# mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SELECT 1"

echo "Firewall tests completed"

🎯 Integration with Example App

Add firewall status endpoint:

// src/routes/admin.routes.ts
router.get('/firewall/status', async (req, res) => {
  const rules = await firewallService.getRules();
  res.json({
    rules,
    totalRules: rules.length,
    lastUpdated: await firewallService.getLastUpdate()
  });
});

✅ Security Checklist

• OS firewall configured (UFW/firewalld/iptables)
• MariaDB firewall plugin enabled
• Only necessary IPs allowed
• Cloud security groups configured
• Fail2Ban installed and configured
• Firewall rules documented
• Regular firewall audits scheduled
• Monitoring and alerting set up
• Dynamic IP management configured
• Backup access IPs configured

🔮 Best Practices

1. Principle of Least Privilege: Only allow necessary IPs
2. Regular Audits: Review firewall rules quarterly
3. Documentation: Document all allowed IPs and reasons
4. Monitoring: Alert on blocked connection attempts
5. Testing: Regularly test firewall rules
6. Backup Access: Ensure backup systems have access
7. Emergency Access: Maintain emergency access method

With proper firewall configuration at multiple layers, your database is protected from unauthorized access while maintaining necessary connectivity for authorized systems.