sql & sqlippool

Tahar BEN ACHOUR tahar_ba at yahoo.fr
Tue Mar 2 17:15:38 CET 2010


Hello I am new in this list I am setting up a freeradius server with mysql and sqlippool but I am facing some problems with sqlippool which cannot deliver an ip address to subscribers. I installed Freeradius 2.1.3 on OpenBSD 4.6 with mysql support

here is the output of radiusd -X



 sqlippool {
        sql-instance-name = "sql"
        lease-duration = 3600
        pool-name = ""
        allocate-begin = "START TRANSACTION"
        allocate-clear = "UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '%{NAS-IP-Address}'   AND pool_key = '%{NAS-Port}'"
        allocate-find = "SELECT framedipaddress FROM radippool   WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0)   ORDER BY (username <> '%{SQL-User-Name}'),   (callingstationid <> '%{Calling-Station-Id}'), expiry_time   LIMIT 1   FOR UPDATE"
        allocate-update = "UPDATE radippool   SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}',   callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}',   expiry_time = 'now'::timestamp(0) + '3600 second'::interval   WHERE framedipaddress = '%I'"
        allocate-commit = "COMMIT"
        allocate-rollback = "ROLLBACK"
        pool-check = "SELECT id FROM radippool   WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
        start-begin = "START TRANSACTION"
        start-update = "UPDATE radippool   SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval   WHERE nasipaddress = '%{NAS-IP-Address}'   AND pool_key = '%{NAS-Port}'"
        start-commit = "COMMIT"
        start-rollback = "ROLLBACK"
        alive-begin = "START TRANSACTION"
        alive-update = "UPDATE radippool   SET expiry_time = 'now'::timestamp(0) + '3600 seconds'::interval   WHERE nasipaddress = '%{Nas-IP-Address}'   AND pool_key = '%{NAS-Port}'   AND framedipaddress = '%{Framed-IP-Address}'   AND username = '%{SQL-User-Name}'   AND callingstationid = '%{Calling-Station-Id}'"
        alive-commit = "COMMIT"
        alive-rollback = "ROLLBACK"
        stop-begin = "START TRANSACTION"
        stop-clear = "UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '%{Nas-IP-Address}'   AND pool_key = '%{NAS-Port}'   AND username = '%{SQL-User-Name}'   AND callingstationid = '%{Calling-Station-Id}'   AND framedipaddress = '%{Framed-IP-Address}'"
        stop-commit = "COMMIT"
        stop-rollback = "ROLLBACK"
        on-begin = "START TRANSACTION"
        on-clear = "UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '%{Nas-IP-Address}'"
        on-commit = "COMMIT"
        on-rollback = "ROLLBACK"
        off-begin = "START TRANSACTION"
        off-clear = "UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '%{Nas-IP-Address}'"
        off-commit = "COMMIT"
        off-rollback = "ROLLBACK"
        sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address}   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
        sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name}   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
        sqlippool_log_clear = "Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
        sqlippool_log_failed = "IP Allocation FAILED from %{control:Pool-Name}   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
        sqlippool_log_nopool = "No Pool-Name defined   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
        defaultpool = "main_pool"
  }
 Module: Instantiating attr_filter.accounting_response
  attr_filter attr_filter.accounting_response {
        attrsfile = "/etc/raddb/attrs.accounting_response"
        key = "%{User-Name}"
  }
 Module: Checking session {...} for more modules to load
 Module: Checking post-auth {...} for more modules to load
 }
radiusd: #### Opening IP addresses and Ports ####
listen {
        type = "auth"
        ipaddr = *
        port = 0
}
listen {
        type = "acct"
        ipaddr = *
        port = 0
}
Listening on authentication address * port 1812
Listening on accounting address * port 1813


and here are my tables on the raidus sql database 

mysql> select * from radcheck;
+----+----------+--------------------+----+--------+
| id | username | attribute          | op | value  |
+----+----------+--------------------+----+--------+
|  1 | tahar    | Cleartext-Password | := | 123    | 
|  2 | tba      | Cleartext-Password | := | 123456 | 
+----+----------+--------------------+----+--------+

mysql> select * from radgroupcheck;
+----+-----------+-----------+----+-----------+
| id | groupname | attribute | op | value     |
+----+-----------+-----------+----+-----------+
|  1 | static    | Pool-Name | := | main_pool | 
|  2 | dynamic   | Pool-Name | := | test_pool | 
+----+-----------+-----------+----+-----------+

