Does proxysql support ip address whitelist?

[gaoqu...@gmail.com]

Does proxysql support ip address whitelist?

[René Cannaò]

Hi,

ProxySQL supports some sort of IP address whitelisting.

Field `mysql_query_rules`.`client_addr` (see https://github.com/sysown/proxysql/wiki/MySQL-Query-Rules) can be used to filter traffic based on clients addresses, therefore it is possible to allow traffic from specific addresses while blocking everything else.

The catch here is that the filtering is done on the queries only: clients not in the "whitelist" can still be able to connect to ProxySQL, although their queries will be blocked.
Is that enough?

Thanks,

René

On 9 February 2017 at 01:01, <gaoqu...@gmail.com> wrote:

Does proxysql support ip address whitelist?

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gaoqu...@gmail.com]

hi!

run_mysql_query_tules: 

+----------+--------------+-----------------------+

| username | client_addr  | destination_hostgroup |

+----------+--------------+-----------------------+

| mha_w    | 192.168.32.36 | 600                   |

| mha_r    | 192.168.32.36 | 601                   |

| mha_rw   | 192.168.32.36 | 600                   |

| mha_rw   | 192.168.32.36 | 601                   |

+----------+--------------+-----------------------+

and execute  mysql -h 192.168.18.89 -P6033 -u mha_rw -pmha_rw -e  "SELECT  * from test_mha.t1 limit 1 ;commit;" on  machine 192.168.18.94

+----+---------+

| id | name    |

+----+---------+

|  1 | gaoquan |

+----+---------+ 

it can return result and not blocking everything.

在 2017年2月10日星期五 UTC+8下午2:18:56，René Cannaò写道：

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

Hi Son,

can you please retry?

It works for me, here an example.

ProxySQL is listening on any interface:

Admin> SHOW VARIABLES LIKE 'mysql-interfaces';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| mysql-interfaces | 0.0.0.0:6033;/tmp/proxysql.sock |
+------------------+---------------------------------+

I now try to connect to ProxySQL using both 127.0.0.1 and 192.168.254.128 . They both work (for now):

vagrant@ubuntu-14:~$ mysql -u root -h 127.0.0.1 -P6033 -e "SELECT 1" -NB
1
vagrant@ubuntu-14:~$ mysql -u root -h 192.168.254.128 -P6033 -e "SELECT 1" -NB
1

Now I create a rule to block 127.0.0.1 :

Admin> INSERT INTO mysql_query_rules (rule_id,active,client_addr,error_msg,apply) VALUES (10,1,'127.0.0.1','This IP cannot send queries',1);
Query OK, 1 row affected (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

And finally I will re-run the simple "SELECT 1" as before. You can see that the query coming from 127.0.0.1 is blocked:

vagrant@ubuntu-14:~$ mysql -u root -h 127.0.0.1 -P6033 -e "SELECT 1" -NB
ERROR 1148 (42000) at line 1: This IP cannot send queries
vagrant@ubuntu-14:~$ mysql -u root -h 192.168.254.128 -P6033 -e "SELECT 1" -NB
1

Thanks,

René

On 13 February 2017 at 20:37, Son Nguyen <nmso...@gmail.com> wrote:

Hmm, I'm using : proxysql-1.3.3-1.x86_64

and got the same result, proxysql does not block my query when I connect from different IP with IP in : client_addr

Vào 13:18:56 UTC+7 Thứ Sáu, ngày 10 tháng 2 năm 2017, René Cannaò đã viết:

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[gaoqu...@gmail.com]

hi,René,

   Thanks ,when the client is full like 10.110.32.36,the client 10.110.32.36 will be block

   if I want to blocking 10.110.32.%,how should i do?

   I try to filter with 10.110.32.% or 10.110.32.* ,can not blocking.

在 2017年2月14日星期二 UTC+8下午4:16:46，René Cannaò写道：

[René Cannaò]

Unfortunately, IP wildcard marks are currently not supported (pull requests are welcome).

Although not an elegant solution, you can create a long list of rules to block multiple IPs.

Or block incoming requests using iptables

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.