Hi I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why? I have inserted a new table to my mysql for radius 'authorizedmacs'. mysql> show tables; +------------------+ | Tables_in_radius | +------------------+ | authorizedmacs | | radacct | | radcheck | | radgroupcheck | | radgroupreply | | radpostauth | | radreply | | radusergroup | +------------------+ mysql> describe authorizedmacs; +------------+----------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------------------+------+-----+-------------------+-----------------------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | macaddress | varchar(12) | NO | MUL | NULL | | | created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +------------+----------------------+------+-----+-------------------+-----------------------------+ I also added a entry in sql.conf macauth_table = "authorizedmacs" Then I created query in dailup.conf 'macuseradd_query ' ####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication ####################################################################### postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')" macuseradd_query = "INSERT INTO ${macauth_table} \ (macaddress) \ VALUES ( \ '%{Calling-Station-Id}')" It would be much appreciated if anyone can tell me why macuseradd_query isn’t run as postauth_query is. BR /Daniel
On Thu, Aug 31, 2017 at 1:46 PM, Johansson, Daniel (GIS) <Daniel.Johansson2@sony.com> wrote:
Hi I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
I also added a entry in sql.conf macauth_table = "authorizedmacs" Then I created query in dailup.conf 'macuseradd_query '
####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication #######################################################################
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
macuseradd_query = "INSERT INTO ${macauth_table} \ (macaddress) \ VALUES ( \ '%{Calling-Station-Id}')"
It would be much appreciated if anyone can tell me why macuseradd_query isn’t run as postauth_query is.
That's not how FR works. It won't call macuseradd_query simply because you put it after postauth_query. IIRC you can either: - create a stored procedure (in mysql) to write data into the two tables, and call that procedure in postauth_query. OR - use rlm_perl to perform the inserts -- Fajar
No. You can't just make up syntax. FR won't call that function because it doesn't use it that way. All you need to do is use some unlang in the post-auth section with your insert statement in a simple %sql: wrapper , no need for perl or stored procedures etc alan On 31 Aug 2017 7:47 am, "Johansson, Daniel (GIS)" < Daniel.Johansson2@sony.com> wrote:
Hi I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
I have inserted a new table to my mysql for radius 'authorizedmacs'. mysql> show tables; +------------------+ | Tables_in_radius | +------------------+ | authorizedmacs | | radacct | | radcheck | | radgroupcheck | | radgroupreply | | radpostauth | | radreply | | radusergroup | +------------------+
mysql> describe authorizedmacs; +------------+----------------------+------+-----+---------- ---------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------------------+------+-----+---------- ---------+-----------------------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | macaddress | varchar(12) | NO | MUL | NULL | | | created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +------------+----------------------+------+-----+---------- ---------+-----------------------------+
I also added a entry in sql.conf macauth_table = "authorizedmacs" Then I created query in dailup.conf 'macuseradd_query '
############################################################ ########### # Authentication Logging Queries ############################################################ ########### # postauth_query - Insert some info after authentication ############################################################ ###########
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
macuseradd_query = "INSERT INTO ${macauth_table} \ (macaddress) \ VALUES ( \ '%{Calling-Station-Id}')"
It would be much appreciated if anyone can tell me why macuseradd_query isn’t run as postauth_query is.
BR /Daniel
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On Thu, Aug 31, 2017 at 4:51 PM, Alan Buxey <alan.buxey@gmail.com> wrote:
No. You can't just make up syntax. FR won't call that function because it doesn't use it that way. All you need to do is use some unlang in the post-auth section with your insert statement in a simple %sql: wrapper , no need for perl or stored procedures etc
Didn't unlang sql with insert fail in the past, and you can only use it for select? Or has this changed? -- Fajar
On Aug 31, 2017, at 2:46 AM, Johansson, Daniel (GIS) <Daniel.Johansson2@sony.com> wrote:
I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
Why not just add a stored procedure to do multiple things? And then call the stored procedure from the postauth_query. And yes, you can't just invent things in the configuration file and expect them to do something. The documentation explains what those queries are, and when they're run. Alan DeKok.
Hi and thanks for your answers. I do this now and it works fine. post-auth { sql %{sql:INSERT INTO authorizedmacs (macaddress) VALUES ('%{Calling-Station-Id}')} } I will read the CREATE PROCEDURE and CREATE FUNCTION for mysql I haven’t worked with those before but they look handy. Maybe there could be a sql.runMyFunction ? I like to invent things :) Thanks again /Daniel -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+daniel.johansson2=sony.com@lists.freeradius.org] On Behalf Of Alan DeKok Sent: den 31 augusti 2017 13:40 To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: new table and query On Aug 31, 2017, at 2:46 AM, Johansson, Daniel (GIS) <Daniel.Johansson2@sony.com> wrote:
I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
Why not just add a stored procedure to do multiple things? And then call the stored procedure from the postauth_query. And yes, you can't just invent things in the configuration file and expect them to do something. The documentation explains what those queries are, and when they're run. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Aug 31, 2017, at 8:07 AM, Johansson, Daniel (GIS) <Daniel.Johansson2@sony.com> wrote:
I do this now and it works fine.
post-auth { sql %{sql:INSERT INTO authorizedmacs (macaddress) VALUES ('%{Calling-Station-Id}')} }
That's good.
Maybe there could be a sql.runMyFunction ?
That's what the %{sql:...} syntax does. The normal use-case is that the parameters to the insert are dynamically expanded. So any new syntax has to allow for that. It's easier to use existing syntax. Alan DeKok.
Not to confuse things but there is a way of doing that. Well out of scope and for those who are adventurous ;) However, I would advise that you wrap your SQL: unlang with an if() wrapper checking for presence of a calling-station-id - that would avoid any stage corner cases and not lead to weirdness alan On 31 Aug 2017 1:07 pm, "Johansson, Daniel (GIS)" < Daniel.Johansson2@sony.com> wrote:
Hi and thanks for your answers.
I do this now and it works fine.
post-auth { sql %{sql:INSERT INTO authorizedmacs (macaddress) VALUES ('%{Calling-Station-Id}')} }
I will read the CREATE PROCEDURE and CREATE FUNCTION for mysql I haven’t worked with those before but they look handy.
Maybe there could be a sql.runMyFunction ? I like to invent things :)
Thanks again /Daniel
-----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+daniel.johansson2= sony.com@lists.freeradius.org] On Behalf Of Alan DeKok Sent: den 31 augusti 2017 13:40 To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: new table and query
On Aug 31, 2017, at 2:46 AM, Johansson, Daniel (GIS) < Daniel.Johansson2@sony.com> wrote:
I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
Why not just add a stored procedure to do multiple things? And then call the stored procedure from the postauth_query.
And yes, you can't just invent things in the configuration file and expect them to do something. The documentation explains what those queries are, and when they're run.
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
Hi and thanks for your answers.
I do this now and it works fine.
post-auth { sql %{sql:INSERT INTO authorizedmacs (macaddress) VALUES ('%{Calling-Station-Id}')} }
I will read the CREATE PROCEDURE and CREATE FUNCTION for mysql I haven’t worked with those before but they look handy.
Maybe there could be a sql.runMyFunction ? I like to invent things :)
Thanks again /Daniel
-----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+daniel.johansson2= sony.com@lists.freeradius.org] On Behalf Of Alan DeKok Sent: den 31 augusti 2017 13:40 To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: new table and query
On Aug 31, 2017, at 2:46 AM, Johansson, Daniel (GIS) < Daniel.Johansson2@sony.com> wrote:
I use FreeRADIUS Version 2.2.6 I want to make a custom table and query for freeradius. sql is enabled in sites-enabled/default for 'post-auth {sql}' and postauth_query is populating radpostauth However my 'macuseradd_query ' is not run can anyone tell me why?
Why not just add a stored procedure to do multiple things? And then call the stored procedure from the postauth_query.
And yes, you can't just invent things in the configuration file and expect them to do something. The documentation explains what those queries are, and when they're run.
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
Thanks, i will add the if() statement it's not good as it is now. I just wanted to get something working and will make it nice now when it is working. /Daniel -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+daniel.johansson2=sony.com@lists.freeradius.org] On Behalf Of Alan Buxey Sent: den 31 augusti 2017 14:11 To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: RE: new table and query Not to confuse things but there is a way of doing that. Well out of scope and for those who are adventurous ;) However, I would advise that you wrap your SQL: unlang with an if() wrapper checking for presence of a calling-station-id - that would avoid any stage corner cases and not lead to weirdness alan On 31 Aug 2017 1:07 pm, "Johansson, Daniel (GIS)" < Daniel.Johansson2@sony.com> wrote: - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 31/08/17 13:07, Johansson, Daniel (GIS) wrote:
Hi and thanks for your answers.
I do this now and it works fine.
Hi, You've not stated what your goal with "authorizedmacs" is but for what it's worth we implemented a per-user MAC address limit by storing their authorised MAC addresses in the radcheck table like so: Calling-Station-Id =~ 0C-74-C2-46-56-A5|60-C5-47-00-88-9A|.* and we have a post-authentication script which will add new MACs to radcheck if they have not reached the limit, and email the user (and reject the authentication) if they have. (Originally we were going to remove the wildcard once they had got to their limit, which is why it has been done like this. If I were starting again I probably would have kept them in the "userinfo" table where we keep email address and so on). On the other hand if you are just storing MAC Addresses then you might want to consider storing them as 32 bit integers rather than as strings.
..or use postgresql which understands MAC addresses and will store them all normalised no matter what horrendous format the NAS sends them as :) alan On 31 August 2017 at 13:17, Dom Latter <freeradius-users@latter.org> wrote:
On 31/08/17 13:07, Johansson, Daniel (GIS) wrote:
Hi and thanks for your answers.
I do this now and it works fine.
Hi,
You've not stated what your goal with "authorizedmacs" is but for what it's worth we implemented a per-user MAC address limit by storing their authorised MAC addresses in the radcheck table like so:
Calling-Station-Id =~ 0C-74-C2-46-56-A5|60-C5-47-00-88-9A|.*
and we have a post-authentication script which will add new MACs to radcheck if they have not reached the limit, and email the user (and reject the authentication) if they have. (Originally we were going to remove the wildcard once they had got to their limit, which is why it has been done like this. If I were starting again I probably would have kept them in the "userinfo" table where we keep email address and so on).
On the other hand if you are just storing MAC Addresses then you might want to consider storing them as 32 bit integers rather than as strings.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (5)
-
Alan Buxey -
Alan DeKok -
Dom Latter -
Fajar A. Nugraha -
Johansson, Daniel (GIS)