April 2019 - Freeradius-Users - lists.freeradius.org

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

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

06 Apr '19

OK I am getting somewhere 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 ? 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 ? Thanks for the help so far ... Richard On Saturday 06/04/2019 at 7:28 pm, Arran Cudbard-Bell wrote: > > >> >> On Apr 6, 2019, at 8:18 AM, Richard J Palmer <richard(a)merula.net> >> wrote: >> >> >> HI >> >> I am looking at using this for some connections where a static IP >> address is not needed. >> >> Currently we use mssql for the SQL server for the main radius process. >> >> Looking at rlm_sqlippool there are queries for >> >> mysql >> oracle >> postgresql >> sqlite >> >> Before I start here - would this module support mssql if I create / >> tweak the queries - OR should I run a separate SQL server just for the >> IP Pools >> >> I was not 100% sure after reading the documentation. More than happy >> to adjust the SQL (Create a table / create the queries) Just wanted to >> consult here before doing that. >> >> Assuming it will work OK and I create a working config I am more than >> happy to provide it back to the project > > Yes it'll work, you just need to create the queries. > > -Arran > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html

2 1

rlm_sqlippool Question
by Richard J Palmer 06 Apr '19

06 Apr '19

HI I am looking at using this for some connections where a static IP address is not needed. Currently we use mssql for the SQL server for the main radius process. Looking at rlm_sqlippool there are queries for mysql oracle postgresql sqlite Before I start here - would this module support mssql if I create / tweak the queries - OR should I run a separate SQL server just for the IP Pools I was not 100% sure after reading the documentation. More than happy to adjust the SQL (Create a table / create the queries) Just wanted to consult here before doing that. Assuming it will work OK and I create a working config I am more than happy to provide it back to the project Thanks in advance Richard

2 1

3.0.18: operator = not behaving as expected after proxying to virtual server
by stefan.winter＠restena.lu 06 Apr '19

06 Apr '19

Hello, I have a setup where a proxied-to virtual server sets the attribute Session-Timeout with := and then the calling server sets it subsidiary to a different value with = in post-proxy. The reply_log for both servers shows that the first value is set ok, but the second = operator *adds* the Session-Timeout attribute a second time. This effectively leads to FreeRADIUS sending back a malformed packet because it now contains two Session-Timeout attributes. The reply_log/post_proxy_log contents look like this: Sat Apr 6 12:17:25 2019 Packet-Type = Access-Reject Session-Timeout = 604800 <-- as set with := Timestamp = 1554545845 Sat Apr 6 12:17:25 2019 Packet-Type = Access-Reject Cisco-Account-Info = "ASERVICE_INTERNET-DEFAULT" Session-Timeout = 86400 <-- extra addition in post-proxy Idle-Timeout = 7200 Session-Timeout = 604800 <-- should prevail Timestamp = 1554545845 The post-proxy configuration of the caller is: post-proxy { update reply { Cisco-Account-Info = "ASERVICE_INTERNET-DEFAULT", Session-Timeout = 86400, Idle-Timeout = 7200 } post_proxy_log } This seems to be a bug: Session timeout is already set, and the = operator should refrain from changing the existing value. And also, it should detect that it is doing something non-RFC compliant by adding it twice. The actual reply as seen by radtest is: Received Access-Reject Id 196 from 127.0.0.1:1812 to 127.0.0.1:49664 length 71 Cisco-Account-Info = "ASERVICE_INTERNET-DEFAULT" Session-Timeout = 86400 Idle-Timeout = 7200 Session-Timeout = 604800 (0) -: Expected Access-Accept got Access-Reject So the incorrect attribute list actually made it onto the wire. Greetings, Stefan Winter

3 3