hello, type is possible to insert one new query in the rows sql.conf this: postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" modified in this way with name postauth_query_mac: postauth_query_mac = "INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}' where UserName != '%{SQL-User-Name}'and Attribute != 'Calling-Station-Id' )" where I can find the rows for invocare the new query? Thanks Regards
Please find here below a correction of the previous post in "understandable English" format ;-) Hi all, I have installed freeRADIUS with mySQL backend. Into sql.conf there are defined several queries, on of these is the following: postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" based on the previous one, I would like to build a new query to insert user's MAC address into radcheck table, as users log-out (accounting-stop packet). The query is as follows: MAC_insert_query = "INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}' where UserName != '%{SQL-User-Name}'and Attribute != 'Calling-Station-Id' )"
From freeRADIUS logs (debug mode) I can see that newly defined query is not recognized by freeRADIUS: no logs in produced for MAC_insert_query.
How can I let freeRADIUS accept the new defined query? Thank you in advance Best regards
I have append the query to the existing ones, but without it turns out to you. postauth_mac_query = "INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}')" postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" in the log of start you come only loaded the postauth_query: sql: group_membership_query = "SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}'" sql: connect_failure_retry_delay = 60 sql: simul_count_query = "" sql: simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM radacct WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0" sql: postauth_table = "radpostauth" sql: postauth_query = "INSERT into radpostauth (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" it does not appear and consequently it does not work: ( Solutions? Regards Inviato: giovedì 26 gennaio 2006 18.41 A: FreeRadius users mailing list Oggetto: Re: R: SQL.conf new query
I would like to build a new query to insert user's MAC address into radcheck table, as users log-out (accounting-stop packet).
Just append the query to the existing ones. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thank you Alan for your reply. As written by Paolo, we simply added a query (postauth_mac_query) to sql.conf file that gives back users MAC address: ........... ........... postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" postauth_mac_query = "INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}')" The query was build on the model of the postauth_query one, so no problems should arise about its syntax. In fact, if we change the content of postauth_query with the one of post_mac_query, it works fine (MAC address is inserted into radcheck table). The problem is that it seems that freeRADIUS does not recognize the new defined query (postauth_mac_query) in fact, looking at debug output, we can see calls to all other queries but not to the new one. So the question is: how let freeRADIUS understand when to call each single query defined into sql.conf file? Any advice? Regadrs, Carlo -----Messaggio originale----- Da: freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org [mailto:freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius. org] Per conto di Paolo Pellicori Inviato: venerdì 27 gennaio 2006 10.12 A: 'FreeRadius users mailing list' Oggetto: R: R: SQL.conf new query I have append the query to the existing ones, but without it turns out to you. postauth_mac_query = "INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}')" postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" in the log of start you come only loaded the postauth_query: sql: group_membership_query = "SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}'" sql: connect_failure_retry_delay = 60 sql: simul_count_query = "" sql: simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM radacct WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0" sql: postauth_table = "radpostauth" sql: postauth_query = "INSERT into radpostauth (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" it does not appear and consequently it does not work: ( Solutions? Regards Inviato: giovedì 26 gennaio 2006 18.41 A: FreeRadius users mailing list Oggetto: Re: R: SQL.conf new query
I would like to build a new query to insert
user's MAC address into radcheck table, as users log-out (accounting-stop
packet).
Just append the query to the existing ones. 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 __________ NOD32 1.1381 (20060126) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com
"Carlo Prestopino" <c.prestopino@waitalia.com> wrote:
The problem is that it seems that freeRADIUS does not recognize the new defined query (postauth_mac_query) in fact, looking at debug output, we can see calls to all other queries but not to the new one.
The source code to the module contains the names of the queries it looks for in the configuration file. All other queries are ignored. If you want an additional query, edit the source code. Alan DeKok.
Would seperating the queries with a semicolon work, but keeping both queries under postauth_query? SQL should distinguish it as a seperate query. I haven't tried this so I am not sure if it would work or not. How about something like this? postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW()) ; INSERT into ${authcheck_table} (UserName, Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id', ':=', '%{Calling-Station-Id}')" It seems a lot easier than changing the source code and recompiling... Hope this helps! Richard Carlo Prestopino wrote:
Thank you Alan for your reply.
As written by Paolo, we simply added a query (postauth_mac_query) to sql.conf file that gives back user’s MAC address:
...........
...........
postauth_query = "INSERT into ${postauth_table} (id, user, pass,
reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}',
'%{reply:Packet-Type}', NOW())"
postauth_mac_query = "INSERT into ${authcheck_table} (UserName,
Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id',
':=', '%{Calling-Station-Id}')"
The query was build on the model of the postauth_query one, so no problems should arise about its syntax. In fact, if we change the content of postauth_query with the one of post_mac_query, it works fine (MAC address is inserted into radcheck table).
The problem is that it seems that freeRADIUS does not recognize the new defined query (postauth_mac_query) in fact, looking at debug output, we can see calls to all other queries but not to the new one.
So the question is: how let freeRADIUS understand when to call each single query defined into sql.conf file?
Any advice?
Regadrs,
Carlo
-----Messaggio originale----- Da: freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org [mailto:freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org] Per conto di Paolo Pellicori Inviato: venerdì 27 gennaio 2006 10.12 A: 'FreeRadius users mailing list' Oggetto: R: R: SQL.conf new query
I have append the query to the existing ones, but without it turns out to
you.
postauth_mac_query = "INSERT into ${authcheck_table} (UserName,
Attribute, op, Value) VALUES ('%{SQL-User-Name}'', 'Calling-Station-Id',
':=', '%{Calling-Station-Id}')"
postauth_query = "INSERT into ${postauth_table} (id, user, pass,
reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}',
'%{reply:Packet-Type}', NOW())"
in the log of start you come only loaded the postauth_query:
sql: group_membership_query = "SELECT GroupName FROM usergroup WHERE
UserName='%{SQL-User-Name}'"
sql: connect_failure_retry_delay = 60
sql: simul_count_query = ""
sql: simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol
FROM radacct WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
sql: postauth_table = "radpostauth"
sql: postauth_query = "INSERT into radpostauth (id, user, pass, reply,
date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}',
'%{reply:Packet-Type}', NOW())"
it does not appear and consequently it does not work: (
Solutions?
Regards
Inviato: giovedì 26 gennaio 2006 18.41
A: FreeRadius users mailing list
Oggetto: Re: R: SQL.conf new query
I would like to build a new query to insert
user's MAC address into radcheck table, as users log-out (accounting-stop
packet).
Just append the query to the existing ones.
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
__________ NOD32 1.1381 (20060126) Information __________
This message was checked by NOD32 antivirus system.
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Richard Marriner II Maingear.Net Sr. Network Consultant I.T. Consulting richard@maingear.net www.maingear.net
Rich Marriner <richard@maingear.net> wrote:
Would seperating the queries with a semicolon work, but keeping both queries under postauth_query? SQL should distinguish it as a seperate query. I haven't tried this so I am not sure if it would work or not.
That's what I thought I suggested earlier. Alan DeKok.
That is what I thought but wasn't sure, I don't think the original poster realized this though. I was directing this mostly toward him. Thanks! Alan DeKok wrote:
Rich Marriner <richard@maingear.net> wrote:
Would seperating the queries with a semicolon work, but keeping both queries under postauth_query? SQL should distinguish it as a seperate query. I haven't tried this so I am not sure if it would work or not.
That's what I thought I suggested earlier.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thank you both Richard/Alan, I'll try it on Monday when at office and let you know. Regards, Carlo
I've tried to insert a semicolon after the first query, but it didn't work, so I'm trying to modify the source code. If I correctly understood, I have to modify the file rlm_sql, creating an entry also for the new defined query (e.g. mac_auth_query). May I use as sample postauth_query? Could you please give me some indication on how this file needs to be modified and if it is the only one that I need to change? Best regards, Carlo
Carlo Prestopino wrote:
I've tried to insert a semicolon after the first query, but it didn't work, so I'm trying to modify the source code.
There is no need to modify the source code. You can run two SQL statements if you have two instances of rlm_sql module.
If I correctly understood, I have to modify the file rlm_sql, creating an entry also for the new defined query (e.g. mac_auth_query).
May I use as sample postauth_query? Could you please give me some indication on how this file needs to be modified and if it is the only one that I need to change?
If you don't want several module instances, you can use a semicolon in a SQL statement with a one line change in sql_mysql.c (I've explained this already in a previous mail) -- Nicolas Baradakis
Ok, problem solved, as you can see at this post http://lists.freeradius.org/pipermail/freeradius-devel/2006-February/009446. html Thank you to everyone Regards, Carlo
Rich Marriner wrote:
Would seperating the queries with a semicolon work, but keeping both queries under postauth_query? SQL should distinguish it as a seperate query. I haven't tried this so I am not sure if it would work or not.
I guess it won't work because FreeRADIUS doesn't use the CLIENT_MULTI_STATEMENTS option when connecting to the MySQL database. http://dev.mysql.com/doc/refman/4.1/en/mysql-real-connect.html -- Nicolas Baradakis
Thats too bad. I suppose that in 99% of the installations you would actually want that behavior anyway. Nicolas Baradakis wrote:
Rich Marriner wrote:
Would seperating the queries with a semicolon work, but keeping both queries under postauth_query? SQL should distinguish it as a seperate query. I haven't tried this so I am not sure if it would work or not.
I guess it won't work because FreeRADIUS doesn't use the CLIENT_MULTI_STATEMENTS option when connecting to the MySQL database.
http://dev.mysql.com/doc/refman/4.1/en/mysql-real-connect.html
participants (5)
-
Alan DeKok -
Carlo Prestopino -
Nicolas Baradakis -
Paolo Pellicori -
Rich Marriner