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