[MDEV-22798] proxy-protocol-networks not working since migration in Mariadb 10.4.13 Created: 2020-06-04  Updated: 2022-06-13  Resolved: 2022-06-13

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.4.13
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Nicolas PEYRESAUBES Assignee: Ramesh Sivaraman
Resolution: Incomplete Votes: 0
Labels: galera, proxy_protocol_networks, regression
Environment:

OS : Debian Buster
Mariadb : 10.4.13
Galera : 4
Haproxy : 1.8.19

Issue Links:
Relates
relates to MDEV-28055 Galera ps-protocol fixes Closed

 Description   

Dear All,

Since I migrated from Mariadb 10.3.22 to 10.4.13, I get some regression with the use of proxy-protocol-networks.
Haproxy doesn't see any mysql backend alive anymore and my "clienthost" cannot authenticate themselves in Mariadb based on their IP.
On mariadb's side I tried the following things

  • proxy-protocol-networks = 172.16.42.0/24, localhost
  • proxy-protocol-networks = *
  • proxy-protocol-networks = 172.16.42.8, 172.16.42.9, 172.16.42.10, localhost

and on haproxy's side :

backend mygalera
 
        balance leastconn
 
        mode tcp
 
        option tcpka
 
        option tcplog
 
        option log-health-checks
 
        retries 3
 
        option mysql-check user haproxy
 
        server myglra1 172.16.42.21:3306 check weight 1 send-proxy-v2
 
        server myglra2 172.16.42.22:3306 check weight 1 send-proxy-v2
 
        server myglra3 172.16.42.23:3306 check weight 1 send-proxy-v2

I also tried with "send-proxy"

On haproxy I get the following logs 

May 27 10:10:21 haproxy001 haproxy[2958]: Health check for server mygalera/myglra1 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2ms, status: 0/2 DOWN.
 
May 27 10:10:21 haproxy001 haproxy[2958]: Health check for server mygalera/myglra1 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2ms, status: 0/2 DOWN.
 
May 27 10:31:53 haproxy001 haproxy[3056]: Health check for server mygalera/myglra1 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 1ms, status: 0/2 DOWN.
 
May 27 10:31:53 haproxy001 haproxy[3056]: Health check for server mygalera/myglra1 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 1ms, status: 0/2 DOWN.
 
May 27 10:31:54 haproxy001 haproxy[3056]: Health check for server mygalera/myglra2 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2ms, status: 0/2 DOWN.
 
May 27 10:31:54 haproxy001 haproxy[3056]: Health check for server mygalera/myglra2 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2ms, status: 0/2 DOWN.
 
May 27 10:31:54 haproxy001 haproxy[3056]: Health check for server mygalera/myglra3 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2ms, status: 0/2 DOWN.
 
May 27 10:31:54 haproxy001 haproxy[3056]: Health check for server mygalera/myglra3 failed, reason: Layer7 wrong status, code: 0, info: "#HY000Proxy header is not accepted from 172.16.42.9", check duration: 2

and after a while I get the followinf error for each of my galera's nodes

May 28 10:07:22 haproxy001 haproxy[4028]: Health check for server mygalera/myglra1 failed, reason: Layer7 wrong status, code: 0, info: "Host '172.16.42.9' is blocked because of many connection errors; unbloc
 
k with 'mysqladmin flush-hosts'", check duration: 3ms, status: 0/2 DOWN.

On my galera's nodes I get following errors repeating till the source ip is blocked

May 28 10:51:01 myglra001 mysqld[19634]: 2020-05-28 10:51:01 2144 [Warning] Aborted connection 2144 to db: 'unconnected' user: 'unauthenticated' host: '172.16.42.9' (This connection closed normally without authentication)

All of this leads to all backend seen as down.
I had a some test being done on a maxscale node and the behaviour/results are the same as haproxy (no live backend seen)
I was using proxy_protocol=true in my servers' definition in maxscale.cnf

If I skip all these proxy configuration things and create users in mariadb on haproxy's ips everything is working fine, but i lose the client IP.

Regards

 Comments   
Comment by Nicolas PEYRESAUBES [ 2020-07-24 ]

I tried to upgrade to 10.5.3 ... but it doesn't change anything concerning this problem.

Comment by Vladislav Vaintroub [ 2020-08-13 ]

jplindst, I'm positive that this work in normal standalone server, which is not Galera.

