Removing stale expired accounts from SQL
Hi, I am running into a problem where a user's account is expired (using the Expiration module), however their radreply entry still exists in mysql. This becomes a problem when the user signs up again with the same MAC address because they will be rejected since their MAC address already exists in radreply as expired. I know that a cron task can be used to clean up the database and wipe out all expired accounts, but is there a better way. I'm afraid that even a 5 minute interval won't be good enough if the user signs back up immediately after their account expires. Should I be doing this with unlang? I was thinking of using unlang to delete all of the associated radreply entries in modules/expiration. Has anyone experienced this problem before, and what solution do you think is best? Thanks, Ethan
2013/6/4 Ethan Hayon <ehayon@gmail.com>:
Should I be doing this with unlang? I was thinking of using unlang to delete all of the associated radreply entries in modules/expiration.
Has anyone experienced this problem before, and what solution do you think is best?
You can do with unlang. Put the following in the authorize section, before "sql": if(User-Name){ "%{sql:UPDATE radreply SET zzzz WHERE yyyy}" } -- Roney Eduardo
Hi, Thanks for the response. I only want to run the SQL query if the user's account is already expired. Wouldn't I need to put it in here? expiration { # # The Reply-Message which will be sent back in case the # account has expired. Dynamic substitution is supported # reply-message = "Password Has Expired\r\n" # ****** DELETE THE RADREPLY ENTRIES ********** #reply-message = "Your account has expired, %{User-Name}\r\n" } On Tue, Jun 4, 2013 at 4:06 PM, Rôney Eduardo <roneyeduardosantos@gmail.com>wrote:
2013/6/4 Ethan Hayon <ehayon@gmail.com>:
Should I be doing this with unlang? I was thinking of using unlang to
delete
all of the associated radreply entries in modules/expiration.
Has anyone experienced this problem before, and what solution do you think is best?
You can do with unlang. Put the following in the authorize section, before "sql":
if(User-Name){ "%{sql:UPDATE radreply SET zzzz WHERE yyyy}" }
-- Roney Eduardo - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Ethan Hayon wrote:
Hi, Thanks for the response.
I only want to run the SQL query if the user's account is already expired. Wouldn't I need to put it in here?
expiration {
No. That is a MODULE configuration. The entries it takes are documented in that file. You can't put SQL statements here. The earlier response said:
You can do with unlang. Put the following in the authorize section, before "sql":
if(User-Name){ "%{sql:UPDATE radreply SET zzzz WHERE yyyy}" }
What part of that is unclear? The "expiration" module isn't the "authorize" section. So you're either not reading the instructions, or you're ignoring them. Alan DeKok.
2013/6/4 Ethan Hayon <ehayon@gmail.com>:
Hi, Thanks for the response.
I only want to run the SQL query if the user's account is already expired. Wouldn't I need to put it in here?
No, You can adapt the instruction I gave you to delete the entries based on the attributes you want to check for a "stale expired account". You could do, for instance: if(User-Name){ "%{sql:DELETE FROM radreply WHERE username='%{User-Name}' AND attribute='%{WhatEverYouUse} AND value='%{Calling-Station-Id}' }" } -- Roney Eduardo
Ah, now I understand! So in my query I would delete the entries if the current date is > expiration date That makes sense! Thanks so much, Ethan On Jun 4, 2013, at 5:41 PM, Rôney Eduardo <roneyeduardosantos@gmail.com> wrote: 2013/6/4 Ethan Hayon <ehayon@gmail.com>: Hi, Thanks for the response. I only want to run the SQL query if the user's account is already expired. Wouldn't I need to put it in here? No, You can adapt the instruction I gave you to delete the entries based on the attributes you want to check for a "stale expired account". You could do, for instance: if(User-Name){ "%{sql:DELETE FROM radreply WHERE username='%{User-Name}' AND attribute='%{WhatEverYouUse} AND value='%{Calling-Station-Id}' }" } -- Roney Eduardo - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html On Tue, Jun 4, 2013 at 5:41 PM, Rôney Eduardo <roneyeduardosantos@gmail.com>wrote:
2013/6/4 Ethan Hayon <ehayon@gmail.com>:
Hi, Thanks for the response.
I only want to run the SQL query if the user's account is already expired. Wouldn't I need to put it in here?
No,
You can adapt the instruction I gave you to delete the entries based on the attributes you want to check for a "stale expired account".
You could do, for instance:
if(User-Name){ "%{sql:DELETE FROM radreply WHERE username='%{User-Name}' AND attribute='%{WhatEverYouUse} AND value='%{Calling-Station-Id}' }" }
-- Roney Eduardo - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (3)
-
Alan DeKok -
Ethan Hayon -
Rôney Eduardo