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.
