[RADIATOR] AuthBy SQL - multiple rows/attributes

Michael ringo at vianet.ca
Wed Jun 27 09:37:34 CDT 2012


you could use GENERIC like how Heikki suggested but still have separate rows in your sql and use SQL to join all the results, and apply the attribute names.  For MySQL, group_concat:



mysql> select * from temp;
+----+------------+-------------+
| id | Server     | Password    |
+----+------------+-------------+
|  5 | 172.16.1.1 | tunnelpass1 |
|  6 | 172.16.1.2 | tunnelpass2 |
|  7 | 172.16.1.3 | tunnelpass3 |
+----+------------+-------------+
3 rows in set (0.00 sec)

mysql> select GROUP_CONCAT( CONCAT('Tunnel-Server-Endpoint=',Server )) AS Servers, GROUP_CONCAT( CONCAT('Tunnel-Server-Endpoint=', Password)) AS Passwords from temp order by id;
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
| Servers                                                                                               | Passwords                                                                                                |
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
| Tunnel-Server-Endpoint=172.16.1.1,Tunnel-Server-Endpoint=172.16.1.2,Tunnel-Server-Endpoint=172.16.1.3 | Tunnel-Server-Endpoint=tunnelpass1,Tunnel-Server-Endpoint=tunnelpass2,Tunnel-Server-Endpoint=tunnelpass3 |
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


Michael





On 12-06-26 07:33 AM, Heikki Vatiainen wrote:
> On 06/26/2012 12:28 PM, 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.
>
> How about changing the config to use GENERIC:
>
>             AuthColumnDef   0,GENERIC,reply
>             AuthColumnDef   1,GENERIC,reply
>
> The DB would then have Endpoint and Password columns with values like these:
>
> Endpoint column for row n:
> Tunnel-Server-Endpoint=172.16.1.1,Tunnel-Server-Endpoint=172.16.1.2
> Password column for row n:
> Tunnel-Password="tunnelpass1",Tunnel-Password="tunnelpass2"
>
> With GENERIC you could actually put all reply attributes into the same
> column but that would likely make maintaining the values harder.
>
> With Hugh's solution you could get rid of repeating the attribute names
> and storing just the values.
>
> Heikki
>
>
>> 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
>
>


More information about the radiator mailing list