We do have a test for this inside mysql_client_test, this is in test suite (https://github.com/MariaDB/server/blob/10.5/tests/mysql_client_test.c, look for test_proxy_header()) , and it always succeeds. Besides , there is not difference between 10.3 and 10.4 proxy protocol handling. But there is a difference in Galera, so I'm assigning you to check what's wrong.

You'd need to implement test like this , also for Galera.

Comment by Yannis Fragkoulis [ 2021-09-03 ]

Have the same issue after upgrading to 10.5 from 10.3 (debian buster to bullseye). I am also behind haproxy and running mariadb in a container. Using `send-proxy` on the haproxy backend configuration now fails and mariadb logs:

```
2021-09-03 17:03:01 505 [Warning] Aborted connection 505 to db: 'unconnected' user: 'unauthenticated' host: 'connecting host' (This connection closed normally without authentication)
```

Comment by Daniel Black [ 2022-05-05 ]

Note the mysql-check in haproxy is rather crude.

Comment by Ramesh Sivaraman [ 2022-05-09 ]

Could not reproduce the issue on 10.4.24. Tried to configure galera hosts in HAproxy listen section to check the connection status. HAProxy could not be started while using the backend section mentioned in the bug description. Please share the haproxy.cfg file and try to check the connection status using the {{listen} section as mentioned in the test case below.

 
 
MariaDB [(none)]> select @@proxy_protocol_networks;
 
+---------------------------+
 
| @@proxy_protocol_networks |
 
+---------------------------+
 
| 192.168.100.0/24          |
 
+---------------------------+
 
1 row in set (0.000 sec)
 
 
 
 
 
HAproxy connection 
vagrant@haproxy:~$ mysql -uhaproxy -h192.168.100.40 -e  'select  user(),current_user(),@@hostname';
 
+------------------------+------------------------+------------+
 
| user()                 | current_user()         | @@hostname |
 
+------------------------+------------------------+------------+
 
| haproxy@192.168.100.40 | haproxy@192.168.100.40 | node1      |
 
+------------------------+------------------------+------------+
 
vagrant@haproxy:~$ mysql -uhaproxy -h192.168.100.40 -e  'select  user(),current_user(),@@hostname';
 
+------------------------+------------------------+------------+
 
| user()                 | current_user()         | @@hostname |
 
+------------------------+------------------------+------------+
 
| haproxy@192.168.100.40 | haproxy@192.168.100.40 | node2      |
 
+------------------------+------------------------+------------+
 
vagrant@haproxy:~$ cat /etc/haproxy/haproxy.cfg
 
global
 
    log 127.0.0.1 local0 notice
 
    log 127.0.0.1 local1
 
    user haproxy
 
    group haproxy
 
 
 
defaults
 
    log global
 
    retries 2
 
    timeout connect 3000
 
    timeout server 5000
 
    timeout client 5000
 
 
 
 
 
listen mysql-cluster
 
    bind *:3306
 
    mode tcp
 
    option mysql-check user haproxy
 
    balance roundrobin
 
    server mysql-1 192.168.100.10:3306 check send-proxy-v2
 
    server mysql-2 192.168.100.20:3306 check send-proxy-v2
 
 
 
HAProxy cluster node status
 
 
 
May  9 10:27:46 haproxy haproxy[7584]: [WARNING] 128/102746 (7584) : Server mysql-cluster/mysql-2 is UP, reason: Layer7 check passed, code: 0, info: "5.5.5-10.4.24-MariaDB-1:10.4.24+maria~focal-log", check duration: 67ms. 1 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.
 
May  9 10:27:47 haproxy haproxy[7584]: [WARNING] 128/102747 (7584) : Server mysql-cluster/mysql-1 is UP, reason: Layer7 check passed, code: 0, info: "5.5.5-10.4.24-MariaDB-1:10.4.24+maria~focal-log", check duration: 70ms. 2 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.
 
 
 
 
 
Sysbench log
 
vagrant@haproxy:~$ sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=test --mysql-user=haproxy  --mysql-host=192.168.100.40 --db-driver=mysql   --threads=10 --tables=10 --table-size=1000 --report-interval=10 --time=50 run 
sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 10
 
Report intermediate results every 10 second(s)
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
[ 10s ] thds: 10 tps: 4224.45 qps: 4224.45 (r/w/o: 0.00/4224.45/0.00) lat (ms,95%): 3.55 err/s: 0.00 reconn/s: 0.00
 
[ 20s ] thds: 10 tps: 4201.87 qps: 4201.87 (r/w/o: 0.00/4201.87/0.00) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
 
[ 30s ] thds: 10 tps: 4300.32 qps: 4300.32 (r/w/o: 0.00/4300.32/0.00) lat (ms,95%): 3.55 err/s: 0.00 reconn/s: 0.00
 
[ 40s ] thds: 10 tps: 4413.48 qps: 4413.48 (r/w/o: 0.00/4413.48/0.00) lat (ms,95%): 3.49 err/s: 0.00 reconn/s: 0.00
 
[ 50s ] thds: 9 tps: 3930.46 qps: 3930.46 (r/w/o: 0.00/3930.46/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
 
 
 
 
 
vagrant@haproxy:~$ haproxy -version
 
HA-Proxy version 2.0.13-2ubuntu0.5 2022/03/02 - https://haproxy.org/
 
vagrant@haproxy:~$ cat /etc/os-release 
NAME="Ubuntu"
 
VERSION="20.04.2 LTS (Focal Fossa)"
 
ID=ubuntu

Generated at Thu Feb 08 09:17:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.