1.1.7 sqlippool %{SQL-User-Name}
Peter . as per your postgres 1.1.7 sqlippool queries, I changed the MySQL ones to use %{SQL-User-Name} instead of %{User-Name} . only it doesn't seem to pick up a value, so the UserName is coming up blank in the radippool table. Example: sqlippool_expand: 'UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE NASIPAddress = '%{Nas-IP-Address}' AND pool_key = '%{Calling-Station-Id}' AND UserName = '%{SQL-User-Name}' AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress = '%{Framed-IP-Address}'' radius_xlat: 'UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE NASIPAddress = '216.108.219.36' AND pool_key = '00:14:6C:37:16:49' AND UserName = '' AND CallingStationId = '00:14:6C:37:16:49' AND FramedIPAddress = '172.168.124.120'' This happens on both 1.1.6 and 1.1.7. Should that be something like %{control:SQL-User-Name} ? My apologies - I don't know how I missed this when testing yesterday. I guess it returned an IP just fine, so I didn't actually look at what it was doing too closely! -- hugh
On Tue 17 Jul 2007, Hugh Messenger wrote:
Peter . as per your postgres 1.1.7 sqlippool queries, I changed the MySQL ones to use %{SQL-User-Name} instead of %{User-Name} . only it doesn't seem to pick up a value, so the UserName is coming up blank in the radippool table.
Alan. Can you help out here? From memory I am seeing the same thing in cvs head. I ended up commenting out the username part of the query as I don't actually do anything based on username in my system. It definitely needs to be %{SQL-User-Name} though, as I was getting escape characters as the username from some users and it was blowing up the sql queries. (HUGE GAPPING SECURITY HOLE) Is there something special we need to do in rlm_sqlippool to get access to %{SQL-User-Name}? Cheers -- Peter Nixon http://peternixon.net/
Peter Nixon wrote:
Alan. Can you help out here? From memory I am seeing the same thing in cvs head. I ended up commenting out the username part of the query as I don't actually do anything based on username in my system. It definitely needs to be %{SQL-User-Name} though, as I was getting escape characters as the username from some users and it was blowing up the sql queries. (HUGE GAPPING SECURITY HOLE)
Is there something special we need to do in rlm_sqlippool to get access to %{SQL-User-Name}?
Yes. Call sql_set_user(). Patch is attached. Also, the sqlippool_expand() function could be done better. The use of single-character values is awkward. Instead, it should register an xlat() function, to allow things like %{sqlippool:Pool-Name}. Hmm... that could be in the server core, come to think of it. Alan DeKok.
On Tue 17 Jul 2007, Alan DeKok wrote:
Peter Nixon wrote:
Alan. Can you help out here? From memory I am seeing the same thing in cvs head. I ended up commenting out the username part of the query as I don't actually do anything based on username in my system. It definitely needs to be %{SQL-User-Name} though, as I was getting escape characters as the username from some users and it was blowing up the sql queries. (HUGE GAPPING SECURITY HOLE)
Is there something special we need to do in rlm_sqlippool to get access to %{SQL-User-Name}?
Yes. Call sql_set_user(). Patch is attached.
Hugh I have applied Alan's patch to the 1.1.x branch. Can you test and see if %{SQL-User-Name} works in rlm_sqlippool for MySQL now? Cheers -- Peter Nixon http://peternixon.net/
Peter Nixon [mailto:listuser@peternixon.net] said:
I have applied Alan's patch to the 1.1.x branch. Can you test and see if %{SQL-User-Name} works in rlm_sqlippool for MySQL now?
Doesn't seem to. I updated cvs: -rw-r--r-- 1 root root 25278 Jul 17 10:30 rlm_sqlippool.c ... and did the usual make, make install and server restart, but I still see this: sqlippool_expand: 'UPDATE radippool SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', CallingStationId = '%{Calling-Station-Id}', UserName = '%{SQL-User-Name}', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE FramedIPAddress = '172.168.34.205'' radius_xlat: 'UPDATE radippool SET NASIPAddress = 'x.x.x.x', pool_key = '00:1A:70:57:91:C1', CallingStationId = '00:1A:70:57:91:C1', UserName = '', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE FramedIPAddress = '172.168.34.205'' I don't know if it makes any difference, but whereas I see this whenever rlm_sql does something: rlm_sql (sql): sql_set_user escaped user --> '3notchmuseum' ... I don't see anything to that effect when rlm_sqlippool does its stuff.
Peter Nixon http://peternixon.net/
-- hugh
I don't know if it makes any difference, but whereas I see this whenever rlm_sql does something:
rlm_sql (sql): sql_set_user escaped user --> '3notchmuseum'
... I don't see anything to that effect when rlm_sqlippool does its stuff.
BTW, something I noticed in the code, although it calls sql_set_user in sqlippool_query1, it doesn't call it in sqlippool_command. However, I'm not even seeing the SQL-User-Name in SELECT's (which I think is the difference ... selects go thru sqlippool_query1, updates go thru sqlippool_command), which I would have expected to work.
Peter Nixon http://peternixon.net/
-- hugh
Replying to myself again! -- hugh
On Tue 17 Jul 2007, Hugh Messenger wrote:
I don't know if it makes any difference, but whereas I see this whenever rlm_sql does something:
rlm_sql (sql): sql_set_user escaped user --> '3notchmuseum'
... I don't see anything to that effect when rlm_sqlippool does its stuff.
BTW, something I noticed in the code, although it calls sql_set_user in sqlippool_query1, it doesn't call it in sqlippool_command.
However, I'm not even seeing the SQL-User-Name in SELECT's (which I think is the difference ... selects go thru sqlippool_query1, updates go thru sqlippool_command), which I would have expected to work.
Good eyes. Hopefully it should work now. -- Peter Nixon http://peternixon.net/
Peter Nixon said:
Good eyes. Hopefully it should work now.
Erm ... nope ... [root@PMX66 radiusd]# pwd /usr/local/src/freeradius/radiusd [root@PMX66 radiusd]# cvs update [blah] [root@PMX66 radiusd]# ls -l src/modules/rlm_sqlippool/rlm_sqlippool.c -rw-r--r-- 1 root root 25416 Jul 17 13:53 [root@PMX66 radiusd]# make [blah] [root@PMX66 radiusd]# make install [blah] [root@PMX66 radiusd]# ls -l `which radiusd` -rwxr-xr-x 1 root root 346903 Jul 17 13:54 /usr/local/sbin/radiusd [root@PMX66 radiusd]# radiusd -X [mucho blah] Ready to process requests. rad_recv: Access-Request packet from host x.x.x.x:2829, id=50, length=93 User-Name = "radiustest" User-Password = "test123" NAS-Port = 123456 NAS-IP-Address = x.x.x.x Calling-Station-Id = "00:60:B3:45:6A:98" Called-Station-Id = "rht900_124" Processing the authorize section of radiusd.conf modcall: entering group post-auth for request 0 Value Of the Pool-Name is [rht900_124] and its [10] Chars rlm_sql (sql): Reserving sql socket id: 2 sqlippool_expand: 'BEGIN' radius_xlat: 'BEGIN' sqlippool_expand: 'UPDATE radippool SET NASIPAddress = '', pool_key = 0, CallingStationId = '', expiry_time = NOW() - INTERVAL 1 SECOND WHERE pool_key = '%{Calling-Station-Id}' AND NASIPAddress = '%{NAS-IP-Address}'' radius_xlat: 'UPDATE radippool SET NASIPAddress = '', pool_key = 0, CallingStationId = '', expiry_time = NOW() - INTERVAL 1 SECOND WHERE pool_key = '00:60:B3:45:6A:98' AND NASIPAddress = 'x.x.x.x'' sqlippool_expand: 'SELECT FramedIPAddress FROM radippool WHERE pool_name = 'rht900_124' AND expiry_time < NOW() ORDER BY (UserName <> '%{SQL-User-Name}'), (CallingStationId <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE' radius_xlat: 'SELECT FramedIPAddress FROM radippool WHERE pool_name = 'rht900_124' AND expiry_time < NOW() ORDER BY (UserName <> ''), (CallingStationId <> '00:60:B3:45:6A:98'), expiry_time LIMIT 1 FOR UPDATE' rlm_sqlippool: ip=[172.168.124.52] len=14 sqlippool_expand: 'UPDATE radippool SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', CallingStationId = '%{Calling-Station-Id}', UserName = '%{SQL-User-Name}', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE FramedIPAddress = '172.168.124.52'' radius_xlat: 'UPDATE radippool SET NASIPAddress = 'x.x.x.x', pool_key = '00:60:B3:45:6A:98', CallingStationId = '00:60:B3:45:6A:98', UserName = '', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE FramedIPAddress = '172.168.124.52'' rlm_sqlippool: Allocated IP 172.168.124.52 [347ca8ac] sqlippool_expand: 'COMMIT' radius_xlat: 'COMMIT' rlm_sql (sql): Released sql socket id: 2 modcall[post-auth]: module "sqlippool" returns ok for request 0 rlm_sql (sql): Processing sql_postauth radius_xlat: 'radiustest' rlm_sql (sql): sql_set_user escaped user --> 'radiustest' radius_xlat: 'INSERT into radpostauth (user, pass, reply, date) values ('radiustest', 'xxxxxxxx', 'Access-Accept', NOW())' rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (user, pass, reply, date) values ('radiustest', 'xxxxxxxx', 'Access-Accept', NOW()) On the bright side, the rlm_sql postauth_query now works Out Of Box. :-)
Peter Nixon http://peternixon.net/
-- hugh
I said:
Peter Nixon said:
Good eyes. Hopefully it should work now.
Erm ... nope ...
Ignore me. It works: radius_xlat: 'BEGIN' radius_xlat: 'radiustest' rlm_sql (sql): sql_set_user escaped user --> 'radiustest' radius_xlat: 'UPDATE radippool SET NASIPAddress = '', pool_key = 0, CallingStationId = '', expiry_time = NOW() - INTERVAL 1 SECOND WHERE NASIPAddress = 'x.x.x.x' AND pool_key = '00:60:B3:45:6A:98' AND UserName = 'radiustest' AND CallingStationId = '00:60:B3:45:6A:98' AND FramedIPAddress = '172.168.124.52'' Can we add sqlippool to the ./modules/stable list? (embarrassed <cough>) -- hugh
On Tue 17 Jul 2007, Hugh Messenger wrote:
I said:
Peter Nixon said:
Good eyes. Hopefully it should work now.
Erm ... nope ...
Ignore me. It works:
radius_xlat: 'BEGIN' radius_xlat: 'radiustest' rlm_sql (sql): sql_set_user escaped user --> 'radiustest' radius_xlat: 'UPDATE radippool SET NASIPAddress = '', pool_key = 0, CallingStationId = '', expiry_time = NOW() - INTERVAL 1 SECOND WHERE NASIPAddress = 'x.x.x.x' AND pool_key = '00:60:B3:45:6A:98' AND UserName = 'radiustest' AND CallingStationId = '00:60:B3:45:6A:98' AND FramedIPAddress = '172.168.124.52''
Heh. Good. Because I was getting confused too ;-)
Can we add sqlippool to the ./modules/stable list?
It is in the stable list for 2.0 but its up to Alan whether we put it in for 1.1.7
(embarrassed <cough>)
*grin* -- Peter Nixon http://peternixon.net/
Peter Nixon quoth:
On Tue 17 Jul 2007, Hugh Messenger wrote:
Can we add sqlippool to the ./modules/stable list?
It is in the stable list for 2.0 but its up to Alan whether we put it in for 1.1.7
It's been pretty darn stable for me in 1.1.6. And now we've gotten the MySQL stuff whipped into shape and fixed a few other issues for 1.1.7, I'd say it's ready for Prime Time. Alan?
Peter Nixon http://peternixon.net/
-- hugh
On Wed 18 Jul 2007, Hugh Messenger wrote:
Peter Nixon quoth:
On Tue 17 Jul 2007, Hugh Messenger wrote:
Can we add sqlippool to the ./modules/stable list?
It is in the stable list for 2.0 but its up to Alan whether we put it in for 1.1.7
It's been pretty darn stable for me in 1.1.6. And now we've gotten the MySQL stuff whipped into shape and fixed a few other issues for 1.1.7, I'd say it's ready for Prime Time.
OK. Thats good enough for me. I have added it :-) -- Peter Nixon http://peternixon.net/
Hugh Messenger wrote:
It's been pretty darn stable for me in 1.1.6. And now we've gotten the MySQL stuff whipped into shape and fixed a few other issues for 1.1.7, I'd say it's ready for Prime Time.
Alan?
I have that Internet thing working again, so yes. Tomorrow looks good. Alan DeKok.
On Tue 17 Jul 2007, Alan DeKok wrote:
Also, the sqlippool_expand() function could be done better. The use of single-character values is awkward. Instead, it should register an xlat() function, to allow things like %{sqlippool:Pool-Name}.
Heh. I never said it was beautiful, just that it works (mostly) :-)
Hmm... that could be in the server core, come to think of it.
I'm not territorial. Please feel free to rewrite it any way you see fit. :-) I think we can probably leave it as is for 1.1.7 though... Cheers -- Peter Nixon http://peternixon.net/
On Tue 17 Jul 2007, Alan DeKok wrote:
Peter Nixon wrote:
Alan. Can you help out here? From memory I am seeing the same thing in cvs head. I ended up commenting out the username part of the query as I don't actually do anything based on username in my system. It definitely needs to be %{SQL-User-Name} though, as I was getting escape characters as the username from some users and it was blowing up the sql queries. (HUGE GAPPING SECURITY HOLE)
Is there something special we need to do in rlm_sqlippool to get access to %{SQL-User-Name}?
Yes. Call sql_set_user(). Patch is attached.
Also, the sqlippool_expand() function could be done better. The use of single-character values is awkward. Instead, it should register an xlat() function, to allow things like %{sqlippool:Pool-Name}.
Hmm... that could be in the server core, come to think of it.
As it turns out I was just looking at rlm_sqlcounter and it has a sqlcounter_expand() function which is very similar. I think if we were to go through rlm_sql, rlm_sqlcounter and rlm_sqlippool there is a fair bit that could be refactored.. Cheers -- Peter Nixon http://peternixon.net/
participants (3)
-
Alan DeKok -
Hugh Messenger -
Peter Nixon