Hi, I need to convert my users/group file to rlm_sql, but after reading the wiki, I can't figure out how to reproduce the flow. I want to authorize all connections, except if the user exists and the password is wrong. If the users doesn't exist or is disabled, I set an unrouteable address to the user. The point is to prevent DSL routers to keep on trying to connect when they are misconfigured, or customer is suspended. I use rlm_sql and rlm_sqlippool with a MySQL backend. The users file I try to convert looks like this : ------ # Default profile for everyone DEFAULT Pool-Name = "default" Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Netmask = 255.255.255.254, Framed-MTU = 1492, Framed-Compression = Van-Jacobson-TCP-IP, Qos-Policy-Metering = DL128, Qos-Policy-Policing = UL64, Fall-Through = Yes # Set group attributes DEFAULT Group-Name == "SUSPENDED", Auth-Type := Accept, \ Pool-Name := "disabled" DEFAULT Group-Name == "DSL500", Pool-Name := "dsl500" Qos-Policy-Metering := DL512, Qos-Policy-Policing := UL128, Fall-Through = Yes [... more groups ...] # Set user attributes testuser ClearText-Password := 'test' Framed-IP-Address := [...], Framed-Route := [...], Framed-IP-Netmask := [...], [... more users ...] DEFAULT Auth-Type := Accept ------ According to the wiki, rlm_sql process users first, then groups. With this order, a user could have his IP/routes set even if he is disabled (because he is not rejected). As I have about 10000 users, using big users/group files is not really an option :-) Maybe I did something wrong, any help would be appreciated. Thanks, -- David Majorel Offratel/Lagoon ISP
It will work fine if you set the pool to override the IP address already set. 1. Leave the # Default profile for everyone in the users file 2. Make groups SUSPENDED, DSL500 etc. in groupcheck table. You need 2 entries for group SUSPENDED, and one for others: (GroupName, Attribute, op, Value) SUSPENDED, Auth-Type, :=, Accept SUSPENDED, Pool-Name, :=, disabled DSL500, Pool-Name, :=, dsl500 3. Make groupreply entries: (GroupName, Attribute, op, Value) DSL500, Qos-Policy-Metering, :=, DL512 DSL500, Qos-Policy-Policing, :=, UL128 4. Create user entries in radcheck table: (UserName, Attribute, op, Value) testuser, Cleartext-Password, :=, test 5. Set static IP addresses and routes in radreply table: (UserName, Attribute, op, Value) testuser, Framed-IP-Address, :=, ... etc. 6. Connect users to groups in usergroup table If the user is suspended IP address from the "disabled" IP pool will override the static IP address from the radreply table. Without the correct IP address route won't work. WARNING: Are you sure that you don't want to reject connections from suspended/non-existatnt users? All they have to do is set a static IP address from your pool on their end (it's ever so simple to force a static IP address in Connection Properties) and gain access. Dana 5/6/2007, "David Majorel" <dm@lagoon.nc> piše:
Hi,
I need to convert my users/group file to rlm_sql, but after reading the wiki, I can't figure out how to reproduce the flow.
I want to authorize all connections, except if the user exists and the password is wrong. If the users doesn't exist or is disabled, I set an unrouteable address to the user. The point is to prevent DSL routers to keep on trying to connect when they are misconfigured, or customer is suspended. I use rlm_sql and rlm_sqlippool with a MySQL backend.
The users file I try to convert looks like this :
------ # Default profile for everyone
DEFAULT Pool-Name = "default" Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Netmask = 255.255.255.254, Framed-MTU = 1492, Framed-Compression = Van-Jacobson-TCP-IP, Qos-Policy-Metering = DL128, Qos-Policy-Policing = UL64, Fall-Through = Yes
# Set group attributes
DEFAULT Group-Name == "SUSPENDED", Auth-Type := Accept, \ Pool-Name := "disabled"
DEFAULT Group-Name == "DSL500", Pool-Name := "dsl500" Qos-Policy-Metering := DL512, Qos-Policy-Policing := UL128, Fall-Through = Yes
[... more groups ...]
# Set user attributes
testuser ClearText-Password := 'test' Framed-IP-Address := [...], Framed-Route := [...], Framed-IP-Netmask := [...],
[... more users ...]
DEFAULT Auth-Type := Accept ------
According to the wiki, rlm_sql process users first, then groups. With this order, a user could have his IP/routes set even if he is disabled (because he is not rejected). As I have about 10000 users, using big users/group files is not really an option :-)
Maybe I did something wrong, any help would be appreciated.
Thanks, -- David Majorel Offratel/Lagoon ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
It doesn't work. When the user is member of SUSPENDED and has a static IP address, rlm_sqlippool doesn't override the Framed-IP-Address. This is the behavior I expect for all other cases/groups. I had another problem with this configuration. A user can be member of multiple groups. If he is member of SUSPENDED, I want it to be the only one group evaluated. This can be achieved with "Fall-Though" in users file, but it don't know how to do it with SQL. Finally, I tried to use SQL-Group in users file (see below). I can pull user attributes from database after processing group attributes in users file. Nevertheless, I still have a problem with group SUSPENDED. I need to end all the authorize processing instead of going on next module. Is it possible ? ------ users ------ DEFAULT Pool-Name := "default" Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Netmask = 255.255.255.254, Framed-MTU = 1492, Framed-Compression = Van-Jacobson-TCP-IP, Qos-Policy-Metering = DL128, Qos-Policy-Policing = UL64, Fall-Through = Yes # Group processing DEFAULT SQL-Group == "SUSPENDED", Auth-Type := Accept, \ Pool-Name := "disabled" # If user if disabled, I want to end authorize process now DEFAULT SQL-Group == "DSL500", Pool-Name := "dsl500" Qos-Policy-Metering := DL512, Qos-Policy-Policing := UL128, Fall-Through = Yes # go on with sql and user specific attributes ----- ---- radiusd.conf ---- [...] authorize { files chap sql } [...] -----
WARNING: Are you sure that you don't want to reject connections from suspended/non-existatnt users? All they have to do is set a static IP address from your pool on their end (it's ever so simple to force a static IP address in Connection Properties) and gain access.
I am not able to gain access by adding an IP address in connection properties. Connection is rejected by the BAS when an IP (in the pool or not) is requested.
It will work fine if you set the pool to override the IP address already set.
1. Leave the # Default profile for everyone in the users file
2. Make groups SUSPENDED, DSL500 etc. in groupcheck table. You need 2 entries for group SUSPENDED, and one for others:
(GroupName, Attribute, op, Value) SUSPENDED, Auth-Type, :=, Accept SUSPENDED, Pool-Name, :=, disabled
DSL500, Pool-Name, :=, dsl500
3. Make groupreply entries:
(GroupName, Attribute, op, Value) DSL500, Qos-Policy-Metering, :=, DL512 DSL500, Qos-Policy-Policing, :=, UL128
4. Create user entries in radcheck table:
(UserName, Attribute, op, Value) testuser, Cleartext-Password, :=, test
5. Set static IP addresses and routes in radreply table:
(UserName, Attribute, op, Value) testuser, Framed-IP-Address, :=, ... etc.
6. Connect users to groups in usergroup table
If the user is suspended IP address from the "disabled" IP pool will override the static IP address from the radreply table. Without the correct IP address route won't work.
Thanks -- David Majorel Offratel/Lagoon ISP
It doesn't work. When the user is member of SUSPENDED and has a static IP address, rlm_sqlippool doesn't override the Framed-IP-Address. This is the behavior I expect for all other cases/groups.
I am not sure if sqlippool has the option to override Framed-IP-Address if it is already set. Ordinary ippool in radiusd.conf does. As I said, this is not a smart thing to do - even if you override IP address, he can change it to his static IP address in his Connection Properties after the connection is made and gain access. Anyone with basic IT skills can do this. And they DO know their static IP address.
I had another problem with this configuration. A user can be member of multiple groups. If he is member of SUSPENDED, I want it to be the only one group evaluated. This can be achieved with "Fall-Though" in users file, but it don't know how to do it with SQL.
By managing groups properly. Going to group2 after failing with group1 is a good thing. That's how dial backup is done for our broadband customers. But if I suspend them, broadband group is changed to suspend while dial group is deleted. When suspension is lifted, suspend is changed to his broadband group while dial group is added as No.2. If he is suspended, he should be removed from other groups in usergroup table. Ivan Kalik Kalik Informatika ISP
I had made a mistake when importing my users file. Is there a way to purge the user data from mysql and it clears all their info from all the tables without reinstalling the database? Jeff
run this SQL command from mysql prompt: DELETE FROM radcheck,usergroup,... whatever table you have written to It will delete data but not reset the indexes. But wouldn't it be wise to learn a little bit about SQL before embarking on something like this? Ivan Kalik Kalik Informatika ISP Dana 7/6/2007, "Jeff" <jeffa@jahelpdesk.com> piše:
I had made a mistake when importing my users file.
Is there a way to purge the user data from mysql and it clears all their info from all the tables
without reinstalling the database?
Jeff
k, that did it but the issue is when importing entrys are going into the raccheck and usergroup but nothing in the radreply hence none of the user attributes associated are being imported but is see no errors when after the script ran running back through what it outputted. _____ From: tnt@kalik.co.yu To: FreeRadius users mailing list [mailto:freeradius-users@lists.freeradius.org] Sent: Thu, 07 Jun 2007 08:41:37 -0400 Subject: Re: users2mysql Problem run this SQL command from mysql prompt: DELETE FROM radcheck,usergroup,... whatever table you have written to It will delete data but not reset the indexes. But wouldn't it be wise to learn a little bit about SQL before embarking on something like this? Ivan Kalik Kalik Informatika ISP Dana 7/6/2007, "Jeff" <jeffa@jahelpdesk.com> piše:
I had made a mistake when importing my users file.
Is there a way to purge the user data from mysql and it clears all their info from all the tables
without reinstalling the database?
Jeff
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Jeff wrote:
k, that did it but the issue is when importing entrys are going into the raccheck and usergroup but nothing in the radreply hence none of the user attributes associated are being imported but is see no errors when after the script ran running back through what it outputted.
This seems mostly like a training issue. You need to learn at least some rudimentary SQL queries for MySQL if that is what you will be using. The MySQL manual is fairly well written. However, out of curiosity, how are you importing entries? Please be specific. I don't want to know that you a running a script, I want to know what the script is doing and why you think it should be putting things in radreply. -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com
participants (5)
-
David Majorel -
Dennis Skinner -
Jeff -
Robert Cross -
tnt＠kalik.co.yu