DBI/MySQL Proxy

satanklawz has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

As we all know, silly things happen that sometimes require a proof of concept. This one happens to have a core need for writing a mysql "proxy" server, if you will. The functionality consists of one type of query. If a client service sends a specific query, where it's looking up a username/pass combo, then the script needs to ignore the password stored in mysql but then reference a RADIUS server for that information. The application's result is based on the success/failure of the RADIUS query whereby the rows returned would be 1/0 (if 1, it contains the pass that was used). All other queries go right to the mysql server for this application.

So, the question to all is.... does anyone know of a way of making a "mysql server" in perl (ie the existence of a perl module). I've searched cpan/google. I'm sure I'm not asking the right question as all the results that come back discuss on how to make your perl script talk to a mysql server. http://search.cpan.org/~timb/DBI-1.53/lib/DBI/ProxyServer.pm is the closet thing I came across, but I'm not sure it's the right thing.

Yes, I know there are TONS better ways of attacking this... but as I said before there are silly things that happened which lead up to this. In essence, if this proof of concept doesnt work then no harm/foul because the system as a whole can go back into it's previous state. If this does work, then it will justify that the whole system will be rebuilt from scratch to accommodate this new password system.

Comment on DBI/MySQL Proxy

Replies are listed 'Best First'.
Re: DBI/MySQL Proxy by Errto (Vicar) on Jan 22, 2007 at 21:01 UTC
The normal way to do this would be to write a server with which your clients would communicate using the protocol of your choice (could even be HTTP or something), and then call out to MySQL or whatever else based on the query. But if I'm understanding you, you have existing clients you cannot change that expect to be talking to a MySQL server. Well, I suppose that you could bust out the MySQL Server source code, decode the internal protocol, and write a Perl program that implements that protocol in such a way that it passes all requests except the specific kind it is trying to trap on to the real MySQL server and handles that kind on its own, generating a response which, again, has to conform to the MySQL communication protocol. Surely there is some way you can get out of doing that. Update: If in fact your clients are Perl programs and they call into MySQL using the DBI, then DBI::Proxy may indeed be of use after all. I have used it only in a sort of minimal test case myself, but from what I've seen, you could run the DBI proxyserver on your server system, let the clients use DBD::Proxy on their side to connect with your server, and use the dbiproxy to forward all the requests on to MySQL except the ones you want to trap. Should be a heck of a lot easier than writing a custom MySQL protocol handler.	[reply]
Re: DBI/MySQL Proxy by perrin (Chancellor) on Jan 22, 2007 at 21:50 UTC
Are the clients using Perl and DBI, or does this have to work for any type of client trying to reach MySQL?	[reply]
Re^2: DBI/MySQL Proxy by satanklawz (Beadle) on Jan 23, 2007 at 00:50 UTC
It's for a mail server cluster using postfix, courier-imap, and the like. The whole MTA system is MySQL driven.	[reply]
Re^3: DBI/MySQL Proxy by perrin (Chancellor) on Jan 23, 2007 at 02:12 UTC
Well, it's going to be tough then. You'll have to write a multi-process daemon that understands the MySQL wire protocol well enough to modify incoming traffic and send it to the real server. You can look at DBD::mysqlPP as a guide.	[reply]
Re^3: DBI/MySQL Proxy by Argel (Prior) on Jan 23, 2007 at 02:39 UTC
My trigger idea mentioned in Re: DBI/MySQL Proxy is sounding more and more attractive by the minute. Can your RADIUS servers be modifed to use a backend database? Or can you import the RADIUS information into your database?	[reply]
Re^4: DBI/MySQL Proxy by satanklawz (Beadle) on Jan 26, 2007 at 21:04 UTC
Re: DBI/MySQL Proxy by Argel (Prior) on Jan 23, 2007 at 00:46 UTC
If DBI::Proxy will not work then could you use an intermediate database (or the actual one) coupled with stored procedures and triggers to pull off what you want to do? Or could you have the RADIUS servers use the database? That would make writing a trigger much easier.	[reply]