Re: Selecting backend based on the user connecting

[Jesper Juhl]

Well, there does seem to be one problem with this.
The connections in the pool stay alive so once a connection has been opened to each backend I can send a query to a backend of my choice, but it doesn't always work since the client sending the connection may not have the proper permissions for the client that is currently using it.

What I have setup looks like this;

I have two backend servers BE1 & BE2.
I have two users U1 and U2 - these user accounts exist on both backends.
On BE1 I have DB1 that U1 has access to and on BE2 I have DB2 that U2 has access to.

When U1 connects I send all his queries over a connection to BE1 since that's where his database lives and I send all queries from U2 to BE2.
The problem is that if the connection to BE1 that I'm reusing from the pool was originally opened by U2, then I get permission denied errors since U2 that authed the connection originally does not have any access to U1's database.

ERROR 1044 (42000): Access denied for user 'U2'@'localhost' to database 'DB1'

As far as I can see this makes the proxy pretty useless for my purposes.

I would suspect this would also be a problem for R/W splitting if one user sends queries over a connection authed by a different user.

How can I overcome this?