Hi, I'm afraid I'm currently not seeing the wood for the trees, please help me out. ;-) I'm using stored procedures in MySQL to query for check and reply items for users. I don't need (or want) user groups so there's always a positive Fall-Through attribute returned. There are quite a few possibilities why a user can get rejected: Wrong login, banned from this location, generally blocked, session time/ timespan exceeded and so on... As a result of the information gathered in these stored procedures I know whether the user may connect or not. My question: What attributes do I have to return from MySQL to reject this user??? Am I getting my wires crossed? Thanks a lot! JB
JB wrote:
Hi,
I'm afraid I'm currently not seeing the wood for the trees, please help me out. ;-)
I'm using stored procedures in MySQL to query for check and reply items for users. I don't need (or want) user groups so there's always a positive Fall-Through attribute returned.
Be aware that unless you are using FreeRadius 2.0, the Fall-Through attribute does not affect SQL group processing; see here: http://marc.info/?l=freeradius-users&m=119010719300080&w=2
There are quite a few possibilities why a user can get rejected: Wrong login, banned from this location, generally blocked, session time/timespan exceeded and so on... As a result of the information gathered in these stored procedures I know whether the user may connect or not. My question: What attributes do I have to return from MySQL to reject this user??? Am I getting my wires crossed?
Return: attr = 'Auth-Type' op = ':=' value = 'Reject'
Thanks a lot! JB
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Phil Mayers (07.02.2008 19:27):
JB wrote:
Hi, I'm afraid I'm currently not seeing the wood for the trees, please help me out. ;-) I'm using stored procedures in MySQL to query for check and reply items for users. I don't need (or want) user groups so there's always a positive Fall-Through attribute returned.
Be aware that unless you are using FreeRadius 2.0, the Fall-Through attribute does not affect SQL group processing; see here: http://marc.info/?l=freeradius-users&m=119010719300080&w=2
Yes, I'm using 2.0
There are quite a few possibilities why a user can get rejected: Wrong login, banned from this location, generally blocked, session time/timespan exceeded and so on... As a result of the information gathered in these stored procedures I know whether the user may connect or not. My question: What attributes do I have to return from MySQL to reject this user??? Am I getting my wires crossed?
Return:
attr = 'Auth-Type' op = ':=' value = 'Reject'
Of course! How embarrassing. ;-) I actually tried that before but during the reply-items-query which has no effect. Returning Auth-Type := Reject from the check-items- query does the trick. Makes sense, doesn't it? Thanks a lot! JB
Return:
attr = 'Auth-Type' op = ':=' value = 'Reject'
Of course! How embarrassing. ;-) I actually tried that before but during the reply-items-query which has no effect. Returning Auth-Type := Reject from the check-items- query does the trick. Makes sense, doesn't it?
Ok, now I'm returning Auth-Type := Reject from my check-items-query and I hoped to be able to send a little more in depth information along the way in the Reply-Message attribute, but unfortunately this info gets lost. It seems that I have to fill this attribute in the reply-items-query. Does this mean the reply-items-query has to trigger the same functions as the check-items-query again to find out what the reason for the reject was? Or do I have to fill a temporary table with the reply message in the check-items-query which gets then returned in the reply- items-query? I guess there is a much better way without unnecessary database load. What's the common practice here? Is there a control attribute which can be used for internal communication among these queries? Thank you! JB
JB wrote:
Return:
attr = 'Auth-Type' op = ':=' value = 'Reject'
Of course! How embarrassing. ;-) I actually tried that before but during the reply-items-query which has no effect. Returning Auth-Type := Reject from the check-items-query does the trick. Makes sense, doesn't it?
Ok, now I'm returning Auth-Type := Reject from my check-items-query and I hoped to be able to send a little more in depth information along the way in the Reply-Message attribute, but unfortunately this info gets lost. It seems that I have to fill this attribute in the reply-items-query.
Does this mean the reply-items-query has to trigger the same functions as the check-items-query again to find out what the reason for the reject was? Or do I have to fill a temporary table with the reply message in the check-items-query which gets then returned in the reply-items-query?
Hmm. I guess you're doing something like: authorize_check_query = "select myproc('%{SQL-User-Name}','...etc...')" ...and are trying to avoid re-calling the same (or another) function in the reply query. What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section: i.e. return 2 rows from the stored proc: attr | op | value -----------+----+-------------- My-Reply | := | some message Auth-Type | := | Reject in /etc/raddb/dictionary: ATTRIBUTE My-Reply 3000 string and have: authorize { sql } post-auth { Post-Auth-Type Reject { if (control:My-Reply) { update reply { Reply-Message = "%{control:My-Reply}" } } } } ...be aware however that almost *nothing* pays any attention to Reply-Message :o(
Phil Mayers (08.02.2008 12:03):
Ok, now I'm returning Auth-Type := Reject from my check-items-query and I hoped to be able to send a little more in depth information along the way in the Reply-Message attribute, but unfortunately this info gets lost. It seems that I have to fill this attribute in the reply-items-query. Does this mean the reply-items-query has to trigger the same functions as the check-items-query again to find out what the reason for the reject was? Or do I have to fill a temporary table with the reply message in the check-items-query which gets then returned in the reply-items-query?
Hmm. I guess you're doing something like:
authorize_check_query = "select myproc('%{SQL-User- Name}','...etc...')"
...and are trying to avoid re-calling the same (or another) function in the reply query.
That's the problem. How will the reply query be aware that the user has already be rejected without using additional queries? I tried calling the check query with %{control:My-Reply} or % {control:Auth-Type} as attributes but those are empty though set in the check query.
What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section:
i.e. return 2 rows from the stored proc:
attr | op | value -----------+----+-------------- My-Reply | := | some message Auth-Type | := | Reject
in /etc/raddb/dictionary:
ATTRIBUTE My-Reply 3000 string
and have:
authorize { sql } post-auth { Post-Auth-Type Reject { if (control:My-Reply) { update reply { Reply-Message = "%{control:My-Reply}" } } } }
Thanks Phil, that was it! Now I'm getting the right Reply-Message. Actually, if (control:My-Reply) must be if ("%{control:My-Reply}") to check if it's empty. JB
I'm sorry, I have to ask again. Have you found a way to let the reply query know that the user has already been rejected in the check-query? I'm trying to avoid executing the same queries twice and also to avoid using temporary tables. Thank you, JB JB (08.02.2008 14:00):
Phil Mayers (08.02.2008 12:03):
Ok, now I'm returning Auth-Type := Reject from my check-items- query and I hoped to be able to send a little more in depth information along the way in the Reply-Message attribute, but unfortunately this info gets lost. It seems that I have to fill this attribute in the reply-items-query. Does this mean the reply-items-query has to trigger the same functions as the check-items-query again to find out what the reason for the reject was? Or do I have to fill a temporary table with the reply message in the check-items-query which gets then returned in the reply-items-query?
Hmm. I guess you're doing something like:
authorize_check_query = "select myproc('%{SQL-User- Name}','...etc...')"
...and are trying to avoid re-calling the same (or another) function in the reply query.
That's the problem. How will the reply query be aware that the user has already be rejected without using additional queries? I tried calling the check query with %{control:My-Reply} or % {control:Auth-Type} as attributes but those are empty though set in the check query.
JB wrote:
I'm sorry, I have to ask again. Have you found a way to let the reply query know that the user has already been rejected in the check-query? I'm trying to avoid executing the same queries twice and also to avoid using temporary tables.
I thought I'd answered this? """What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section...""" Which you said worked in a later email
Phil Mayers:
JB wrote:
I'm sorry, I have to ask again. Have you found a way to let the reply query know that the user has already been rejected in the check-query? I'm trying to avoid executing the same queries twice and also to avoid using temporary tables.
I thought I'd answered this? """What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section...""" Which you said worked in a later email
Sorry if I haven't made myself clear enough. These were two different things. On the on hand, I wanted to return a Reply-Message to the user which is set in one of the two queries, which works fine the way you proposed. On the other hand, I wanted to avoid executing unnecessary sub-queries in the reply query (a stored procedure in my case), or the reply query itself, if the user has already been rejected in the check query. It seems that the reply query is always executed. And if I call the stored procedure with attributes like "%{control:Auth-Type}" or "% {control:My-Reply}", they don't get resolved although they're set in the first query. In pseudo-code: Check query: reject user because of reason 'xyz', set My-Attr to 'xyz'. [works] If rejected, don't call reply query (or at least call reply query with resolved attributes to avoid unnecessary sub-queries) [doesn't work] If rejected copy My-Attr to Reply-Message [works] JB
JB wrote:
Phil Mayers:
JB wrote:
I'm sorry, I have to ask again. Have you found a way to let the reply query know that the user has already been rejected in the check-query? I'm trying to avoid executing the same queries twice and also to avoid using temporary tables.
I thought I'd answered this? """What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section...""" Which you said worked in a later email
Sorry if I haven't made myself clear enough. These were two different things.
On the on hand, I wanted to return a Reply-Message to the user which is set in one of the two queries, which works fine the way you proposed.
On the other hand, I wanted to avoid executing unnecessary sub-queries in the reply query (a stored procedure in my case), or the reply query itself, if the user has already been rejected in the check query. It seems that the reply query is always executed. And if I call the stored procedure with attributes like "%{control:Auth-Type}" or "%{control:My-Reply}", they don't get resolved although they're set in the first query.
In pseudo-code:
Check query: reject user because of reason 'xyz', set My-Attr to 'xyz'. [works] If rejected, don't call reply query (or at least call reply query with resolved attributes to avoid unnecessary sub-queries) [doesn't work] If rejected copy My-Attr to Reply-Message [works]
Ah I see. No, the "sql" module doesn't work that way - if *any* check pairs are returned (and match) the reply query is run, but the pairxlatmove() is done *after* the reply query is done - i.e. it does this: check_items = sql(check_query) if paircompare(request, check_items): reply_items = sql(reply_query) pairxlatmove(request->reply_items, reply_items) pairxlatmove(request->check_items, check_items) The only way you could change this would be with source-code patches or use rlm_perl/python to do the logic you want. Arguably the check items pairxlatmove() should be before the reply query, but then if the xlat of the reply query or reply query itself fail, you'd have added check items without corresponding reply items (but the module would have returned a fail error code, so it's probably not a big deal) You could move the check items pairxlatmove() call - it's line 669 in src/modules/rlm_sql/rlm_sql.c in my copy of 2.0.0, and would need to move to just above line 651 i.e. the radius_xlat of the reply query.
Phil Mayers (29.02.2008):
JB wrote:
Phil Mayers:
JB wrote:
I'm sorry, I have to ask again. Have you found a way to let the reply query know that the user has already been rejected in the check-query? I'm trying to avoid executing the same queries twice and also to avoid using temporary tables.
I thought I'd answered this? """What you could do is place a local attribute in the check items, then copy it to the reply items in an unlang section...""" Which you said worked in a later email Sorry if I haven't made myself clear enough. These were two different things. On the on hand, I wanted to return a Reply-Message to the user which is set in one of the two queries, which works fine the way you proposed. On the other hand, I wanted to avoid executing unnecessary sub- queries in the reply query (a stored procedure in my case), or the reply query itself, if the user has already been rejected in the check query. It seems that the reply query is always executed. And if I call the stored procedure with attributes like "%{control:Auth- Type}" or "%{control:My-Reply}", they don't get resolved although they're set in the first query. In pseudo-code: Check query: reject user because of reason 'xyz', set My-Attr to 'xyz'. [works] If rejected, don't call reply query (or at least call reply query with resolved attributes to avoid unnecessary sub-queries) [doesn't work] If rejected copy My-Attr to Reply-Message [works]
Ah I see.
No, the "sql" module doesn't work that way - if *any* check pairs are returned (and match) the reply query is run, but the pairxlatmove() is done *after* the reply query is done - i.e. it does this:
check_items = sql(check_query) if paircompare(request, check_items): reply_items = sql(reply_query) pairxlatmove(request->reply_items, reply_items) pairxlatmove(request->check_items, check_items)
The only way you could change this would be with source-code patches or use rlm_perl/python to do the logic you want.
Arguably the check items pairxlatmove() should be before the reply query, but then if the xlat of the reply query or reply query itself fail, you'd have added check items without corresponding reply items (but the module would have returned a fail error code, so it's probably not a big deal)
You could move the check items pairxlatmove() call - it's line 669 in src/modules/rlm_sql/rlm_sql.c in my copy of 2.0.0, and would need to move to just above line 651 i.e. the radius_xlat of the reply query.
Thanks a lot, your help really is appreciated! I'll try that. Do you think there is a chance that this patch would make it into a final release? Could this break current installations in any way? JB
participants (2)
-
JB -
Phil Mayers