Variable expansion for SQL attributes
Hello, I have FreeRADIUS running with a sql database in production. I am using the sql database to set user's check and reply attributes. The problem I have is that there are some attributes that I need to set them at runtime depending on the NAS identity (or any other FreeRADIUS variable), so I have to edit the configuration file and add something like this: if (...) { update control { Pool-Name := "%{NAS-Identifier}" } } Please, ignore the fact that I am updating the Pool-Name attribute. It is just an example but it has sense in my case to let users switch between NAS and IP pools automatically. I want to be able to use variables in attributes column (for radcheck, radreply, radgroupcheck, and radgroupreply tables). Then FreeRADIUS will get those values for attributes and expand or evaluate them replacing variable placeholders for concrete values. I don't want to modify the configuration file to store any user's configuration as I am currently doing. Will that feature make sense? I can help in programming it or even make a donation to have someone help me in doing this. I post a request on Github: https://github.com/FreeRADIUS/freeradius-server/issues/1870 I was suggested to use SQL xlat expansion %{sql:SELECT ...} but that is not useful in my case because I don't want to customize requests. Regards, Javier
On Fri, Dec 09, 2016 at 03:24:35PM +0100, Javier Matos Odut wrote:
I have FreeRADIUS running with a sql database in production. I am using the sql database to set user's check and reply attributes. The problem I have is that there are some attributes that I need to set them at runtime depending on the NAS identity (or any other FreeRADIUS variable), so I have to edit the configuration file and add something like this:
if (...) { update control { Pool-Name := "%{NAS-Identifier}" } }
Please, ignore the fact that I am updating the Pool-Name attribute. It is just an example but it has sense in my case to let users switch between NAS and IP pools automatically.
So you want to dynamically set NAS-Identifier here? or Pool-Name?
I want to be able to use variables in attributes column (for radcheck, radreply, radgroupcheck, and radgroupreply tables). Then FreeRADIUS will get those values for attributes and expand or evaluate them replacing variable placeholders for concrete values. I don't want to modify the configuration file to store any user's configuration as I am currently doing.
If you want to set particular attributes for particular users, then just add entries for those attributes into the radcheck table. You don't need to add the same attributes for all users.
I post a request on Github: https://github.com/FreeRADIUS/freeradius-server/issues/1870
I was suggested to use SQL xlat expansion %{sql:SELECT ...} but that is not useful in my case because I don't want to customize requests.
I guess I'm not understanding what you want to do well enough. Maybe someone else can? It sounds like you either want to a) dynamically expand what the attribute is being set to for a user based on some random SQL values, in which case %{sql:...} should work, or b) dynamically change which attributes are being set, in which case different entries in radcheck should do it. ...or... something else? Maybe an example of what you want to put into the database and what you want to get out would help. Matthew -- Matthew Newton, Ph.D. <mcn4@leicester.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
On Fri, Dec 9, 2016 at 3:52 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Fri, Dec 09, 2016 at 03:24:35PM +0100, Javier Matos Odut wrote:
I have FreeRADIUS running with a sql database in production. I am using the sql database to set user's check and reply attributes. The problem I have is that there are some attributes that I need to set them at runtime depending on the NAS identity (or any other FreeRADIUS variable), so I have to edit the configuration file and add something like this:
if (...) { update control { Pool-Name := "%{NAS-Identifier}" } }
Please, ignore the fact that I am updating the Pool-Name attribute. It is just an example but it has sense in my case to let users switch between NAS and IP pools automatically.
So you want to dynamically set NAS-Identifier here? or Pool-Name?
I want to be able to use variables in attributes column (for radcheck, radreply, radgroupcheck, and radgroupreply tables). Then FreeRADIUS will get those values for attributes and expand or evaluate them replacing variable placeholders for concrete values. I don't want to modify the configuration file to store any user's configuration as I am currently doing.
If you want to set particular attributes for particular users, then just add entries for those attributes into the radcheck table. You don't need to add the same attributes for all users.
I post a request on Github: https://github.com/FreeRADIUS/freeradius-server/issues/1870
I was suggested to use SQL xlat expansion %{sql:SELECT ...} but that is not useful in my case because I don't want to customize requests.
I guess I'm not understanding what you want to do well enough. Maybe someone else can?
It sounds like you either want to
a) dynamically expand what the attribute is being set to for a user based on some random SQL values, in which case %{sql:...} should work, or
b) dynamically change which attributes are being set, in which case different entries in radcheck should do it.
...or... something else?
Thank you so much for your answer Matthew, My case I think is close to your b) case, and is as follows: I need to have independent IP pools: one per NAS. For scaling my network this is the best approach as my ip routes will be quite simple. I know that I can set the Pool-Name attribute for a user by setting Pool-Name := "Poolname" in radcheck or in radgroupcheck. The problem with that approach are (1) I have to know the NAS the user is connecting to and assign the correct Pool-Name, (2) that Pool-Name attribute is "fixed" and if I move a user from one NAS to another NAS, then a wrong Pool-Name will be assigned to the user, I have it working right now doing this in default config: authorize { ... update control { Pool-Name := "%{NAS-Identifier}" } } And it works fine. I have many NAS and an IP pool for each. Users will connect and get a correct IP address based on the IP pool the NAS has assigned. I don't like to have user configuration in FreeRADIUS configuration file, so if I am able to save Pool-Name := %{NAS-Identifier} in radcheck and then FreeRADIUS can get and expand that value it will be the same as I am doing in configuration but without touching FreeRADIUS configuration at all.
Maybe an example of what you want to put into the database and what you want to get out would help.
Let's say I have: 1) a NAS whose NAS-Identifier is "nas01", 2) a user "user01" in database that has an attribute -> Pool-Name := "%{NAS-Identifier}-hello" Then when user01 tries to connect and nas01 send a request to FreeRADIUS the attribute Pool-Name will be evaluated to "%{NAS-Identifier}-hello" => "nas01-hello". Obviously, if user01 is moved to a future nas02, then Pool-Name will get evaluated to "nas02-hello" when user tries to connect. I hope my example is good to explain what I am trying to do.
Matthew
-- Matthew Newton, Ph.D. <mcn4@leicester.ac.uk>
Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom
For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk> - List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On Fri, Dec 09, 2016 at 04:21:22PM +0100, Javier Matos Odut wrote:
On Fri, Dec 9, 2016 at 3:52 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
b) dynamically change which attributes are being set, in which case different entries in radcheck should do it.
My case I think is close to your b) case, and is as follows:
I need to have independent IP pools: one per NAS. For scaling my network this is the best approach as my ip routes will be quite simple.
OK, so you have different users that can connect to any NAS, and one pool per NAS? That should be simple.
I know that I can set the Pool-Name attribute for a user by setting Pool-Name := "Poolname" in radcheck or in radgroupcheck. The problem with that approach are
(1) I have to know the NAS the user is connecting to and assign the correct Pool-Name,
Obviously, if the pool in use is defined by the NAS, not the user that logs in.
(2) that Pool-Name attribute is "fixed" and if I move a user from one NAS to another NAS, then a wrong Pool-Name will be assigned to the user,
OK...
I have it working right now doing this in default config:
authorize {
...
update control { Pool-Name := "%{NAS-Identifier}" } }
That looks fine to me (though note NAS-Identifier is sent by the NAS, so you might want to use some other means if the NASes aren't under your control).
And it works fine.
Good.
I have many NAS and an IP pool for each. Users will connect and get a correct IP address based on the IP pool the NAS has assigned.
Right.
I don't like to have user configuration in FreeRADIUS configuration file,
So you've got a working config... but you don't like having custom things in your config? Sorry, I'm not getting it. The config is there so you can configure it to work in your environment. Maybe I'm still missing something.
so if I am able to save Pool-Name := %{NAS-Identifier} in radcheck and then FreeRADIUS can get and expand that value it will be the same as I am doing in configuration but without touching FreeRADIUS configuration at all.
And include slower SQL lookups when it could be much faster using three lines of unlang in the config.
Maybe an example of what you want to put into the database and what you want to get out would help.
Let's say I have:
1) a NAS whose NAS-Identifier is "nas01",
2) a user "user01" in database that has an attribute -> Pool-Name := "%{NAS-Identifier}-hello"
Then when user01 tries to connect and nas01 send a request to FreeRADIUS the attribute Pool-Name will be evaluated to "%{NAS-Identifier}-hello" => "nas01-hello".
Obviously, if user01 is moved to a future nas02, then Pool-Name will get evaluated to "nas02-hello" when user tries to connect.
I hope my example is good to explain what I am trying to do.
If you really want to slow things down by looking up the NAS-Identifier in SQL, you could configure a second instance of sql that uses NAS-Identifier as the sql_user_name, then the lookups will be based on the NAS rather than the user that is logging in. But I really can't see the point at the moment if you've got a working config as-is, unless there's something more complicated you're trying to do that's not coming across in your message. SQL lookups are always going to be slower than an update in unlang. It might be worthwhile if you had a big switch or if/elseif type block that you have to keep editing, but for static config it doesn't make any sense to me. Matthew -- Matthew Newton, Ph.D. <mcn4@leicester.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
On Fri, Dec 9, 2016 at 6:39 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Fri, Dec 09, 2016 at 04:21:22PM +0100, Javier Matos Odut wrote:
On Fri, Dec 9, 2016 at 3:52 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
b) dynamically change which attributes are being set, in which case different entries in radcheck should do it.
My case I think is close to your b) case, and is as follows:
I need to have independent IP pools: one per NAS. For scaling my network this is the best approach as my ip routes will be quite simple.
OK, so you have different users that can connect to any NAS, and one pool per NAS?
Exactly that. I have in my FreeRADIUS a network range with many ip addresses (I use ippool table) for every NAS (using poolname = "%{NAS-Identifier}").
That should be simple.
I know that I can set the Pool-Name attribute for a user by setting Pool-Name := "Poolname" in radcheck or in radgroupcheck. The problem with that approach are
(1) I have to know the NAS the user is connecting to and assign the correct Pool-Name,
Obviously, if the pool in use is defined by the NAS, not the user that logs in.
The pool is not exactly defined in the NAS. The NAS just receives a network range and users that connect to it will have an IP address in that network range managed by FreeRADIUS using the ippool table in SQL.
(2) that Pool-Name attribute is "fixed" and if I move a user from one NAS to another NAS, then a wrong Pool-Name will be assigned to the user,
OK...
I have it working right now doing this in default config:
authorize {
...
update control { Pool-Name := "%{NAS-Identifier}" } }
That looks fine to me (though note NAS-Identifier is sent by the NAS, so you might want to use some other means if the NASes aren't under your control).
And it works fine.
Good.
I have many NAS and an IP pool for each. Users will connect and get a correct IP address based on the IP pool the NAS has assigned.
Right.
I don't like to have user configuration in FreeRADIUS configuration file,
So you've got a working config... but you don't like having custom things in your config?
Sorry, I'm not getting it. The config is there so you can configure it to work in your environment.
Maybe I'm still missing something.
I have done a user interface to avoid non-technical people to access and edit configuration. If I have to edit database + a config file my interface will get more complicated.
so if I am able to save Pool-Name := %{NAS-Identifier} in radcheck and then FreeRADIUS can get and expand that value it will be the same as I am doing in configuration but without touching FreeRADIUS configuration at all.
And include slower SQL lookups when it could be much faster using three lines of unlang in the config.
Queries will be the same. I don't want to save any extra thing on database. What I want are the values from attributes coming from the database for the user to be "expanded" or evaluated after they have been retrieved from the database. Exactly the same I have when using strings in unlang: they get evaluated after reading the string patterin in config file. At that point of execution FreeRADIUS has it's variables in memory and the result of the query in memory. Just evaluate the values from attributes coming from the database and expand variable placeholders with the values those variables have. NAS request --> [ FreeRADIUS -> get SQL result -> evaluate SQL result to expand variables using unlang -> ... ] --> NAS reply
Maybe an example of what you want to put into the database and what you want to get out would help.
Let's say I have:
1) a NAS whose NAS-Identifier is "nas01",
2) a user "user01" in database that has an attribute -> Pool-Name := "%{NAS-Identifier}-hello"
Then when user01 tries to connect and nas01 send a request to FreeRADIUS the attribute Pool-Name will be evaluated to "%{NAS-Identifier}-hello" => "nas01-hello".
Obviously, if user01 is moved to a future nas02, then Pool-Name will get evaluated to "nas02-hello" when user tries to connect.
I hope my example is good to explain what I am trying to do.
If you really want to slow things down by looking up the NAS-Identifier in SQL, you could configure a second instance of sql that uses NAS-Identifier as the sql_user_name, then the lookups will be based on the NAS rather than the user that is logging in.
No, I don't want to fetch NAS-Identifier from database. I want to retrieve a record like this from database: username: 'user01', attribute: 'Pool-Name', op: ':=' value: '%{NAS-Identifier}-hello' and after the record has been fetched, I want FreeRADIUS to evaluate the value column so from database it will take "%{NAS-Identifier}-hello" but after the expansion or evaluation it will give me "nas01-hello". It is just adding an additional step after the SQL lookup for "post-processing" the result from database, use the values as "patterns" and replace them if required. This extra step can be done without doing any SQL lookup. It is like saving a format strings for PHP programming language in database, get it back and then evaluate the string to produce the result: 1) save in database format string 'There are %d monkeys in the %s' 2) retrieve from database format string 'There are %d monkeys in the %s' 3) evaluate format string with sprintf and use that result for the rest of the process -> sprintf('There are %d monkeys in the %s', ...) You actually have variable expansion in unlang, and you also know the variable values when evaluating requests. Once you get the result from database, I want to "pipe" it to unlang expansion engine to replace pattern strings with placeholders to produce the real value. This way I can save format strings in SQL that will get evaluated at runtime. This has nothing to do with extra SQL lookup or any strange thing. Just evaluate values from attributes after they come from database so instead of returning "raw" results from SQL lookup, I want to return expanded or post-processed "raw" results, so I get my variable expansion. I think I made a mistake in my previous mail by pointing to the "attribute" column. What I want to expand or evaluate is the string in "value" column so FreeRADIUS will replace placeholders for concrete values. Sorry I am this bad explaining the feature.
But I really can't see the point at the moment if you've got a working config as-is, unless there's something more complicated you're trying to do that's not coming across in your message.
SQL lookups are always going to be slower than an update in unlang. It might be worthwhile if you had a big switch or if/elseif type block that you have to keep editing, but for static config it doesn't make any sense to me.
Matthew
-- Matthew Newton, Ph.D. <mcn4@leicester.ac.uk>
Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom
For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk> - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Fri, Dec 09, 2016 at 07:35:52PM +0100, Javier Matos Odut wrote:
On Fri, Dec 9, 2016 at 6:39 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Fri, Dec 09, 2016 at 04:21:22PM +0100, Javier Matos Odut wrote:
I need to have independent IP pools: one per NAS. For scaling my network this is the best approach as my ip routes will be quite simple.
OK, so you have different users that can connect to any NAS, and one pool per NAS?
Exactly that. I have in my FreeRADIUS a network range with many ip addresses (I use ippool table) for every NAS (using poolname = "%{NAS-Identifier}").
I have done a user interface to avoid non-technical people to access and edit configuration. If I have to edit database + a config file my interface will get more complicated.
That's fine - but what you are saying here contradicts what you say below. If you had the existing unlang that works and there is one pool per NAS then there is no need to do anything else.
Maybe an example of what you want to put into the database and what you want to get out would help.
Let's say I have:
1) a NAS whose NAS-Identifier is "nas01",
2) a user "user01" in database that has an attribute -> Pool-Name := "%{NAS-Identifier}-hello"
OK... this isn't one pool per NAS - at least, that may be what you have got at the moment, but this is keying the pool off the User-Name *and* the NAS.
Then when user01 tries to connect and nas01 send a request to FreeRADIUS the attribute Pool-Name will be evaluated to "%{NAS-Identifier}-hello" => "nas01-hello".
Obviously, if user01 is moved to a future nas02, then Pool-Name will get evaluated to "nas02-hello" when user tries to connect.
I hope my example is good to explain what I am trying to do.
This is better, thanks. You can do at least a couple of things. What you are asking for is update request { Pool-Name := "%{xlat:&Pool-Name}" } which will expand Pool-Name a second time. The other thing you could do is fetch Pool-Name from the database, being just the second part of the actual pool name (in your example, "hello"), then do update request { Pool-Name := "%{NAS-Identifier}-%{Pool-Name}" } to get the complete name. Matthew -- Matthew Newton, Ph.D. <mcn4@leicester.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
participants (2)
-
Javier Matos Odut -
Matthew Newton