Re: [Sqlrelay-discussion] Query filter per user

[Matias R. <cy...@sa...>]

Hi David,

	 Thanks for your reply. My problem with having a different instance for each client is that my application may have up to 500 clients... and having 500 instances I think it would be way too much.

	 Maybe adding the posibility of using variables would be very interesting. Maybe something like this? 



	 <users>
		<user user="clientid1" password="clientid1"/>
		<user user="clientid2" password="clientid2"/>
		<user user="clientid3" password="clientid3"/>
		<user user="clientid4" password="clientid4"/>
                  </users>
                  <connections>
                  </connections>
                  <router>
                          <!-- send queries to "mysqldb" which match the
                                  specified patterns -->
                          <route host="" port="" socket="/tmp/database.socket" user="mysqluser" password="mysqlpassword">
                                  <!-- allow selects with a where clause -->
                                  <query pattern="^\s*select\s+.*\s+from\s+.*\s+where\s+client_id=$clientidN"/>
                          </route>
                          <!-- all other queries will be filtered out -->
                          <filter>
                                  <query pattern=".*"/>
                          </filter>
                  </router>

	And when using "$clientidN" translate the variable to the username that was actually used to log into that specific connectio. I think that having the ability to do something like that will be very interesting (not only to say that will solve my problem :) )
	
	Regards,

--
Matias Rollan
<cy...@sa...>
	

	
On Wed, Apr 21, 2010 at 11:42:03AM -0400, David Muse wrote:
> Hello Matias,
> 
> I'm glad you got it working.
> 
> You can have multiple queries per router instance.  For instance, you 
> can add another query tag with a pattern for updates to that same router 
> instance, rather than having to create a separate router instance for 
> updates.
> 
> But you'd need to create a separate instance for each client.
> 
> I've recently gotten requests for a variety of enhanced routing 
> features, including several user-based routing features.  They probably 
> won't make it into the very next release, 0.42, but are on the list for 
> 0.43.
> 
> David Muse
> dav...@fi...
> 
> On 04/14/2010 10:40 AM, Matias Rollan wrote:
> > Hola!
> >
> > I created the following sqlrelay.conf and so far is working as expected. Only filtering select where client_id=2 for the sqlrelay "clientid2" user.
> >
> > <?xml version="1.0"?>
> > <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> >
> > <instances>
> >
> >          <!-- This instance maintains connections to a MySQL database -->
> >          <instance id="database" port="" socket="/tmp/database.socket" dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1">
> >                  <users>
> >                          <user user="mysqluser" password="mysqlpassword"/>
> >                  </users>
> >                  <connections>
> >                          <connection connectionid="db" string="user=mysqluser;password=mysqlpassword;host=localhost;db=database;" metric="1" behindloadbalancer="no"/>
> >                  </connections>
> >          </instance>
> >
> >
> >          <!-- This instance sends queries to the
> >                  mysqldb instance after filtering them -->
> >          <instance id="router" port="9001" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1">
> >                  <users>
> >                          <user user="clientid2" password="clientid2"/>
> >                  </users>
> >                  <connections>
> >                  </connections>
> >                  <router>
> >                          <!-- send queries to "mysqldb" which match the
> >                                  specified patterns -->
> >                          <route host="" port="" socket="/tmp/database.socket" user="mysqluser" password="mysqlpassword">
> >                                  <!-- allow selects with a where clause -->
> >                                  <query pattern="^\s*select\s+.*\s+from\s+.*\s+where\s+client_id=2"/>
> >                          </route>
> >                          <!-- all other queries will be filtered out -->
> >                          <filter>
> >                                  <query pattern=".*"/>
> >                          </filter>
> >                  </router>
> >          </instance>
> >
> >
> > </instances>
> >
> > I only found that that the dbase="router" wasn't working because the "router" value wasn't listed in sqlrelay.dtd . I added it to sqlrelay.dtd file and now is working fine.
> >
> > The sqlrelay version that I am currently using is:
> > ii  sqlrelay                          1:0.39.4-6ubuntu1               Database connection pooling, proxying and lo
> >
> >
> > Now I guess only need to create one entry per "client" to solve the problem I explained in the previous email but I am wondering if there's a better way to achieve this than creating one instance per user and query pattern to solve my problem of filtering each SQLrelay user to a certain query form ?
> >
> > Thanks in advance,
> >
> > --
> > Matias Rollan
> > <cy...@sa...>
> >
> > On Tue, Apr 13, 2010 at 04:49:59PM -0300, Matias Rollan wrote:
> >    
> >> Hola!
> >>
> >> I am new to SQLrelay and I just installed it and connected it to MySQL for testing purposes as I think it may solve my problem.
> >>
> >>
> >> I need to find a way to filter queries that are currently being executed from differents mysql clients so each client can only modify the rows that belong to them and not the others. Right now every client is connecting to a MySQL server with the same user/passwd so I have no control on how to limit each client to certains rows from a table.
> >>
> >> An example to try to clarify this:
> >>
> >> table:   M
> >> fields:  a,b,c,d,client
> >>
> >> I need that client 1 can only "UPDATE" the rows from table M WHERE client=1 and so on.
> >>
> >> Since MySQL has no permissions per row I think that I can use SQLrelay in the middle, connecting each "client" to SQLrelay to handle the SQL query permissions at the SQLrelay level.
> >>
> >> The clients queries are written the following way:
> >>
> >> "UPDATE M set field=value WHERE client=$client"
> >>
> >> I was thinking that I could create one SQLrelay user per client and only allow the queries with the form written above. Is this possible? Do I have to add one user per client with the query that is allowed for that user?
> >>
> >> Any recommendation would be highly appreciated.
> >>
> >> Thanks in advance,
> >>
> >> -- 
> >> Matias Rollan
> >> <cy...@sa...>
> >>