Hi, I know this is new, and not yet documented, but I saw some good posts about it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping. doc/rlm_sqlippool states: The only required fields are, pool_name and ip_address. A pool consists of one or more rows in the table with the same pool_name and a different ip_address. The is no restriction on which ip addresses/ranges may be in the same pool, and addresses do not need to be concurrent. Yet, raddb/sqlippool.conf, makes absolutely NO sense to me at the moment at all, and there is WAY more than merely a pool name and a IP address referenced in the queries... I understand that there is some unique elements required in the table to indicate that a IP is allocated, and to know where the IP is allocated (and obviously to release that IP once the session terminates). Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :( Regards, Chris. PS: FR 1.1.3, is the NAS tables being used yet in this release??
"Chris Knipe" <savage@savage.za.org> wrote:
I know this is new, and not yet documented, but I saw some good posts about it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping.
Well, yes. I've spent 5 minutes looking at it, and got the basic idea of "SQL" and "IP pools". :)
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The general idea is to... grab IP's from a pool, based on a unique key. If an allocated IP for that key doesn't exist, then a free IP is taken, and the key written to the table. As for additional elements in the queries, the schema was changed from the original one, so the queries might be old. But you're right, it really needs a flow diagram, with explanations of what happens when. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The general idea is to... grab IP's from a pool, based on a unique key. If an allocated IP for that key doesn't exist, then a free IP is taken, and the key written to the table.
Fair enough, and yes, that bit I figured out as well.... Quick debug output however: Value Of the Pool-Name is [MYPOOL] and its [6] Chars rlm_sql (sql): Reserving sql socket id: 10 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #10 rlm_sql_mysql: Starting connect to MySQL server for #10 rlm_sql (sql): Connected new DB handle, #10 radius_xlat: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE pool_key = '00:0F:EA:61:0F:B3'' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_command: database query error radius_xlat: 'SELECT framedipaddress FROM radippool WHERE pool_name = '' AND expiry_time < 'now'::timestamp(0) ORDER BY pool_name, (username <> 'user@domain.com'), (callingstationid <> '00:0F:EA:61:0F:B3'), expiry_time LIMIT 1 FOR UPDATE' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_query1: database query error rlm_sqlippool: ip=[] len=0 radius_xlat: 'COMMIT' rlm_sqlippool: IP number could not be allocated. rlm_sql (sql): Released sql socket id: 10 Ok, now sure... We have SQL errors because the tables doesn't even exist in the database (that is not why I'm asking about how the module works). I use a rather complex system, and it's important that it's integrated correctly. Looking at the UPDATE and the SELECT queries above... They are completely invalid. It's not even propper SQL syntax... UNLESS, expiry time *should* be a varchar instead of a datetime field - which again makes the database very big. Let's face it, a varchar uses a lot more space than a datetime, or even a int - should UNIX_TIMESTAMP be used instead. I *suppose* what it is trying to do, is that it saw that a user is trying to authenticate, and then it tried via the UPDATE to release any possible IP address which may have been allocated (I must admit, that is actually very clever!). It then proceeded to attempt to get a new dymaic IP address from the pool, but completely disregarded the pool-name ??? The config is stock standard, query in the config: allocate-find = "SELECT framedipaddress FROM radippool \ WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \ ORDER BY pool_name, (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \ LIMIT 1 \ FOR UPDATE" Should it not use the *check* attribute variable for the pool name (Pool-Name is, surely, a Check item and not a Reply item)??? Why also does timestamp(0) not expand? Is timestamp(0) a valid variable (It's not in variables.txt), and what is this supposed to be format wise? Can it be substituted with MySQL's NOW() instead? Not ranting or anything Alan, I'll figure this out... But even the example configuration file needs to get a serious kick up the rear.... It's invalid as it ships standard with the distribution tarball, and I'm possitive, it will confuse the living daylight out of users that is not so experienced.... -- C
On Sun 27 Aug 2006 01:47, Chris Knipe wrote:
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The general idea is to... grab IP's from a pool, based on a unique key. If an allocated IP for that key doesn't exist, then a free IP is taken, and the key written to the table.
Fair enough, and yes, that bit I figured out as well.... Quick debug output however:
Value Of the Pool-Name is [MYPOOL] and its [6] Chars rlm_sql (sql): Reserving sql socket id: 10 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #10 rlm_sql_mysql: Starting connect to MySQL server for #10 rlm_sql (sql): Connected new DB handle, #10 radius_xlat: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE pool_key = '00:0F:EA:61:0F:B3'' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_command: database query error radius_xlat: 'SELECT framedipaddress FROM radippool WHERE pool_name = '' AND expiry_time < 'now'::timestamp(0) ORDER BY pool_name, (username <> 'user@domain.com'), (callingstationid <> '00:0F:EA:61:0F:B3'), expiry_time LIMIT 1 FOR UPDATE' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_query1: database query error rlm_sqlippool: ip=[] len=0 radius_xlat: 'COMMIT' rlm_sqlippool: IP number could not be allocated. rlm_sql (sql): Released sql socket id: 10
Ok, now sure... We have SQL errors because the tables doesn't even exist in the database (that is not why I'm asking about how the module works). I use a rather complex system, and it's important that it's integrated correctly.
Looking at the UPDATE and the SELECT queries above... They are completely invalid. It's not even propper SQL syntax... UNLESS, expiry time *should* be a varchar instead of a datetime field - which again makes the database very big. Let's face it, a varchar uses a lot more space than a datetime, or even a int - should UNIX_TIMESTAMP be used instead.
It is very unlikely that it will work with MySQL. It is designed for Postgresql. -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Looking at the UPDATE and the SELECT queries above... They are completely invalid. It's not even propper SQL syntax... UNLESS, expiry time *should* be a varchar instead of a datetime field - which again makes the database very big. Let's face it, a varchar uses a lot more space than a datetime, or even a int - should UNIX_TIMESTAMP be used instead.
It is very unlikely that it will work with MySQL. It is designed for Postgresql.
Classic homer simpson style... *DOH* Guess I'll have to sit back and wait for mysql support then... This will also explain my other post then about not being able to reconnect to the databases I suppose... -- C
Hmm. There seems to also be a bug in the code. If one of the mysql sockets to the database goes down, rlm_sqlippool won't reconnect to the database handles... Debug below for 2 auth requests... mySQL Logs show that the connection attempt does not even come through. Debug logs show that the username of the SQL connection string is incorrect. The radius server connects as user 'radius', bug debug shows it attempts to connect as 0164 And ty BTW Alan, think I'm starting to get the hang on what's going on irt the configuration of the module :-) Regards, Chris. rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 91 NAS-Port-Type = Ethernet User-Name = "username@domain.com" Calling-Station-Id = "00:0F:EA:61:0F:B3" Called-Station-Id = "NAS01" NAS-Port-Id = "Server Network" MS-CHAP-Domain = "domain.com" User-Password = "password" NAS-Identifier = "NAS01" NAS-IP-Address = 192.168.1.20 Mikrotik-Realm = "domain.com" rad_lowerpair: User-Name now 'username@domain.com' rad_lowerpair: User-Password now 'password' rad_rmspace_pair: User-Name now 'username@domain.com' rad_rmspace_pair: User-Password now 'password' <SNIP> rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 5 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #5 rlm_sql_mysql: Starting connect to MySQL server for #5 rlm_sql (sql): Connected new DB handle, #5 radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' rlm_sql (sql): Released sql socket id: 5 modcall[authorize]: module "sql" returns ok for request 6 modcall: leaving group authorize (returns ok) for request 6 <SNIP> radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #4 rlm_sql_mysql: Starting connect to MySQL server for #4 rlm_sql (sql): Connected new DB handle, #4 rlm_sql (sql): Released sql socket id: 4 modcall[session]: module "sql" returns ok for request 6 modcall: leaving group session (returns ok) for request 6 Login OK: [username@domain.com] (from client NAS01 port 91 cli 00:0F:EA:61:0F:B3) <SNIP> Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 6 Value Of the Pool-Name is [6d9a0ffb-8330-1029-8ba8-00005e000164] and its [36] Chars rlm_sql (sql): Reserving sql socket id: 6 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error <DOES NOT RECONNECT> radius_xlat: 'SQL QUERY' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error <STILL NOT> radius_xlat: 'SQL QUERY' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_query1: database query error rlm_sqlippool: ip=[] len=0 radius_xlat: 'COMMIT' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error <STILL DOWN> rlm_sqlippool: IP number could not be allocated. rlm_sql (sql): Released sql socket id: 6 modcall[post-auth]: module "sqlippool" returns noop for request 6 <SNIP> Sending Access-Accept of id 212 to 192.168.1.20 port 1071 Acct-Interim-Interval := 3600 Class := 0x574c414e Framed-Netmask := 255.255.255.255 Framed-Protocol := PPP Framed-Routing := Broadcast-Listen MS-Primary-DNS-Server := 36.0.18.198 MS-Secondary-DNS-Server := 4.0.18.198 Service-Type := Framed-User Finished request 6 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Sending duplicate reply to client NAS01:1071 - ID: 212 Re-sending Access-Accept of id 212 to 192.168.1.20 port 1071 --- Walking the entire request list --- Cleaning up request 6 ID 212 with timestamp 44f0ebc5 Nothing to do. Sleeping until we see a request. rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 91 NAS-Port-Type = Ethernet User-Name = "username@domain.com" Calling-Station-Id = "00:0F:EA:61:0F:B3" Called-Station-Id = "NAS01" NAS-Port-Id = "Server Network" MS-CHAP-Domain = "domain.com" User-Password = "password" NAS-Identifier = "NAS01" NAS-IP-Address = 192.168.1.20 Mikrotik-Realm = "domain.com" <SNIP> radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #3 rlm_sql_mysql: Starting connect to MySQL server for #3 rlm_sql (sql): Connected new DB handle, #3 <IMMEDIATELY RECONNECTS> radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' <SNIP> radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 2 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #2 rlm_sql_mysql: Starting connect to MySQL server for #2 rlm_sql (sql): Connected new DB handle, #2 <RECONNECTS> rlm_sql (sql): Released sql socket id: 2 modcall[session]: module "sql" returns ok for request 7 modcall: leaving group session (returns ok) for request 7 Login OK: [username@domain.com] (from client NAS01 port 91 cli 00:0F:EA:61:0F:B3) Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 7 Value Of the Pool-Name is [6d9a0ffb-8330-1029-8ba8-00005e000164] and its [36] Chars rlm_sql (sql): Reserving sql socket id: 5 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #5 rlm_sql_mysql: Starting connect to MySQL server for #5 <FAILS TO RECONNECT>
Hi Chris Just to summarise regarding the rlm_sqlippool module as it exists in FreeRADIUS 1.1.3 It is ONLY tested on Postgresql 8.1.x (On 32bit and 64bit SUSE Linux) although I expect it should work fine on any 8.X version of postgresql and probably earlier versions as well. If you want to run it on a different database, the driver itself should support it, but you will need to modify the schema and the queries. If you get it working please let us know (And send us your schema and queries) We have done a fair bit of load testing and the code seems to be solid on Postgres 8.1.x This included us writing a python based radius client which we ran on a powerfull Operton server hammering away at FreeRADIUS + Postgresql on a 1GHz P111 server as a load test. With the current code we could not force it to hand out duplicate IP addresses or otherwise fail (Unless the pool runs out of addresses). The code is in operation on a production FreeRADIUS cluster with a shared DB backend so it also appears to be clean in that respect. The module uses your existing DB connection defined in postgresl.conf Regards Peter On Sun 27 Aug 2006 04:03, Chris Knipe wrote:
Hmm.
There seems to also be a bug in the code. If one of the mysql sockets to the database goes down, rlm_sqlippool won't reconnect to the database handles... Debug below for 2 auth requests...
mySQL Logs show that the connection attempt does not even come through. Debug logs show that the username of the SQL connection string is incorrect. The radius server connects as user 'radius', bug debug shows it attempts to connect as 0164
And ty BTW Alan, think I'm starting to get the hang on what's going on irt the configuration of the module :-)
Regards, Chris.
rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 91 NAS-Port-Type = Ethernet User-Name = "username@domain.com" Calling-Station-Id = "00:0F:EA:61:0F:B3" Called-Station-Id = "NAS01" NAS-Port-Id = "Server Network" MS-CHAP-Domain = "domain.com" User-Password = "password" NAS-Identifier = "NAS01" NAS-IP-Address = 192.168.1.20 Mikrotik-Realm = "domain.com" rad_lowerpair: User-Name now 'username@domain.com' rad_lowerpair: User-Password now 'password' rad_rmspace_pair: User-Name now 'username@domain.com' rad_rmspace_pair: User-Password now 'password'
<SNIP>
rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 5 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #5 rlm_sql_mysql: Starting connect to MySQL server for #5 rlm_sql (sql): Connected new DB handle, #5 radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' rlm_sql (sql): Released sql socket id: 5 modcall[authorize]: module "sql" returns ok for request 6 modcall: leaving group authorize (returns ok) for request 6
<SNIP>
radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #4 rlm_sql_mysql: Starting connect to MySQL server for #4 rlm_sql (sql): Connected new DB handle, #4 rlm_sql (sql): Released sql socket id: 4 modcall[session]: module "sql" returns ok for request 6 modcall: leaving group session (returns ok) for request 6 Login OK: [username@domain.com] (from client NAS01 port 91 cli 00:0F:EA:61:0F:B3)
<SNIP>
Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 6 Value Of the Pool-Name is [6d9a0ffb-8330-1029-8ba8-00005e000164] and its [36] Chars rlm_sql (sql): Reserving sql socket id: 6 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error
<DOES NOT RECONNECT>
radius_xlat: 'SQL QUERY' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error
<STILL NOT>
radius_xlat: 'SQL QUERY' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_query1: database query error rlm_sqlippool: ip=[] len=0 radius_xlat: 'COMMIT' rlm_sql_mysql: Socket not connected rlm_sql (sql): Attempting to connect rlm_sql_mysql #6 rlm_sql_mysql: Starting connect to MySQL server for #6 rlm_sql_mysql: Couldn't connect socket to MySQL server 0164@mysqldb01.domain.com:UltimateRadius rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on 'mysqldb01.domain.com' (60)' rlm_sql (sql): Failed to connect DB handle #6 rlm_sql (sql): reconnect failed, database down? sqlippool_command: database query error
<STILL DOWN>
rlm_sqlippool: IP number could not be allocated. rlm_sql (sql): Released sql socket id: 6 modcall[post-auth]: module "sqlippool" returns noop for request 6
<SNIP>
Sending Access-Accept of id 212 to 192.168.1.20 port 1071 Acct-Interim-Interval := 3600 Class := 0x574c414e Framed-Netmask := 255.255.255.255 Framed-Protocol := PPP Framed-Routing := Broadcast-Listen MS-Primary-DNS-Server := 36.0.18.198 MS-Secondary-DNS-Server := 4.0.18.198 Service-Type := Framed-User Finished request 6 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Sending duplicate reply to client NAS01:1071 - ID: 212 Re-sending Access-Accept of id 212 to 192.168.1.20 port 1071 --- Walking the entire request list --- Cleaning up request 6 ID 212 with timestamp 44f0ebc5 Nothing to do. Sleeping until we see a request. rad_recv: Access-Request packet from host 192.168.1.20:1071, id=212, length=209 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 91 NAS-Port-Type = Ethernet User-Name = "username@domain.com" Calling-Station-Id = "00:0F:EA:61:0F:B3" Called-Station-Id = "NAS01" NAS-Port-Id = "Server Network" MS-CHAP-Domain = "domain.com" User-Password = "password" NAS-Identifier = "NAS01" NAS-IP-Address = 192.168.1.20 Mikrotik-Realm = "domain.com"
<SNIP>
radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #3 rlm_sql_mysql: Starting connect to MySQL server for #3 rlm_sql (sql): Connected new DB handle, #3
<IMMEDIATELY RECONNECTS>
radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY' radius_xlat: 'SQL QUERY'
<SNIP>
radius_xlat: 'username@domain.com' rlm_sql (sql): sql_set_user escaped user --> 'username@domain.com' radius_xlat: 'SQL QUERY' rlm_sql (sql): Reserving sql socket id: 2 rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #2 rlm_sql_mysql: Starting connect to MySQL server for #2 rlm_sql (sql): Connected new DB handle, #2
<RECONNECTS>
rlm_sql (sql): Released sql socket id: 2 modcall[session]: module "sql" returns ok for request 7 modcall: leaving group session (returns ok) for request 7 Login OK: [username@domain.com] (from client NAS01 port 91 cli 00:0F:EA:61:0F:B3) Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 7 Value Of the Pool-Name is [6d9a0ffb-8330-1029-8ba8-00005e000164] and its [36] Chars rlm_sql (sql): Reserving sql socket id: 5 radius_xlat: 'BEGIN' rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_mysql #5 rlm_sql_mysql: Starting connect to MySQL server for #5
<FAILS TO RECONNECT>
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
It is ONLY tested on Postgresql 8.1.x (On 32bit and 64bit SUSE Linux) although I expect it should work fine on any 8.X version of postgresql and probably earlier versions as well. If you want to run it on a different database, the driver itself should support it, but you will need to modify the schema and the queries. If you get it working please let us know (And send us your schema and queries)
See below. It can more than likely do with more indexes though. I'm at this stage obviously only experimenting... I'm still checking, but I'm *baffled* as to why the rlm_sqlippool won't reconnect to the database then! As you said, it uses the SQL driver, whether it's PostGRE, mySQL, MSSQL, Oracle, surely, the reconnections are handled in the sql driver itself and not the module... Alan, anything I can look at perhaps??? My structures below should be quick and easy to understand. I'm sure there's mistakes in it as well (which I hope will be pointed out to me), and I hope other SQL servers will support INET_ATON() and INET_NTOA. Perhaps add these as variables in FreeRadius (Alan?). Considering pools are moving to SQL as well now - which is VERY good IMHO, I think it's a major waiste of space to allocate a VARCHAR(16) (at the minimum) to hold a IP Address in a database, when we can do it as a integer... Cheers Chris CREATE TABLE `IPPools` ( `EntryID` bigint(21) NOT NULL auto_increment, `GroupName` varchar(64) NOT NULL, `IPAddress` int(5) unsigned NOT NULL, `NASIPAddress` int(5) unsigned NOT NULL, `CallingStationID` varchar(50) default NULL, `PoolKey` char(33) NOT NULL, `ExpireTime` datetime default NULL, PRIMARY KEY (`EntryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # This series of queries allocates an IP address allocate-clear = "UPDATE GroupIPPools SET NASIPAddress = '0', PoolKey = MD5('0'), CallingStationID = NULL, ExpireTime = NOW() - INTERVAL 1 SECOND WHERE PoolKey = MD5(CONCAT('%{NAS-IP-Address}', '%{Calling-Station-Id}'))" allocate-find = "SELECT INET_NTOA(IPAddress) FROM GroupIPPools WHERE GroupID = '%{check:Pool-Name}' AND ExpireTime < NOW() ORDER BY GroupID, (CallingStationID <> '%{Calling-Station-Id}'), ExpireTime, RAND() LIMIT 1 FOR UPDATE" allocate-update = "UPDATE GroupIPPools SET NASIPAddress = INET_ATON('%{NAS-IP-Address}'), PoolKey = MD5(CONCAT('%{NAS-IP-Address}', '%{Calling-Station-Id}')), CallingStationID = '%{Calling-Station-Id}', ExpireTime = NOW() + INTERVAL ${lease-duration} SECOND WHERE IPAddress = INET_ATON('%I')" # This series of queries frees an IP number when an accounting START record arrives start-update = "UPDATE GroupIPPools SET ExpireTime = NOW() + INTERVAL %J SECOND WHERE NASIPAddress = INET_ATON('%n') AND IPAddress = INET_ATON('%{Framed-IP-Address}')" # This series of queries frees an IP number when an accounting STOP record arrives stop-clear = "UPDATE GroupIPPools SET NASIPAddress = '0', PoolKey = MD5('0'), CallingStationID = NULL, ExpireTime = NOW() - INTERVAL 1 SECOND WHERE NASIPAddress = INET_ATON('%{Nas-IP-Address}') AND PoolKey = MD5(CONCAT('%{NAS-IP-Address}', '${pool-key}')) AND CallingStationID = '%{Calling-Station-Id}' AND IPAddress = INET_ATON('%{Framed-IP-Address}')" # This series of queries frees an IP number when an accounting ALIVE record arrives alive-update = "UPDATE GroupIPPools SET ExpireTime = NOW() + INTERVAL ${lease-duration} SECOND WHERE NASIPAddress = INET_ATON('%{Nas-IP-Address}') AND PoolKey = MD5(CONCAT('%{NAS-IP-Address}', '%{Calling-Station-Id}')) AND CallingStationID = '%{Calling-Station-Id}' AND IPAddress = INET_ATON('%{Framed-IP-Address}')" # This series of queries frees the IP numbers allocate to a NAS when an accounting ON record arrives on-clear = "UPDATE GroupIPPools SET NASIPaddress = '0', PoolKey = MD5('0'), CallingStationID = NULL, ExpireTime = NOW() - INTERVAL 1 SECOND WHERE NASIPaddress = INET_ATON('%{Nas-IP-Address}') AND CallingStationId = '%{Calling-Station-Id}' AND IPAddress = INET_ATON('%{Framed-IP-Address}')" # This series of queries frees the IP numbers allocate to a NAS when an accounting OFF record arrives off-clear = "UPDATE GroupIPPools SET NASIPAddress = '0', PoolKey = MD5('0'), CallingStationID = NULL, ExpireTime = NOW() - INTERVAL 1 SECOND WHERE NASIPAddress = INET_ATON('%{Nas-IP-Address}') AND CallingStationID = '%{Calling-Station-Id}' AND IPAddress = INET_ATON('%{Framed-IP-Address}')"
On Sun 27 Aug 2006 18:56, Chris Knipe wrote:
It is ONLY tested on Postgresql 8.1.x (On 32bit and 64bit SUSE Linux) although I expect it should work fine on any 8.X version of postgresql and probably earlier versions as well. If you want to run it on a different database, the driver itself should support it, but you will need to modify the schema and the queries. If you get it working please let us know (And send us your schema and queries)
See below. It can more than likely do with more indexes though. I'm at this stage obviously only experimenting... I'm still checking, but I'm *baffled* as to why the rlm_sqlippool won't reconnect to the database then! As you said, it uses the SQL driver, whether it's PostGRE, mySQL, MSSQL, Oracle, surely, the reconnections are handled in the sql driver itself and not the module... Alan, anything I can look at perhaps???
I am not sure of the status of that. Reconnect may not be working at present. We manage our database fairly carefully on a dedicated system so it _never_ goes down :-)
My structures below should be quick and easy to understand. I'm sure there's mistakes in it as well (which I hope will be pointed out to me), and I hope other SQL servers will support INET_ATON() and INET_NTOA. Perhaps add these as variables in FreeRadius (Alan?). Considering pools are moving to SQL as well now - which is VERY good IMHO, I think it's a major waiste of space to allocate a VARCHAR(16) (at the minimum) to hold a IP Address in a database, when we can do it as a integer...
Actually, they ip_address file should be of type INET. I will make the change this week after testing it. -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
See below. It can more than likely do with more indexes though. I'm at this stage obviously only experimenting... I'm still checking, but I'm *baffled* as to why the rlm_sqlippool won't reconnect to the database then! As you said, it uses the SQL driver, whether it's PostGRE, mySQL, MSSQL, Oracle, surely, the reconnections are handled in the sql driver itself and not the module... Alan, anything I can look at perhaps???
I am not sure of the status of that. Reconnect may not be working at present. We manage our database fairly carefully on a dedicated system so it _never_ goes down :-)
This, is weird. I'll have to dig and test here. I had a error in one of my queries (only saw it now after I posted my queries in the email). It *seems* that if the DB Handle is down and it tries to execute a incorrect query when reconnecting, the driver stalls. I fixed my error in my query, and am running at 12,800 successfull authentications using rlm_sqlippool, without a single problem. The main thing with my test rig is that it's not busy. Part of managing a database is killing idle connections :-) That's why radius needs to reconnect the whole time... I'm not sure now whether the above should be seen as a possible bug in rlm_sql, or in rlm_sqlippool, or whether it should be seen as a bug at all. IMHO however, the handles should reconnect and the radius server should not 'stall' as such nevermind what happens. It creates a major backlog of queries and no other requests can be processed untill the timeout occured (not tested in a threaded environment). So far, it shows that IP addresses are also allocated correctly, as as it is supposed to by the queries, and specifically, the WHERE clauses.... So it seems all is well. Provided enough attention is given and you have your thinking cap on, I'm pretty much happy to say that this works with mySQL as well then... +-------------------+----------------------------+ | CallingStationID | INET_NTOA(FramedIPAddress) | +-------------------+----------------------------+ | 00:01:4A:5E:86:80 | 198.19.240.2 | | 00:0F:EA:61:0F:B3 | 198.19.240.1 | +-------------------+----------------------------+ 2 rows in set (0.01 sec)
My structures below should be quick and easy to understand. I'm sure there's mistakes in it as well (which I hope will be pointed out to me), and I hope other SQL servers will support INET_ATON() and INET_NTOA. Perhaps add these as variables in FreeRadius (Alan?). Considering pools are moving to SQL as well now - which is VERY good IMHO, I think it's a major waiste of space to allocate a VARCHAR(16) (at the minimum) to hold a IP Address in a database, when we can do it as a integer...
Actually, they ip_address file should be of type INET. I will make the change this week after testing it.
Is that supported on all database platforms though? As a 'default' configuration shipping with the FreeRadius distribution, I just feel that whatever is created / decided should be made generic enough so that it will work 'out of the box' so to speak.. Regards, Chris.
On Sat 26 Aug 2006 23:09, Chris Knipe wrote:
Hi,
I know this is new, and not yet documented, but I saw some good posts about it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping.
doc/rlm_sqlippool states: The only required fields are, pool_name and ip_address. A pool consists of one or more rows in the table with the same pool_name and a different ip_address. The is no restriction on which ip addresses/ranges may be in the same pool, and addresses do not need to be concurrent.
Yet, raddb/sqlippool.conf, makes absolutely NO sense to me at the moment at all, and there is WAY more than merely a pool name and a IP address referenced in the queries... I understand that there is some unique elements required in the table to indicate that a IP is allocated, and to know where the IP is allocated (and obviously to release that IP once the session terminates).
it is really not that complex :-) As the docs state put one or more records in the tabe with a pool_name and ip_address and then use the pool_name the same way you do with the standard ippool module. Thats it.
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The table structure is in the same file as all the rest of the database schema at doc/examples/postgresql.sql For reference it is: CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL ); I have only tested this with Postgresql, although I will probably be testing on Oracle at some point. If you want to test it on some other database you are welcome. Please report the results :-) Regards -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Hi; I was reading this email, and I've followed the steps. I have created the postgresql database, but what should I do to make the radius get the authentication from the postgresql database? And where should I add the configuration if I want to declare the username and the password in the database, and what changes should I do in the radiusd.conf and the users file? Thanks -----Original Message----- From: freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org [mailto:freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org] On Behalf Of Peter Nixon Sent: Sunday, August 27, 2006 5:05 PM To: Chris Knipe; FreeRadius users mailing list Subject: Re: rlm_sqlippool On Sat 26 Aug 2006 23:09, Chris Knipe wrote:
Hi,
I know this is new, and not yet documented, but I saw some good posts about it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping.
doc/rlm_sqlippool states: The only required fields are, pool_name and ip_address. A pool consists of one or more rows in the table with the same pool_name and a different ip_address. The is no restriction on which ip addresses/ranges may be in the same pool, and addresses do not need to be concurrent.
Yet, raddb/sqlippool.conf, makes absolutely NO sense to me at the moment at all, and there is WAY more than merely a pool name and a IP address referenced in the queries... I understand that there is some unique elements required in the table to indicate that a IP is allocated, and to know where the IP is allocated (and obviously to release that IP once the session terminates).
it is really not that complex :-) As the docs state put one or more records in the tabe with a pool_name and ip_address and then use the pool_name the same way you do with the standard ippool module. Thats it.
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The table structure is in the same file as all the rest of the database schema at doc/examples/postgresql.sql For reference it is: CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL ); I have only tested this with Postgresql, although I will probably be testing on Oracle at some point. If you want to test it on some other database you are welcome. Please report the results :-) Regards -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Hi Elie My instructions assume that you already know how to setup rlm_sql. If you do not, you first need to read doc/rlm_sql Alternatively you can read the wiki: http://wiki.freeradius.org/index.php/Rlm_sql Regards Peter On Mon 28 Aug 2006 18:04, Elie Hani wrote:
Hi;
I was reading this email, and I've followed the steps. I have created the postgresql database, but what should I do to make the radius get the authentication from the postgresql database? And where should I add the configuration if I want to declare the username and the password in the database, and what changes should I do in the radiusd.conf and the users file?
Thanks
-----Original Message----- From: freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org [mailto:freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org] On Behalf Of Peter Nixon Sent: Sunday, August 27, 2006 5:05 PM To: Chris Knipe; FreeRadius users mailing list Subject: Re: rlm_sqlippool
On Sat 26 Aug 2006 23:09, Chris Knipe wrote:
Hi,
I know this is new, and not yet documented, but I saw some good posts
about
it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping.
doc/rlm_sqlippool states: The only required fields are, pool_name and ip_address. A pool consists of one or more rows in the table with the same pool_name and a different ip_address. The is no restriction on which ip addresses/ranges may be in the same pool, and addresses do not need to be concurrent.
Yet, raddb/sqlippool.conf, makes absolutely NO sense to me at the moment
at
all, and there is WAY more than merely a pool name and a IP address referenced in the queries... I understand that there is some unique elements required in the table to indicate that a IP is allocated, and to know where the IP is allocated (and obviously to release that IP once the session terminates).
it is really not that complex :-) As the docs state put one or more records in the tabe with a pool_name and ip_address and then use the pool_name the same
way you do with the standard ippool module. Thats it.
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The table structure is in the same file as all the rest of the database schema at doc/examples/postgresql.sql
For reference it is:
CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL );
I have only tested this with Postgresql, although I will probably be testing
on Oracle at some point. If you want to test it on some other database you are welcome. Please report the results :-)
Regards
-- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Hi Peter, Well the databse is configured, and I made some tests and it's working. But what I need to know is what changes should I do in the radiusd.conf file and especially in the users file, to oblige the users to use the authentication from the database not locally. Secondly, what is the entry that declares the ip pool in the database? Is it framed-pool? Thanks Peter. Elie -----Original Message----- From: freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org [mailto:freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org] On Behalf Of Peter Nixon Sent: Monday, August 28, 2006 4:53 PM To: FreeRadius users mailing list Subject: Re: rlm_sqlippool Hi Elie My instructions assume that you already know how to setup rlm_sql. If you do not, you first need to read doc/rlm_sql Alternatively you can read the wiki: http://wiki.freeradius.org/index.php/Rlm_sql Regards Peter On Mon 28 Aug 2006 18:04, Elie Hani wrote:
Hi;
I was reading this email, and I've followed the steps. I have created the postgresql database, but what should I do to make the radius get the authentication from the postgresql database? And where should I add the configuration if I want to declare the username and the password in the database, and what changes should I do in the radiusd.conf and the users file?
Thanks
-----Original Message----- From: freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org [mailto:freeradius-users-bounces+ehani=wise.net.lb@lists.freeradius.org] On Behalf Of Peter Nixon Sent: Sunday, August 27, 2006 5:05 PM To: Chris Knipe; FreeRadius users mailing list Subject: Re: rlm_sqlippool
On Sat 26 Aug 2006 23:09, Chris Knipe wrote:
Hi,
I know this is new, and not yet documented, but I saw some good posts
about
it being stable, so I'm looking at implementing it at the moment... But alas, I'm confused and the lack of documentation is not helping.
doc/rlm_sqlippool states: The only required fields are, pool_name and ip_address. A pool consists of one or more rows in the table with the same pool_name and a different ip_address. The is no restriction on which ip addresses/ranges may be in the same pool, and addresses do not need to be concurrent.
Yet, raddb/sqlippool.conf, makes absolutely NO sense to me at the moment
at
all, and there is WAY more than merely a pool name and a IP address referenced in the queries... I understand that there is some unique elements required in the table to indicate that a IP is allocated, and to know where the IP is allocated (and obviously to release that IP once the session terminates).
it is really not that complex :-) As the docs state put one or more records in the tabe with a pool_name and ip_address and then use the pool_name the same
way you do with the standard ippool module. Thats it.
Can someone perhaps please just take a moment to explain what exactly is going on in those queries?? I'm not referring to the SQL as such, but rather as to what is updated, and why. A table structure accompanying those queries in sqlippool.conf may help significantly as well, as I'm guessing at the moment what needs to go where :(
The table structure is in the same file as all the rest of the database schema at doc/examples/postgresql.sql
For reference it is:
CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL );
I have only tested this with Postgresql, although I will probably be testing
on Oracle at some point. If you want to test it on some other database you are welcome. Please report the results :-)
Regards
-- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
participants (4)
-
Alan DeKok -
Chris Knipe -
Elie Hani -
Peter Nixon