doc/rlm_sql is wrong?

[Phil Mayers]

Peter Nixon wrote:
>> The code in rlm_sql.c definitely does not do that, at least in 1.1.3 as
>> far as I can understand the code? Instead it appears to smoosh the user
>> and all the group check items together, compares them, and if they *all*
>> match adds *all* the reply items.
>>
>> This seems to make groups pretty useless except for using the SQL-Group
>> construct in the users file.
>>
>> Comments?
> 
> I believe you are correct. It's been a while since I looked at the SQL Groups 
> functionality, but last time I did I quickly decided to do the processing I 
> required from my own table structure with an SQL function. That way you get 
> _exactly_ what you want at the cost of having to think about a schema that 
> fits your need. Works pretty well for us :-)
> 
> Someone really needs to take a knife the the SQL Groups code.. But, there you 
> have it. Feel free to help out any time you want :-)

Actually, having just done a "cvs up"the CVS code appears to do things 
"the right way", and is generally a lot cleaner; none of the query_table 
config options for example, and a much cleaner iteration logic for groups.

 From what I can tell a straight swap of the src/modules/rlm_sql 
directory would have a reasonable chance of working - I might try that.

The specific driver for this was wanting a NIS netgroup-style group 
membership table, i.e.:

create table groups (
   id serial,
   precedence integer not null default 0,
   username text,
   callingstationid text,
   groupname text not null,
   primary key (id)
);

insert into groups (precedence,username,callingstationid,groupname)

-- ban joe on all hosts
...values (10, 'joe', null, 'BANNED');

-- ban this MAC for all users
...values (10, null, '00:11:22:33:44:55', 'BANNED');

-- permit this guest from their laptop only
...values (5, 'guest', 'aa:bb:cc:dd:ee:ff', 'OK');
...values (4, 'guest', null, 'BANNED');



...then set the "group membership" query to:

select distinct groupname from (
  select * from groups where
   username='%{SQL-User-Name}'
  or
   callingstationid='%{Calling-Station-Id}'
  order by precedence,groupname
) as groups

...which would allow you to e.g. put MAC addresses into BANNED groups, 
users into BANNED groups, but maybe permit a user to login from certain 
specific machines, by manipulating the precedence correctly.

If someone isn't already working on it I'll have a crack at backporting 
the CVS SQL code.