mysql> select * from radippool;    
+----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+
| id | pool_name | framedipaddress | nasipaddress | calledstationid | callingstationid | expiry_time | username | pool_key |
+----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+
|  1 | main_pool | 192.168.100.10  |              |                 |                  | NULL        |          |          | 
|  2 | test_pool | 192.168.100.50  |              |                 |                  | NULL        |          |          | 
|  3 | main_pool | 192.168.100.11  |              |                 |                  | NULL        |          |          | 
|  4 | main_pool | 192.168.100.12  |              |                 |                  | NULL        |          |          | 
|  5 | main_pool | 192.168.100.13  |              |                 |                  | NULL        |          |          | 
|  6 | main_pool | 192.168.100.14  |              |                 |                  | NULL        |          |          | 
|  7 | main_pool | 192.168.100.15  |              |                 |                  | NULL        |          |          | 
|  8 | test_pool | 192.168.100.51  |              |                 |                  | NULL        |          |          | 
|  9 | test_pool | 192.168.100.52  |              |                 |                  | NULL        |          |          | 
| 10 | test_pool | 192.168.100.53  |              |                 |                  | NULL        |          |          | 
| 11 | test_pool | 192.168.100.54  |              |                 |                  | NULL        |          |          | 
| 12 | test_pool | 192.168.100.55  |              |                 |                  | NULL        |          |          | 
+----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+


     mysql> select * from radgroupreply;
     +----+-----------+--------------------+---------------------+------+
     | id | GroupName | Attribute          | Value               | Op   |
     +----+-----------+--------------------+---------------------+------+
     | 34 | dynamic   | Framed-Compression | Van-Jacobsen-TCP-IP | :=   |
     | 33 | dynamic   | Framed-Protocol    | PPP                 | :=   |
     | 32 | dynamic   | Service-Type       | Framed-User         | :=   |
     | 35 | dynamic   | Framed-MTU         | 1500                | :=   |
     | 37 | static    | Framed-Protocol    | PPP                 | :=   |
     | 38 | static    | Service-Type       | Framed-User         | :=   |
     | 39 | static    | Framed-Compression | Van-Jacobsen-TCP-IP | :=   |
     | 41 | netdial   | Service-Type       | Framed-User         | :=   |
     | 42 | netdial   | Framed-Protocol    | PPP                 | :=   |
     +----+-----------+--------------------+---------------------+------+
I left radreply empty because I would to have dinamic IPs

When I do a test 

radtest tahar 123 localhost 1812 testing123 

I have this output and i says that the IP cannot be allocate anyone know why ? 

Thank you



rad_recv: Access-Request packet from host 127.0.0.1 port 16536, id=68, length=57
        User-Name = "tahar"
        User-Password = "123"
        NAS-IP-Address = 193.95.93.219
        NAS-Port = 1812
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] No '@' in User-Name = "tahar", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
++[files] returns noop
[sql]   expand: %{User-Name} -> tahar
[sql] sql_set_user escaped user --> 'tahar'
rlm_sql (sql): Reserving sql socket id: 2
[sql]   expand: SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = 'tahar'           ORDER BY id
[sql] User found in radcheck table
[sql]   expand: SELECT id, username, attribute, value, op           FROM radreply           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'tahar'           ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'tahar'           ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = 'dynamic'           ORDER BY id
[sql] User found in group dynamic
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'dynamic'           ORDER BY id
rlm_sql (sql): Released sql socket id: 2
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "123"
[pap] Using clear text password "123"
[pap] User authenticated successfully
++[pap] returns ok
+- entering group post-auth {...}
rlm_sql (sql): Reserving sql socket id: 1
[sqlippool]     expand: %{User-Name} -> tahar
[sqlippool] sql_set_user escaped user --> 'tahar'
[sqlippool]     expand: START TRANSACTION -> START TRANSACTION
[sqlippool]     expand: UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '%{NAS-IP-Address}'   AND pool_key = '%{NAS-Port}' -> UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '193.95.93.219'   AND pool_key = '1812'
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_command: database query error in: 'UPDATE radippool   SET nasipaddress = '', pool_key = 0, callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 second'::interval   WHERE nasipaddress = '193.95.93.219'   AND pool_key = '1812''
[sqlippool]     expand: SELECT framedipaddress FROM radippool   WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0)   ORDER BY (username <> '%{SQL-User-Name}'),   (callingstationid <> '%{Calling-Station-Id}'), expiry_time   LIMIT 1   FOR UPDATE -> SELECT framedipaddress FROM radippool   WHERE pool_name = 'test_pool' AND expiry_time < 'now'::timestamp(0)   ORDER BY (username <> 'tahar'),   (callingstationid <> ''), expiry_time   LIMIT 1   FOR UPDATE
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_query1: database query error
[sqlippool]     expand: COMMIT -> COMMIT
[sqlippool]     expand: SELECT id FROM radippool   WHERE pool_name='%{control:Pool-Name}' LIMIT 1 -> SELECT id FROM radippool   WHERE pool_name='test_pool' LIMIT 1
rlm_sql (sql): Released sql socket id: 1
[sqlippool] pool appears to be full
[sqlippool]     expand: IP Allocation FAILED from %{control:Pool-Name}   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from test_pool   (did  cli  port 1812 user tahar)
IP Allocation FAILED from test_pool   (did  cli  port 1812 user tahar)
++[sqlippool] returns notfound
++[exec] returns noop
Sending Access-Accept of id 68 to 127.0.0.1 port 16536
Finished request 1.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 1 ID 68 with timestamp +58
Ready to process requests.


      




More information about the Freeradius-Users mailing list