Sqlippool ip range question
Hi, I'm using Debian Lenny with freeradius version 2.0.4 and postgresql database backend. I want to dynamically alocate a range of ip's from a pool i've created but it doesn't work. It only works if I add single ip's not ranges. I 've added in the radippool table the following data: 192.168.0.0/24 in the framedipaddress & main_pool in the pool_named columns. Here's the output of freeradius -X : freeradius -X FreeRADIUS Version 2.0.4, for host i486-pc-linux-gnu, built on Sep 7 2008 at 23:35:34 Copyright (C) 1999-2008 The FreeRADIUS server project and contributors. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. You may redistribute copies of FreeRADIUS under the terms of the GNU General Public License. Starting - reading configuration files ... including configuration file /etc/freeradius/radiusd.conf including configuration file /etc/freeradius/clients.conf including configuration file /etc/freeradius/snmp.conf including configuration file /etc/freeradius/sql.conf including configuration file /etc/freeradius/sql/postgresql/dialup.conf including configuration file /etc/freeradius/sqlippool.conf including configuration file /etc/freeradius/sql/postgresql/ippool.conf including configuration file /etc/freeradius/policy.conf including files in directory /etc/freeradius/sites-enabled/ including configuration file /etc/freeradius/sites-enabled/default including configuration file /etc/freeradius/sites-enabled/inner-tunnel including dictionary file /etc/freeradius/dictionary main { prefix = "/usr" localstatedir = "/var" logdir = "/var/log/freeradius" libdir = "/usr/lib/freeradius" radacctdir = "/var/log/freeradius/radacct" hostname_lookups = no max_request_time = 30 cleanup_delay = 5 max_requests = 1024 allow_core_dumps = no pidfile = "/var/run/freeradius/freeradius.pid" user = "freerad" group = "freerad" checkrad = "/usr/sbin/checkrad" debug_level = 0 proxy_requests = no security { max_attributes = 200 reject_delay = 1 status_server = yes } } client localhost { ipaddr = 127.0.0.1 require_message_authenticator = no secret = "testing123" nastype = "other" } client 192.168.107.100 { require_message_authenticator = no secret = "authlan" nastype = "other" } radiusd: #### Loading Realms and Home Servers #### radiusd: #### Instantiating modules #### instantiate { Module: Linked to module rlm_exec Module: Instantiating exec exec { wait = yes input_pairs = "request" shell_escape = yes } Module: Linked to module rlm_expr Module: Instantiating expr Module: Linked to module rlm_expiration Module: Instantiating expiration expiration { reply-message = "Password Has Expired " } Module: Linked to module rlm_logintime Module: Instantiating logintime logintime { reply-message = "You are calling outside your allowed timespan " minimum-timeout = 60 } } radiusd: #### Loading Virtual Servers #### server inner-tunnel { modules { Module: Checking authenticate {...} for more modules to load Module: Linked to module rlm_pap Module: Instantiating pap pap { encryption_scheme = "auto" auto_header = yes } Module: Linked to module rlm_chap Module: Instantiating chap Module: Linked to module rlm_mschap Module: Instantiating mschap mschap { use_mppe = yes require_encryption = no require_strong = no with_ntdomain_hack = no } Module: Linked to module rlm_unix Module: Instantiating unix unix { radwtmp = "/var/log/freeradius/radwtmp" } Module: Checking authorize {...} for more modules to load Module: Linked to module rlm_realm Module: Instantiating suffix realm suffix { format = "suffix" delimiter = "@" ignore_default = no ignore_null = no } Module: Linked to module rlm_files Module: Instantiating files files { usersfile = "/etc/freeradius/users" acctusersfile = "/etc/freeradius/acct_users" preproxy_usersfile = "/etc/freeradius/preproxy_users" compat = "no" } Module: Checking session {...} for more modules to load Module: Linked to module rlm_radutmp Module: Instantiating radutmp radutmp { filename = "/var/log/freeradius/radutmp" username = "%{User-Name}" case_sensitive = yes check_with_nas = yes perm = 384 callerid = yes } Module: Checking post-auth {...} for more modules to load Module: Linked to module rlm_attr_filter Module: Instantiating attr_filter.access_reject attr_filter attr_filter.access_reject { attrsfile = "/etc/freeradius/attrs.access_reject" key = "%{User-Name}" } } } server { modules { Module: Checking authenticate {...} for more modules to load Module: Checking authorize {...} for more modules to load Module: Linked to module rlm_preprocess Module: Instantiating preprocess preprocess { huntgroups = "/etc/freeradius/huntgroups" hints = "/etc/freeradius/hints" with_ascend_hack = no ascend_channels_per_line = 23 with_ntdomain_hack = no with_specialix_jetstream_hack = no with_cisco_vsa_hack = no with_alvarion_vsa_hack = no } Module: Linked to module rlm_sql Module: Instantiating sql sql { driver = "rlm_sql_postgresql" server = "127.0.0.1" port = "5432" login = "radius" password = "radiusdata" radius_db = "radius" read_groups = yes sqltrace = no sqltracefile = "/var/log/freeradius/sqltrace.sql" readclients = no deletestalesessions = yes num_sql_socks = 5 sql_user_name = "%{User-Name}" default_user_profile = "DEFAULT" nas_query = "SELECT id, nasname, shortname, type, secret FROM nas" authorize_check_query = "SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id" authorize_group_check_query = "SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{Sql-Group}' ORDER BY id" authorize_group_reply_query = "SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{Sql-Group}' ORDER BY id" accounting_onoff_query = "UPDATE radacct SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctSessionTime = (EXTRACT(EPOCH FROM ('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{%{Acct-Delay-Time}:-0}'::interval)))::BIGINT, AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0 WHERE AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'::timestamp" accounting_update_query = "UPDATE radacct SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint) WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_update_query_alt = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), '%{Acct-Session-Time}', '%{Acct-Authentic}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')" accounting_start_query = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0, '%{X-Ascend-Session-Svr-Key}')" accounting_start_query_alt = "UPDATE radacct SET AcctStartTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctStartDelay = 0, ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query = "UPDATE radacct SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}' END, AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0, FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query_alt = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, '%{Acct-Authentic}', '%{Connect-Info}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0)" group_membership_query = "SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority" connect_failure_retry_delay = 60 simul_count_query = "" simul_verify_query = "" postauth_query = "INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW())" safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } rlm_sql (sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded and linked rlm_sql (sql): Attempting to connect to radius@127.0.0.1:5432/radius rlm_sql (sql): starting 0 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #0 rlm_sql (sql): Connected new DB handle, #0 rlm_sql (sql): starting 1 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #1 rlm_sql (sql): Connected new DB handle, #1 rlm_sql (sql): starting 2 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #2 rlm_sql (sql): Connected new DB handle, #2 rlm_sql (sql): starting 3 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #3 rlm_sql (sql): Connected new DB handle, #3 rlm_sql (sql): starting 4 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #4 rlm_sql (sql): Connected new DB handle, #4 Module: Checking preacct {...} for more modules to load Module: Linked to module rlm_acct_unique Module: Instantiating acct_unique acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port" } Module: Checking accounting {...} for more modules to load Module: Linked to module rlm_detail Module: Instantiating detail detail { detailfile = "/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } Module: Linked to module rlm_sqlippool Module: Instantiating sqlippool sqlippool { sql-instance-name = "sql" lease-duration = 3600 pool-name = "main_pool" 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 RANDOM() 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" } rlm_sql (sql): Reserving sql socket id: 4 Module: Instantiating attr_filter.accounting_response attr_filter attr_filter.accounting_response { attrsfile = "/etc/freeradius/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 } main { snmp = no smux_password = "" snmp_write_access = no } Listening on authentication address * port 1812 Listening on accounting address * port 1813 Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1 port 56795, id=186, length=113 Service-Type = Framed-User Framed-Protocol = PPP User-Name = "akkersson" CHAP-Challenge = 0x82d49b7b65d4926e1291a1fb29cf2b802faa CHAP-Password = 0x0a459cbb9d04878fcc268ed0e18995b321 Calling-Station-Id = "00:11:5B:84:FF:ED" NAS-IP-Address = 127.0.0.1 NAS-Port = 0 +- entering group authorize ++[preprocess] returns ok rlm_chap: Setting 'Auth-Type := CHAP' ++[chap] returns ok ++[mschap] returns noop rlm_realm: No '@' in User-Name = "akkersson", looking up realm NULL rlm_realm: No such realm "NULL" ++[suffix] returns noop ++[unix] returns notfound users: Matched entry DEFAULT at line 172 ++[files] returns ok expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' rlm_sql (sql): Reserving sql socket id: 3 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 = 'akkersson' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 rlm_sql (sql): User found in radcheck table 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 = 'akkersson' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 expand: SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority -> SELECT GroupName FROM radusergroup WHERE UserName='akkersson' ORDER BY priority rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 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 = 'static' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 rlm_sql (sql): User found in group static 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 = 'static' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 3 , fields = 5 rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop rlm_pap: Found existing Auth-Type, not changing it. ++[pap] returns noop rad_check_password: Found Auth-Type CHAP auth: type "CHAP" +- entering group CHAP rlm_chap: login attempt by "akkersson" with CHAP password rlm_chap: Using clear text password "mumulina" for user akkersson authentication. rlm_chap: chap user akkersson authenticated succesfully ++[chap] returns ok Login OK: [akkersson/<CHAP-Password>] (from client localhost port 0 cli 00:11:5B:84:FF:ED) +- entering group post-auth rlm_sql (sql): Reserving sql socket id: 2 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: START TRANSACTION -> START TRANSACTION rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' 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 = '127.0.0.1' AND pool_key = '0' rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) ORDER BY RANDOM() LIMIT 1 FOR UPDATE -> SELECT framedipaddress FROM radippool WHERE pool_name = 'main_pool' AND expiry_time < 'now'::timestamp(0) ORDER BY RANDOM() LIMIT 1 FOR UPDATE rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: COMMIT -> COMMIT rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 rlm_sqlippool: Invalid IP number [192.168.0.0/24] returned from database query. rlm_sql (sql): Released sql socket id: 2 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 main_pool (did cli 00:11:5B:84:FF:ED port 0 user akkersson) IP Allocation FAILED from main_pool (did cli 00:11:5B:84:FF:ED port 0 user akkersson) ++[sqlippool] returns noop rlm_sql (sql): Processing sql_postauth expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: %{User-Password} -> expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW()) -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('akkersson', 'Chap-Password', 'Access-Accept', NOW()) rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('akkersson', 'Chap-Password', 'Access-Accept', NOW()) rlm_sql (sql): Reserving sql socket id: 1 rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1 rlm_sql (sql): Released sql socket id: 1 ++[sql] returns ok ++[exec] returns noop Sending Access-Accept of id 186 to 127.0.0.1 port 56795 Framed-Protocol = PPP Framed-Compression = Van-Jacobson-TCP-IP Service-Type = Framed-User Finished request 0. Going to the next request Waking up in 4.9 seconds. rad_recv: Accounting-Request packet from host 127.0.0.1 port 27342, id=187, length=120 Acct-Session-Id = "4912BE2378F100" User-Name = "akkersson" Acct-Status-Type = Start Service-Type = Framed-User Framed-Protocol = PPP Calling-Station-Id = "00:11:5B:84:FF:ED" Acct-Authentic = RADIUS NAS-Port-Type = Async Framed-IP-Address = 10.67.15.6 NAS-IP-Address = 127.0.0.1 NAS-Port = 0 Acct-Delay-Time = 0 +- entering group preacct ++[preprocess] returns ok rlm_acct_unique: Hashing 'NAS-Port = 0,Client-IP-Address = 127.0.0.1,NAS-IP-Address = 127.0.0.1,Acct-Session-Id = "4912BE2378F100",User-Name = "akkersson"' rlm_acct_unique: Acct-Unique-Session-ID = "c8a56ec2b4afca98". ++[acct_unique] returns ok rlm_realm: No '@' in User-Name = "akkersson", looking up realm NULL rlm_realm: No such realm "NULL" ++[suffix] returns noop ++[files] returns noop +- entering group accounting expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/freeradius/radacct/127.0.0.1/detail-20081106 rlm_detail: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/freeradius/radacct/127.0.0.1/detail-20081106 expand: %t -> Thu Nov 6 11:51:31 2008 ++[detail] returns ok ++[unix] returns ok expand: /var/log/freeradius/radutmp -> /var/log/freeradius/radutmp expand: %{User-Name} -> akkersson ++[radutmp] returns ok rlm_sql (sql): Reserving sql socket id: 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: START TRANSACTION -> START TRANSACTION rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' -> UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '127.0.0.1' AND pool_key = '0' rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: COMMIT -> COMMIT rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 rlm_sql (sql): Released sql socket id: 0 ++[sqlippool] returns ok expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: %{NAS-Port} -> 0 expand: %{Acct-Delay-Time} -> 0 expand: INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0, '%{X-Ascend-Session-Svr-Key}') -> INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('4912BE2378F100', 'c8a56ec2b4afca98', 'akkersson', NULLIF('', ''), '127.0.0.1', 0::integer, 'Async', ('2008-11-06 11:51:31'::timestamp - '0'::interval), 'RADIUS', '', '', '00:11:5B:84:FF:ED', 'Framed-User', 'PPP', NULLIF('10.67.15.6', '')::inet, 0, '') rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1 rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok expand: %{User-Name} -> akkersson attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 187 to 127.0.0.1 port 27342 Finished request 1. Cleaning up request 1 ID 187 with timestamp +7 Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 186 with timestamp +6 Ready to process requests. It doesn't assign an ip address from the pool i've specify, instead one from the pppoe-server default range. If i put a single ip in radippool table it works as it should. This line is troublesome: rlm_sqlippool: Invalid IP number [192.168.0.0/24] returned from database query. If the framedipaddress is of type inet why it doesn't work properly ??? Any hints ???
Hi, I'm using Debian Lenny with freeradius version 2.0.4 and postgresql database backend. I want to dynamically alocate a range of ip's from a pool i've created but it doesn't work. It only works if I add single ip's not ranges.
Yes, that's how it's supposed to work. Ivan Kalik Kalik Informatika ISP
Have your ranges[network/prefix] created in a separate table and use a "stored procedure"(i've used in MySQL) to populate your radippool table with corresponding ip addresses. HTH On Thu, Nov 6, 2008 at 10:58 AM, Ovi C <csoft2k5@yahoo.com> wrote:
Hi, I'm using Debian Lenny with freeradius version 2.0.4 and postgresql database backend. I want to dynamically alocate a range of ip's from a pool i've created but it doesn't work. It only works if I add single ip's not ranges.
I 've added in the radippool table the following data: 192.168.0.0/24 in the framedipaddress & main_pool in the pool_named columns.
Here's the output of freeradius -X :
freeradius -X FreeRADIUS Version 2.0.4, for host i486-pc-linux-gnu, built on Sep 7 2008 at 23:35:34 Copyright (C) 1999-2008 The FreeRADIUS server project and contributors. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. You may redistribute copies of FreeRADIUS under the terms of the GNU General Public License. Starting - reading configuration files ... including configuration file /etc/freeradius/radiusd.conf including configuration file /etc/freeradius/clients.conf including configuration file /etc/freeradius/snmp.conf including configuration file /etc/freeradius/sql.conf including configuration file /etc/freeradius/sql/postgresql/dialup.conf including configuration file /etc/freeradius/sqlippool.conf including configuration file /etc/freeradius/sql/postgresql/ippool.conf including configuration file /etc/freeradius/policy.conf including files in directory /etc/freeradius/sites-enabled/ including configuration file /etc/freeradius/sites-enabled/default including configuration file /etc/freeradius/sites-enabled/inner-tunnel including dictionary file /etc/freeradius/dictionary main { prefix = "/usr" localstatedir = "/var" logdir = "/var/log/freeradius" libdir = "/usr/lib/freeradius" radacctdir = "/var/log/freeradius/radacct" hostname_lookups = no max_request_time = 30 cleanup_delay = 5 max_requests = 1024 allow_core_dumps = no pidfile = "/var/run/freeradius/freeradius.pid" user = "freerad" group = "freerad" checkrad = "/usr/sbin/checkrad" debug_level = 0 proxy_requests = no security { max_attributes = 200 reject_delay = 1 status_server = yes } } client localhost { ipaddr = 127.0.0.1 require_message_authenticator = no secret = "testing123" nastype = "other" } client 192.168.107.100 { require_message_authenticator = no secret = "authlan" nastype = "other" } radiusd: #### Loading Realms and Home Servers #### radiusd: #### Instantiating modules #### instantiate { Module: Linked to module rlm_exec Module: Instantiating exec exec { wait = yes input_pairs = "request" shell_escape = yes } Module: Linked to module rlm_expr Module: Instantiating expr Module: Linked to module rlm_expiration Module: Instantiating expiration expiration { reply-message = "Password Has Expired " } Module: Linked to module rlm_logintime Module: Instantiating logintime logintime { reply-message = "You are calling outside your allowed timespan " minimum-timeout = 60 } } radiusd: #### Loading Virtual Servers #### server inner-tunnel { modules { Module: Checking authenticate {...} for more modules to load Module: Linked to module rlm_pap Module: Instantiating pap pap { encryption_scheme = "auto" auto_header = yes } Module: Linked to module rlm_chap Module: Instantiating chap Module: Linked to module rlm_mschap Module: Instantiating mschap mschap { use_mppe = yes require_encryption = no require_strong = no with_ntdomain_hack = no } Module: Linked to module rlm_unix Module: Instantiating unix unix { radwtmp = "/var/log/freeradius/radwtmp" } Module: Checking authorize {...} for more modules to load Module: Linked to module rlm_realm Module: Instantiating suffix realm suffix { format = "suffix" delimiter = "@" ignore_default = no ignore_null = no } Module: Linked to module rlm_files Module: Instantiating files files { usersfile = "/etc/freeradius/users" acctusersfile = "/etc/freeradius/acct_users" preproxy_usersfile = "/etc/freeradius/preproxy_users" compat = "no" } Module: Checking session {...} for more modules to load Module: Linked to module rlm_radutmp Module: Instantiating radutmp radutmp { filename = "/var/log/freeradius/radutmp" username = "%{User-Name}" case_sensitive = yes check_with_nas = yes perm = 384 callerid = yes } Module: Checking post-auth {...} for more modules to load Module: Linked to module rlm_attr_filter Module: Instantiating attr_filter.access_reject attr_filter attr_filter.access_reject { attrsfile = "/etc/freeradius/attrs.access_reject" key = "%{User-Name}" } } } server { modules { Module: Checking authenticate {...} for more modules to load Module: Checking authorize {...} for more modules to load Module: Linked to module rlm_preprocess Module: Instantiating preprocess preprocess { huntgroups = "/etc/freeradius/huntgroups" hints = "/etc/freeradius/hints" with_ascend_hack = no ascend_channels_per_line = 23 with_ntdomain_hack = no with_specialix_jetstream_hack = no with_cisco_vsa_hack = no with_alvarion_vsa_hack = no } Module: Linked to module rlm_sql Module: Instantiating sql sql { driver = "rlm_sql_postgresql" server = "127.0.0.1" port = "5432" login = "radius" password = "radiusdata" radius_db = "radius" read_groups = yes sqltrace = no sqltracefile = "/var/log/freeradius/sqltrace.sql" readclients = no deletestalesessions = yes num_sql_socks = 5 sql_user_name = "%{User-Name}" default_user_profile = "DEFAULT" nas_query = "SELECT id, nasname, shortname, type, secret FROM nas" authorize_check_query = "SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id" authorize_group_check_query = "SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{Sql-Group}' ORDER BY id" authorize_group_reply_query = "SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{Sql-Group}' ORDER BY id" accounting_onoff_query = "UPDATE radacct SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctSessionTime = (EXTRACT(EPOCH FROM ('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{%{Acct-Delay-Time}:-0}'::interval)))::BIGINT, AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0 WHERE AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'::timestamp" accounting_update_query = "UPDATE radacct SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint) WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_update_query_alt = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), '%{Acct-Session-Time}', '%{Acct-Authentic}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')" accounting_start_query = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0, '%{X-Ascend-Session-Svr-Key}')" accounting_start_query_alt = "UPDATE radacct SET AcctStartTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctStartDelay = 0, ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query = "UPDATE radacct SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}' END, AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0, FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query_alt = "INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, '%{Acct-Authentic}', '%{Connect-Info}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0)" group_membership_query = "SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority" connect_failure_retry_delay = 60 simul_count_query = "" simul_verify_query = "" postauth_query = "INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW())" safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } rlm_sql (sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded and linked rlm_sql (sql): Attempting to connect to radius@127.0.0.1:5432/radius rlm_sql (sql): starting 0 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #0 rlm_sql (sql): Connected new DB handle, #0 rlm_sql (sql): starting 1 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #1 rlm_sql (sql): Connected new DB handle, #1 rlm_sql (sql): starting 2 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #2 rlm_sql (sql): Connected new DB handle, #2 rlm_sql (sql): starting 3 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #3 rlm_sql (sql): Connected new DB handle, #3 rlm_sql (sql): starting 4 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #4 rlm_sql (sql): Connected new DB handle, #4 Module: Checking preacct {...} for more modules to load Module: Linked to module rlm_acct_unique Module: Instantiating acct_unique acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port" } Module: Checking accounting {...} for more modules to load Module: Linked to module rlm_detail Module: Instantiating detail detail { detailfile = "/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } Module: Linked to module rlm_sqlippool Module: Instantiating sqlippool sqlippool { sql-instance-name = "sql" lease-duration = 3600 pool-name = "main_pool" 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 RANDOM() 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" } rlm_sql (sql): Reserving sql socket id: 4 Module: Instantiating attr_filter.accounting_response attr_filter attr_filter.accounting_response { attrsfile = "/etc/freeradius/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 } main { snmp = no smux_password = "" snmp_write_access = no } Listening on authentication address * port 1812 Listening on accounting address * port 1813 Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1 port 56795, id=186, length=113 Service-Type = Framed-User Framed-Protocol = PPP User-Name = "akkersson" CHAP-Challenge = 0x82d49b7b65d4926e1291a1fb29cf2b802faa CHAP-Password = 0x0a459cbb9d04878fcc268ed0e18995b321 Calling-Station-Id = "00:11:5B:84:FF:ED" NAS-IP-Address = 127.0.0.1 NAS-Port = 0 +- entering group authorize ++[preprocess] returns ok rlm_chap: Setting 'Auth-Type := CHAP' ++[chap] returns ok ++[mschap] returns noop rlm_realm: No '@' in User-Name = "akkersson", looking up realm NULL rlm_realm: No such realm "NULL" ++[suffix] returns noop ++[unix] returns notfound users: Matched entry DEFAULT at line 172 ++[files] returns ok expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' rlm_sql (sql): Reserving sql socket id: 3 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 = 'akkersson' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 rlm_sql (sql): User found in radcheck table 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 = 'akkersson' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 expand: SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority -> SELECT GroupName FROM radusergroup WHERE UserName='akkersson' ORDER BY priority rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 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 = 'static' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 rlm_sql (sql): User found in group static 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 = 'static' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 3 , fields = 5 rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop rlm_pap: Found existing Auth-Type, not changing it. ++[pap] returns noop rad_check_password: Found Auth-Type CHAP auth: type "CHAP" +- entering group CHAP rlm_chap: login attempt by "akkersson" with CHAP password rlm_chap: Using clear text password "mumulina" for user akkersson authentication. rlm_chap: chap user akkersson authenticated succesfully ++[chap] returns ok Login OK: [akkersson/<CHAP-Password>] (from client localhost port 0 cli 00:11:5B:84:FF:ED) +- entering group post-auth rlm_sql (sql): Reserving sql socket id: 2 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: START TRANSACTION -> START TRANSACTION rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' 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 = '127.0.0.1' AND pool_key = '0' rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) ORDER BY RANDOM() LIMIT 1 FOR UPDATE -> SELECT framedipaddress FROM radippool WHERE pool_name = 'main_pool' AND expiry_time < 'now'::timestamp(0) ORDER BY RANDOM() LIMIT 1 FOR UPDATE rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: COMMIT -> COMMIT rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 rlm_sqlippool: Invalid IP number [192.168.0.0/24] returned from database query. rlm_sql (sql): Released sql socket id: 2 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 main_pool (did cli 00:11:5B:84:FF:ED port 0 user akkersson) IP Allocation FAILED from main_pool (did cli 00:11:5B:84:FF:ED port 0 user akkersson) ++[sqlippool] returns noop rlm_sql (sql): Processing sql_postauth expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: %{User-Password} -> expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW()) -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('akkersson', 'Chap-Password', 'Access-Accept', NOW()) rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ('akkersson', 'Chap-Password', 'Access-Accept', NOW()) rlm_sql (sql): Reserving sql socket id: 1 rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1 rlm_sql (sql): Released sql socket id: 1 ++[sql] returns ok ++[exec] returns noop Sending Access-Accept of id 186 to 127.0.0.1 port 56795 Framed-Protocol = PPP Framed-Compression = Van-Jacobson-TCP-IP Service-Type = Framed-User Finished request 0. Going to the next request Waking up in 4.9 seconds. rad_recv: Accounting-Request packet from host 127.0.0.1 port 27342, id=187, length=120 Acct-Session-Id = "4912BE2378F100" User-Name = "akkersson" Acct-Status-Type = Start Service-Type = Framed-User Framed-Protocol = PPP Calling-Station-Id = "00:11:5B:84:FF:ED" Acct-Authentic = RADIUS NAS-Port-Type = Async Framed-IP-Address = 10.67.15.6 NAS-IP-Address = 127.0.0.1 NAS-Port = 0 Acct-Delay-Time = 0 +- entering group preacct ++[preprocess] returns ok rlm_acct_unique: Hashing 'NAS-Port = 0,Client-IP-Address = 127.0.0.1,NAS-IP-Address = 127.0.0.1,Acct-Session-Id = "4912BE2378F100",User-Name = "akkersson"' rlm_acct_unique: Acct-Unique-Session-ID = "c8a56ec2b4afca98". ++[acct_unique] returns ok rlm_realm: No '@' in User-Name = "akkersson", looking up realm NULL rlm_realm: No such realm "NULL" ++[suffix] returns noop ++[files] returns noop +- entering group accounting expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/freeradius/radacct/127.0.0.1/detail-20081106 rlm_detail: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/freeradius/radacct/127.0.0.1/detail-20081106 expand: %t -> Thu Nov 6 11:51:31 2008 ++[detail] returns ok ++[unix] returns ok expand: /var/log/freeradius/radutmp -> /var/log/freeradius/radutmp expand: %{User-Name} -> akkersson ++[radutmp] returns ok rlm_sql (sql): Reserving sql socket id: 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: START TRANSACTION -> START TRANSACTION rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' -> UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '127.0.0.1' AND pool_key = '0' rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: COMMIT -> COMMIT rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 0 rlm_sql (sql): Released sql socket id: 0 ++[sqlippool] returns ok expand: %{User-Name} -> akkersson rlm_sql (sql): sql_set_user escaped user --> 'akkersson' expand: %{NAS-Port} -> 0 expand: %{Acct-Delay-Time} -> 0 expand: INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, 0, '%{X-Ascend-Session-Svr-Key}') -> INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) VALUES('4912BE2378F100', 'c8a56ec2b4afca98', 'akkersson', NULLIF('', ''), '127.0.0.1', 0::integer, 'Async', ('2008-11-06 11:51:31'::timestamp - '0'::interval), 'RADIUS', '', '', '00:11:5B:84:FF:ED', 'Framed-User', 'PPP', NULLIF('10.67.15.6', '')::inet, 0, '') rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1 rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok expand: %{User-Name} -> akkersson attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 187 to 127.0.0.1 port 27342 Finished request 1. Cleaning up request 1 ID 187 with timestamp +7 Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 186 with timestamp +6 Ready to process requests.
It doesn't assign an ip address from the pool i've specify, instead one from the pppoe-server default range. If i put a single ip in radippool table it works as it should. This line is troublesome: rlm_sqlippool: Invalid IP number [192.168.0.0/24] returned from database query. If the framedipaddress is of type inet why it doesn't work properly ???
Any hints ???
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (3)
-
Ovi C -
rsg -
tnt＠kalik.net