When you connect to a server (or cluster) using a TCP proxy level 7, also referred to as an application-level or Layer 7 proxy (highest level of the OSI model), the application doesn’t connect directly to the back-end server(s). The proxy usually understands the protocol used and can eventually take some decisions or even change the request.
The problem when using such proxy (like HA Proxy, ProxySQL and MySQL Router) is that the server doesn’t really know from where the client is connecting. The server sees the IP address of the proxy/router as the source IP of the client.
HA Proxy initially designed the Proxy Protocol, a simple protocol that allows a TCP connection to transport proxy-related information between the client, the proxy server and the destination server. The main purpose of the Proxy Protocol is then to preserve the client’s original IP address (with some others metadata). See HA Proxy’s manual.
The back-end application must also be aware of this protocol and understand it to be able to benefit from it.
However, there are potential security issues associated with the Proxy Protocol (like spoofing, information leakage, Denial of Service using malformed or very large Proxy Protocol headers, …).
For these reasons, as you know security is very important for us at Oracle, MySQL doesn’t support the Proxy Protocol.
Does this mean that if you use MySQL Router you have no way of knowing the client’s IP address?
Of course not, if you use a secure connection (SSL client), the MySQL Router adds attributes to the handshake and these attributes are available on the server. See Connections Attributes.
With this simple query, it is now possible to list the connections and originating IP addresses of clients when they connect via MySQL Router:
select program_name, last_statement, user,attr_value 'client ip'
from performance_schema.session_account_connect_attrs
join sys.processlist on conn_id=processlist_id
where attr_name='_client_ip' ;
+--------------+----------------------------------+-------------------------+-----------+
| program_name | last_statement | user | client ip |
+--------------+----------------------------------+-------------------------+-----------+
| mysqlsh | NULL | root@mysql-router-shell | 127.0.0.1 |
| mysql | select @@version_comment limit 1 | root@mysql-router-shell | 10.0.0.76 |
+--------------+----------------------------------+-------------------------+-----------+MySQL Server and MySQL Router don’t support Proxy Protocol but implement the connection attributes that provides even more information.
As usual, enjoy MySQL !
Hi Frederic;
Thanks for your article, can you confirm from which MySQL version, it will populate with data, and do we need to enable anything before running this query?
Hi Mohsin,
I think that this was already enabled in 8.0.23. Nothing needs to be configured (other than ssl_client_mode, but the default is good).
Thanks.
Hi Fred,
we created user as ‘user’@’10.0.0.1’ and tried to connect mysql innodb cluster db using mysql router. We are getting access denied error for user ‘user’@’routerproxy’ error. We could see that the mysql router used its own hostname as the client ip which in turn made the connection failed. Since the purpose is to restrict the unwanted client authentication when using ‘%’. How we can ensure client can authenticate as per the user id?
Hi Fred,
we created user as ‘user’@’10.0.0.1’ and tried to connect mysql innodb cluster db using mysql router. We are getting access denied error for user ‘user’@’routerproxy’ error. Could see that the mysql router used its own hostname envelop and send the connection request to database available instance which in turn made the connection failed. Since the purpose is to restrict the unwanted client authentication when using ‘%’. How we can ensure client can authenticate as per the user id?
I really wish the example had used session_connect_attrs instead of session_account_connect_attrs. I was seeing only 1 row (mine) and assumed it was because only my session was using a SSL client. A year later, I had reason to revisit and realized simply modifying the view name in the SQL was all it takes to see client ip for other sessions.
select program_name, last_statement, user, attr_value ‘client ip’
from performance_schema.session_connect_attrs
join sys.processlist on conn_id=processlist_id
where attr_name=’_client_ip’ ;