Using HAProxy with the Proxy Protocol to Better Secure Your Database

The Proxy protocol is a widely used invention of our CTO at HAProxy Technologies, Willy Tarreau, to solve the problem of TCP connection parameters being lost when relaying TCP connections through proxies. Its primary purpose is to chain proxies and reverse-proxies without losing client information, and it’s used and supported by AWS ELB, Apache, NGINX, Varnish, Citrix, and many more (here’s a longer list of Proxy protocol supported technologies).

Why use the Proxy protocol? Well, when you lose client information like IP address when relaying connections through proxies, this tends to prevent you from being able to implement some pretty basic IP-level security and logging.

So you may not really know who you’re letting in to access your data:

In this post, I’ll show you how to use the Proxy protocol with HAProxy to enhance the security of your database.

Background

Let’s take a step back and explain a bit (Proxy protocol experts can skip this section). A proxy uses its IP stack to connect to remote servers, but this process normally loses the initial TCP connection data, including the source IP address, destination IP address, and port number.

Traditional workarounds to this problem include Tproxy, which requires you to compile your kernel and set your proxy as your server’s default gateway. Furthermore, Tproxy can’t pass IP packets through firewalls that use NAT. You can also use HTTP X-Forwarded-For headers to retain TCP connection data, although this approach only works for HTTP. Many of these workarounds also require either a specific protocol or architectural changes, which prevents you from scaling up.

The Proxy protocol is protocol independent, meaning that it works with any layer-7 protocol. It doesn’t require infrastructure changes, works with NAT firewalls, and is scalable. The Proxy protocol’s only technical requirement is that both of the connection’s endpoints must be compatible with the Proxy protocol. These endpoints could include proxies, reverse proxies, load-balancers, application servers, and WAFs.

The Database Security Problem

An HAProxy server running without the Proxy protocol can create a couple security problems when you grant users access to the MySQL servers behind it.

First, Slow query logs and “show full processlist” commands on a MySQL server behind an HAProxy server that isn’t running the Proxy protocol won’t show the correct IP addresses of the clients, making it more difficult to identify hosts which are sending unoptimized, incorrect or queries injected via SQLi. Consider the following entry from a slow query log:

# Time: 2017-03-09T21:56:07.640875Z
# User@Host: test[test] @ centos7vert [192.168.122.64] Id: 11
# Schema: Last_errno: 0 Killed: 0
# Query_time: 7.026677 Lock_time: 0.025969 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 104
SET timestamp=1489096567;
SELECT * FROM test.test WHERE sleep(7);

The above example shows that a host on or behind the HAProxy server at 192.168.122.64 has issued a query that is suspicious or has performance issues, but we can’t identify the host. You could review the HAProxy logs for activity that occurred around the indicated time to identify the host, but this approach is usually impractical due to the delay between the timestamps of the HAProxy and MySQL logs.

The second security issue you may face from seeing the incorrect IP address is that MySQL grants can no longer allow just one client to use a given username if it is allowed to access MySQL through the HAProxy server via its firewall/ACL’s, as to MySQL all IP’s look the same. With the Proxy protocol, you can maintain better privilege segregation between databases even if an attacker manages to get the password for a MySQL user dedicated to another client.

Using the Proxy Protocol for a More Secure Database

Now here’s the good part: how to install, configure and use the Proxy protocol with a MySQL database. Note: you can use either HAProxy Community or HAProxy Enterprise with these instructions.

1. Percona installation

The instructions on the Percona website provide the details for installing Percona Server.

2. HAProxy configuration

Add a section to the HAProxy configuration file like the following:

frontend fe_mysqld
bind *:3306
mode tcp
log global
option tcplog
use_backend be_mysqld
backend be_mysqld
mode tcp
option mysql-check user haproxy post-41
server percona_server 192.168.122.185:3306 check

Add a grant to the Percona server so it will allow health checks with a MySQL command as follows:

mysql> CREATE USER 'haproxy'@'192.168.122.64';

Restart HAProxy after adding the grant statements to the Percona server.

