Variable expansion for SQL attributes

[Javier Matos Odut]

On Fri, Dec 9, 2016 at 6:39 PM, Matthew Newton <mcn4 at 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 at 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 at 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 at le.ac.uk>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html