[Feat.Req.] ProxySQL as an authentication middleware (separate credentials for frontend and backend)

C-Duv commented 3 years ago

I would like to use ProxySQL as an auth middleware/proxy for MySQL server. To my understanding, ProxySQL currently reuses the credentials it received from the client to connect to the backend server, I would need to have it's own list of credentials to accept connections from AND it's own list of credentials to use when connecting to a backend.

The need: Delegate authentication to a MySQL server/database to another brick (thus. ProxySQL).

I want my users to auth themselves on the middleware/proxy (ProxySQL) using it's own local users database (or an external auth such as LDAP, PAM, etc.) which will then connect to the real MySQL server (the backend) associated to the user using the backend's credentials.

Schema:


                 +---------------+    +-------------+
                 |users2hostgroup|    |mysql_servers|
                 +---------------+    +-------------+
                                ^      ^
                                |      |                         +-----------------------+
                             (3)|      |(5)                      |db1.app-foo.example.com|
                                |      |                         +-----------------------+
                                v      v(4)
+------------+        (1)      +--------+          (6)           +-----------------------+
|MySQL client|---------------->|ProxySQL|----------------------->|db2.app-foo.example.com|
+------------+                 +--------+                        +-----------------------+
                                  ^       
                                  |                              +----------------------+
                               (2)|                              |db.app-bar.example.com|
                                  |                              +----------------------+
                                  v
                              +-----------+
                              |mysql_users|
                              +-----------+

Steps:

Client opens connection to ProxySQL using credentials bob_foo:secretB1
ProxySQL authenticates the user by validating the provided credentials from step 1. with it's mysql_users table (=> user_id=2). (If credentials from step 1. are not OK: ProxySQL refuses connection. If they are OK, continue)
ProxySQL checks the users2hostgroup table to determine which group of MySQL servers the user from step 1. has access to (=> hostgroup_id=1).
ProxySQL looks into mysql_group_replication_hostgroups and mysql_servers to determine the MySQL server to connect to (classic process) (=> hostname=db2.app-foo.example.com).
ProxySQL retrieves from the mysql_servers table the credentials to connect with (=> app-foo:secretF).
ProxySQL connects to determined MySQL server (step 4.) using the credentials found in step 5.

Table mysql_users:

id	login	password
1	alice	secretA
2	bob_foo	secretB1
3	bob_bar	secretB2

Table users2hostgroup:

user_id	hostgroup_id
1	1
2	1
3	2

Table mysql_servers:

hostgroup_id	hostname	port	login	password
1	db1.app-foo.example.com	3306	app-foo	secretF
1	db2.app-foo.example.com	3306	app-foo	secretF
2	db.app-bar.example.com	3306	app-bar	secretB

kjanss commented 3 years ago

we could really use this feature as well. the use case is to bypass the need in Azure to use fully-qualified (user@host) names for applications which do not support this.

lucagervasi commented 2 years ago

This would be a great addition. When I first scouted proxysql I was in hope that "frontend" and "backend" fields in the user tables had this exact purpose. Following this request :)