[RADIATOR] AuthBy SQL - multiple rows/attributes

[Hugh Irvine]

Hello Jim -

I generally do this sort of thing in a hook, using a "dummy" AuthBy SQL clause to operate against.

Doing it this way allows to you use all of the normal Radiator code to do most of the heavy lifting.

There are a number of examples doing similar things in "goodies/hooks.txt" that should give you the general idea.

regards

Hugh


On 26 Jun 2012, at 19:28, Jim Tyrrell wrote:

> 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
>>> 
>>> 
> 
> 
> _______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator


--

Hugh Irvine
hugh at open.com.au

Radiator: the most portable, flexible and configurable RADIUS server 
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, 
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, 
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER etc. 
Full source on Unix, Windows, MacOSX, Solaris, VMS, NetWare etc.