mysql_users doesn't seem to support host restrictions

[bytemarkjlawrie]

I know proxysql is designed to run on the app servers, but if used as a standalone proxy box in front of the app servers, all connections to the MySQL backends come from this box. The proxy doesn't support user@host restrictions, so I'm losing the ability to host-restrict on a per-user basis.

Is this something that could be added in?

[renecannao]

proxysql can run either in the app server, in the database server, or in a middle layer (like you are suggesting). Currently, it doesn't support the restriction at host level like user@host, although it is already possible to define query rules that filter on client host (mysql_query_rules.client_addr). It can be added the possibility to define user@host, but handling user@host1 and user@host2 (same user, different host) as two different users with different credential is perhaps not a big priority at the moment.

Thoughts?

[bytemarkjlawrie]

Hi René,

I really appreciate your quick responses here.

I'm happy there's a way to reject inside the proxy itself based on host (would have to abandon the middle layer if could only filter with a firewall).

Being able to do this one day would be nice, but not a big deal - I see now it would affect your query rules too.

Best wishes,

James Lawrie

[renecannao]

James, do you think would make sense to have username@hosts only for the purpose of defining ACL (who can connect and who not) yet passwords are associated only with username and not with username@hostname? Some time in the future the users management needs to be extended with additional features, so it is a good time for suggestions. Thanks.

[bytemarkjlawrie]

Hi René,

If you could allow auth to be pushed through PAM, pam_access could do this without the app needing to care about the host (and could potentially be extended in more complicated ways without proxysql necessarily needing to care).

ProxySQL only supports access or no access so I guess the only functionality you'd lose doing it this way is the ability to have different passwords for the same username from different hosts but I'm not sure anyone does that on purpose anyway?

Another nice feature might be the ability to rewrite users in the future when you split backend/frontend users. So for instance if a badly configured (but untouchable) app uses root@, proxysql could accept the auth but rewrite it to be limiteduser@ before passing it to the backends.

James Lawrie

[nzhook]

ProxySQL only supports access or no access so I guess the only functionality you'd lose doing it this way is the ability to have different passwords for the same username from different hosts but I'm not sure anyone does that on purpose anyway?

We do this currently in Shared Hosting as different customer websites on different servers have different access to databases on the same server. Although what we were looking for with ProxySQL was a routing engine so user1@serverX goes to cluster0 and user1@serverY goes to cluster3. Having unique users across the platform opens up a scalability question.

It would also be nice to set up a rule to change the IP of the outbound connection as well, then we can map connections for user1@serverX to an IP that matches serverX (PTR wise) on the real server - but that's a different feature request and another can of worms.

A short term solution might be to allow for a username to be stored everywhere as user@server, that way passwords and routing rules can be unique per connecting server. The auth code is then updated to look for user@server and if that fails then just user.

[markuman]

proxysql can run either in the app server, in the database server, or in a middle layer (like you are suggesting). Currently, it doesn't support the restriction at host level like user@host, although it is already possible to define query rules that filter on client host (mysql_query_rules.client_addr). It can be added the possibility to define user@host, but handling user@host1 and user@host2 (same user, different host) as two different users with different credential is perhaps not a big priority at the moment.

Thoughts?

That works for me. but there might be some pitfalls.

MySQL [(none)]> select * from runtime_mysql_query_rules where username = 'adm105';
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern        | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 19      | 1      | adm105   | NULL       | 0      | 10.103.0.49 | NULL       | NULL       | NULL   | NULL         | ^SELECT              | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 25      | 1      | adm105   | NULL       | 0      | 10.103.0.49 | NULL       | NULL       | NULL   | NULL         | ^SELECT.*FOR UPDATE$ | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 31      | 1      | adm105   | NULL       | 0      | 10.103.0.49 | NULL       | NULL       | NULL   | NULL         | ^SELECT              | 1                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+

1. ^SELECT for user adm105 from client ip 10.103.0.49 to reader node ( like typical read/write split setup)
2. ^SELECT.*FOR UPDATE$ for user adm105 from clientip 10.103.0.49 ( like typical read/write split setup_)
3. finally everthing what's not ^SELECT (negate_match_pattern: 1) from client ip 10.103.0.49 also for writer group.

The important part is to set the default_hostgroup to a none-existing one

MySQL [(none)]> select * from runtime_mysql_users where username = 'adm105';
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| adm105   | *993223C3BB787D1ECBF83E1069A89A3681CE4008 | 1      | 1       | 0                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |            |         |
| adm105   | *993223C3BB787D1ECBF83E1069A89A3681CE4008 | 1      | 1       | 0                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |            |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
2 rows in set (0.001 sec)

Otherwise connections are bypassed by the mysql_users table, where the default hostgroup is used when runtime_mysql_query_rules does not match!

MySQL [(none)]> select user, json_extract(extended_info, '$.client.encrypted') as encrypted, cli_host from stats_mysql_processlist where user = 'adm105';
+--------+-----------+-------------+
| user   | encrypted | cli_host    |
+--------+-----------+-------------+
| adm105 | 1         | 10.115.4.13 |
+--------+-----------+-------------+