3. New grant statements

An HAProxy server that doesn’t use the Proxy protocol requires you to add grant statements just for the IP address of the HAProxy server. The Proxy protocol requires you to add grants for servers that will be behind an HAProxy server as if the proxy wasn’t there. For example, assume that you want the host at IP address 192.168.122.1 to connect to the database. The following MySQL statement will add said grant:

mysql> CREATE USER 'test'@'192.168.122.1';

The HAProxy server will use these grants instead of the existing grants once the Proxy protocol has been enabled on the HAProxy and Percona servers.

4. Percona configuration

In the mysqld section of /etc/mysql/percona-server.conf.d/mysqld.cnf add the following line:

proxy_protocol_networks="192.168.122.64"

The above statement will accept a single IP address, multiple IP addresses separated by commas or a masked IP address like 192.168.122.0/24. An asterisk (“*”) for the IP address will cause the server to accept the Proxy protocol from any host, although this isn’t recommended for security reasons. These IPs must be trusted to send correct Proxy protocol information before adding them to the list.

Note that Percona won’t accept the Proxy protocol from 127.0.0.1 even if it is in proxyprotocolnetworks; so if you are running HAProxy on the same server that you are running Percona on this issue may rear its ugly head.

Add an entry for each HAProxy server that will handle traffic to a Percona server. You must enable the Proxy protocol for the Percona and HAProxy servers or disable it for both at the same time. If the Proxy protocol configurations of the HAProxy and Percona servers don’t match, the connections will fail with a “packets received out of order” error. You may also want to configure the firewall on the Percona server to only accept connections from an HAProxy server, which ensures that clients must use an HAProxy server to access the Percona server.

The status of the Percona server will be marked as “down” in HAProxy after you enable the proxyprotocolnetworks statement on the Percona server, provided you didn’t manually disable it during transmission (and are following this guide in order and haven’t added send-proxy yet). This problem occurs because Percona now requires the Proxy protocol but HAProxy isn’t yet sending it. You’ll fix this in the next step. Health checks will mark the node as down until send-proxy is added (and regular connections through it will encounter the same error).

5. Send-proxy

Append the following argument send-proxy to the server line (of the Percona server) in the backend section of the HAProxy configuration:

server percona_server 192.168.122.185:3306 check send-proxy

The above statement tells HAProxy to send the Proxy protocol packet for both health checks and normal connections.

If you are chaining multiple HAProxy servers together you may wish to use send-proxy-v2 instead of send-proxy. Its a newer version of the protocol that is binary rather than text so is faster to parse. The downside is that servers need to support v2 of the protocol (Percona server does, so you can just put send-proxy-v2 in and it will work as expected) and that debugging its parsing is a bit more difficult from a packet capture.

With this added HAProxy will also send the Proxy protocol for health checks, but the result won’t be used by Percona as it won’t accept 127.0.0.1; as such your existing grants for health checks will continue to be used.

6. Rest easier with better security in place

The proxy protocol makes a Percona server more secure and easier to manage as shown by the now-correct text in the highlighted line (2) int the following Percona slow log entry:

# Time: 2017-03-09T22:41:20.195387Z
# User@Host: test[test] @ [192.168.122.1] Id: 540
# Schema: Last_errno: 0 Killed: 0
# Query_time: 7.039918 Lock_time: 0.038505 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 104
SET timestamp=1489099280;
SELECT * FROM test.test WHERE sleep(7);

The previous output is produced by the same query (and from the same node) that was shown in The Database Security Problem section, but we can now see that the client’s actual IP address is 192.168.122.1.

IP addresses with the proxy protocol in the process list table

The IP addresses in the process list table are also more useful with the Proxy protocol as can be seen here:

You can also run grants on the MySQL server with the client’s real IP address instead of just the HAProxy server’s IP address. This change is primarily useful to better understand the items in the grant tables during later changes/audits and to ensure that users can only access select user accounts from select IP addresses.

So you go from letting anyone in who seems to have legitimate credentials (with possible malicious intent):