- Freeradius-Users - lists.freeradius.org

Re: rlm_sqlippool Question
by Richard J Palmer 07 Apr '19

07 Apr '19

HI One more question (sorry) With each accounts call I am calling the sqlippool module. This is wasteful as most sessions on the server *currently* are not from an IP pool. The connections that are in the pool all have Framed-IPs which are '172.x' I have added ... if (Framed-IP-Address =~ /172\.\d{1,3}\.\d{1,3}\.\d{1,3}\b/) { sqlippool } Which seems to work and I am now only seeing the sqlippool calls when the IP is that match... Now my knowledge of regex is not the greatest - so I would really appreciate someone here taking a quick look and saying if that looks correct Thanks in advance Richard On Saturday 06/04/2019 at 11:39 pm, Richard J Palmer wrote: > > Hi Alan > > That fixes it - thank you as ever for your help > > It is appreciated > > > Richard >> >> --- Original message --- >> Subject: Re: rlm_sqlippool Question >> From: Alan DeKok <aland(a)deployingradius.com> >> To: FreeRadius users mailing list >> <freeradius-users(a)lists.freeradius.org> >> Date: Saturday, 06/04/2019 10:53 PM >> >> On Apr 6, 2019, at 5:40 PM, Richard J Palmer <richard(a)merula.net> >> wrote: >>> >>> >>> >>> Going back more (sorry I am talking to myself ..... >>> >>> The config works file with 3.0.15 >>> BUT breaks with that error with 3.0.16 onwards >> >> Use the v3.0.x branch from GitHub. It has a fix. We're >> releasing >> 3.0.19 next week. >> >> Alan DeKok. >> >> >> - >> List info/subscribe/unsubscribe? See >> http://www.freeradius.org/list/users.html > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

3 2

Re: Updating Multipe variables with one SQL Query
by Richard J Palmer 07 Apr '19

07 Apr '19

Hi I have sent an email to the company so will see what they can do - these are Broadband L2TP sessions so the Calling-Staion-Id in these cases are a circuid ID - I do get them at session start - I have asked if they can be added to interim and end packets I made a mistake with CUI - that's something that we send back with the reply so we do generate this and it comes with every update - so I may be able to work on that too. On the positive side - even with that - because of the flexibility of FreeRadius - I can make this work - and work properly - Hopefully they can adjust the code on the devide to send the extra fields and that will make things even easier I'll let you know how I get on Thanks again for the pointers Richard > > --- Original message --- > Subject: Re: Updating Multipe variables with one SQL Query > From: Alan DeKok <aland(a)deployingradius.com> > To: FreeRadius users mailing list > <freeradius-users(a)lists.freeradius.org> > Date: Sunday, 07/04/2019 8:33 PM > > On Apr 7, 2019, at 3:24 PM, Richard J Palmer <richard(a)merula.net> > wrote: >> >> I have spoken to Adrian/RevK It's something they are looking at but >> not a 'simple' change - so not something happening 'immediately'. > > If there's a User-Name, it should be sent in accounting packets. > The same goes for Calling-Station-ID, which is usually the MAC > address. > > Doing anything else is severely unfriendly to every other RADIUS > system. > >> >> Currently they set 'Chargeable-User-Identity' which is OK - and I >> could use that. The Firebrick does not hold the username (as things >> stand) once the session is setup > >> >> - BUT we don't have the chargeable ID until the auth start hits >> freeradius Which means it's not available at the time of the IP >> allocation > > I'm not sure I understand that. CUI is sent by the RADIUS server > to the NAS. The NAS *never* creates a CUI and sends it to the server. > See RFC 4372 for details. > >> >> The reason for not returning the Framed-IP they say is that the >> devices allow multiple IPs and the framed IP can be changed with a CoA >> request even - which can be nice - you can even have multiple routing >> tables - so you can have the sane IP in multiple IPs in different >> tables in a scenario like this (putting some users into walled >> gardens). Obviously in this scenario it won't change > > That doesn't make sense. > > If the RADIUS server sends a Framed-IP-Address to the NAS, the NAS > should echo it back in accounting packets. If the Framed-IP-Address > is changed in a CoA request, then the NAS should echo the *new* > Framed-IP-Address in subsequent accounting requests. > >> >> I totally agree it is unhelpful in this case and I am not defending >> them - Just some of their thinking when I asked them- I will continue >> to nudge this one! > > They can ask RADIUS people for help. > > i.e. EVERYONE PLEASE ASK. > > It's better to fix things in the engineering stage. The > alternative is to ship garbage, and then have the poor users wonder > why the software isn't compatible with anything else. > > Alan DeKok. > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

1 0

Re: Updating Multipe variables with one SQL Query
by Richard J Palmer 07 Apr '19

07 Apr '19

HI Matthew I have spoken to Adrian/RevK It's something they are looking at but not a 'simple' change - so not something happening 'immediately'. Currently they set 'Chargeable-User-Identity' which is OK - and I could use that. The Firebrick does not hold the username (as things stand) once the session is setup - BUT we don't have the chargeable ID until the auth start hits freeradius Which means it's not available at the time of the IP allocation The reason for not returning the Framed-IP they say is that the devices allow multiple IPs and the framed IP can be changed with a CoA request even - which can be nice - you can even have multiple routing tables - so you can have the sane IP in multiple IPs in different tables in a scenario like this (putting some users into walled gardens). Obviously in this scenario it won't change I totally agree it is unhelpful in this case and I am not defending them - Just some of their thinking when I asked them- I will continue to nudge this one! These are the big brother of the 2900's (the 6000s :) This is v3 here - though happy to use / test v4 if that works for this scenario Could you possibly point me at some documentation for either - Happy to read and test with these. I have https://networkradius.com/doc/3.0.8/raddb/mods-available/redis.html which I am working through and seems sensible - but any pointers welcomed I'll have a read of that and see how I get on - I may come back with more questions Thanks for the pointers so far Richard On Sunday 07/04/2019 at 8:07 pm, Matthew Newton wrote: > On Sun, 2019-04-07 at 14:47 -0400, Alan DeKok wrote: >> >> On Apr 7, 2019, at 11:07 AM, Richard J Palmer <richard(a)merula.net> >> wrote: >>> >>> The cause it seems is what the Interim updates send to Freeradius - >>> the Router (a firebrick LNS server) >> >> That's not only unusual, it's unfriendly. Most RADIUS clients >> send >> everything they know about the session in every accounting packet. >> >>> >>> I can get round that in pre-acc by running: >> >> It doesn't event send the User-Name? What a piece of garbage. > > Richard, have you tried contacting RevK/Firebrick and asking if they > can add that? > > As Alan says, it's pretty unusual not to include those attributes > (even > though I'm not sure if any RFCs actually specify they _have_ to be > included). > > Firebricks are nice bits of kit (I've got a 2900), I'm guessing this > is > a case of "no need to do that if not explicitly stated in the RFCs". > > >> >>> >>> is there a built in way to return the three columns from sql and >>> then within FreeRadius and assign them into the items as need be. >> >> You can grab the 3 parameters in one SQL query, and then separate >> them via a regex. But that's about it. > > Can use a map{} in v4. Or (in v3) backend it with Redis, which will > pull this stuff back before SQL has even parsed the query. > > -- > Matthew > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

2 1

Updating Multipe variables with one SQL Query
by Richard J Palmer 07 Apr '19

07 Apr '19

Hi Following on from yesterdays ippool query - I have had an issue with Session being updated & freed The cause it seems is what the Interim updates send to Freeradius - the Router (a firebrick LNS server) basically sends back just the 'variables' that have changed, the unique ID and the accounting session ID. This is find for accounting updates - however with pools I need the IP address / calling-station-ID etc - these are not returned. I can get round that in pre-acc by running: update request { User-Name := "%{sql1:SELECT UserName from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" Framed-IP-Address := "%{sql1:SELECT FramedIPAddress from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" Calling-Station-Id := "%{sql1:SELECT CallingStationId from radius..radacct WITH(NOLOCK) where AcctSessionId = '%{Acct-Session-Id}' AND NASIPAddress = '%{NAS-IP-Address}'}" } The query itself is very quick however that is having to run the same query three times to get the separate variables I can think of several ways to get round this (create this as a long string I pass into three parts etc ... However before I think of that - is there a built in way to return the three columns from sql and then within FreeRadius and assign them into the items as need be. Thanks again in advance Richard Palmer | Director | Merula Limited Company Registered in England and Wales No. 3243995 5 Avro Court, Huntingdon, Cambridgeshire, PE29 6XS Phone 01480 222940 | Support 0845 330 0666 Support Email support(a)merula.net

3 3

Help with external authentication using PHP
by Ekene Ezeasor 07 Apr '19

07 Apr '19

Hi all, Please our users' password are encrypted using crypt() (blowfish) function in PHP. Now I want to use password_verify() to check the submitted password and I intend doing that in PHP. I have updated my authorize section to use the external PHP script like this: update control { Auth-type := "/usr/bin/php -f /etc/freeradius/3.0/php/checkpassword.php %{User-Name} %{User-Password}" &Proxy-To-Realm := LOCAL } But only the username is sent to the external PHP file. The password is empty. We are already running a large database and it may not be easy to change to another encryption method. Therefore this is very important and we really need to implement it. Please can someone help. Thanks. Regards ___________________________ Ekene Ezeasor IT Consultant, Codee Solutions *Phone*: 08063961963 *Web*: www.codeeltd.com ------------------------------ *DISCLAIMER NOTICE:* *This e-mail, any attachments thereto and response string is intended solely for the attention and use of the addressee(s) named herein and may contain legally privileged and/or confidential information. In the event that you are not the intended recipient(s) of this e-mail and any attachments thereto, be notified that any dissemination, distribution or copying of this e-mail and any attachments thereto, is strictly prohibited. If you have received or otherwise encountered this e-mail in error, please immediately notify the sender and permanently delete the e-mail, any attachments and response string as well as any copy printout in connection therewith.* ------------------------------

8 13

Re: rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

Hi Alan That fixes it - thank you as ever for your help It is appreciated Richard > --- Original message --- > Subject: Re: rlm_sqlippool Question > From: Alan DeKok <aland(a)deployingradius.com> > To: FreeRadius users mailing list > <freeradius-users(a)lists.freeradius.org> > Date: Saturday, 06/04/2019 10:53 PM > > On Apr 6, 2019, at 5:40 PM, Richard J Palmer <richard(a)merula.net> > wrote: >> >> >> Going back more (sorry I am talking to myself ..... >> >> The config works file with 3.0.15 >> BUT breaks with that error with 3.0.16 onwards > > Use the v3.0.x branch from GitHub. It has a fix. We're releasing > 3.0.19 next week. > > Alan DeKok. > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

1 0

Re: rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

OK Going back more (sorry I am talking to myself ..... The config works file with 3.0.15 BUT breaks with that error with 3.0.16 onwards I am running temporarily with 3.0.15 but if anyone can help locate / find the problem with the later version it would be nice. Just to confirm - If I install an 3.0.15 compiled from source (same config) it just works 3.0.16 onwards gives the crash I sent earlier Richard On Saturday 06/04/2019 at 10:06 pm, Richard J Palmer wrote: > > OK Further to this - it's not due to the config changes > > I removed them - but it is something from the upgrade between 3.0.15 > that was on this server and 3.0.18 which I am now using > > Logins are working fine using sql - it is only the sqlippool that is > crashing here > > > Happy to provide any details needed here / make any changes ... > > Richard > > On Saturday 06/04/2019 at 9:55 pm, Richard J Palmer wrote: >> >> >> Hmmmm >> >> OK Now I am getting a little stuck... >> >> I created a version on my 'test' server - and reverted the hard coded >> config and added: >> >> start_begin = "BEGIN TRANSACTION" >> allocate_begin = "BEGIN TRANSACTION" >> stop_begin = "BEGIN TRANSACTION" >> on_begin = "BEGIN TRANSACTION" >> off_begin = "BEGIN TRANSACTION" >> >> >> To the config file .. >> >> And during a login that uses ippool - I get: >> >> -- >> rlm_sql (sql1): Reserved connection (2) >> (0) sqlippool: EXPAND %{User-Name} >> (0) sqlippool: --> cgnattest(a)adsl.merula.net >> (0) sqlippool: SQL-User-Name set to 'cgnattest(a)adsl.merula.net' >> (0) sqlippool: EXPAND BEGIN TRANSACTION >> (0) sqlippool: --> BEGIN TRANSACTION >> (0) sqlippool: Executing query: BEGIN TRANSACTION >> src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: name[rlm_sql_t] >> expected[rlm_sql_handle_t] >> >> talloc abort: src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: >> name[rlm_sql_t] expected[rlm_sql_handle_t] >> ---- >> >> >> Have I done something silly here ? >> >> This is using a newly upgraded 3.0.18 install >> >> Thanks in advance >> >> Richard >> >> >> On Saturday 06/04/2019 at 9:10 pm, Alan DeKok wrote: >>> >>> >>> On Apr 6, 2019, at 3:54 PM, Richard J Palmer <richard(a)merula.net> >>> wrote: >>>> >>>> >>>> >>>> I am getting somewhere >>> >>> Sounds good. >>> >>>> >>>> >>>> >>>> There's actually a couple of other changes - One in the source code - >>>> one for the queries. >>>> >>>> I have it running in theory and will test and come back on this one >>>> soon.... >>>> >>>> With the source code - the issue I found was the Transaction code was >>>> different in Mssql to mysql - The start / end transaction calls seem >>>> to be hard coded - so for now I have tweaked the code to send the MS >>>> SQL commit functions - BUT I suspect that may be better as a config >>>> setting ? >>> >>> The transaction code should be pretty common: start >>> transaction, do >>> work, end transaction. And all of those SQL statements can go into >>> the configuration files. >>> >>> e.g. having "allocate_commit = COMMIT" in the source is >>> just a >>> default. If you need a different statement for commit, just put >>> "allocate_commit = whatever" into the configuration files. No source >>> code changes required. >>> >>> What's different about the MS-SQL commit functions? >>> >>>> >>>> >>>> >>>> I am going to test this 'in anger' now so we get accounting data for a >>>> few test accounts and make sure things look OK... >>>> >>>> Once working would you like a copy of the code changes / working >>>> queries to apply to the code ? >>> >>> Yes. Probably only one file with the queries is >>> needed. >>> >>> Alan DeKok. >>> >>> >>> - >>> List info/subscribe/unsubscribe? See >>> http://www.freeradius.org/list/users.html >> >> - >> List info/subscribe/unsubscribe? See >> http://www.freeradius.org/list/users.html > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

2 1

Re: rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

OK Further to this - it's not due to the config changes I removed them - but it is something from the upgrade between 3.0.15 that was on this server and 3.0.18 which I am now using Logins are working fine using sql - it is only the sqlippool that is crashing here Happy to provide any details needed here / make any changes ... Richard On Saturday 06/04/2019 at 9:55 pm, Richard J Palmer wrote: > > Hmmmm > > OK Now I am getting a little stuck... > > I created a version on my 'test' server - and reverted the hard coded > config and added: > > start_begin = "BEGIN TRANSACTION" > allocate_begin = "BEGIN TRANSACTION" > stop_begin = "BEGIN TRANSACTION" > on_begin = "BEGIN TRANSACTION" > off_begin = "BEGIN TRANSACTION" > > > To the config file .. > > And during a login that uses ippool - I get: > > -- > rlm_sql (sql1): Reserved connection (2) > (0) sqlippool: EXPAND %{User-Name} > (0) sqlippool: --> cgnattest(a)adsl.merula.net > (0) sqlippool: SQL-User-Name set to 'cgnattest(a)adsl.merula.net' > (0) sqlippool: EXPAND BEGIN TRANSACTION > (0) sqlippool: --> BEGIN TRANSACTION > (0) sqlippool: Executing query: BEGIN TRANSACTION > src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: name[rlm_sql_t] > expected[rlm_sql_handle_t] > > talloc abort: src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: > name[rlm_sql_t] expected[rlm_sql_handle_t] > ---- > > > Have I done something silly here ? > > This is using a newly upgraded 3.0.18 install > > Thanks in advance > > Richard > > > On Saturday 06/04/2019 at 9:10 pm, Alan DeKok wrote: >> >> On Apr 6, 2019, at 3:54 PM, Richard J Palmer <richard(a)merula.net> >> wrote: >>> >>> >>> I am getting somewhere >> >> Sounds good. >> >>> >>> >>> There's actually a couple of other changes - One in the source code - >>> one for the queries. >>> >>> I have it running in theory and will test and come back on this one >>> soon.... >>> >>> With the source code - the issue I found was the Transaction code was >>> different in Mssql to mysql - The start / end transaction calls seem >>> to be hard coded - so for now I have tweaked the code to send the MS >>> SQL commit functions - BUT I suspect that may be better as a config >>> setting ? >> >> The transaction code should be pretty common: start >> transaction, do >> work, end transaction. And all of those SQL statements can go into >> the configuration files. >> >> e.g. having "allocate_commit = COMMIT" in the source is just a >> default. If you need a different statement for commit, just put >> "allocate_commit = whatever" into the configuration files. No source >> code changes required. >> >> What's different about the MS-SQL commit functions? >> >>> >>> >>> I am going to test this 'in anger' now so we get accounting data for a >>> few test accounts and make sure things look OK... >>> >>> Once working would you like a copy of the code changes / working >>> queries to apply to the code ? >> >> Yes. Probably only one file with the queries is needed. >> >> Alan DeKok. >> >> >> - >> List info/subscribe/unsubscribe? See >> http://www.freeradius.org/list/users.html > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

1 0

Re: rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

Hmmmm OK Now I am getting a little stuck... I created a version on my 'test' server - and reverted the hard coded config and added: start_begin = "BEGIN TRANSACTION" allocate_begin = "BEGIN TRANSACTION" stop_begin = "BEGIN TRANSACTION" on_begin = "BEGIN TRANSACTION" off_begin = "BEGIN TRANSACTION" To the config file .. And during a login that uses ippool - I get: -- rlm_sql (sql1): Reserved connection (2) (0) sqlippool: EXPAND %{User-Name} (0) sqlippool: --> cgnattest(a)adsl.merula.net (0) sqlippool: SQL-User-Name set to 'cgnattest(a)adsl.merula.net' (0) sqlippool: EXPAND BEGIN TRANSACTION (0) sqlippool: --> BEGIN TRANSACTION (0) sqlippool: Executing query: BEGIN TRANSACTION src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: name[rlm_sql_t] expected[rlm_sql_handle_t] talloc abort: src/modules/rlm_sql/rlm_sql.c:381: Type mismatch: name[rlm_sql_t] expected[rlm_sql_handle_t] ---- Have I done something silly here ? This is using a newly upgraded 3.0.18 install Thanks in advance Richard On Saturday 06/04/2019 at 9:10 pm, Alan DeKok wrote: > On Apr 6, 2019, at 3:54 PM, Richard J Palmer <richard(a)merula.net> > wrote: >> >> I am getting somewhere > > Sounds good. > >> >> There's actually a couple of other changes - One in the source code - >> one for the queries. >> >> I have it running in theory and will test and come back on this one >> soon.... >> >> With the source code - the issue I found was the Transaction code was >> different in Mssql to mysql - The start / end transaction calls seem >> to be hard coded - so for now I have tweaked the code to send the MS >> SQL commit functions - BUT I suspect that may be better as a config >> setting ? > > The transaction code should be pretty common: start transaction, do > work, end transaction. And all of those SQL statements can go into > the configuration files. > > e.g. having "allocate_commit = COMMIT" in the source is just a > default. If you need a different statement for commit, just put > "allocate_commit = whatever" into the configuration files. No source > code changes required. > > What's different about the MS-SQL commit functions? > >> >> I am going to test this 'in anger' now so we get accounting data for a >> few test accounts and make sure things look OK... >> >> Once working would you like a copy of the code changes / working >> queries to apply to the code ? > > Yes. Probably only one file with the queries is needed. > > Alan DeKok. > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

1 0

Re: rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

HI Alan The current code has 'START TRANSACTION' Microsoft chose to have 'BEGIN TRANSACTION' Thanks for the confirmation that this can be in the config - not needing to edit source that is much better - sorry I missed that bit - reading the code again it seems obvious ... I'll do some more testing but this seems to be working very well ... Thanks again Richard On Saturday 06/04/2019 at 9:10 pm, Alan DeKok wrote: > On Apr 6, 2019, at 3:54 PM, Richard J Palmer <richard(a)merula.net> > wrote: >> >> I am getting somewhere > > Sounds good. > >> >> There's actually a couple of other changes - One in the source code - >> one for the queries. >> >> I have it running in theory and will test and come back on this one >> soon.... >> >> With the source code - the issue I found was the Transaction code was >> different in Mssql to mysql - The start / end transaction calls seem >> to be hard coded - so for now I have tweaked the code to send the MS >> SQL commit functions - BUT I suspect that may be better as a config >> setting ? > > The transaction code should be pretty common: start transaction, do > work, end transaction. And all of those SQL statements can go into > the configuration files. > > e.g. having "allocate_commit = COMMIT" in the source is just a > default. If you need a different statement for commit, just put > "allocate_commit = whatever" into the configuration files. No source > code changes required. > > What's different about the MS-SQL commit functions? > >> >> I am going to test this 'in anger' now so we get accounting data for a >> few test accounts and make sure things look OK... >> >> Once working would you like a copy of the code changes / working >> queries to apply to the code ? > > Yes. Probably only one file with the queries is needed. > > Alan DeKok. > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

1 0