[RADIATOR] AuthBy SQL - multiple rows/attributes

[Jim Tyrrell]

At the moment I just have a single very simple table that I am testing 
with, 2 columns 'Endpoint' & 'Password' with 2 rows in the table for 2 
different Tunnel endpoints.

I could have multiple columns for the multiple tunnels, but then if I 
wanted to add or remove tunnels I would need to update the Radiator 
query each time to add/remove the extra AuthAttrDefs, but I'd like the 
flexibility to just add/remove entries to the SQL table without having 
to change the Radiator config.

ie - if I have one tunnel in the table then the handler needs to return:

Code:       Access-Accept
           Tunnel-Server-Endpoint = 172.16.1.1
           Tunnel-Password = "tunnelpass"

And if an extra entry is added to the table then return the following 
format:

Code:       Access-Accept
           Tunnel-Server-Endpoint = 172.16.1.1
           Tunnel-Password = "tunnelpass"
           Tunnel-Server-Endpoint = 172.16.1.2
           Tunnel-Password = "tunnelpass2"


If I was able to use LDAP I could just have an object such as:

uid=TunnelEndPoints
tunnelip=172.16.0.1
tunnelip=172.16.0.2
tunnelpass=blah1
tunnelpass=blah2

And then use an AuthBy LDAP including the following:

                 AuthAttrDef tunnelip,Tunnel-Server-Endpoint,reply
                 AuthAttrDef     tunnelpass,Tunnel-Password,reply


Is there not an equivalent of this for MySQL authentication?  How do 
people store multiple attributes such as Framed-Route in MySQL and then 
return multiple instances of this when they exist? (The examples above 
would actually be returned as tagged attributes but I can worry about 
that later).

Thanks.

Jim.


On 25/06/2012 18:05, Michael wrote:
>
> I seem to remember reading somewhere in the Radiator manual that it 
> will only process the first sql row received therefore I don't think 
> it will process multiple row results.  I can't seem to find in the 
> manual where i read that though. On the other hand, you could have all 
> reply values on the same row in the table, or create an sql statement 
> that returns them all on one row.
>
> What is your sql table structure?  multiple tables?
>
> mike
>
>
> On 12-06-25 08:52 AM, Jim Tyrrell wrote:
>> Hi,
>>
>> Is it possible for AuthBy SQL to return multiple attributes if the query
>> returns multiple rows?
>>
>> I am currently using AuthBy SQL to return a Tunnel-Endpoint to a LAC
>> with the following simplified config:
>>
>> <AuthBy SQL>
>>           DBSource        dbi:mysql:databasename:192.168.10.3
>>           DBUsername      DBuser
>>           DBAuth          DBPass
>>           AuthSelect SELECT Endpoint, Password FROM endpoints
>>           AuthColumnDef   0,Tunnel-Server-Endpoint,reply
>>           AuthColumnDef   1,Tunnel-Password,reply
>> </AuthBy>
>>
>> This works fine at the moment as I only have 1 row in the table which
>> represents 1 endpoint.  But I now want to return multiple endpoints so
>> the Access-Accept would be something along the lines of:
>>
>> Code:       Access-Accept
>> Attributes:
>>           Tunnel-Server-Endpoint = 172.16.1.1
>>           Tunnel-Password = "tunnelpass"
>>           Tunnel-Server-Endpoint = 172.16.1.2
>>           Tunnel-Password = "tunnelpass2"
>>
>> I had hoped to just add a 2nd row to the table, but the handler just
>> returns the values from the 1st row of the result.  I'd like to be able
>> to return additional attributes for each row returned so I can easily
>> add/remove more endpoints to the table as and when I need to.
>>
>> Thanks.
>>
>> Jim.
>> _______________________________________________
>> radiator mailing list
>> radiator at open.com.au
>> http://www.open.com.au/mailman/listinfo/radiator
>>
>>