Dave <dave@optionsdsl.ca> said:
I use the sqlippool setup for handling IP pools, and it works well, except I want to rid of the expiry time, (maximum timeout=0). right now its setting for 24 hours, and then it cleans itself out, and then freeradius starts handing out already assigned/used IP addresses. Im not sure where to put the maximum timeout=0 value when using sqlippool.
That shouldn't happen, regardless of the expiry time. The expiry_time value in the radippool entries is derived from the 'lease-duraction' set in sqlippool.conf. But the expiry_time should only affect clearing unique sessions which have gotten "stuck" (like for lost 'stop' packets). It shouldn't just start handing out in-use IP's based on expiry time. What do you have $pool-key set to in sqlippool.conf? And are you sure whatever value you are using (usually either NAS-Port or Calling-Station-Id) is a unique value from the NAS? What flavor of db do you have - postgres or mysql? They use different configs, and depending where you got the query file from, you may have an earlier broken version (especially if you are using MySQL). What version of freeradius are you running? -- hugh
Hugh Messenger wrote:
Dave <dave@optionsdsl.ca> said:
I use the sqlippool setup for handling IP pools, and it works well, except I want to rid of the expiry time, (maximum timeout=0). right now its setting for 24 hours, and then it cleans itself out, and then freeradius starts handing out already assigned/used IP addresses. Im not sure where to put the maximum timeout=0 value when using sqlippool.
That shouldn't happen, regardless of the expiry time. The expiry_time value in the radippool entries is derived from the 'lease-duraction' set in sqlippool.conf. But the expiry_time should only affect clearing unique sessions which have gotten "stuck" (like for lost 'stop' packets). It shouldn't just start handing out in-use IP's based on expiry time.
What do you have $pool-key set to in sqlippool.conf? And are you sure whatever value you are using (usually either NAS-Port or Calling-Station-Id) is a unique value from the NAS?
I have pool-key = "%{Calling-Station-Id}", which I just realized is not always unique, (NAS is returning MAC address for Calling station ID, which if passing thru one of my bridge devices always returns the MAC address of the ethernet bridge)
What flavor of db do you have - postgres or mysql? They use different configs, and depending where you got the query file from, you may have an earlier broken version (especially if you are using MySQL).
What version of freeradius are you running?
Im using mysql, and I believe I have a working version of sqlippool.conf, Ill paste it here. Freeradius 1.1.6 sqlippool.conf ******************************************************************* sqlippool { ## SQL instance to use (from sql.conf) sql-instance-name = "sql" ## Table to keep ippool info ippool_table = "radippool" ## lease_duration. fix for lost acc-stop packets lease-duration = 3600 ## Attribute which should be considered unique per NAS ## Using NAS-Port gives behaviour similar to rlm_ippool. Calling-Station-Id is for NAS that send fixed NAS-Port # pool-key = "%{NAS-Port}" pool-key = "%{Calling-Station-Id}" ## Logging configuration. 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 %{check: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 %{check: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})" # ## This series of queries allocates an IP address # allocate-clear = "UPDATE ${ippool_table} \ # SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \ # expiry_time = '0000-00-00 00:00:00' \ # WHERE pool_key = '${pool-key}'" ## This will clear all expired leases for lost acc-stop packets allocate-clear = "UPDATE radippool \ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \ expiry_time = '0000-00-00 00:00:00' \ WHERE expiry_time <= NOW() - INTERVAL 1 SECOND" # ## The ORDER BY clause of this query tries to allocate the same IP-address # ## which user had last session... # allocate-find = "SELECT framedipaddress FROM ${ippool_table} \ # WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < NOW() \ # ORDER BY (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \ # LIMIT 1 \ # FOR UPDATE" ## If you prefer to allocate a random IP address every time, use this query instead allocate-find = "SELECT framedipaddress FROM ${ippool_table} \ WHERE pool_name = '%{check:Pool-Name}' AND expiry_time = '0000-00-00 00:00:00' \ ORDER BY RAND() \ LIMIT 1 \ FOR UPDATE" ## If an IP could not be allocated, check to see if the pool exists or not ## This allows the module to differentiate between a full pool and no pool ## Note: If you are not running redundant pool modules this query may be commented ## out to save running this query every time an ip is not allocated. pool-check = "SELECT id FROM ${ippool_table} WHERE pool_name='%{check:Pool-Name}' LIMIT 1" allocate-update = "UPDATE ${ippool_table} \ SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \ callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \ expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ WHERE framedipaddress = '%I'" ## This series of queries frees an IP number when an accounting ## START record arrives start-update = "UPDATE ${ippool_table} \ SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'" ## This series of queries frees an IP number when an accounting ## STOP record arrives stop-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \ expiry_time = '0000-00-00 00:00:00' \ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" ## This series of queries frees an IP number when an accounting ## ALIVE record arrives alive-update = "UPDATE ${ippool_table} \ SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" ## This series of queries frees the IP numbers allocate to a ## NAS when an accounting ON record arrives on-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \ expiry_time = '0000-00-00 00:00:00' \ WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" ## This series of queries frees the IP numbers allocate to a ## NAS when an accounting OFF record arrives off-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \ expiry_time = '0000-00-00 00:00:00' \ WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" } ************************************************************************
On Tue 03 Jul 2007, Dave wrote:
Hugh Messenger wrote:
Dave <dave@optionsdsl.ca> said:
I use the sqlippool setup for handling IP pools, and it works well, except I want to rid of the expiry time, (maximum timeout=0). right now its setting for 24 hours, and then it cleans itself out, and then freeradius starts handing out already assigned/used IP addresses. Im not sure where to put the maximum timeout=0 value when using sqlippool.
That shouldn't happen, regardless of the expiry time. The expiry_time value in the radippool entries is derived from the 'lease-duraction' set in sqlippool.conf. But the expiry_time should only affect clearing unique sessions which have gotten "stuck" (like for lost 'stop' packets). It shouldn't just start handing out in-use IP's based on expiry time.
What do you have $pool-key set to in sqlippool.conf? And are you sure whatever value you are using (usually either NAS-Port or Calling-Station-Id) is a unique value from the NAS?
I have pool-key = "%{Calling-Station-Id}", which I just realized is not always unique, (NAS is returning MAC address for Calling station ID, which if passing thru one of my bridge devices always returns the MAC address of the ethernet bridge)
Yep. That would cause havoc. Use pool-key = "%{NAS-Port}" unless you have a very good reason not to!
What flavor of db do you have - postgres or mysql? They use different configs, and depending where you got the query file from, you may have an earlier broken version (especially if you are using MySQL).
What version of freeradius are you running?
Im using mysql, and I believe I have a working version of sqlippool.conf, Ill paste it here. Freeradius 1.1.6 -snip-
Hugh recently spent quite some effort sending and testing patches for rlm_sqlippool with MySQL. They are in cvs head as of a few days ago. As always, I recommend sqlippool users run 2.0preX or cvs head as we have made a lot of changed since 1.1.x which have not been backported. Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database, again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..? I can make an attempt at upgrading to 2.0 but Im concerned with downtime if I cant get the new version up quick enough, how much do the configuration files need to be changed for the new version? Peter Nixon wrote:
On Tue 03 Jul 2007, Dave wrote:
Hugh Messenger wrote:
Dave <dave@optionsdsl.ca> said:
I use the sqlippool setup for handling IP pools, and it works well, except I want to rid of the expiry time, (maximum timeout=0). right now its setting for 24 hours, and then it cleans itself out, and then freeradius starts handing out already assigned/used IP addresses. Im not sure where to put the maximum timeout=0 value when using sqlippool.
That shouldn't happen, regardless of the expiry time. The expiry_time value in the radippool entries is derived from the 'lease-duraction' set in sqlippool.conf. But the expiry_time should only affect clearing unique sessions which have gotten "stuck" (like for lost 'stop' packets). It shouldn't just start handing out in-use IP's based on expiry time.
What do you have $pool-key set to in sqlippool.conf? And are you sure whatever value you are using (usually either NAS-Port or Calling-Station-Id) is a unique value from the NAS?
I have pool-key = "%{Calling-Station-Id}", which I just realized is not always unique, (NAS is returning MAC address for Calling station ID, which if passing thru one of my bridge devices always returns the MAC address of the ethernet bridge)
Yep. That would cause havoc. Use pool-key = "%{NAS-Port}" unless you have a very good reason not to!
What flavor of db do you have - postgres or mysql? They use different configs, and depending where you got the query file from, you may have an earlier broken version (especially if you are using MySQL).
What version of freeradius are you running?
Im using mysql, and I believe I have a working version of sqlippool.conf, Ill paste it here. Freeradius 1.1.6
-snip-
Hugh recently spent quite some effort sending and testing patches for rlm_sqlippool with MySQL. They are in cvs head as of a few days ago. As always, I recommend sqlippool users run 2.0preX or cvs head as we have made a lot of changed since 1.1.x which have not been backported.
Cheers
Dave wrote:
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database,
That would seem to fit the 24-hour expiry time you set.
again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..?
No. The leases are set to expire after 24 hours, as you said. If you don't want the leases to expire, edit the SQL queries so that they don't set or look for an expiry field. Alan DeKok.
Alan DeKok wrote:
Dave wrote:
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database,
That would seem to fit the 24-hour expiry time you set.
again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..?
No. The leases are set to expire after 24 hours, as you said.
If you don't want the leases to expire, edit the SQL queries so that they don't set or look for an expiry field.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Ok I will look into that, and anyone else reading, I would love other sqlippool.conf files that work for you :) Im new to using this module.
On Sun 08 Jul 2007, Dave wrote:
Alan DeKok wrote:
Dave wrote:
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database,
That would seem to fit the 24-hour expiry time you set.
again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..?
No. The leases are set to expire after 24 hours, as you said.
If you don't want the leases to expire, edit the SQL queries so that they don't set or look for an expiry field.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Ok I will look into that, and anyone else reading, I would love other sqlippool.conf files that work for you :) Im new to using this module.
Are you receiving accounting packets from your NAS? -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
On Behalf Of Dave said:
Yes accounting is working well from the NAS
Are you sure the NAS is sending 'interim update' accounting packets, not just start/stop? Here's my understanding of how it works (I'm sure Peter will correct me if I'm wrong!): On an access request, sqlippool will first check to see if this looks like a 'lost stop' case (allocate-clear) by checking to see if there are any assigned IP's in the pool with the same 'pool-key' (NAS-Port in a dialup context) as the request. If so, free up that IP. Then it looks for an IP to assign (allocate-find), by checking for a free or expired IP in the pool, allocates it (allocate-update) and sets the expiry_time to "now + lease-duration". On an accounting 'stop', it frees up the IP (stop-clear). On an accounting 'update', it extends the expiry_time by 'lease-duration' seconds (alive-update). There's a little more to it than that (like accounting on/off), but that's the basic life cycle of an IP assignment. So ... if your NAS isn't sending accounting updates, then it will start re-assigning IP's after the initial expiry_time (lease-duration). If your NAS doesn't implement accounting updates, you may have to set session timeouts to less than your lease-duration. -- hugh
Hugh Messenger wrote:
On Behalf Of Dave said:
Yes accounting is working well from the NAS
Are you sure the NAS is sending 'interim update' accounting packets, not just start/stop?
My NAS is currently NOT sending interm updates, but there is an option to use that, just wasn't sure what it did, or how it would apply to me, but it makes sense, that it "extends" the lease time, do all NAS's send interim updates? on the DSL side of my operation I don't see any interim updates until the user logs off (or lost carrier) (this is a proxied operation to me) I don't have control of that NAS, only my wireless NAS
Here's my understanding of how it works (I'm sure Peter will correct me if I'm wrong!):
On an access request, sqlippool will first check to see if this looks like a 'lost stop' case (allocate-clear) by checking to see if there are any assigned IP's in the pool with the same 'pool-key' (NAS-Port in a dialup context) as the request. If so, free up that IP.
Then it looks for an IP to assign (allocate-find), by checking for a free or expired IP in the pool, allocates it (allocate-update) and sets the expiry_time to "now + lease-duration".
On an accounting 'stop', it frees up the IP (stop-clear).
On an accounting 'update', it extends the expiry_time by 'lease-duration' seconds (alive-update).
There's a little more to it than that (like accounting on/off), but that's the basic life cycle of an IP assignment.
So ... if your NAS isn't sending accounting updates, then it will start re-assigning IP's after the initial expiry_time (lease-duration). If your NAS doesn't implement accounting updates, you may have to set session timeouts to less than your lease-duration.
-- hugh
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Tue 10 Jul 2007, Dave wrote:
Hugh Messenger wrote:
On Behalf Of Dave said:
Yes accounting is working well from the NAS
Are you sure the NAS is sending 'interim update' accounting packets, not just start/stop?
My NAS is currently NOT sending interm updates, but there is an option to use that, just wasn't sure what it did, or how it would apply to me, but it makes sense, that it "extends" the lease time, do all NAS's send interim updates? on the DSL side of my operation I don't see any interim updates until the user logs off (or lost carrier) (this is a proxied operation to me) I don't have control of that NAS, only my wireless NAS
Then we have found the problem. Basically you need to set the expiry time greater then the time in between in interim accounting updates. If you don't get interim accounting updates set the expiry time to larger than your maximim possible session length. Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
To update, turning on interim updates on my NAS, fixed my problem... Thanks for all your help. Peter Nixon wrote:
On Tue 10 Jul 2007, Dave wrote:
My NAS is currently NOT sending interm updates, but there is an option to use that, just wasn't sure what it did, or how it would apply to me, but it makes sense, that it "extends" the lease time, do all NAS's send interim updates? on the DSL side of my operation I don't see any interim updates until the user logs off (or lost carrier) (this is a proxied operation to me) I don't have control of that NAS, only my wireless NAS
Then we have found the problem. Basically you need to set the expiry time greater then the time in between in interim accounting updates. If you don't get interim accounting updates set the expiry time to larger than your maximim possible session length.
Cheers
You are welcome. I suppose we should update the docs/wiki to make this clearer.. -Peter On Wed 11 Jul 2007, Dave wrote:
To update, turning on interim updates on my NAS, fixed my problem... Thanks for all your help.
Peter Nixon wrote:
On Tue 10 Jul 2007, Dave wrote:
My NAS is currently NOT sending interm updates, but there is an option to use that, just wasn't sure what it did, or how it would apply to me, but it makes sense, that it "extends" the lease time, do all NAS's send interim updates? on the DSL side of my operation I don't see any interim updates until the user logs off (or lost carrier) (this is a proxied operation to me) I don't have control of that NAS, only my wireless NAS
Then we have found the problem. Basically you need to set the expiry time greater then the time in between in interim accounting updates. If you don't get interim accounting updates set the expiry time to larger than your maximim possible session length.
-- Peter Nixon http://peternixon.net/
On Mon 09 Jul 2007, Hugh Messenger wrote:
On Behalf Of Dave said:
Yes accounting is working well from the NAS
Are you sure the NAS is sending 'interim update' accounting packets, not just start/stop?
Here's my understanding of how it works (I'm sure Peter will correct me if I'm wrong!):
On an access request, sqlippool will first check to see if this looks like a 'lost stop' case (allocate-clear) by checking to see if there are any assigned IP's in the pool with the same 'pool-key' (NAS-Port in a dialup context) as the request. If so, free up that IP.
Then it looks for an IP to assign (allocate-find), by checking for a free or expired IP in the pool, allocates it (allocate-update) and sets the expiry_time to "now + lease-duration".
On an accounting 'stop', it frees up the IP (stop-clear).
On an accounting 'update', it extends the expiry_time by 'lease-duration' seconds (alive-update).
There's a little more to it than that (like accounting on/off), but that's the basic life cycle of an IP assignment.
So ... if your NAS isn't sending accounting updates, then it will start re-assigning IP's after the initial expiry_time (lease-duration). If your NAS doesn't implement accounting updates, you may have to set session timeouts to less than your lease-duration.
I couldn't have summarised it any better :-) -- Peter Nixon http://peternixon.net/
participants (4)
-
Alan DeKok -
Dave -
Hugh Messenger -
Peter Nixon