DHCP sqlippool reply values
I've added two new fields into radippool table that i am using for DHCP dynamic pools. `gateway` varchar(15) NOT NULL DEFAULT '', `netmask` varchar(15) NOT NULL DEFAULT '', in ippool-dhcp.conf i've added new fields: allocate-find = SELECT framedipaddress,gateway,netmask FROM ${ippool_table} I am not able to figure out how to address new fields inside policy.conf. Is it even possible? I need to have two new fields, currently i have: update reply { DHCP-Your-IP-Address = %{reply:Framed-IP-Address} } I am trying to get two new fields: DHCP-Subnet-Mask DHCP-Gateway-IP-Address I can use perl module to add those two fields but that means that i need two more database queries. Any other way? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: DHCP sqlippool reply values
On 28/02/13 13:36, Igor Smitran wrote: I've added two new fields into radippool table that i am using for DHCP dynamic pools. `gateway` varchar(15) NOT NULL DEFAULT '', `netmask` varchar(15) NOT NULL DEFAULT '', in ippool-dhcp.conf i've added new fields: allocate-find = SELECT framedipaddress,gateway,netmask FROM ${ippool_table} I am not able to figure out how to address new fields inside policy.conf. Is it even possible? I don't think so. The sqlippool module has large parts of the logic in C code, including which columns it expects the query to return and what it does with them. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: DHCP sqlippool reply values
On Fri, Mar 1, 2013 at 4:15 AM, Phil Mayers p.may...@imperial.ac.uk wrote: On 28/02/13 13:36, Igor Smitran wrote: I've added two new fields into radippool table that i am using for DHCP dynamic pools. `gateway` varchar(15) NOT NULL DEFAULT '', `netmask` varchar(15) NOT NULL DEFAULT '', in ippool-dhcp.conf i've added new fields: allocate-find = SELECT framedipaddress,gateway,netmask FROM ${ippool_table} I am not able to figure out how to address new fields inside policy.conf. Is it even possible? I don't think so. The sqlippool module has large parts of the logic in C code, including which columns it expects the query to return and what it does with them. If you only have small number of networks, it should be easier to define gateway and netmask inside an IF block. Another possible workaround (untested) is to borrow sql.authorize (e.g. using dummy User-Name control variable, set to client's MAC IP address), and then store the data inside radreply table. Should be more suitable if you have lots of small networks (e.g /29). -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: dhcp sqlippool reauthenticate users every minute
Ethan Hayon wrote: Yes, MAC address is unique for each user. The MAC should be a unique identifier when assigning IP's. That's the goal. Yes, the NAS is sending accounting data. This is what redacct looks like (some columns omitted) That seems OK. Right now, I'm thinking there is a mismatch either in the nasipaddress or some other attribute. The NAS has a WAN ip of 98.109.201.89 and a LAN IP of 192.168.1.1. The RADIUS server is on LAN at 192.168.1.2. I have noticed that sometimes the nasipaddress appears as 192.168.1.1 and other times as 98.109.201.89. I think I am going to start with a fresh install of freeradius. I messed with too many queries (such as adjusting the Pool-Key) and I am worried that I have created a mess. Well, start over. See man radiusd. Change one thing at a time. Get it working. Once it's working, change something else. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
dhcp sqlippool reauthenticate users every minute
Hi, I'm trying to set up FreeRADIUS 2.2 to act as an authentication and accounting system as well as a DHCP server. (I'm relaying DHCP requests from a pfSense box). I am trying to use sqlippools on FreeRADIUS, but I have noticed that my ippools are filling up quickly (I am only testing with two devices). I have pfSense configured to reauthenticate user every minute, so I believe that FreeRADIUS is running post-auth dhcp leasing each time the user is reauthenticated. FreeRADIUS is serving the same client a new IP address each minute. I have adjusted my policy conf to the below. I may be approaching this the wrong way, but this is essentially what I am trying to do: I want to have multiple pools on different subnets. When a new client connects to the network (they don't yet have a radius account) they are sent to a default (pending) pool. This may be on the 192.168.1/24 subnet. After they have set up their account and have been added to the RADIUS database, they will be given a pool-name, maybe `pool2`. `pool2` will be on a different subnet, for example, 192168.2/24. The pfsense box knows how to route these subnets, so that is not a problem. I only want clients to receive a different IP address if their pool-name changes for whatever reason, if there are no changes to their account, they shouldn't be leased a new IP every time they reauthenticate (every minute). Eventually I will disable the reauthenticate every minute feature as the production system would not be able to handle this many requests. Am I approaching this problem correctly? I have written some un-lang in the policy.conf file for handling some of this. I am getting hung up on this problem. The pool is getting exhausted in minutes since each client is leased a new ip every minute. Any help is appreciated! Thanks, Ethan …. dhcp_sqlippool.post-auth { # Do some minor hacks to the request so that it looks # like a RADIUS request to the SQL IP Pool module. # check whether the requester is a user or not, if they do not have a pool-name, send them to pfpriv by default if(%{sql: SELECT COUNT(*) FROM radcheck where username='%{DHCP-Client-Hardware-Address}' AND attribute='Pool-Name'} != 0) { update control { Pool-Name := %{sql: SELECT `value` FROM radcheck WHERE username = '%{DHCP-Client-Hardware-Address}' AND attribute='Pool-Name'} } } else { update control { Pool-Name := pfpriv # default pool if the user doesn't have an account } } update reply { DHCP-Domain-Name-Server = 192.168.1.1 # we will ultimately create a mysql table `radpoolinfo` to store per-pool attributes DHCP-Router-Address = 192.168.0.1 # this will also be in `radpoolinfo` eventually } update request { User-Name = DHCP-%{DHCP-Client-Hardware-Address} Calling-Station-Id = %{DHCP-Client-Hardware-Address} NAS-IP-Address = %{%{DHCP-Gateway-IP-Address}:-127.0.0.1} Acct-Status-Type = Start } # Call the actual module # # Uncomment this in order to really call it! dhcp_sqlippool #fail # Convert Framed-IP-Address to DHCP, but only if we # actually allocated an address. if (ok) { update reply { DHCP-Your-IP-Address = %{reply:Framed-IP-Address} } } } … - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: dhcp sqlippool reauthenticate users every minute
Ethan Hayon wrote: Hi, I'm trying to set up FreeRADIUS 2.2 to act as an authentication and accounting system as well as a DHCP server. (I'm relaying DHCP requests from a pfSense box). Are you doing DHCP *and* RADIUS? I am trying to use sqlippools on FreeRADIUS, but I have noticed that my ippools are filling up quickly (I am only testing with two devices). I have pfSense configured to reauthenticate user every minute, so I believe that FreeRADIUS is running post-auth dhcp leasing each time the user is reauthenticated. FreeRADIUS is serving the same client a new IP address each minute. There's no need to believe anything. Run the server in debugging mode to see exactly what it's doing. Any IP allocation MUST be done on a key which is unique to each device. That key should remain the same across multiple re-authentications. So... is it? Please check. I want to have multiple pools on different subnets. When a new client connects to the network (they don't yet have a radius account) they are sent to a default (pending) pool. This may be on the 192.168.1/24 subnet. After they have set up their account and have been added to the RADIUS database, they will be given a pool-name, maybe `pool2`. `pool2` will be on a different subnet, for example, 192168.2/24. The pfsense box knows how to route these subnets, so that is not a problem. I only want clients to receive a different IP address if their pool-name changes for whatever reason, if there are no changes to their account, they shouldn't be leased a new IP every time they reauthenticate (every minute). This is what databases are for. If you want to map each client to a pool, you'll need a table with a client identifier column, and a pool-name column. Then, assign IPs based on the pool name, as looked up in the table. Am I approaching this problem correctly? I have written some un-lang in the policy.conf file for handling some of this. I am getting hung up on this problem. The pool is getting exhausted in minutes since each client is leased a new ip every minute. Solve that problem first. Run the server in debugging mode to see *why* it's assigning a new IP. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: dhcp sqlippool reauthenticate users every minute
Jan 10 23:53:34 2013 : Info: # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default Thu Jan 10 23:53:34 2013 : Info: +- entering group post-auth {...} Thu Jan 10 23:53:34 2013 : Info: [sql] expand: %{User-Name} - b8:8d:12:10:8d:f6 Thu Jan 10 23:53:34 2013 : Info: [sql] sql_set_user escaped user -- 'b8:8d:12:10:8d:f6' Thu Jan 10 23:53:34 2013 : Info: [sql] expand: %{User-Password} - [redacted] Thu Jan 10 23:53:34 2013 : Info: [sql] expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') - INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'b8:8d:12:10:8d:f6', '[redacted]', 'Access-Accept', '2013-01-10 23:53:34') Thu Jan 10 23:53:34 2013 : Debug: rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'b8:8d:12:10:8d:f6', '[redacted]', 'Access-Accept', '2013-01-10 23:53:34') Thu Jan 10 23:53:34 2013 : Debug: rlm_sql (sql): Reserving sql socket id: 4 Thu Jan 10 23:53:34 2013 : Debug: rlm_sql (sql): Released sql socket id: 4 Thu Jan 10 23:53:34 2013 : Info: ++[sql] returns ok Thu Jan 10 23:53:34 2013 : Debug: rlm_sql (sql): Reserving sql socket id: 3 Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: %{User-Name} - b8:8d:12:10:8d:f6 Thu Jan 10 23:53:34 2013 : Info: [sqlippool] sql_set_user escaped user -- 'b8:8d:12:10:8d:f6' Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: START TRANSACTION - START TRANSACTION Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}' - UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND AND nasipaddress = '[redacted]' Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time NOW() OR expiry_time IS NULL) ORDER BY (username '%{User-Name}'), (callingstationid '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE - SELECT framedipaddress FROM radippool WHERE pool_name = 'pfpriv' AND (expiry_time NOW() OR expiry_time IS NULL) ORDER BY (username 'b8:8d:12:10:8d:f6'), (callingstationid 'b8:8d:12:10:8d:f6'), expiry_time LIMIT 1 FOR UPDATE Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE framedipaddress = '192.168.0.50' AND expiry_time IS NULL - UPDATE radippool SET nasipaddress = '[redacted]', pool_key = 'b8:8d:12:10:8d:f6', callingstationid = 'b8:8d:12:10:8d:f6', username = 'b8:8d:12:10:8d:f6', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE framedipaddress = '192.168.0.50' AND expiry_time IS NULL Thu Jan 10 23:53:34 2013 : Info: [sqlippool] Allocated IP 192.168.0.50 [3200a8c0] Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: COMMIT - COMMIT Thu Jan 10 23:53:34 2013 : Debug: rlm_sql (sql): Released sql socket id: 3 Thu Jan 10 23:53:34 2013 : Info: [sqlippool]expand: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) - Allocated IP: 192.168.0.50 from pfpriv (did [redacted] cli b8:8d:12:10:8d:f6 port 30 user b8:8d:12:10:8d:f6) Thu Jan 10 23:53:34 2013 : Info: Allocated IP: 192.168.0.50 from pfpriv (did [redacted] cli b8:8d:12:10:8d:f6 port 30 user b8:8d:12:10:8d:f6) Thu Jan 10 23:53:34 2013 : Info: ++[sqlippool] returns ok Thu Jan 10 23:53:34 2013 : Info: ++[exec] returns noop Sending Access-Accept of id 32 to 192.168.1.1 port 7053 WISPr-Bandwidth-Max-Up := 500 WISPr-Bandwidth-Max-Down := 1200 Framed-IP-Address = 192.168.0.50 Thu Jan 10 23:53:34 2013 : Info: Finished request 10. Thu Jan 10 23:53:34 2013 : Debug: Going to the next request Thu Jan 10 23:53:34 2013 : Debug: Waking up in 4.9 seconds. Thu Jan 10 23:53:39 2013 : Info: Cleaning up request 10 ID 32 with timestamp +268 Thu Jan 10 23:53:39 2013 : Info: Ready to process requests. On Jan 11, 2013, at 4:34 PM, Alan DeKok al...@deployingradius.com wrote: Ethan Hayon wrote: Hi, I'm trying
Re: dhcp sqlippool reauthenticate users every minute
Ethan Hayon wrote: When I run the server in debug mode the Acct-Unique-Session-ID remains the same across the interim accounting updates. However, re-authentications don't seem to have a unique key associated with them. That makes no sense. There is *nothing* unique to each user you can key off of? Name? MAC address? In my post-auth policy, I am updating control with the proper pool-name (with an unlang), changing some other reply attributes, then calling dhcp_sqlippool. What I am doing doesn't /feel/ right. I am very new to this, does this sound like the proper way of handling the serving of ip's on multiple subnets. DHCP-Domain-Name-Server and DHCP-Router-Address will change between pools. Get one thing working first. Only then look at the next thing. I guess I'm asking if I am approaching this correctly: Using unlang in policy.conf to handle these rules. unlang is for policy rules. Databases are for data. You've got some kind of mixup between the two. Sorry to put such a long debug message in here. I pulled out one authorization request, but they all look the same. It looks like They don't all look the same. They contain different information for each user. How else does the server tell users apart? This is what my authorization looks like: The request comes in with a framed ip of 192.168.0.43, but it tries to serve it 192.168.0.50. The default queries use Calling-Station-Id to track IP addresses. They *also* assume that the NAS sends accounting packets, so that each user has an accounting entry in SQL. It reallocates a new IP for each auth every minute. Probably because the NAS isn't sending accounting data. So the IP is never tracked in SQL. So... did you look in the SQL database to see what's there? Is it tracking the IP? Does the user have an accounting record? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: dhcp sqlippool reauthenticate users every minute
On Jan 11, 2013, at 6:38 PM, Alan DeKok al...@deployingradius.com wrote: Hi Alan, Thanks for the response Ethan Hayon wrote: When I run the server in debug mode the Acct-Unique-Session-ID remains the same across the interim accounting updates. However, re-authentications don't seem to have a unique key associated with them. That makes no sense. There is *nothing* unique to each user you can key off of? Name? MAC address? Yes, MAC address is unique for each user. The MAC should be a unique identifier when assigning IP's. In my post-auth policy, I am updating control with the proper pool-name (with an unlang), changing some other reply attributes, then calling dhcp_sqlippool. What I am doing doesn't /feel/ right. I am very new to this, does this sound like the proper way of handling the serving of ip's on multiple subnets. DHCP-Domain-Name-Server and DHCP-Router-Address will change between pools. Get one thing working first. Only then look at the next thing. Good point I guess I'm asking if I am approaching this correctly: Using unlang in policy.conf to handle these rules. unlang is for policy rules. Databases are for data. You've got some kind of mixup between the two. Sorry for the misunderstanding. I understand this. I'm just making sure it is normal to use unlang in the policy.conf to perform sql queries and use the results to build up a response. Again, I need to get this working before worrying about that. Sorry to put such a long debug message in here. I pulled out one authorization request, but they all look the same. It looks like They don't all look the same. They contain different information for each user. How else does the server tell users apart? I am only using one device right now, so the auth requests look the same, hence why I only included one below. The auth requests will look different if i introduce more devices into the system. This is what my authorization looks like: The request comes in with a framed ip of 192.168.0.43, but it tries to serve it 192.168.0.50. The default queries use Calling-Station-Id to track IP addresses. They *also* assume that the NAS sends accounting packets, so that each user has an accounting entry in SQL. It reallocates a new IP for each auth every minute. Probably because the NAS isn't sending accounting data. So the IP is never tracked in SQL. So... did you look in the SQL database to see what's there? Is it tracking the IP? Does the user have an accounting record? Yes, the NAS is sending accounting data. This is what redacct looks like (some columns omitted) +---+--+--+---+---+---+-+-+ | radacctid | acctsessionid| acctuniqueid | username | nasipaddress | callingstationid | calledstationid | framedipaddress | +---+--+--+---+---+---+-+-+ |17 | 9e90e1a3b02da713 | 068649e121f096f2 | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.40| |18 | 61ebc2f61333e8d4 | 857f2f856c1ea384 | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.43| |19 | a8aed7c0d9ce3bd1 | 541ef5a9672cc6e7 | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.43| |20 | 5bd18f3ccb1edf8a | e3c55f048d9a680b | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.43| |21 | 72ad87c6b43a08b4 | e427b47f54737c4f | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.43| |22 | bff889e83c3b469b | 70ec2fe5fa197bcc | b8:8d:12:10:8d:f6 | 98.109.201.89 | b8:8d:12:10:8d:f6 | 98.109.201.89 | 192.168.0.43| +---+--+--+---+---+---+-+-+ So there is an accounting record for each user and each user session. Right now, I'm thinking there is a mismatch either in the nasipaddress or some other attribute. The NAS has a WAN ip of 98.109.201.89 and a LAN IP of 192.168.1.1. The RADIUS server is on LAN at 192.168.1.2. I have noticed that sometimes the nasipaddress appears as 192.168.1.1 and other times as 98.109.201.89. I think I am going to start with a fresh install of freeradius. I messed with too many queries (such as adjusting the Pool-Key) and I am worried that I have created a mess. Ethan Hayon Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Bypass SQLIPPOOL via variable?
On Nov 1, 2012, at 1:02 PM, bea...@gmail.com wrote: If they are coming in as a Virtual connection, drop out of SQLIPPOOL and just let freeradius auth the connection and then let OpenVPN use it's predefined IP pool which we will in turn NAT? This should be possible with a bit of unlang. post-auth { if(NAS-Port-Type != Virtual){ sqlippool } } Or something of the sort. 'man 5 unlang' for more info. -- Blake Covarrubias - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Bypass SQLIPPOOL via variable?
Also, not wishing to drag this too much into the OpenVPN support group, but you can make OpenVPN use p2p mode which will allocate IP addresses from a /24 or greater, or you can assign individual IP addresses with a script. Obviously, these are things to research in the OpenVPN configuration pages. Regards, -- Jon The Nice Guy Spriggs On 1 November 2012 20:28, Blake Covarrubias bl...@covarrubi.as wrote: On Nov 1, 2012, at 1:02 PM, bea...@gmail.com wrote: If they are coming in as a Virtual connection, drop out of SQLIPPOOL and just let freeradius auth the connection and then let OpenVPN use it's predefined IP pool which we will in turn NAT? This should be possible with a bit of unlang. post-auth { if(NAS-Port-Type != Virtual){ sqlippool } } Or something of the sort. 'man 5 unlang' for more info. -- Blake Covarrubias - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Bypass SQLIPPOOL via variable?
You can either use an unlang wrapper around the sqlippoolto either skip it or to call another sqlippool instanceor (and maybe better) define a virtual-server instance for queries coming from openvpn and have a totally different config for them (this keeps the server functionality isolated for each use case...leading to sanity later on ;-) ) alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool - duplicated ip address after freeradius restart
Hi, I use radius sqlippool to assign IP address to my users, yesterday, after a restart on radius service, it started to assign IP address that were already in use to the users. I'm almost crazy here, I don't know what can be causing this, we have been using this scheme for at least three months and this problem never happened. freeradius-2.1.12_1 I have this configuration in sites-enabled/default, post-auth block: if (Framed-Protocol == PPP) { sqlippool } and this in the accounting block: sqlippool this in the authorize block: update control { Pool-Name := %{sql: SELECT nas_pool_name FROM naspool WHERE nas_ip=INET_ATON('%{NAS-IP-Address}')} } mysql desc naspool; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | nas_ip | int(10) unsigned | NO | PRI | 0 | | | nas_pool_name | varchar(30) | YES | | NULL | | +---+--+--+-+-+---+ The strange is that most, if not all of the duplicated address, are between a specific NAS and a random one. Obs. The expiry-time is set on 1h, and the interim update is set on 10m. Any suggestions? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
On 23/10/12 11:52, Antonio Modesto wrote: Hi, I use radius sqlippool to assign IP address to my users, yesterday, after a restart on radius service, it started to assign IP address that were already in use to the users. I'm almost crazy here, I don't know what can be causing this, we have been using this scheme for at least three months and this problem never happened. Can you capture a debug of FR assigning a duplicate? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
2012/10/23 Phil Mayers p.may...@imperial.ac.uk On 23/10/12 11:52, Antonio Modesto wrote: Hi, I use radius sqlippool to assign IP address to my users, yesterday, after a restart on radius service, it started to assign IP address that were already in use to the users. I'm almost crazy here, I don't know what can be causing this, we have been using this scheme for at least three months and this problem never happened. Can you capture a debug of FR assigning a duplicate? Unfortunately, it's kinda of hard to me to get a debug right now, it's a production server. I solved a part of the problem, I noticed that there where 98 clients connected on a specific NAS, but there was only 80 entries for that NAS on radippool table, thus there was a possibility of when a new client was establishing a connection, it would get an IP that was in use but was not in the table. The workaround was to disconnect these clients. - List info/subscribe/unsubscribe? See http://www.freeradius.org/** list/users.html http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
Antonio Modesto wrote: I use radius sqlippool to assign IP address to my users, yesterday, after a restart on radius service, it started to assign IP address that were already in use to the users. Then the SQL IP Pool was erased somehow. Restarting FreeRADIUS won't change the SQL database. The strange is that most, if not all of the duplicated address, are between a specific NAS and a random one. Obs. The expiry-time is set on 1h, and the interim update is set on 10m. Any suggestions? Find out what is removing the IPs from the SQL pool. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
Antonio Modesto wrote: I noticed that there where 98 clients connected on a specific NAS, but there was only 80 entries for that NAS on radippool table, thus there was a possibility of when a new client was establishing a connection, it would get an IP that was in use but was not in the table. Well, that's the issue. Somehow it's assigning IPs, but not recording them. That's not really supposed to happen. Is your database OK? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
It seems to be ok, the load average of the server: load averages: 0.00, 0.00, 0.00 I even created some indexes on radippool table: +---++--+--+--+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+--+---+-+--++--++-+ | radippool | 0 | PRIMARY |1 | id | A |1047 | NULL | NULL | | BTREE | | | radippool | 1 | FramedIPAddress |1 | FramedIPAddress | A | 523 | NULL | NULL | | BTREE | | | radippool | 1 | NASIPAddress |1 | NASIPAddress | A | 69 | NULL | NULL | | BTREE | | | radippool | 1 | CallingStationID |1 | CallingStationID | A |1047 | NULL | NULL | | BTREE | | | radippool | 1 | username |1 | username | A |1047 | NULL | NULL | | BTREE | | | radippool | 1 | pool_name|1 | pool_name | A | 1 | NULL | NULL | | BTREE | | +---++--+--+--+---+-+--++--++-+ 6 rows in set (0.00 sec) 2012/10/23 Alan DeKok al...@deployingradius.com Antonio Modesto wrote: I noticed that there where 98 clients connected on a specific NAS, but there was only 80 entries for that NAS on radippool table, thus there was a possibility of when a new client was establishing a connection, it would get an IP that was in use but was not in the table. Well, that's the issue. Somehow it's assigning IPs, but not recording them. That's not really supposed to happen. Is your database OK? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Atenciosamente, * Antônio Modesto Gerente de TI* Praça Getúlio Vargas, 77 – Sala 308 – Centro Santo Antônio do Monte – MG – CEP: 35560-000 Tel:(37) 3281-2800 Contato: isimp...@isimples.com.br http://www.isimples.com.br Aviso:Esta mensagem e quaisquer arquivos em anexo podem conter informações confidenciais e/ou privilegiadas. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, por favor, não leia, copie, repasse, imprima, guarde, nem tome qualquer ação baseada nessas informações. Notifique o remetente imediatamente por e-mail e apague a mensagem permanentemente. Atenção: embora a Isimples Telecom, tome seus cuidados para garantir a ausência de vírus neste e-mail, a empresa não se responsabiliza por quaisquer perdas ou danos decorrentes do uso da mensagem e seus anexos. A segurança e ausência de erros na transmissão do e-mail não podem ser garantidas, já que as informações podem ser interceptadas, corrompidas, perdidas, destruídas, atrasadas, chegarem incompletas, ou, ainda, conter vírus. Recomendamos checar se o e-mail e seus anexos contém vírus, uma vez que nem a Isimples Telecom ou o remetente se responsabilizam pela transmissão destes. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - duplicated ip address after freeradius restart
Antonio Modesto wrote: It seems to be ok, the load average of the server: load averages: 0.00, 0.00, 0.00 I even created some indexes on radippool table: Well.. the default IP Pool indexes should be OK. And the default constraints should be OK. It's not an index problem. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Little problem with sqlippool
Hi, I'm testing sqlippool, so far it's working well, but I'm with a exception that I haven't thought about it before. We use radius to authenticate the clients on wireless access points and with PPPoE, and now I started using sqlippool to dynamically distribute the IP's and BGP to announce the routes through the NAS'es. The problem is that radius allocates a IP for the client when he associates to an access point, (the wireless authentication is done with the MAC Address as the UserName and Password) and another IP when he connects on PPPoE, of course the IP allocated for the MAC is not used, but the record stays on the radippool table, and cannot be allocated to another user. Is there a way to do a regex or something like that before selecting a pool for the client? Thanks. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Little problem with sqlippool
On 21 Aug 2012, at 16:20, Antonio Modesto mode...@isimples.com.br wrote: Hi, I'm testing sqlippool, so far it's working well, but I'm with a exception that I haven't thought about it before. We use radius to authenticate the clients on wireless access points and with PPPoE, and now I started using sqlippool to dynamically distribute the IP's and BGP to announce the routes through the NAS'es. The problem is that radius allocates a IP for the client when he associates to an access point, (the wireless authentication is done with the MAC Address as the UserName and Password) and another IP when he connects on PPPoE, of course the IP allocated for the MAC is not used, but the record stays on the radippool table, and cannot be allocated to another user. Is there a way to do a regex or something like that before selecting a pool for the client? see man unlang? :) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Little problem with sqlippool
On 21/08/12 16:20, Antonio Modesto wrote: Hi, I'm testing sqlippool, so far it's working well, but I'm with a exception that I haven't thought about it before. We use radius to authenticate the clients on wireless access points and with PPPoE, and now I started using sqlippool to dynamically distribute the IP's and BGP to announce the routes through the NAS'es. The problem is that radius allocates a IP for the client when he associates to an access point, (the wireless authentication is done with the MAC Address as the UserName and Password) and another IP when he connects on PPPoE, of course the IP allocated for the MAC is not used, but the record stays on the radippool table, and cannot be allocated to another user. Is there a way to do a regex or something like that before selecting a pool for the client? sqlippool only runs if you tell it to. So, you need to conditionally run it in post-auth. For example: post-auth { ... if (Huntgroup-Name == PPPoE) { # only allocate an IP on PPPoE sqlippool } } Alternatively, use virtual servers and client/listen statements to break the wireless and PPPoE policies out, and just don't use sqlippool in the wireless virtual server. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Little problem with sqlippool
2012/8/21 Phil Mayers p.may...@imperial.ac.uk On 21/08/12 16:20, Antonio Modesto wrote: Hi, I'm testing sqlippool, so far it's working well, but I'm with a exception that I haven't thought about it before. We use radius to authenticate the clients on wireless access points and with PPPoE, and now I started using sqlippool to dynamically distribute the IP's and BGP to announce the routes through the NAS'es. The problem is that radius allocates a IP for the client when he associates to an access point, (the wireless authentication is done with the MAC Address as the UserName and Password) and another IP when he connects on PPPoE, of course the IP allocated for the MAC is not used, but the record stays on the radippool table, and cannot be allocated to another user. Is there a way to do a regex or something like that before selecting a pool for the client? sqlippool only runs if you tell it to. So, you need to conditionally run it in post-auth. For example: post-auth { ... if (Huntgroup-Name == PPPoE) { # only allocate an IP on PPPoE sqlippool } } Alternatively, use virtual servers and client/listen statements to break the wireless and PPPoE policies out, and just don't use sqlippool in the wireless virtual server. Can I test this 'if' statement against a radius attribute, such as Service-Type? Anyway, I had another idea, though it doesn't seem to be the best one. As I have two servers and just one is running radius 2.X with sqlippool, I could use one for wireless authentication and another for ppp authentication. Bad Idea? - List info/subscribe/unsubscribe? See http://www.freeradius.org/** list/users.html http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Little problem with sqlippool
I've configured it this way: if (Framed-Protocol == PPP) { sqlippool } It's working so far, I'll do some more tests. Thanks a lot. 2012/8/21 Antonio Modesto mode...@isimples.com.br 2012/8/21 Phil Mayers p.may...@imperial.ac.uk On 21/08/12 16:20, Antonio Modesto wrote: Hi, I'm testing sqlippool, so far it's working well, but I'm with a exception that I haven't thought about it before. We use radius to authenticate the clients on wireless access points and with PPPoE, and now I started using sqlippool to dynamically distribute the IP's and BGP to announce the routes through the NAS'es. The problem is that radius allocates a IP for the client when he associates to an access point, (the wireless authentication is done with the MAC Address as the UserName and Password) and another IP when he connects on PPPoE, of course the IP allocated for the MAC is not used, but the record stays on the radippool table, and cannot be allocated to another user. Is there a way to do a regex or something like that before selecting a pool for the client? sqlippool only runs if you tell it to. So, you need to conditionally run it in post-auth. For example: post-auth { ... if (Huntgroup-Name == PPPoE) { # only allocate an IP on PPPoE sqlippool } } Alternatively, use virtual servers and client/listen statements to break the wireless and PPPoE policies out, and just don't use sqlippool in the wireless virtual server. Can I test this 'if' statement against a radius attribute, such as Service-Type? Anyway, I had another idea, though it doesn't seem to be the best one. As I have two servers and just one is running radius 2.X with sqlippool, I could use one for wireless authentication and another for ppp authentication. Bad Idea? - List info/subscribe/unsubscribe? See http://www.freeradius.org/** list/users.html http://www.freeradius.org/list/users.html -- Atenciosamente, * Antônio Modesto Gerente de TI* Praça Getúlio Vargas, 77 – Sala 308 – Centro Santo Antônio do Monte – MG – CEP: 35560-000 Tel:(37) 3281-2800 Contato: isimp...@isimples.com.br http://www.isimples.com.br Aviso:Esta mensagem e quaisquer arquivos em anexo podem conter informações confidenciais e/ou privilegiadas. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, por favor, não leia, copie, repasse, imprima, guarde, nem tome qualquer ação baseada nessas informações. Notifique o remetente imediatamente por e-mail e apague a mensagem permanentemente. Atenção: embora a Isimples Telecom, tome seus cuidados para garantir a ausência de vírus neste e-mail, a empresa não se responsabiliza por quaisquer perdas ou danos decorrentes do uso da mensagem e seus anexos. A segurança e ausência de erros na transmissão do e-mail não podem ser garantidas, já que as informações podem ser interceptadas, corrompidas, perdidas, destruídas, atrasadas, chegarem incompletas, ou, ainda, conter vírus. Recomendamos checar se o e-mail e seus anexos contém vírus, uma vez que nem a Isimples Telecom ou o remetente se responsabilizam pela transmissão destes. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Tue, 2012-01-31 at 21:00 +0100, Alan DeKok wrote: Antonio Modesto wrote: I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Set: update control { Pool-Name := main_pool } Ok, I will try it. That sets the pool to use. and I have this user: select * from radcheck; ++--+---++---+---+ | id | UserName | Attribute | op | Value | ativo | ++--+---++---+---+ | 1 | modesto | User-Password | == | 12345 | S | ++--+---++---+---+ That's wrong. Use Cleartext-Password := instead of User-Password == The problem is that my production server is running Freeradius 1.8, and it uses the Attribute User-Password, but the module rlm_sqlippool is not working in this version of freeradius. Then if I decide to use sqlippool, will I need to upgrade to freeradius 2.x and change this Attribute in all my MySQL database? Thanks. I've already tried to associate the Pool-Name attribute with this user, but it didn't work. See the FAQ for it doesn't work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Wed, 2012-02-01 at 09:19 -0200, Antonio Modesto wrote: On Tue, 2012-01-31 at 21:00 +0100, Alan DeKok wrote: Antonio Modesto wrote: I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Set: update control { Pool-Name := main_pool } Ok, I will try it. Sorry for the ignorance, but where must I put this section? Can I just put it anywhere in radiusd.conf or it must be inside another section? That sets the pool to use. and I have this user: select * from radcheck; ++--+---++---+---+ | id | UserName | Attribute | op | Value | ativo | ++--+---++---+---+ | 1 | modesto | User-Password | == | 12345 | S | ++--+---++---+---+ That's wrong. Use Cleartext-Password := instead of User-Password == The problem is that my production server is running Freeradius 1.8, and it uses the Attribute User-Password, but the module rlm_sqlippool is not working in this version of freeradius. Then if I decide to use sqlippool, will I need to upgrade to freeradius 2.x and change this Attribute in all my MySQL database? Thanks. I've already tried to associate the Pool-Name attribute with this user, but it didn't work. See the FAQ for it doesn't work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
Antonio Modesto wrote: That's wrong. Use Cleartext-Password := instead of User-Password == The problem is that my production server is running Freeradius 1.8, and it uses the Attribute User-Password No. The change to Cleartext-Password was done in 1.1.4. The 1.1.8 version was released long after that.w , but the module rlm_sqlippool is not working in this version of freeradius. Then if I decide to use sqlippool, will I need to upgrade to freeradius 2.x and change this Attribute in all my MySQL database? It's better if you upgrade, *and* if you change the attribute. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
2012/2/1 Antonio Modesto mode...@isimples.com.br: On Wed, 2012-02-01 at 09:19 -0200, Antonio Modesto wrote: On Tue, 2012-01-31 at 21:00 +0100, Alan DeKok wrote: Antonio Modesto wrote: I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Set: update control { Pool-Name := main_pool } Ok, I will try it. Sorry for the ignorance, but where must I put this section? Can I just put it anywhere in radiusd.conf or it must be inside another section? It depends. Basically Pool-Name is a control item, similar to Cleartext-Password. Where do you put cleartext-password for your user? That's also the place where you put pool-name, if you want per-user pool setting. Another way is to set pool-name unconditionally for all user with unlang. In this case the place to do it is authorize section. But then again the example Alan wrote is using unlang, and AFAIK it's not available in FR 1.x. You need to upgrade. There might (as in I haven't tested it) also the possiblity of using users file to set default check items. Something like this on top uf users file: DEFAULTPool-Name := main_pool Fall-Through = Yes -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Wed, 2012-02-01 at 13:31 +0100, Alan DeKok wrote: Antonio Modesto wrote: That's wrong. Use Cleartext-Password := instead of User-Password == The problem is that my production server is running Freeradius 1.8, and it uses the Attribute User-Password No. The change to Cleartext-Password was done in 1.1.4. The 1.1.8 version was released long after that.w , but the module rlm_sqlippool is not working in this version of freeradius. Then if I decide to use sqlippool, will I need to upgrade to freeradius 2.x and change this Attribute in all my MySQL database? It's better if you upgrade, *and* if you change the attribute. I find that the pool_name is not set, it's trying to find sqlippool, I changed the name of my pool to this name and it worked. Maybe I am putting the update control in the wrong place, I've put it in the post-auth section. [sqlippool] expand: SELECT FramedIPAddress FROM radippool WHERE pool_name = 'sqlippool' AND expiry_time NOW() ORDER BY RAND() LIMIT 1 FOR UPDATE - SELECT FramedIPAddress FROM radippool WHERE pool_name = 'sqlippool' AND expiry_time NOW() ORDER BY RAND() LIMIT 1 FOR UPDATE Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
Antonio Modesto wrote: Sorry for the ignorance, but where must I put this section? Can I just put it anywhere in radiusd.conf or it must be inside another section? You don't. I had assumed you were running version 2. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Wed, 2012-02-01 at 19:53 +0700, Fajar A. Nugraha wrote: 2012/2/1 Antonio Modesto mode...@isimples.com.br: On Wed, 2012-02-01 at 09:19 -0200, Antonio Modesto wrote: On Tue, 2012-01-31 at 21:00 +0100, Alan DeKok wrote: Antonio Modesto wrote: I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Set: update control { Pool-Name := main_pool } Ok, I will try it. Sorry for the ignorance, but where must I put this section? Can I just put it anywhere in radiusd.conf or it must be inside another section? It depends. Basically Pool-Name is a control item, similar to Cleartext-Password. Where do you put cleartext-password for your user? That's also the place where you put pool-name, if you want per-user pool setting. Another way is to set pool-name unconditionally for all user with unlang. In this case the place to do it is authorize section. But then again the example Alan wrote is using unlang, and AFAIK it's not available in FR 1.x. You need to upgrade. There might (as in I haven't tested it) also the possiblity of using users file to set default check items. Something like this on top uf users file: DEFAULTPool-Name := main_pool Fall-Through = Yes I use MySQL to store the users information, my database is still with the User-Password attribute instead of Cleartext-Password, it is in the radcheck table. But I don't want to use per-user pools, if it is possible, the better solution for me would be setting the pool based on some information of the NAS, because my routing topology doesn't permit a single pool for all cities that we attend. I am sorry for my lack of knowledge about freeradius, but I didn't find some place where explain how freeradius configuration files works together, and what it's the order of the authentication process. I know that it's because it's a very flexible tool. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Wed, 2012-02-01 at 14:24 +0100, Alan DeKok wrote: Antonio Modesto wrote: Sorry for the ignorance, but where must I put this section? Can I just put it anywhere in radiusd.conf or it must be inside another section? You don't. I had assumed you were running version 2. Not in my production server, the server I'm using to test is running version 2. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
2012/2/1 Antonio Modesto mode...@isimples.com.br: But I don't want to use per-user pools, if it is possible, the better solution for me would be setting the pool based on some information of the NAS, because my routing topology doesn't permit a single pool for all cities that we attend. It depends. If your NAS sends some attribute that you can use (e.g NAS-IP-Address), AND the number of possible values for that attribute is small, AND you use FR2, you can just use unlang in authorize or post-auth section to set pool-name. Manpage of unlang: http://freeradius.org/radiusd/man/unlang.html Look at if and update keyword. Alan already wrote the update example. Another possibility is to store the NAS-IP-Address (or whatever attribute you use) - Pool-Name mapping in your own sql table, and using unlang you can do something like update control { Pool-Name := %{sql: SELECT pool_name FROM nas_pool_name WHERE nas_ip_address='%{NAS-IP-Address}' } ... where nas_pool_name is your own custom table. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
On Wed, 2012-02-01 at 20:59 +0700, Fajar A. Nugraha wrote: 2012/2/1 Antonio Modesto mode...@isimples.com.br: But I don't want to use per-user pools, if it is possible, the better solution for me would be setting the pool based on some information of the NAS, because my routing topology doesn't permit a single pool for all cities that we attend. It depends. If your NAS sends some attribute that you can use (e.g NAS-IP-Address), AND the number of possible values for that attribute is small, AND you use FR2, you can just use unlang in authorize or post-auth section to set pool-name. Manpage of unlang: http://freeradius.org/radiusd/man/unlang.html Look at if and update keyword. Alan already wrote the update example. Another possibility is to store the NAS-IP-Address (or whatever attribute you use) - Pool-Name mapping in your own sql table, and using unlang you can do something like update control { Pool-Name := %{sql: SELECT pool_name FROM nas_pool_name WHERE nas_ip_address='%{NAS-IP-Address}' Yes, good idea, I can create a custom table for that. I just need to understand how this stuff (authorize, post-auth) works, I'm a little lost with these things. So far I didn't get setting the pool_name to a custom name, I am testing with the default sqlippool. My production radius server is running freeradius 1.1.8, I installed freeradius 2 in another server with another SQL database and copied the configuration files from the production server to the test server and changed some stuff to get it running, then I am doing these tests in this server. I don't know if Freeradius 2.x are compatible with freeradius 1.1.8 configuration files. Thanks and sorry for the amount of questions. Regards. } ... where nas_pool_name is your own custom table. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
Antonio Modesto wrote: My production radius server is running freeradius 1.1.8, I installed freeradius 2 in another server with another SQL database and copied the configuration files from the production server to the test server and changed some stuff to get it running, then I am doing these tests in this server. I don't know if Freeradius 2.x are compatible with freeradius 1.1.8 configuration files. It's mostly compatible. There are a few changes, but nothing major. Thanks and sorry for the amount of questions. It's OK to ask good questions. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
2012/2/2 Antonio Modesto mode...@isimples.com.br: My production radius server is running freeradius 1.1.8, I installed freeradius 2 in another server with another SQL database and copied the configuration files from the production server to the test server and changed some stuff to get it running, then I am doing these tests in this server. I don't know if Freeradius 2.x are compatible with freeradius 1.1.8 configuration files. An advice from someone who has done similar migration: don't copy the config files :) While Alan said it's mostly compatible, FR2 restructured some files to make it more logical and easy to understand. My advice is to look at your old config files, and: - note what changes you made from the original (copying an original config directory from a fresh installation and do diff -Naru can help) - list what functionalities you need After that, implement the changes/functionality in the new config. The benefit is that the config is cleaner, plus you know exactly what changes you made, so there will be no unknown magic in your new setup. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Newbie and Sqlippool
Hi, I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Can someone explain me how can I do this? i've set up a pool called 'main_pool' in my database: select * from radippool; ++---+-+--+-+--+-+--+--+ | id | pool_name | FramedIPAddress | NASIPAddress | CalledStationId | CallingStationID | expiry_time | username | pool_key | ++---+-+--+-+--+-+--+--+ | 1 | main_pool | 172.16.254.1| | | | -00-00 00:00:00 | | | ++---+-+--+-+--+-+--+--+ 1 row in set (0.01 sec) and I have this user: select * from radcheck; ++--+---++---+---+ | id | UserName | Attribute | op | Value | ativo | ++--+---++---+---+ | 1 | modesto | User-Password | == | 12345 | S | ++--+---++---+---+ 1 row in set (0.00 sec) I've already tried to associate the Pool-Name attribute with this user, but it didn't work. Can someone explain me how this stuff works or send me a more detailed documentation? Thanks in advance. Regards. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Newbie and Sqlippool
Antonio Modesto wrote: I am trying to test sqlippool on freeradius, but i've found that the documentation explains very well how to implement it, but it doesn't explain very well how I can set a user or a group to use this pool. Set: update control { Pool-Name := main_pool } That sets the pool to use. and I have this user: select * from radcheck; ++--+---++---+---+ | id | UserName | Attribute | op | Value | ativo | ++--+---++---+---+ | 1 | modesto | User-Password | == | 12345 | S | ++--+---++---+---+ That's wrong. Use Cleartext-Password := instead of User-Password == I've already tried to associate the Pool-Name attribute with this user, but it didn't work. See the FAQ for it doesn't work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
optimize sqlippool scheme
hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work. I trying to add indexes to this table. It make fast selects, but slow updates. [code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code] Next I tried to make table from the begin. Here it is [code] CREATE TABLE `radippool_new` ( `id` int(4) unsigned NOT NULL AUTO_INCREMENT, `pool_name` tinyint(1) unsigned NOT NULL, `framedipaddress` int(4) unsigned NOT NULL, `nasipaddress` int(4) unsigned DEFAULT NULL, `calledstationid` bigint(8) unsigned NOT NULL, `callingstationid` bigint(8) unsigned NOT NULL, `expiry_time` timestamp NULL DEFAULT NULL, `username` varchar(64) NOT NULL, `pool_key` int(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Ip pool for customers' CREATE TABLE `radippool_names` ( `id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 [/code] It's working two time faster, than default with updates queries. But two time slower with selects queries. Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it? *Regards,* Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh avkos...@gmail.com wrote: hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work. I have over 1 million subscribers. Then again, I'm using mysql cluster :) I trying to add indexes to this table. It make fast selects, but slow updates. Duh! :D That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously. [code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code] You really shouldn't use myisam for heavy-write tables. Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it? It's kinda complicated. I've been meaning to submit a patch, but it's just not that easy. For starters, about mysql storage engines: - myisam - good for majority of reads, terrible for writes. Plus it lacks transactional support, which usually means that with the default setup you can either hand out duplicate IP to clients, or stuck with slow table locks. - innodb - somewhat balanced for both read-write, and have transactional support, but mostly disk-bound. especially if you enforce cosistency by flushing to disk on every transaction. - memory - good-enough for writes (i.e. not disk-bound, obviously), but still suffer from table locks - NDB (i.e. mysql cluster) - good for lots of parallel writes, usually not disk-bound Now performance-wise using NDB or memory would be best, but it won't be the best choice for common mysql setups. You MIGHT be able to gain some improvements by using innodb (without changing anything else). Maybe. Then there's the problem with queries. The default sqlippool query would basically lock the table during IP assignment (due to SELECT ... FOR UPDATE). I opted to change the query to NOT use table locks (using randomization instead), at the expense of possible duplicate ip assignment. In my case when a client gets duplicate IP, the NAS will reject the user, so the user will dial again, and (hopefully) get a unique free ip address this time. It's an acceptable workaround for me, but it won't be implementable in normal setups. Add to that the fact that I had to implement it using stored procedures, and you can see how it gets pretty complicated. So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote: That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously. Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them. However, I'm quite surprised that you're having problems with 25k subscribers; that's not a large table. What is the query rate? Do you have very low interim accounting values perhaps, meaning you're extending the IP lease times too frequently? So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) - use postgres ;o) In all seriousness, It's worth noting that postgres does have the advantage that select ... for update uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
One more question. Where can I take nas-type value to use it in user authorization? Radius take it from mysql nasinfo table at startup. I take it from DB every time subscriber try to authorize. Regards, Alexander. 2012/1/12 Fajar A. Nugraha l...@fajar.net On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh avkos...@gmail.com wrote: hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work. I have over 1 million subscribers. Then again, I'm using mysql cluster :) I trying to add indexes to this table. It make fast selects, but slow updates. Duh! :D That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously. [code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code] You really shouldn't use myisam for heavy-write tables. Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it? It's kinda complicated. I've been meaning to submit a patch, but it's just not that easy. For starters, about mysql storage engines: - myisam - good for majority of reads, terrible for writes. Plus it lacks transactional support, which usually means that with the default setup you can either hand out duplicate IP to clients, or stuck with slow table locks. - innodb - somewhat balanced for both read-write, and have transactional support, but mostly disk-bound. especially if you enforce cosistency by flushing to disk on every transaction. - memory - good-enough for writes (i.e. not disk-bound, obviously), but still suffer from table locks - NDB (i.e. mysql cluster) - good for lots of parallel writes, usually not disk-bound Now performance-wise using NDB or memory would be best, but it won't be the best choice for common mysql setups. You MIGHT be able to gain some improvements by using innodb (without changing anything else). Maybe. Then there's the problem with queries. The default sqlippool query would basically lock the table during IP assignment (due to SELECT ... FOR UPDATE). I opted to change the query to NOT use table locks (using randomization instead), at the expense of possible duplicate ip assignment. In my case when a client gets duplicate IP, the NAS will reject the user, so the user will dial again, and (hopefully) get a unique free ip address this time. It's an acceptable workaround for me, but it won't be implementable in normal setups. Add to that the fact that I had to implement it using stored procedures, and you can see how it gets pretty complicated. So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On Thu, Jan 12, 2012 at 8:15 PM, Phil Mayers p.may...@imperial.ac.uk wrote: So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) - use postgres ;o) I avoid postgres like plague in the past due to vacuum :) It has autovacuum now, but the bad image remains. To be fair, it's probably similar with the way many people avoid mysql due to myisam-related issues :) In all seriousness, It's worth noting that postgres does have the advantage that select ... for update uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally. Really? Did you personally test it, running the select queries manually? Mysql is also capable to do so (at least innodb and ndb does), but the thing is how many rows got locked depends on how many rows were selected. So a query like SELECT ... FOR UPDATE LIMIT would end up locking all rows (the limit clause pretty much don't matter), in effect doing a table lock, while SELECT ... FOR UPDATE LIMIT WHERE ID IN (...) would end up locking only several rows. This is where ndb truly shines, as huge amounts of concurrent process with row locks (around 128 in my setup) can run in paralel without interfering with each other, as long as the selected rows don't overlap (this is where randomization comes in). The process to limit the id in my setup right now has the possiblity of causing duplicate IP address handed out. It should be possible to fix this, but I haven't had the time and my current setup is good enough for now. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On Thu, Jan 12, 2012 at 8:30 PM, Alexander Kosykh avkos...@gmail.com wrote: One more question. Where can I take nas-type value to use it in user authorization? Radius take it from mysql nasinfo table at startup. I take it from DB every time subscriber try to authorize. That's pretty much the only way. Unless your nas sends it as an attribute (which should be visible when you ran FR in debug mode) -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
I have interim accounting value 10 minutes and IP lease time is 30 minutes. 2012/1/12 Phil Mayers p.may...@imperial.ac.uk On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote: That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously. Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them. However, I'm quite surprised that you're having problems with 25k subscribers; that's not a large table. What is the query rate? Do you have very low interim accounting values perhaps, meaning you're extending the IP lease times too frequently? So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) - use postgres ;o) In all seriousness, It's worth noting that postgres does have the advantage that select ... for update uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On 01/12/2012 01:33 PM, Fajar A. Nugraha wrote: I avoid postgres like plague in the past due to vacuum :) It has autovacuum now, but the bad image remains. To be fair, it's probably similar with the way many people avoid mysql due to myisam-related issues :) Funnily enough I avoid MySQL like the plague because of list of things You're right of course. People must weigh the products and make their own choice. In all seriousness, It's worth noting that postgres does have the advantage that select ... for update uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally. Really? Did you personally test it, running the select queries manually? Yes. This is a core feature. Postgres has done row-level locking for many, many years. We use it all over the place for lots of things. would end up locking only several rows. This is where ndb truly shines, as huge amounts of concurrent process with row locks (around The multiple storage engine nonsense (just my opinion, of course) is one of the many reasons I don't use MySQL. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: optimize sqlippool scheme
On 01/12/2012 01:40 PM, Alexander Kosykh wrote: I have interim accounting value 10 minutes and IP lease time is 30 minutes. So you should be getting, what, ~40 accounting packets/second with 25k users online. Does this correspond to the query load you're seeing? 40 SQL updates/second is not a lot... If you increase your interim accounting to 1800 seconds, you'll decrease that load to about 15 queries/second, which might be a quick and easy win. But it shouldn't be going that slow with that load IMHO. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re[4]: sqlippool IP conflict problem
Здравствуйте, Fajar. Вы писали 20 декабря 2011 г., 9:37:41: FAN 2011/12/20 Коньков Евгений kes-...@yandex.ru: I see that expire_time keep updated while radiusd will receive interim-update packets. BUT radius listen on UPD port and this mean that: no garantee that server will receive interim-update packet. So there is a chance to lose two interim packets for current user. FAN Correct. Therefore IP leased for current user will be countered as expired and may be leased for other user causing IP conflict error. FAN That's why I suggested two times Acct-Interim-Interval would be a good FAN choice. If one packet goes missing hopefully we'll be able to get the FAN next one. If you're feeling paranoid you could use a big timeout value FAN (e.g. 10x Acct-Interim-Interval), with the consequence that it will FAN take longer before IP addresses used by stale sessions can be reused. I have Acct-Interim-Interval = 300 And I have script that ckeck zombie sessions once per 15min Date: Sun, 18 Dec 2011 21:15:01 + (UTC) $VAR1 = [ { 'NASPORT' = 193, 'ID' = 1221104, 'USERNAME' = '10228', 'TERMINATECAUSE' = 'OnLine', 'FRAMEDIPADDRESS' = '192.168.16.195', 'SESSIONID' = 'dfe6caf212e8f842', 'BILL' = undef, 'BYTESOUT' = '53208742', 'STARTTIME' = '2011-12-18 19:02:49', 'CALLTO' = '10.5.0.17', 'STOPTIME' = '2011-12-18 20:49:03', 'BYTESIN' = '9158505', 'CALLFROM' = '10.7.30.58 / 00:24:54:5d:1a:b5 / vlan76', 'ONLINETIME' = 6375 } ]; It check 'OnLine' and STOPTIME more that 15min (3times AcctInterimInterval) As you see 20:49:03 was last acct-interim-interval was received script was runned at 21:15:01 so 25min are passed from last AcctInterimInterval it is 5times. Per day I get about from 5 to 10 such sessions. It is noising ( NAS server and freeradius both are on localhost and there is about 50%Idle on CPU there are about 200-300users online with 5min acctInterimInterval. Is it possible to force FreeRadius to listen on TCP port? I think there must be a mechanism to force check that IP is *really* unused before freeing or 're lease' it. FAN It's a tradeoff, really. In theory, you COULD write your own module FAN that verifies whether a user is online. Kinda like what simultaneous FAN use using radutmp checkrad does, which performs checking using FAN snmp/telnet/ssh to the NAS. However that would inflict heavy FAN performance penalty. It might be acceptable if you only handle (for FAN example) 10 AAA/minute, but it won't be feasible if you need to handle FAN 10 AAA/second. FAN For most practical purposes, the current sqlippool is good enough. Not FAN perfect, but good enough. -- С уважением, Коньков mailto:kes-...@yandex.ru - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Re[4]: sqlippool IP conflict problem
2011/12/20 Коньков Евгений kes-...@yandex.ru: I have Acct-Interim-Interval = 300 And I have script that ckeck zombie sessions once per 15min Does it also remove the zombie sessions? If yes, set lease-duration = 900 on your sqlippool.conf. NAS server and freeradius both are on localhost and there is about 50%Idle on CPU there are about 200-300users online with 5min acctInterimInterval. Is it possible to force FreeRadius to listen on TCP port? No. That's not how radius work. There's radsec, but I doubt your NAS supports it. Lost accounting packets are common, even in big carriers. Configure your system to deal with it properly. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool IP conflict problem
Hi, FreeRadius. while frame IP address to user there are three stage in sqlippool clear find update clear stage - clear info about expired framed IP (JUST CLEAR!) so *new* connected user have a chance get IP address of *current* online user whose IP address is expired. This will cause IP conflict. does radius make any signal to nitify this situation? or is it possible to frame other IP address to *current* online user? or at least drop *current* online user? -- С уважением, Коньков mailto:kes-...@yandex.ru - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool IP conflict problem
2011/12/20 Коньков Евгений kes-...@yandex.ru: Hi, FreeRadius. while frame IP address to user there are three stage in sqlippool clear find update clear stage - clear info about expired framed IP (JUST CLEAR!) so *new* connected user have a chance get IP address of *current* online user whose IP address is expired. That is how sqlippool works This will cause IP conflict. Not if you configure it correctly. does radius make any signal to nitify this situation? or is it possible to frame other IP address to *current* online user? or at least drop *current* online user? For sqlipool to work correctly, the NAS needs to send accounting packets, and interim updates must be enabled. You'd then set sqlippool's lease-duration to a reasonable value (two times Acct-Interim-Interval would be a good choice). Follow that, and you won't have IP address conflict. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re[2]: sqlippool IP conflict problem
Здравствуйте, Fajar. Вы писали 20 декабря 2011 г., 8:30:36: FAN 2011/12/20 Коньков Евгений kes-...@yandex.ru: Hi, FreeRadius. while frame IP address to user there are three stage in sqlippool clear find update clear stage - clear info about expired framed IP (JUST CLEAR!) so *new* connected user have a chance get IP address of *current* online user whose IP address is expired. FAN That is how sqlippool works This will cause IP conflict. FAN Not if you configure it correctly. does radius make any signal to nitify this situation? or is it possible to frame other IP address to *current* online user? or at least drop *current* online user? FAN For sqlipool to work correctly, the NAS needs to send accounting FAN packets, and interim updates must be enabled. You'd then set FAN sqlippool's lease-duration to a reasonable value (two times FAN Acct-Interim-Interval would be a good choice). Follow that, and you FAN won't have IP address conflict. sql/mysql/ippool.conf allocate-update = ... ... expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ I see that expire_time keep updated while radiusd will receive interim-update packets. BUT radius listen on UPD port and this mean that: no garantee that server will receive interim-update packet. So there is a chance to lose two interim packets for current user. Therefore IP leased for current user will be countered as expired and may be leased for other user causing IP conflict error. I think there must be a mechanism to force check that IP is *really* unused before freeing or 're lease' it. -- С уважением, Коньков mailto:kes-...@yandex.ru - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re[3]: sqlippool IP conflict problem
Здравствуйте, Коньков. Вы писали 20 декабря 2011 г., 9:22:27: КЕ Здравствуйте, Fajar. КЕ Вы писали 20 декабря 2011 г., 8:30:36: FAN 2011/12/20 Коньков Евгений kes-...@yandex.ru: Hi, FreeRadius. while frame IP address to user there are three stage in sqlippool clear find update clear stage - clear info about expired framed IP (JUST CLEAR!) so *new* connected user have a chance get IP address of *current* online user whose IP address is expired. FAN That is how sqlippool works This will cause IP conflict. FAN Not if you configure it correctly. does radius make any signal to nitify this situation? or is it possible to frame other IP address to *current* online user? or at least drop *current* online user? FAN For sqlipool to work correctly, the NAS needs to send accounting FAN packets, and interim updates must be enabled. You'd then set FAN sqlippool's lease-duration to a reasonable value (two times FAN Acct-Interim-Interval would be a good choice). Follow that, and you FAN won't have IP address conflict. КЕ sql/mysql/ippool.conf КЕ allocate-update = ... КЕ ... КЕ expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ КЕ I see that expire_time keep updated while radiusd will receive КЕ interim-update packets. BUT radius listen on UPD port and this КЕ mean that: no garantee that server will receive interim-update packet. So there КЕ is a chance to lose two interim packets for current user. КЕ Therefore IP leased for current user will be countered as expired and may be КЕ leased for other user causing IP conflict error. КЕ I think there must be a mechanism to force check that IP is *really* КЕ unused before freeing or 're lease' it. Or may be force radius to listen TCP port. Is it possible? -- С уважением, Коньков mailto:kes-...@yandex.ru - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Re[2]: sqlippool IP conflict problem
2011/12/20 Коньков Евгений kes-...@yandex.ru: I see that expire_time keep updated while radiusd will receive interim-update packets. BUT radius listen on UPD port and this mean that: no garantee that server will receive interim-update packet. So there is a chance to lose two interim packets for current user. Correct. Therefore IP leased for current user will be countered as expired and may be leased for other user causing IP conflict error. That's why I suggested two times Acct-Interim-Interval would be a good choice. If one packet goes missing hopefully we'll be able to get the next one. If you're feeling paranoid you could use a big timeout value (e.g. 10x Acct-Interim-Interval), with the consequence that it will take longer before IP addresses used by stale sessions can be reused. I think there must be a mechanism to force check that IP is *really* unused before freeing or 're lease' it. It's a tradeoff, really. In theory, you COULD write your own module that verifies whether a user is online. Kinda like what simultaneous use using radutmp checkrad does, which performs checking using snmp/telnet/ssh to the NAS. However that would inflict heavy performance penalty. It might be acceptable if you only handle (for example) 10 AAA/minute, but it won't be feasible if you need to handle 10 AAA/second. For most practical purposes, the current sqlippool is good enough. Not perfect, but good enough. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool problem 2 NAS
--no Problem-- nas1 (localhost) Only one NAS login and logout No problem --Problem-- nas1 (localhost) nas2(remote) Username NASIPAddress CalledStationIdCallingStationID expiry_timepool_key user1 127.0.0.1 1xx.2xx.3xx.1 2011-09-19 20:00:00 0 user2 127.0.0.1 1xx.3xx.3xx.22011-09-29 20:21:13 10 user3 127.0.0.1 1xx.4xx.3xx.32011-09-29 20:21:13 9 user4 127.0.0.1 1xx.5xx.3xx.42011-09-29 20:21:13 4 Now time is 2011-09-19 20:00:00 user5 login... Username NASIPAddress CalledStationIdCallingStationID expiry_timepool_key user1 2011-09-19 20:00:00 0 user2 127.0.0.1 1xx.3xx.3xx.22011-09-29 20:21:13 10 user3 127.0.0.1 1xx.4xx.3xx.32011-09-29 20:21:13 9 user4 127.0.0.1 1xx.5xx.3xx.42011-09-29 20:21:13 4 user5 127.0.0.1 1xx.6xx.3xx.52011-09-29 20:21:13 0 NASIPAddress,CallingStationID,pool_key is random over time nas1 10 user connect nas2 8 user connect but sqlippool is Username NASIPAddress CalledStationIdCallingStationID expiry_timepool_key user1 2011-09-19 20:00:00 0 user2 127.0.0.1 1xx.3xx.3xx.22011-09-29 20:21:13 10 user3 127.0.0.1 1xx.4xx.3xx.32011-09-29 20:21:13 9 user4 127.0.0.1 1xx.5xx.3xx.42011-09-29 20:21:13 4 user5 127.0.0.1 1xx.6xx.3xx.52011-09-29 20:21:13 0 user6 2011-09-19 20:00:00 0 user7 2011-09-19 20:00:00 0 user8 2011-09-19 20:00:00 0 user5 127.0.0.1 1xx.9xx.3xx.92011-09-29 20:21:13 20 .. .. nas1(localhost) no problem nas1(localhost) + nas2(remote) == This problem Help me. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
On 01/01/2011 08:56 PM, Marten Pape wrote: But back to Topic: Is it possible to call a module manually in another section? I'd like to Yes, IIRC: accounting { ippool.post-auth } This might fail for other reasons (for example, the accounting request might not contain required fields). And it will try to add a Framed-IP-Address to the accounting-response, which is invalid/illegal. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
Alan DeKok schrieb: Marten Pape wrote: Ji Bryan, hi all, Does anyone know such a dhcp server (couldn't figure out such a feature in ISC dhcp server) or a script that transfers this? So I wouldn't have to script this on my own. The ISC DHCP server doesn't have this feature. FreeRADIUS, however, can work as a DHCP server. :) Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html But it's still marked as experimental. And I need to set up a little bit more complex system than just using dhcp with freeradius. There's something more. Could be interesting to use freeradius-dhcp in future, but it looks as there's a lot of coding necessary to get this dhcp to work as I want to have it. Marten Pape - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
Alan DeKok schrieb: Marten Pape wrote: Ji Bryan, hi all, Does anyone know such a dhcp server (couldn't figure out such a feature in ISC dhcp server) or a script that transfers this? So I wouldn't have to script this on my own. The ISC DHCP server doesn't have this feature. FreeRADIUS, however, can work as a DHCP server. :) Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html But back to Topic: Is it possible to call a module manually in another section? I'd like to execute an ip-pool in accounting (start) instead of post-auth. This is, beaucse I'd like to use the attribute Framed-IP-Address in my own program (knowing that it won't have an effect on WLAN clients). So I need to make the ip-pool feeling like it were called during the post-auth state, so that it will give me a new IP-Address Best regards, Marten Pape - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
Ji Bryan, hi all, Does anyone know such a dhcp server (couldn't figure out such a feature in ISC dhcp server) or a script that transfers this? So I wouldn't have to script this on my own. Regards, Marten Bryan Rank schrieb: Hi Marten, Im no expert, and you probably realize this, but I thought I would mention it anyhow, the accounting start packet contains the ip and mac in it. Some dhcp servers are smart enough to receive them. On Dec 25, 2010, at 13:31, Marten Pape marten.p...@pape-hn.de wrote: Hello, I want to use FreeRadius for a set of WLAN AccessPoints. The general setup is done and seems to work well. Due to some demands of the local area network, I need to manipulate the local dhcp server every time a user eners this network via WLAN. This will be done via an external program that is called in the accounting-section of the default-server. This external program needs to know, which IP should be assigned to the Client. Therefore I need to set up an IP-Pool management - which IP is in use, which one not? The modules ippool and espacially sqlippool are very nice to use and configure. Is it possible to execute them manually? I'd like to retrieve the ip as a parameter that can be passed to this external program. The other way round I'd like to tell the module, which IP can be released. Is that possible and how? Thank you and merry christmas! Marten Pape - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
Marten Pape wrote: Ji Bryan, hi all, Does anyone know such a dhcp server (couldn't figure out such a feature in ISC dhcp server) or a script that transfers this? So I wouldn't have to script this on my own. The ISC DHCP server doesn't have this feature. FreeRADIUS, however, can work as a DHCP server. :) Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Call IPPool / SQLIPPool manually
Hi Marten, Im no expert, and you probably realize this, but I thought I would mention it anyhow, the accounting start packet contains the ip and mac in it. Some dhcp servers are smart enough to receive them. On Dec 25, 2010, at 13:31, Marten Pape marten.p...@pape-hn.de wrote: Hello, I want to use FreeRadius for a set of WLAN AccessPoints. The general setup is done and seems to work well. Due to some demands of the local area network, I need to manipulate the local dhcp server every time a user eners this network via WLAN. This will be done via an external program that is called in the accounting-section of the default-server. This external program needs to know, which IP should be assigned to the Client. Therefore I need to set up an IP-Pool management - which IP is in use, which one not? The modules ippool and espacially sqlippool are very nice to use and configure. Is it possible to execute them manually? I'd like to retrieve the ip as a parameter that can be passed to this external program. The other way round I'd like to tell the module, which IP can be released. Is that possible and how? Thank you and merry christmas! Marten Pape - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Call IPPool / SQLIPPool manually
Hello, I want to use FreeRadius for a set of WLAN AccessPoints. The general setup is done and seems to work well. Due to some demands of the local area network, I need to manipulate the local dhcp server every time a user eners this network via WLAN. This will be done via an external program that is called in the accounting-section of the default-server. This external program needs to know, which IP should be assigned to the Client. Therefore I need to set up an IP-Pool management - which IP is in use, which one not? The modules ippool and espacially sqlippool are very nice to use and configure. Is it possible to execute them manually? I'd like to retrieve the ip as a parameter that can be passed to this external program. The other way round I'd like to tell the module, which IP can be released. Is that possible and how? Thank you and merry christmas! Marten Pape - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sql sqlippool
Hello I am new in this list I am setting up a freeradius server with mysql and sqlippool but I am facing some problems with sqlippool which cannot deliver an ip address to subscribers. I installed Freeradius 2.1.3 on OpenBSD 4.6 with mysql support here is the output of radiusd -X sqlippool { sql-instance-name = sql lease-duration = 3600 pool-name = allocate-begin = START TRANSACTION allocate-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' allocate-find = SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time 'now'::timestamp(0) ORDER BY (username '%{SQL-User-Name}'), (callingstationid '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE allocate-update = UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}', callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE framedipaddress = '%I' allocate-commit = COMMIT allocate-rollback = ROLLBACK pool-check = SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1 start-begin = START TRANSACTION start-update = UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' start-commit = COMMIT start-rollback = ROLLBACK alive-begin = START TRANSACTION alive-update = UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 seconds'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND framedipaddress = '%{Framed-IP-Address}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}' alive-commit = COMMIT alive-rollback = ROLLBACK stop-begin = START TRANSACTION stop-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}' stop-commit = COMMIT stop-rollback = ROLLBACK on-begin = START TRANSACTION on-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' on-commit = COMMIT on-rollback = ROLLBACK off-begin = START TRANSACTION off-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' off-commit = COMMIT off-rollback = ROLLBACK sqlippool_log_exists = Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_success = Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_clear = Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name}) sqlippool_log_failed = IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_nopool = No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) defaultpool = main_pool } Module: Instantiating attr_filter.accounting_response attr_filter attr_filter.accounting_response { attrsfile = /etc/raddb/attrs.accounting_response key = %{User-Name} } Module: Checking session {...} for more modules to load Module: Checking post-auth {...} for more modules to load } radiusd: Opening IP addresses and Ports listen { type = auth ipaddr = * port = 0 } listen { type = acct ipaddr = * port = 0 } Listening on authentication address * port 1812 Listening on accounting address * port 1813 and here are my tables on the raidus sql database mysql select * from radcheck; ++--++++ | id | username | attribute | op | value | ++--++++ | 1 | tahar| Cleartext-Password | := | 123| | 2 | tba | Cleartext-Password | := | 123456 | ++--++++ mysql
freeradius+sqlippool
Hello I am new in this list I am setting up a freeradius server with mysql and sqlippool but I am facing some problems with sqlippool which cannot deliver an ip address to subscribers. I installed Freeradius 2.1.3 on OpenBSD 4.6 with mysql support here is the output of radiusd -X sqlippool { sql-instance-name = sql lease-duration = 3600 pool-name = allocate-begin = START TRANSACTION allocate-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' allocate-find = SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time 'now'::timestamp(0) ORDER BY (username '%{SQL-User-Name}'), (callingstationid '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE allocate-update = UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}', callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE framedipaddress = '%I' allocate-commit = COMMIT allocate-rollback = ROLLBACK pool-check = SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1 start-begin = START TRANSACTION start-update = UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' start-commit = COMMIT start-rollback = ROLLBACK alive-begin = START TRANSACTION alive-update = UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 seconds'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND framedipaddress = '%{Framed-IP-Address}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}' alive-commit = COMMIT alive-rollback = ROLLBACK stop-begin = START TRANSACTION stop-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}' stop-commit = COMMIT stop-rollback = ROLLBACK on-begin = START TRANSACTION on-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' on-commit = COMMIT on-rollback = ROLLBACK off-begin = START TRANSACTION off-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' off-commit = COMMIT off-rollback = ROLLBACK sqlippool_log_exists = Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_success = Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_clear = Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name}) sqlippool_log_failed = IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_nopool = No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) defaultpool = main_pool } Module: Instantiating attr_filter.accounting_response attr_filter attr_filter.accounting_response { attrsfile = /etc/raddb/attrs.accounting_response key = %{User-Name} } Module: Checking session {...} for more modules to load Module: Checking post-auth {...} for more modules to load } radiusd: Opening IP addresses and Ports listen { type = auth ipaddr = * port = 0 } listen { type = acct ipaddr = * port = 0 } Listening on authentication address * port 1812 Listening on accounting address * port 1813 and here are my tables on the raidus sql database mysql select * from radcheck; ++--++++ | id | username | attribute | op | value | ++--++++ | 1 | tahar| Cleartext-Password | := | 123| | 2 | tba | Cleartext-Password | := | 123456
sqlippool losts Framed-IP-Address
Hi I use rlm_sqlippool with postgress and see strange situation: rlm_sql allocate ip but there is no Framed-IP-Addrees in reply. (radius 2.1.7) radiusd - Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: Status: PGRES_COMMAND_OK Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: query affected rows = 1 Wed Feb 10 11:31:33 2010 : Info: [sqlippool] Allocated IP 95.69.165.245 [f5a5455f] Wed Feb 10 11:31:33 2010 : Info: [sqlippool]expand: COMMIT - COMMIT Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: Status: PGRES_COMMAND_OK Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: query affected rows = 0 Wed Feb 10 11:31:33 2010 : Debug: rlm_sql (sql): Released sql socket id: 2 Wed Feb 10 11:31:33 2010 : Info: [sqlippool]expand: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) - Allocated IP: from main_pool (did cli zeropoint port user art_dz) Wed Feb 10 11:31:33 2010 : Info: Allocated IP: from main_pool (did cli zeropoint port user art_dz) Wed Feb 10 11:31:33 2010 : Info: ++[sqlippool] returns ok And reply packet is: (with out Framed-IP-Address) Acct-Interim-Interval = 400 Service-Type = Framed-User Framed-Protocol = PPP Cisco-AVPair += lcp:interface-config=rate-limit output 2048000 256000 256000 conform-action transmit exceed-action drop Cisco-AVPair += lcp:interface-config=rate-limit input 2048000 256000 256000 conform-action transmit exceed-action drop skipped What I have to check first? Best regards, Max. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool losts Framed-IP-Address
Sorry for all =) I had error in dictionary and some attributes was overriden by MPD-... i.e. NAS-IP-Address After adding BEGIN-VENDOR ... END-VENDOR in old dictionary file, all is OK Best regards Max Mazur. On Wed, 10 Feb 2010, sirmax wrote: Hi I use rlm_sqlippool with postgress and see strange situation: rlm_sql allocate ip but there is no Framed-IP-Addrees in reply. (radius 2.1.7) radiusd - Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: Status: PGRES_COMMAND_OK Wed Feb 10 11:31:33 2010 : Debug: rlm_sql_postgresql: query affected rows = 1 skipped - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: freeradius + sqlippool
Can you send me you configurations for this purpose? Simple example - how do you check check incoming request and what doy send in reply-item. Thanks a lot! Цитирование Roy Kartadinata : /**/Why not put them into groups and assign IP address based on their group? That's how we have ours setup and it worked well during our test run last year.#160;#160;Cheers,#160;Roy Kartadinata#160; - From: freeradius-users-bounces+rkartadinata=pocket@lists.freeradius.org [mailto:freeradius-users-bounces+rkartadinata=pocket@lists.freeradius.org] On Behalf Of Konstantin Chekushin Sent: Thursday, January 14, 2010 7:38 AM To: freeradius-users@lists.freeradius.org Subject: freeradius + sqlippool Good afternoon! I have a question. It is necessary to assign ip-address by RADUIS-server to gprs user. My decision is to use freeradius+sqlippool for this purpose . But there is one problem. ippool name is initially unknown. It should be taken depending on a phone number. I.e. there is one more table in my database, where is a conformity - number (calling station id)=ip pool. (If calling-station-id does not present in the table#160; - RADIUS takes default ippool name.) Is there a good way to realize the similar scheme? Thanks for advices. Links: -- [1] mailto:rkartadin...@pocket.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
freeradius + sqlippool
Good afternoon! I have a question. It is necessary to assign ip-address by RADUIS-server to gprs user. My decision is to use freeradius+sqlippool for this purpose . But there is one problem. ippool name is initially unknown. It should be taken depending on a phone number. I.e. there is one more table in my database, where is a conformity - number (calling station id)=ip pool. (If calling-station-id does not present in the table#160; - RADIUS takes default ippool name.) Is there a good way to realize the similar scheme? Thanks for advices.- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: freeradius + sqlippool
Why not put them into groups and assign IP address based on their group? That's how we have ours setup and it worked well during our test run last year. Cheers, Roy Kartadinata From: freeradius-users-bounces+rkartadinata=pocket@lists.freeradius.org [mailto:freeradius-users-bounces+rkartadinata=pocket@lists.freeradiu s.org] On Behalf Of Konstantin Chekushin Sent: Thursday, January 14, 2010 7:38 AM To: freeradius-users@lists.freeradius.org Subject: freeradius + sqlippool Good afternoon! I have a question. It is necessary to assign ip-address by RADUIS-server to gprs user. My decision is to use freeradius+sqlippool for this purpose . But there is one problem. ippool name is initially unknown. It should be taken depending on a phone number. I.e. there is one more table in my database, where is a conformity - number (calling station id)=ip pool. (If calling-station-id does not present in the table - RADIUS takes default ippool name.) Is there a good way to realize the similar scheme? Thanks for advices. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool
Hello All, I have problems with ip pool. I use sqlippool on my configuration (postgresqlippool.conf included to radiusd.conf and sqlippool added to my default configuration file which in enabled-site directory) But i receive errors that server cannot define ip for client or [sqlippool] No Pool-Name defined. :( Here is my radiusd -X .. Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on command file /var/run/radiusd/radiusd.sock Listening on proxy address * port 1814 Ready to process requests. rad_recv: Access-Request packet from host 10.10.1.1 port 1645, id=118, length=163 Cisco-AVPair = client-mac-address=0030.05e3.e538 Framed-Protocol = PPP User-Name = testuser CHAP-Password = 0x0161a6e63fa662b05f9e996a2bbd95fff7 NAS-Port-Type = Ethernet NAS-Port = 464 NAS-Port-Id = 0/0/0/500 Service-Type = Framed-User NAS-IP-Address = 10.10.1.1 Acct-Session-Id = 01D0 NAS-Identifier = my-cisco-bras +- entering group authorize {...} ++[preprocess] returns ok [chap] Setting 'Auth-Type := CHAP' ++[chap] returns ok [sql] expand: %{User-Name} - testuser [sql] sql_set_user escaped user -- 'testuser' rlm_sql (sql): Reserving sql socket id: 4 [sql] expand: SELECT * FROM check_user_with_mac('%{SQL-User-Name}', '%{Cisco-AVPair[0]}', '%{NAS-Port-Id}') AS foo(id int, UserName character varying, Attribute character varying, Value character varying, Op character varying) - SELECT * FROM check_user_with_mac('testuser', 'client-mac-address=3D0030.05e3.e538', '0/0/0/500') AS foo(id int, UserName character varying, Attribute character varying, Value character varying, Op character varying) rlm_sql_postgresql: query: SELECT * FROM check_user_with_mac('testuser', 'client-mac-address=3D0030.05e3.e538', '0/0/0/500') AS foo(id int, UserName character varying, Attribute character varying, Value character varying, Op character varying) rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 WARNING: Found User-Password == WARNING: Are you sure you don't mean Cleartext-Password? WARNING: See man rlm_pap for more information. [sql] User found in radcheck table [sql] expand: select * from get_user_attributes('%{SQL-User-Name}') order by id - select * from get_user_attributes('testuser') order by id rlm_sql_postgresql: query: select * from get_user_attributes('testuser') order by id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 4 , fields = 5 [sql] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' - SELECT GroupName FROM usergroup WHERE UserName='testuser' rlm_sql_postgresql: query: SELECT GroupName FROM usergroup WHERE UserName='testuser' rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 [sql] expand: SELECT radgroupcheck.id, radgroupcheck.GroupName, radgroupcheck.Attribute, radgroupcheck.Value,radgroupcheck.Op FROM radgroupcheck, usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id - SELECT radgroupcheck.id, radgroupcheck.GroupName, radgroupcheck.Attribute, radgroupcheck.Value,radgroupcheck.Op FROM radgroupcheck, usergroup WHERE usergroup.Username = 'testuser' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id rlm_sql_postgresql: query: SELECT radgroupcheck.id, radgroupcheck.GroupName, radgroupcheck.Attribute, radgroupcheck.Value,radgroupcheck.Op FROM radgroupcheck, usergroup WHERE usergroup.Username = 'testuser' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 [sql] User found in group ActiveUsers [sql] expand: SELECT radgroupreply.id, radgroupreply.GroupName, radgroupreply.Attribute, radgroupreply.Value, radgroupreply.Op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id - SELECT radgroupreply.id, radgroupreply.GroupName, radgroupreply.Attribute, radgroupreply.Value, radgroupreply.Op FROM radgroupreply,usergroup WHERE usergroup.Username = 'testuser' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id rlm_sql_postgresql: query: SELECT radgroupreply.id, radgroupreply.GroupName, radgroupreply.Attribute, radgroupreply.Value, radgroupreply.Op FROM radgroupreply,usergroup WHERE usergroup.Username = 'testuser' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 rlm_sql (sql): Released sql socket id: 4 ++[sql] returns ok Found Auth-Type = CHAP !!! !!! Replacing User-Password in config items with Cleartext-Password
Re: sqlippool
I have problems with ip pool. I use sqlippool on my configuration (postgresqlippool.conf included to radiusd.conf and sqlippool added to my default configuration file which in enabled-site directory) But i receive errors that server cannot define ip for client or [sqlippool] No Pool-Name defined. :( You haven' assigned pool to the user. Pool-Name is a check item (put it in radcheck or radgroupcheck table). Ivan Kalik - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool
Dear all, I'm trying to use rlm_sqlippool with mysql. User can get IP address from pool with Pool-Name attribute but all users get same IP address. How to solve this ? Best regards, Tseveendorj TunamalCom LLC - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool
I'm trying to use rlm_sqlippool with mysql. User can get IP address from pool with Pool-Name attribute but all users get same IP address. How to solve this ? Debug? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool
Hi Ivan Kalik, Sorry, How to debug ? Best regards, Tseveen. Ivan Kalik wrote: I'm trying to use rlm_sqlippool with mysql. User can get IP address from pool with Pool-Name attribute but all users get same IP address. How to solve this ? Debug? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: ippool or sqlippool support for EAP/TLS or TTLS on Wimax
WiMAX can also works in DHCP proxy mode: acquire IP address from ip pool in RADIUS and convert to DHCP message format for the R6 interface. IP address assignment has nothing to do with the EAP method (EAP-TLS) you are using. I make the ip pool assignment working using sqlippool. What is your particular concern regarding ip pool assignment using sqlipppol? Thanks, Jay On Fri, Sep 4, 2009 at 1:51 AM, Alan DeKok al...@deployingradius.comwrote: Victor Tangendjaja wrote: I read from a post back in 2007 that freeRADIUS ippool or sqlippool does not support assigning IP via EAP/TLS. I was wondering if this is still the case? Yes. Because when 802.1X is used, IP address assignment is done via DHCP. Alan mentioned that there would be options that might be part of 2.0 release. Is there any solution for this with the current release? No. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
ippool or sqlippool support for EAP/TLS or TTLS on Wimax
Hi, I read from a post back in 2007 that freeRADIUS ippool or sqlippool does not support assigning IP via EAP/TLS. I was wondering if this is still the case? Alan mentioned that there would be options that might be part of 2.0 release. Is there any solution for this with the current release? Thanks, Victor - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: ippool or sqlippool support for EAP/TLS or TTLS on Wimax
Victor Tangendjaja wrote: I read from a post back in 2007 that freeRADIUS ippool or sqlippool does not support assigning IP via EAP/TLS. I was wondering if this is still the case? Yes. Because when 802.1X is used, IP address assignment is done via DHCP. Alan mentioned that there would be options that might be part of 2.0 release. Is there any solution for this with the current release? No. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - Duplicate IP
Neville wrote: I've facing a problem since rebuild, where every user is being allocated the same IP from the sqlippool, and I'm not sure why this is happening. ... pppd does not pass back Client-IP-Address or Client-Station-Id Calling-Station-Id. table structure for radipool is Yes... we have access to the source code, too. rad_recv: Access-Request packet from host NASIPHERE port 53621, id=117, length=147 Service-Type = Framed-User Framed-Protocol = PPP User-Name = TESTUSER MS-CHAP-Challenge = 0xe325bfbeb22fbbb7a33a21326e5ce18a MS-CHAP2-Response = 0x51009da7f84750dd0f01bed231e11bab1f9a2b9f4dad6844332eaec4aabcc1d8f03911ff654b6a7a8e96 NAS-Identifier = NASIPHERE NAS-Port = 0 OK. So how is the IP pool module supposed assign a unique IP for each user? There's no MAC address in the request (i.e. Calling-Station-Id). The SQL queries (if you read them) use Calling-Station-Id. How are they supposed to work if there's no Calling-Station-Id? Fix your PPPd so that it sends *useful* information. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - Duplicate IP
Neville wrote: I've facing a problem since rebuild, where every user is being allocated the same IP from the sqlippool, and I'm not sure why this is happening. ... pppd does not pass back Client-IP-Address or Client-Station-Id Calling-Station-Id. table structure for radipool is Yes... we have access to the source code, too. rad_recv: Access-Request packet from host NASIPHERE port 53621, id=117, length=147 Service-Type = Framed-User Framed-Protocol = PPP User-Name = TESTUSER MS-CHAP-Challenge = 0xe325bfbeb22fbbb7a33a21326e5ce18a MS-CHAP2-Response = 0x51009da7f84750dd0f01bed231e11bab1f9a2b9f4dad6844332eaec4aabcc1d8f03911ff654b6a7a8e96 NAS-Identifier = NASIPHERE NAS-Port = 0 OK. So how is the IP pool module supposed assign a unique IP for each user? There's no MAC address in the request (i.e. Calling-Station-Id). The SQL queries (if you read them) use Calling-Station-Id. How are they supposed to work if there's no Calling-Station-Id? Fix your PPPd so that it sends *useful* information. Dear Alan, It's not as simple as your making it. Also, I am using %{NAS-Port} and not %{Calling-Station-Id} due to the Lack of Calling-Station-Id. pool-key = %{NAS-Port} # pool-key = %{Calling-Station-Id} $INCLUDE sql/mysql/ippool.conf #$INCLUDE sql/postgresql/ippool.conf if I use ippool module, it works fine so something wrong with the SQL. Please can you confirm in want sections I have to add sqlippool module in order that I can test this again, but given the flakiness of sqlippool I might just stick with ippool module. Thx Nev - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - Duplicate IP
Neville wrote: It's not as simple as your making it. Also, I am using %{NAS-Port} and not %{Calling-Station-Id} due to the Lack of Calling-Station-Id. Why didn't you say that in the first message? Giving out *part* of the information is annoying. pool-key = %{NAS-Port} # pool-key = %{Calling-Station-Id} $INCLUDE sql/mysql/ippool.conf #$INCLUDE sql/postgresql/ippool.conf if I use ippool module, it works fine so something wrong with the SQL. Well, it's not using the allocate-find query, so something is wrong with the configuration. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool - Duplicate IP
Neville wrote: It's not as simple as your making it. Also, I am using %{NAS-Port} and not %{Calling-Station-Id} due to the Lack of Calling-Station-Id. Why didn't you say that in the first message? Giving out *part* of the information is annoying. I Understand, but I was trying to make the message SHORT. pool-key = %{NAS-Port} # pool-key = %{Calling-Station-Id} $INCLUDE sql/mysql/ippool.conf #$INCLUDE sql/postgresql/ippool.conf if I use ippool module, it works fine so something wrong with the SQL. Well, it's not using the allocate-find query, so something is wrong with the configuration. Are you able to provide some pointers please, e.g. where sqlippool should be placed or what other files should I look into or order to track the error down. Thx Nev - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool - Duplicate IP
Hi, I've facing a problem since rebuild, where every user is being allocated the same IP from the sqlippool, and I'm not sure why this is happening. I have a DaloRadius / FreeRadius2.1.6 / Poptop (pptpd) 1.3.4 / ppp 2.4.4.-2 / mysql 5.0.45 pppd does not pass back Client-IP-Address or Client-Station-Id table structure for radipool is ( `id` int(11) unsigned NOT NULL auto_increment, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL default '', `nasipaddress` varchar(15) NOT NULL default '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime default NULL, `username` varchar(64) NOT NULL default '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) 1st Login Ready to process requests. rad_recv: Access-Request packet from host NASIPHERE port 53621, id=117, length=147 Service-Type = Framed-User Framed-Protocol = PPP User-Name = TESTUSER MS-CHAP-Challenge = 0xe325bfbeb22fbbb7a33a21326e5ce18a MS-CHAP2-Response = 0x51009da7f84750dd0f01bed231e11bab1f9a2b9f4dad6844332eaec4aabcc1d8f03911ff654b6a7a8e96 NAS-Identifier = NASIPHERE NAS-Port = 0 +- entering group authorize {...} ++[preprocess] returns ok [auth_log] expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d - /var/log/radius/radacct/NASIPHERE/auth-detail-20090831 [auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/NASIPHERE/auth-detail-20090831 [auth_log] expand: %t - Mon Aug 31 22:47:05 2009 ++[auth_log] returns ok [mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap' ++[mschap] returns ok [sql] expand: %{User-Name} - TESTUSER [sql] sql_set_user escaped user -- 'TESTUSER' rlm_sql (sql): Reserving sql socket id: 3 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id - SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'TESTUSER' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id - SELECT id, username, attribute, value, op FROM radreply WHERE username = 'TESTUSER' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority - SELECT groupname FROM radusergroup WHERE username = 'TESTUSER' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id - SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'USUKTV' ORDER BY id [sql] User found in group USUKTV [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id - SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'USUKTV' ORDER BY id rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop Found Auth-Type = MSCHAP +- entering group MS-CHAP {...} [mschap] Told to do MS-CHAPv2 for TESTUSER with NT-Password [mschap] adding MS-CHAPv2 MPPE keys ++[mschap] returns ok +- entering group session {...} ++[sql] returns noop Login OK: [TESTUSER/via Auth-Type = mschap] (from client VPN1-UK port 0) +- entering group post-auth {...} rlm_sql (sql): Reserving sql socket id: 2 [sqlippool] expand: %{User-Name} - TESTUSER [sqlippool] sql_set_user escaped user -- 'TESTUSER' [sqlippool] expand: START TRANSACTION - START TRANSACTION [sqlippool] expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}' - UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND AND nasipaddress = 'NASIPHERE' [sqlippool] expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time NOW() OR expiry_time IS NULL) ORDER BY (username '%{User-Name}'), (callingstationid '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE - SELECT framedipaddress FROM radippool WHERE pool_name = 'tvpool' AND (expiry_time NOW() OR expiry_time IS NULL) ORDER BY (username 'TESTUSER'), (callingstationid ''), expiry_time LIMIT 1 FOR UPDATE [sqlippool] expand: UPDATE radippool SET nasipaddress = '%{NAS-IP
Ips dont get assigned using sqlippool
Hi, im currently configuring and testing mysqlipool for freeradius. I followed the instructions from the freeradius wiki nad have my radius server running. Im Running the server with the -X Option and whenever a user is connecting I get the following output: Thu Jul 9 14:44:08 2009 : Info: [sqlippool]expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND; - UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND; Thu Jul 9 14:44:08 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1064 received Thu Jul 9 14:44:08 2009 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND;' Im a bit stuck here since I don't understand why this error occurs. I have thre IPs configured in my radipppool table: pool_nameframedipaddress pool 192.168.35.178 pool 192.168.35.179 pool 192.168.35.180 The user with that I'am testing has the following attributes (in table radreply): Service-Type = Framed-User Framed-Protocoll = PPP Fall-Through = Yes And the following in radcheck: Cleatext-Password := testzugang Pool-Name := pool I'm missing something essential I think but I can't see my mistake so far. Anyone who can point me in the correct direction? Mit freundlichen Grüßen Kai Zemke === smartnet Online Service GmbH, Schnackenburgallee 177, 22525 Hamburg === Geschäftsführer: Christian Suding, Claus Masch Ust.IdNr.:DE191136350 Handelsregister HRB 66463 Steuernummer: FA: Hamburg 54/855/01047 Fon: +49 (0) 40 5540-0 Fax: +49 (0) 40 5540-1040 kai.ze...@smartnet.demailto:kai.ze...@smartnet.de Weitere Informationen siehe: http://www.smartnet.dehttp://www.smartnet.de/ === Hinweis: Diese Email kann vertrauliche und/oder rechtlich geschützte Informationen enthalten. Wenn Sie nicht der beabsichtigte Empfänger sind oder diese Email irrtümlich erhalten haben, informieren Sie bitte sofort den Absender telefonisch oder per Email und löschen Sie diese Email aus Ihrem System. Das unerlaubte Kopieren, sowie die unbefugte Weitergabe dieser Email ist nicht gestattet.Wir haften nicht für die Unversehrtheit von Emails, nachdem sie unseren Einfluss- Bereich verlassen haben. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Ips dont get assigned using sqlippool
Zemke, Kai wrote: Thu Jul 9 14:44:08 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1064 received That's a MySQL error. Are you using the standard queries? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Ips dont get assigned using sqlippool
Thu Jul 9 14:44:08 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1064 received Thu Jul 9 14:44:08 2009 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND;' Error 1064 is syntax error. Im a bit stuck here since I don't understand why this error occurs. I have thre IPs configured in my radipppool table: pool_nameframedipaddress pool 192.168.35.178 pool 192.168.35.179 pool 192.168.35.180 Is that the table or are there more fields to it? Did you make the table using ippool.sql? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
AW: Ips dont get assigned using sqlippool
I created the table with the statements that come with the freeradius installation (ippool.sql) and I'm using the standard querries from ippool.conf. I continued to search the mailinglist and discovered an earlyer post where there was described a similiar problem. Concerning to this post i changed the default sql scheme from ippool.sql so that expiry_time now has a default value. I didnt had much time since i had to leave office but my first tests showed that the error did not occur anymore and an Ip address got assigned to my cliennt. Can i have encounter special problems in the future now that i changed the default sql scheme Von: freeradius-users-bounces+kai.zemke=smartnet...@lists.freeradius.org [freeradius-users-bounces+kai.zemke=smartnet...@lists.freeradius.org] im Auftrag von Ivan Kalik [...@kalik.net] Gesendet: Donnerstag, 9. Juli 2009 20:29 An: FreeRadius users mailing list Betreff: Re: Ips dont get assigned using sqlippool Thu Jul 9 14:44:08 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1064 received Thu Jul 9 14:44:08 2009 : Error: sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time = NOW() - INTERVAL 1 SECOND;' Error 1064 is syntax error. Im a bit stuck here since I don't understand why this error occurs. I have thre IPs configured in my radipppool table: pool_nameframedipaddress pool 192.168.35.178 pool 192.168.35.179 pool 192.168.35.180 Is that the table or are there more fields to it? Did you make the table using ippool.sql? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
about sqlippool
Hello, I got it. I defined Pool-Name attribute in wrong place. Tseveen. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
About sqlippool
Hello, Sorry for ask newbie question. I would like to implement sqlippool functionality on FreeRADIUS 2.1.3. I read /usr/local/share/doc/freeradius/rlm_sqlippool there has following note IP-Pool Attribute (Keep in mind that its a **CHECK** item, not reply) I didn't understand. which attribute (IP-Pool or Pool-Name) needed for sqlippool? and I also defined sqlippool in accounting { ...} and post-auth {...} directive. But pool didn't work. You can see more detailed below. Thanks for any help. accounting { # # Create a 'detail'ed log of the packets. # Note that accounting requests which are proxied # are also logged in the detail file. detail # daily # Update the wtmp file # # If you don't use radlast, you can delete this line. #unix # # Log traffic to an SQL database. # # See Accounting queries in sql.conf sql *sqlippool* # # For Simultaneous-Use tracking. # # Due to packet losses in the network, the data here # may be incorrect. There is little we can do about it. radutmp # sradutmp # Return an address to the IP Pool when we see a stop record. # main_pool # # Instead of sending the query to the SQL server, # write it into a log file. # # sql_log # Cisco VoIP specific bulk accounting # pgsql-voip # Filter attributes from the accounting response. attr_filter.accounting_response # # See Autz-Type Status-Server for how this works. # # Acct-Type Status-Server { # # } } and post-auth { # Get an address from the IP Pool. # main_pool # # If you want to have a log of authentication replies, # un-comment the following line, and the 'detail reply_log' # section, above. # reply_log # # After authenticating the user, do another SQL query. # # See Authentication Logging Queries in sql.conf sql *sqlippool* # # Instead of sending the query to the SQL server, # write it into a log file. # # sql_log # # Un-comment the following if you have set # 'edir_account_policy_check = yes' in the ldap module sub-section of # the 'modules' section. # # ldap exec # # Access-Reject packets are sent through the REJECT sub-section of the # post-auth section. # # Add the ldap module name (or instance) if you have set # 'edir_account_policy_check = yes' in the ldap module configuration # Post-Auth-Type REJECT { attr_filter.access_reject } } - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
Hi Ivan, Many thanks for your suggestion. However, (using 2.1.3) my sqlippool.conf file is now: sqlippool { # ## SQL instance to use (from sql.conf) ## # sql-instance-name = sql Pool-Name = %{control:Huntgroup-Name} ## SQL table to use for ippool range and lease info ippool_table = radippool ## IP lease duration. (Leases expire even if Acct Stop packet is lost) lease-duration = 3600 ## Attribute which should be considered unique per NAS ## Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS) ## Using Calling-Station-Id works for NAS that send fixed NAS-Port ## ONLY change this if you know what you are doing! # pool-key = %{NAS-Port} pool-key = %{Calling-Station-Id}-%{control:Huntgroup-Name} # %{request:Huntgroup-Name} $INCLUDE sql/mysql/ippool.conf ## Logging configuration. (Comment out to disable logging) sqlippool_log_exists = Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_success = Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_clear = Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name}) sqlippool_log_failed = IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) sqlippool_log_nopool = No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name} hunt %{Huntgroup-Name} pool %{Pool-Name}) } When running with debugging on, I get: . Wed Feb 18 14:21:03 2009 : Debug: (Loaded rlm_sqlippool, checking if it's valid) Wed Feb 18 14:21:03 2009 : Debug: Module: Linked to module rlm_sqlippool Wed Feb 18 14:21:03 2009 : Debug: Module: Instantiating sqlippool Wed Feb 18 14:21:03 2009 : Debug: sqlippool { Wed Feb 18 14:21:03 2009 : Debug: sql-instance-name = sql Wed Feb 18 14:21:03 2009 : Debug: lease-duration = 3600 Wed Feb 18 14:21:03 2009 : Debug: pool-name = %{control:Huntgroup-Name} Wed Feb 18 14:21:03 2009 : Debug: allocate-begin = START TRANSACTION Wed Feb 18 14:21:03 2009 : Debug: allocate-clear = UPDATE radippool SET expiry_time = NOW() WHERE callingstationid = '%{Calling-Station-Id}' AND pool_name = '%{control:Pool-Name}' Wed Feb 18 14:21:03 2009 : Debug: allocate-find = SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND callingstationid = '%{Calling-Station-Id}' LIMIT 1 FOR UPDATE Wed Feb 18 14:21:03 2009 : Debug: allocate-update = UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', username = '%{User-Name}' WHERE framedipaddress = '%I' Wed Feb 18 14:21:03 2009 : Debug: allocate-commit = COMMIT Wed Feb 18 14:21:03 2009 : Debug: allocate-rollback = ROLLBACK Wed Feb 18 14:21:03 2009 : Debug: pool-check = Wed Feb 18 14:21:03 2009 : Debug: start-begin = START TRANSACTION Wed Feb 18 14:21:03 2009 : Debug: start-update = UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{Calling-Station-Id}-%{control:Huntgroup-Name}' Wed Feb 18 14:21:03 2009 : Debug: start-commit = COMMIT Wed Feb 18 14:21:03 2009 : Debug: start-rollback = ROLLBACK Wed Feb 18 14:21:03 2009 : Debug: alive-begin = START TRANSACTION Wed Feb 18 14:21:03 2009 : Debug: alive-update = UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{Calling-Station-Id}-%{control:Huntgroup-Name}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}' Wed Feb 18 14:21:03 2009 : Debug: alive-commit = COMMIT Wed Feb 18 14:21:03 2009 : Debug: alive-rollback = ROLLBACK Wed Feb 18 14:21:03 2009 : Debug: stop-begin = START TRANSACTION Wed Feb 18 14:21:03 2009 : Debug: stop-clear = UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{Calling-Station-Id}-%{control:Huntgroup-Name}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}' Wed Feb 18 14:21:03 2009 : Debug: stop-commit = COMMIT Wed Feb 18 14:21:03 2009 : Debug: stop-rollback = ROLLBACK Wed Feb 18 14:21:03 2009 : Debug: on-begin = START TRANSACTION Wed Feb 18 14:21:03 2009 : Debug: on-clear = UPDATE radippool SET
Re: Pool-Name woes with sqlippool
Many thanks for your suggestion. However, (using 2.1.3) my sqlippool.conf file is now: sqlippool { # ## SQL instance to use (from sql.conf) ## # sql-instance-name = sql Pool-Name = %{control:Huntgroup-Name} Where did that come from? That should be in the users entry (radcheck or radgroupcheck). Wed Feb 18 14:21:17 2009 : Info: +- entering group post-auth {...} Wed Feb 18 14:21:17 2009 : *Info: [sqlippool] No Pool-Name defined.* You haven't entered that Pool-Name (name of your huntgroup) in the radippool table. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
Hi Ivan, t...@kalik.net wrote: Many thanks for your suggestion. However, (using 2.1.3) my sqlippool.conf file is now: sqlippool { # ## SQL instance to use (from sql.conf) ## # sql-instance-name = sql Pool-Name = %{control:Huntgroup-Name} Where did that come from? That should be in the users entry (radcheck or radgroupcheck). I've tried adding to the radcheck table ( INSERT into `radcheck` SET `id` = 0, `username` = '447', `op` = ':=', `value` = '%{control:Huntgroup-Name}`; ) and then I get ... Wed Feb 18 16:14:34 2009 : Info: [sqlippool]expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND callingstationid = '%{Calling-Station-Id}' LIMIT 1 FOR UPDATE - SELECT framedipaddress FROM radippool WHERE pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' AND callingstationid = '447775777672' LIMIT 1 FOR UPDATE Wed Feb 18 16:14:34 2009 : Debug: rlm_sql_mysql: query: SELECT framedipaddress FROM radippool WHERE pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' AND callingstationid = '447XXX' LIMIT 1 FOR UPDATE Wed Feb 18 16:14:34 2009 : Info: [sqlippool] SQL query did not return any results The same result was obtained when radgroupcheck is used Wed Feb 18 14:21:17 2009 : Info: +- entering group post-auth {...} Wed Feb 18 14:21:17 2009 : *Info: [sqlippool] No Pool-Name defined.* You haven't entered that Pool-Name (name of your huntgroup) in the radippool table. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
I've tried adding to the radcheck table ( INSERT into `radcheck` SET `id` = 0, `username` = '447', `op` = ':=', `value` = '%{control:Huntgroup-Name}`; ) Typo. It should be ' not ` at the end. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
Hi Ivan, t...@kalik.net wrote: I've tried adding to the radcheck table ( INSERT into `radcheck` SET `id` = 0, `username` = '447', `attribute` = 'Pool-Name' `op` = ':=', `value` = '%{control:Huntgroup-Name}`; ) Sorry The result is still the same: Wed Feb 18 16:53:34 2009 : Debug: rlm_sql (sql): Reserving sql socket id: 2 Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: %{Calling-Station-ID} - 4477XX Wed Feb 18 16:53:34 2009 : Info: [sqlippool] sql_set_user escaped user -- '4477XX' Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: START TRANSACTION - START TRANSACTION Wed Feb 18 16:53:34 2009 : Debug: rlm_sql_mysql: query: START TRANSACTION Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: UPDATE radippool SET expiry_time = NOW() WHERE callingstationid = '%{Calling-Station-Id}' AND pool_name = '%{control:Pool-Name}' - UPDATE radippool SET expiry_time = NOW() WHERE callingstationid = '447775777672' AND pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' Wed Feb 18 16:53:34 2009 : Debug: rlm_sql_mysql: query: UPDATE radippool SET expiry_time = NOW() WHERE callingstationid = '4477XX' AND pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND callingstationid = '%{Calling-Station-Id}' LIMIT 1 FOR UPDATE - SELECT framedipaddress FROM radippool WHERE pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' AND callingstationid = ''4477XX'' LIMIT 1 FOR UPDATE Wed Feb 18 16:53:34 2009 : Debug: rlm_sql_mysql: query: SELECT framedipaddress FROM radippool WHERE pool_name = '=25=7Bcontrol:Huntgroup-Name=7D' AND callingstationid = ''4477XX'' LIMIT 1 FOR UPDATE Wed Feb 18 16:53:34 2009 : Info: [sqlippool] SQL query did not return any results Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: COMMIT - COMMIT Wed Feb 18 16:53:34 2009 : Debug: rlm_sql_mysql: query: COMMIT Wed Feb 18 16:53:34 2009 : Debug: rlm_sql (sql): Released sql socket id: 2 Wed Feb 18 16:53:34 2009 : Info: [sqlippool] IP address could not be allocated. Wed Feb 18 16:53:34 2009 : Info: [sqlippool]expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) - IP Allocation FAILED from %{control:Huntgroup-Name} (did eseye.co.uk cli 447775777672 port 12345 user searthrowl) Wed Feb 18 16:53:34 2009 : Info: IP Allocation FAILED from %{control:Huntgroup-Name} (did eseye.co.uk cli 4477XX port 12345 user searthrowl) Wed Feb 18 16:53:34 2009 : Info: ++[sqlippool] returns noop It looks as if the MySQL saved value is somehow transformed when it's just been read, but when output during debug/log, is shown correctly; albeit not transformed to the appropriate variable. BTW I missed `attribute` = 'Pool-Name' earlier, the correct SQL is: INSERT into `radcheck` SET `id` = 0, `username` = '4477XX', `attribute` = 'Pool-Name', `op` = ':=', `value` = '%{control:Huntgroup-Name}'; Is this a bug in expand? Typo. It should be ' not ` at the end. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
I've tried adding to the radcheck table ( INSERT into `radcheck` SET `id` = 0, `username` = '447', `attribute` = 'Pool-Name' `op` = ':=', `value` = '%{control:Huntgroup-Name}`; ) Sorry The result is still the same: OK. sql safe characters in play. Then use unlang: update control { Pool-Name = '%{control:Hungroup-Name}' } Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Pool-Name woes with sqlippool
What I would love to do is set up Huntgroups (OK so that bit works too!) and then in the sqlippool.conf just assign pool-name = %{Huntgroup-Name} This doesn't work, and all I get is pool-name is undefined. Does anyone have any ideas? %{control:Huntgroup-Name} Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Pool-Name woes with sqlippool
Hi all, I have, what I think is a simple problem, with a simple answer that doesn't work. The environment I am supply authentication requests to, require that I respond with a fixed IP address in each NAS domain. eg NAS-IP-Addresses 192.168.1.0/28 should get an IP from the pool 10.0.0.0/16 While NAS-IP-Addresses 192.168.1.128/28 should get an IP from the 10.1.0.0/16 Users have no control from which of NASs they come into. All the normal user name/password stuff works fine (many thanks!) What I would love to do is set up Huntgroups (OK so that bit works too!) and then in the sqlippool.conf just assign pool-name = %{Huntgroup-Name} This doesn't work, and all I get is pool-name is undefined. Does anyone have any ideas? Kind regards Simon - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sqlippool : undefined symbol: rlm_sql_query
Hi, I want to get freeradius running with the sqlippool module and mysql backend on Ubuntu 8.04 LTS Server. Everything works so far, but it seems still impossible to enable the sqlippool on Debian based systems. At first I tried the freeradius 1.1.7-1build4 packages supplied by the distribution, but then I always received the following error: freeradius: symbol lookup error: /usr/lib/freeradius/rlm_sqlippool.so: undefined symbol: sql_get_socket After that I gave the backport packages of version 2.1.0 a try, because I have read somewhere that this error should be solved since version 2.0.4. But unfortunately I had the same result with it. freeradius_2.1.0+dfsg-0ubuntu2~hardy1_i386.deb freeradius-common_2.1.0+dfsg-0ubuntu2~hardy1_all.deb freeradius-mysql_2.1.0+dfsg-0ubuntu2~hardy1_i386.deb freeradius-utils_2.1.0+dfsg-0ubuntu2~hardy1_i386.deb libfreeradius2_2.1.0+dfsg-0ubuntu2~hardy1_i386.deb Now I tried to compile freeradius version 2.1.3 on this server and to build a fresh deb package with success, but using sqlippool now results into a slightly different error message. freeradius: symbol lookup error: /usr/lib/freeradius/rlm_sqlippool-2.1.3.so: undefined symbol: rlm_sql_query In this message a patch to this problem is mentioned. http://lists.cistron.nl/pipermail/freeradius-devel/2009-January/012736.html And here someone relinked rlmsqlippool to rlm_sql to get rid of this issue. http://www.nabble.com/sqlippool-symbol-error-td20331823.html What should I do to get sqlippool working? I don't know how to relink the it. Thanks a lot. Sebastian --- There seems to be a syntax error at line 24 in /usr/local/etc/raddb/sql/mysql/ippool.conf. At the end of the line \ is missing. including configuration file /usr/local/etc/raddb/sql/mysql/ippool.conf /usr/local/etc/raddb/sql/mysql/ippool.conf[24]: Expecting section start brace '{' after AND nasipaddress Errors reading /usr/local/etc/raddb/radiusd.conf WHERE expiry_time = NOW() - INTERVAL 1 SECOND \ AND nasipaddress = '%{Nas-IP-Address}' - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: sqlippool : undefined symbol: rlm_sql_query
Sebastian Krieger wrote: I want to get freeradius running with the sqlippool module and mysql backend on Ubuntu 8.04 LTS Server. Everything works so far, but it seems still impossible to enable the sqlippool on Debian based systems. This should be fixed in 2.1.4 when it's released. Or, you can download the stable version. See git.freeradius.org for instructions. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: IP-Assignment with sqlippool based on nas-ip-address
Original-Nachricht Datum: Fri, 30 Jan 2009 11:51:20 +0100 Von: t...@kalik.net An: FreeRadius users mailing list freeradius-users@lists.freeradius.org Betreff: Re: IP-Assignment with sqlippool based on nas-ip-address Now, the behaviour of the server changed in the way, that the freeradius reserves only one ip-address per user. if the same user logs in again on the same nas (without accounting-stop-packet before), the old ip-address is freed and the user receives a new one. That should happen only if IP allocation has expired (see lease-duration in sqlippool.conf). There is another allocate-find query that issues random IPs. Hmmm, maybe there is another problem in my config. I tried two requests within ten seconds. Attached you'll find the debug. During the second request the first ip-address is freed and can be used again. The lease-duration has the standard value of 3600, so this can't be the reason. This is the table radippool after the second request: +---+-+--+-+--+--+ | pool_name | framedipaddress | nasipaddress | expiry_time | username | pool_key | +---+-+--+-+--+--+ | poolUK| 10.10.10.10 | 10.98.6.95 | 2009-02-02 10:14:32 | peter2 | | | poolUK| 10.10.10.11 | | 2009-02-02 09:14:31 | | 0| +---+-+--+-+--+--+ debug rad_recv: Access-Request packet from host 10.98.6.95 port 3099, id=194, length=46 User-Name = peter2 User-Password = peter2 +- entering group authorize {...} ++[preprocess] returns ok [auth_log] expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d - /var/log/radius/radacct/10.98.6.95/auth-detail-20090202 [auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/10.98.6.95/auth-detail-20090202 [auth_log] expand: %t - Mon Feb 2 09:13:45 2009 ++[auth_log] returns ok ++[chap] returns noop ++[mschap] returns noop [suffix] No '@' in User-Name = peter2, looking up realm NULL [suffix] No such realm NULL ++[suffix] returns noop [eap] No EAP-Message, not doing EAP ++[eap] returns noop ++[unix] returns notfound [files] users: Matched entry DEFAULT at line 183 ++[files] returns ok [sql] expand: %{User-Name} - peter2 [sql] sql_set_user escaped user -- 'peter2' rlm_sql (sql): Reserving sql socket id: 0 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id - SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'peter2' ORDER BY id rlm_sql_mysql: query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'peter2' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id - SELECT id, username, attribute, value, op FROM radreply WHERE username = 'peter2' ORDER BY id rlm_sql_mysql: query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'peter2' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority - SELECT groupname FROM radusergroup WHERE username = 'peter2' ORDER BY priority rlm_sql_mysql: query: SELECT groupname FROM radusergroup WHERE username = 'peter2' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id - SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'UK' ORDER BY id rlm_sql_mysql: query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'UK' ORDER BY id [sql] User found in group UK [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id - SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'UK' ORDER BY id rlm_sql_mysql: query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'UK' ORDER BY id rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering
Re: IP-Assignment with sqlippool based on nas-ip-address
I'm afriad, but this won't work in my environment. I will need a different subnetmask. Can you explain why do you think 255.255.255.255 netmask won't work for you. Do you know how that netmask works? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html