Alan DeKok напиша:

Ljupco Vangelski wrote:

...

I'm trying to allow a user to log in from few Calling-Station-Ids, but to load them at run-time (read them from mysql table). For that I'm trying to use sql statement in radcheck, but it doesn't seem to be working.

Here is a simplified example (mysql, table radcheck):

UserName..........Attribute..............op..........Value (1) test Calling-Station-Id == 001122334455 (is working) (2) test Calling-Station-Id == %{sql:select "001122334455";} (is not working)

That isn't valid SQL, for one.

Why? select "001122334455"; Will return 001122334455 in any MySQL server version (It was just for illustration, I have no intention for using a query of that kind). Anyway, I've tried with more complex examples (select mytable.field from mytable where mytable.username='%{User-Name}';) and it is not working.

...

Can sql statements be used to compare against the Attribute Value in CHECK statements, like in the cases (2) above?

Not really.

The question is whether I can use sql in check items at all? There are two other issues that aren't clear to me and might be connected to this one. Let's take two attributes in the radreply table - Framed-Id and Reply-Message). (1) For one attribute (Reply-Message), the runtime variable is converted into it's runtime value, for the other one (Filter-Id) it just returns the text. Table radreply: ------------------------------------------------------------------------------------ UserName......Attribute..........op.........Value test Framed-Id = %{User-Name} test Reply-Message = %{User-Name} # radtest test test localhost 523 testing123 Sending Access-Request of id 229 to 127.0.0.1 port 1812 User-Name = "test" User-Password = "test" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40 Filter-Id = "%{User-Name}" Reply-Message = "test" ------------------------------------------------------------------------------------ UserName......Attribute..........op.........Value test Framed-Id = %{check:User-Name} test Reply-Message = %{check:User-Name} # radtest test test localhost 523 testing123 Sending Access-Request of id 229 to 127.0.0.1 port 1812 User-Name = "test" User-Password = "test" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40 Filter-Id = "%{check:User-Name}" Reply-Message = "test" -------------------------------------------------------------------------------------- UserName......Attribute..........op.........Value test Framed-Id = %{config:logdir} test Reply-Message = %{config:logdir} # radtest test test localhost 523 testing123 Sending Access-Request of id 229 to 127.0.0.1 port 1812 User-Name = "test" User-Password = "test" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40 Filter-Id = "%{config:logdir}" Reply-Message = "/var/log" -------------------------------------------------------------------------------------- How can I make the Framed-Id attribute to return a runtime variable (example %{User-Name}, %{Calling-Station-Id} or else) ? (2) The second issue is that for the first attribute (Reply-Message), the sql returns a value, for the other one (Filter-Id) it just returns the whole field as text (doesn't use the sql module to extract the Value). Table radreply: -------------------------------------------------------------------------------------- [radreply] UserName......Attribute..........op.........Value test Framed-Id = %{sql:SELECT Name FROM userinfo WHERE UserName='test';} test Reply-Message = %{sql:SELECT Name FROM userinfo WHERE UserName='test';} [userinfo] UserName......Name............................. test test ... ... # radtest test test localhost 523 testing123 Sending Access-Request of id 245 to 127.0.0.1 port 1812 User-Name = "test" User-Password = "test" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=245, length=83 Filter-Id = "%{sql:SELECT Name FROM userinfo WHERE UserName='test';}" Reply-Message = "test" How can I make freeradius use the sql module and extract (return) information for Filter-Id just like it does for Reply-Message? Thanks in advance, -- Ljupco

Alan DeKok напиша:

Ljupco Vangelski wrote:

...

The question is whether I can use sql in check items at all?

Not like that.

...

(1) For one attribute (Reply-Message), the runtime variable is converted into it's runtime value, for the other one (Filter-Id) it just returns the text.

The Reply-Message attribute is treated differently, for historical reasons. See the debug output for more information.

...

How can I make freeradius use the sql module and extract (return) information for Filter-Id just like it does for Reply-Message?

Edit the source code to rlm_sql.

Hi Alan, Thanks for the reply. Been some debugging and I just can't understand this: If I use the users file, for example --------------------------------------------------------------------------------------------------------------- ljupco Auth-Type := Local, User-Password == "ljupco" Filter-Id = "%{sql:select service from userinfo limit 1;}", Reply-Message = "%{sql:select service from userinfo limit 1;}" --------------------------------------------------------------------------------------------------------------- then the everything works great, and the sql function is performed: --------------------------------------------------------------------------------------------------------------- # radtest ljupco ljupco localhost 523 testing123 Sending Access-Request of id 20 to 127.0.0.1 port 1812 User-Name = "ljupco" User-Password = "ljupco" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=20, length=32 Filter-Id = "net1" Reply-Message = "net1" --------------------------------------------------------------------------------------------------------------- And if i put the same value (%{sql:select service from userinfo limit 1;}) in radreply for the same attributes, --------------------------------------------------------------------------------------------------------------- UserName......Attribute..........op.........Value test Framed-Id = %{sql:select service from userinfo limit 1;} test Reply-Message = %{sql:select service from userinfo limit 1;} --------------------------------------------------------------------------------------------------------------- the sql function doesn't get executed for the attribute Filter-Id, and only for Reply-Message. I get the reply: --------------------------------------------------------------------------------------------------------------- # radtest ljupco ljupco localhost 523 testing123 Sending Access-Request of id 20 to 127.0.0.1 port 1812 User-Name = "ljupco" User-Password = "ljupco" NAS-IP-Address = 255.255.255.255 NAS-Port = 523 rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=20, length=32 Filter-Id = "%{sql:select service from userinfo limit 1;}" Reply-Message = "net1" --------------------------------------------------------------------------------------------------------------- Actually, this is what I need, but I don't want to use the users file and want to use the radreply table instead. Is this feature different in later freeradius versions, freeradius2 for example? Thanks for Your time, -- Ljupco