Bypass user verification in ProxySQL

[Dylan Butler]

I understand there are some scenarios where you might want to verify a user/password at the proxy level, but I am wondering if it's possible to bypass the user verification in ProxySQL and just let MySQL handle it. For my needs, having to maintain users and passwords at both the proxy and in MySQL seems like a lot of extra work. It also could lead to problems if a user changes their MySQL password. I am new to ProxySQL and just trying to understand my options here. I read back through the archives but didn't find a definitive answer.

In my case, I run a lot of databases for internal customers. Each of those databases have a number of users and we are adding more all the time. Also, I am planning to run ProxySQL on each node in a Galera cluster, since I don't control the application servers. In that scenario, there is nothing to be saved by verifying a user in the proxy, since they have already paid the price of connecting from the app server to the database server.

Thanks for you help on this! - Dylan

 

[René Cannaò]

Hi Dylan,

If you want to bypass all the user verification, this means that proxysql doesn't know how to connect to the backends (doesn't have any credentials). That has a lot of consequences:

* read/write split is not possible

* query routing (in general) is not possible

* query retry is not possible

* backend failures are not transparent to the client: if a backend dies, proxysql cannot connect to another backend

* multiplexing is not possible

In other words, you will lose a lot of functionalities provided by proxysql, to the point that may make more sense to use haproxy (a forward proxy) instead of proxysql (a reverse proxy).

Does this explanation make sense?

Thanks,

René

--
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.

[Dylan Butler]

Yes, that makes sense. Obviously, some of those reasons listed are some of the reasons I was looking at proxysql to begin with, especially the query retries and transparent backend failures. I had previously evaluated HAProxy and it would work for most of my needs, but I was hoping to take advantage of the some of the MySQL aware features of proxysql. I will need to take a closer look at my environment and what extra work this will require. Thanks for your quick response and all your hard work on this project! - Dylan

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

[Florian Th]

I've got the same scenario as Dylan - it is not possible for me to do any changes in MariaDB users table in proxysql either. There are just too many users and too many change through the day.

I was wonderung if proxysql has some functionality like MaxScale, where I can add an user to the backend-db with access to mysql.user table and the proxy retrieves the user information from the backend-server using this user??

[René Cannaò]

Florian,

This functionality is not built-in in ProxySQL on purpose: being designed to work in heterogeneous and complex systems with potentially a lot of conflicting credentials, it is not possible to decide which server(s) needs to be used for credentials.

Although, ProxySQL is easily configurable and I know of setups where hundreds of users are added daily. Here there is an example of how you can create an external script to keep in sync the credential in the backends with the credentials in ProxySQL (thanks Todd) :
https://groups.google.com/forum/#!msg/proxysql/8a7B_8mjCUA/9s0TGDFMBgAJ

Please note that you may want to customize the script, like excluding certain users (no not sync root, or backup users), and decide from which server(s) the credentials need to get synced.

On 12 July 2017 at 14:04, Florian Th <florian...@plentymarkets.com> wrote:

I've got the same scenario as Dylan - it is not possible for me to do any changes in MariaDB users table in proxysql either. There are just too many users and too many change through the day.

I was wonderung if proxysql has some functionality like MaxScale, where I can add an user to the backend-db with access to mysql.user table and the proxy retrieves the user information from the backend-server using this user??

--

[jbenton....@gmail.com]

Hi Rene,

Thank you for your great work with ProxySql. It's a remarkable tool. 

I'm interested in a related authentication feature as discussed on this thread. 

I would like to run ProxySql as a "sidecar" on the same VM/pod as an application which has a single dedicated backend account, and have ProxySql essentially act as an out-of-proc connection pool for the application.  

I would like to:

* keep the application's single credential in ProxySql configuration (understood password has to be in plaintext due to the way MySql authentication works)

* have ProxySql only accept connections over localhost 

* have ProxySql always accept the application's authentication attempt over localhost- which would be with dummy credentials- and use the credential in its own configuration to connect to the backend

Can this be done?

Thanks,

Jonah

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

[René Cannaò]

Johan,

This feature request sounds like a sub-case of features described in https://github.com/sysown/proxysql/issues/707 and https://github.com/sysown/proxysql/issues/814 .

They are in the ToDo list, but after 1 year (Rick was asking this privately long time before opening the issue) they didn't get the right priority.

It will happen, but I am not sure when.

Thanks,

René

 

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