mysql-proxy-discuss team mailing list archive

[Samit Pal <samit04@xxxxxxxxx>]

So i guess my idea of

having the intial connection go to a master db server (it can contain
all the users info) for authentication and then switching to the
correct backend server based on username from the authed connection
pool won't work because of the problem discussed in
http://forums.mysql.com/read.php?146,169265,169265#msg-169265 . Does
any one know of any other open source solution please?

On Tue, Jun 9, 2009 at 4:51 PM, Samit Pal<samit04@xxxxxxxxx> wrote:
> Thanks for the detailed reply Jan.
>
> The ip based mechanism is an option though it might be a little
> cumbersome to maintain so many ips with the scale that i am in. I had
> another thought. What if i use a master mysql server for the the
> clients to connect to via the proxy till the auth phase. Once
> authenticated can we switch the backend to a new one in the read_query
> phase based on username, now that we have the user name, default db
> etc through the read_auth() hook?
>
>
> On Tue, Jun 9, 2009 at 12:47 AM, Jan Kneschke <jan@xxxxxxxxxxx> wrote:
>> Samit Pal wrote:
>>> Hi Folks,
>>>
>>> I plan to use the following mysql architecture. We will have multiple
>>> mysql servers on different ports on the same machine. Each db server
>>> process will run as a different user. I want to use mysql proxy and
>>> run it on port 3306 (on the same machine may be to start with), so
>>> essentially users connect to the mysql proxy which should switch the
>>> connection to the appropriate db server possibly based on the username
>>> supplied. I am not looking for load balancing, just pure switching. Is
>>> this possible with mysql proxy?
>>
>> Nope. It is not and won't ever be possible in a transparent way.
>>
>> The problem is the way the auth-protocol works:
>> 1) client connects proxy,
>>   proxy connects to a server and get a "challenge",
>>   sends it back to client
>> 2) clients send username and the scrambled password
>>   as response to the challenge to the proxy ... to the server
>>
>> At the time you get access to the username, you already had to select a
>> mysql-server to connect to.
>>
>> The only way to make it work is:
>> 1) client connects to proxy, proxy sends back the challenge
>> 2) client sends username + response to proxy
>>   proxy verifies data against a local auth-storage
>>   proxy connects to server, gets a new challenge
>>   proxy sends client's username + scramble password
>>
>> The problem is: the local auth-storage has to have the same information
>> as the mysql-servers mysql.user table. We need that hashed password or
>> cleartext-password, any of them is fine. None of them is on the network.
>>
>> We don't support that yet, but that's the only way to do it.
>>
>> It is easy if you do something else instead:
>> * give each user its own IP address
>> * put all the IPs as virtual interface on the proxy box
>> * let the proxy check the proxy.connection.client.dst.address what IP
>> the client connected and pick the right backend
>> * that works in 0.7.x
>>
>>> Thanx
>>> Samit
>>
>> cheers,
>>  Jan
>> --
>>  jan: "Gee, Brain^WEric, what'd you wanna do tonight?"
>> eric: Same thing we do everynight: Take over the HelloWorld!
>>
>