2013/3/23 Бен Томпсон <b.thompson@latera.ru>:
2013/3/23 Arran Cudbard-Bell <a.cudbardb@freeradius.org>:
On 23 Mar 2013, at 12:22, Бен Томпсон <b.thompson@latera.ru> wrote:
Hello Everyone
Could anyone advise me what would be required to us dhcp-ippool with Oracle? I had a quick look through the files in git and it seems to me that the only thing missing is queries.conf?
If that is all that is required I am happy to do the work of porting the sql queries from the mysql version, but I just wanted the check that I am not missing something.
Nope you're not.
Please contribute a queries.conf file for Oracle and submit a pull request for master branch. Were actively trying to promote the use of the DHCP side, so such patches are very helpful.
-Arran
Hi Arran
Thanks for the quick reply, I will try and do it in the next few days.
Ben
Hello Arran and everyone on the list I began work on testing with Oracle but I have come across a couple of issues. Firstly one of my SQL statements is throwing up an error, and secondly the server is sending a unicast reply when I need a broadcast. I should be able to fix the dodgy SQL but I wondered if anyone could help me fix the broadcast/unicast problem. Here is the full degug :- # /usr/local/sbin/radiusd -X radiusd: FreeRADIUS Version 3.0.0 (git #d3b1f0a), for host x86_64-unknown-linux-gnu, built on Mar 23 2013 at 21:22:40 Copyright (C) 1999-2013 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. For more information about these matters, see the file named COPYRIGHT. Starting - reading configuration files ... including configuration file /usr/local/etc/raddb/radiusd.conf including configuration file /usr/local/etc/raddb/clients.conf including files in directory /usr/local/etc/raddb/mods-enabled/ including configuration file /usr/local/etc/raddb/mods-enabled/passwd including configuration file /usr/local/etc/raddb/mods-enabled/soh including configuration file /usr/local/etc/raddb/mods-enabled/digest including configuration file /usr/local/etc/raddb/mods-enabled/detail including configuration file /usr/local/etc/raddb/mods-enabled/echo including configuration file /usr/local/etc/raddb/mods-enabled/files including configuration file /usr/local/etc/raddb/mods-enabled/linelog including configuration file /usr/local/etc/raddb/mods-enabled/utf8 including configuration file /usr/local/etc/raddb/mods-enabled/chap including configuration file /usr/local/etc/raddb/mods-enabled/attr_filter including configuration file /usr/local/etc/raddb/mods-enabled/dynamic_clients including configuration file /usr/local/etc/raddb/mods-enabled/unix including configuration file /usr/local/etc/raddb/mods-enabled/eap including configuration file /usr/local/etc/raddb/mods-enabled/sql including configuration file /usr/local/etc/raddb/mods-enabled/../sql/main/oracle/queries.conf including configuration file /usr/local/etc/raddb/mods-enabled/expiration including configuration file /usr/local/etc/raddb/mods-enabled/attr_rewrite including configuration file /usr/local/etc/raddb/mods-enabled/dhcp including configuration file /usr/local/etc/raddb/mods-enabled/ntlm_auth including configuration file /usr/local/etc/raddb/mods-enabled/checkval including configuration file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool including configuration file /usr/local/etc/raddb/sql/ippool-dhcp/oracle/queries.conf including configuration file /usr/local/etc/raddb/mods-enabled/cache_eap including configuration file /usr/local/etc/raddb/mods-enabled/preprocess including configuration file /usr/local/etc/raddb/mods-enabled/mschap including configuration file /usr/local/etc/raddb/mods-enabled/radutmp including configuration file /usr/local/etc/raddb/mods-enabled/detail.log including configuration file /usr/local/etc/raddb/mods-enabled/realm including configuration file /usr/local/etc/raddb/mods-enabled/pap including configuration file /usr/local/etc/raddb/mods-enabled/exec including configuration file /usr/local/etc/raddb/mods-enabled/logintime including configuration file /usr/local/etc/raddb/mods-enabled/replicate including configuration file /usr/local/etc/raddb/mods-enabled/sradutmp including configuration file /usr/local/etc/raddb/mods-enabled/always including configuration file /usr/local/etc/raddb/mods-enabled/expr including files in directory /usr/local/etc/raddb/policy.d/ including configuration file /usr/local/etc/raddb/policy.d/control including configuration file /usr/local/etc/raddb/policy.d/cui including configuration file /usr/local/etc/raddb/policy.d/canonicalization including configuration file /usr/local/etc/raddb/policy.d/eap including configuration file /usr/local/etc/raddb/policy.d/operator-name including configuration file /usr/local/etc/raddb/policy.d/dhcp including configuration file /usr/local/etc/raddb/policy.d/filter including configuration file /usr/local/etc/raddb/policy.d/accounting including files in directory /usr/local/etc/raddb/sites-enabled/ including configuration file /usr/local/etc/raddb/sites-enabled/dhcp main { security { allow_core_dumps = no } } including dictionary file /usr/local/etc/raddb/dictionary main { name = "radiusd" prefix = "/usr/local" localstatedir = "/usr/local/var" sbindir = "/usr/local/sbin" logdir = "/usr/local/var/log/radius" run_dir = "/usr/local/var/run/radiusd" libdir = "/usr/local/lib" radacctdir = "/usr/local/var/log/radius/radacct" hostname_lookups = no max_request_time = 30 cleanup_delay = 5 max_requests = 1024 pidfile = "/usr/local/var/run/radiusd/radiusd.pid" checkrad = "/usr/local/sbin/checkrad" debug_level = 0 proxy_requests = no log { stripped_names = no auth = no auth_badpass = no auth_goodpass = no colourise = yes } security { max_attributes = 200 reject_delay = 1 status_server = yes } } radiusd: #### Loading Realms and Home Servers #### radiusd: #### Loading Clients #### client localhost { ipaddr = 127.0.0.1 require_message_authenticator = no secret = "testing123" nastype = "other" proto = "*" limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } radiusd: #### Instantiating modules #### instantiate { } modules { # Loaded module rlm_passwd # Instantiating module "etc_passwd" from file /usr/local/etc/raddb/mods-enabled/passwd passwd etc_passwd { filename = "/etc/passwd" format = "*User-Name:Crypt-Password:" delimiter = ":" ignorenislike = no ignoreempty = yes allowmultiplekeys = no hashsize = 100 } rlm_passwd: nfields: 3 keyfield 0(User-Name) listable: no # Loaded module rlm_soh # Instantiating module "soh" from file /usr/local/etc/raddb/mods-enabled/soh soh { dhcp = yes } # Loaded module rlm_digest # Instantiating module "digest" from file /usr/local/etc/raddb/mods-enabled/digest # Loaded module rlm_detail # Instantiating module "detail" from file /usr/local/etc/raddb/mods-enabled/detail detail { detailfile = "/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } # Loaded module rlm_exec # Instantiating module "echo" from file /usr/local/etc/raddb/mods-enabled/echo exec echo { wait = yes program = "/bin/echo %{User-Name}" input_pairs = "request" output_pairs = "reply" shell_escape = yes } # Loaded module rlm_files # Instantiating module "files" from file /usr/local/etc/raddb/mods-enabled/files files { usersfile = "/usr/local/etc/raddb/users" acctusersfile = "/usr/local/etc/raddb/acct_users" preproxy_usersfile = "/usr/local/etc/raddb/preproxy_users" compat = "no" } reading pairlist file /usr/local/etc/raddb/users reading pairlist file /usr/local/etc/raddb/acct_users reading pairlist file /usr/local/etc/raddb/preproxy_users # Loaded module rlm_linelog # Instantiating module "linelog" from file /usr/local/etc/raddb/mods-enabled/linelog linelog { filename = "/usr/local/var/log/radius/linelog" permissions = 384 format = "This is a log message for %{User-Name}" reference = "%{%{Packet-Type}:-format}" } # Instantiating module "linelog-dhcp" from file /usr/local/etc/raddb/mods-enabled/linelog linelog linelog-dhcp { filename = "/usr/local/var/log/radius/linelog-dhcp" permissions = 384 format = "" reference = "%{%{reply:DHCP-Message-Type}:-%{request:DHCP-Message-Type}}" } # Loaded module rlm_utf8 # Instantiating module "utf8" from file /usr/local/etc/raddb/mods-enabled/utf8 # Loaded module rlm_chap # Instantiating module "chap" from file /usr/local/etc/raddb/mods-enabled/chap # Loaded module rlm_attr_filter # Instantiating module "attr_filter.post-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter attr_filter attr_filter.post-proxy { file = "/usr/local/etc/raddb/filter/post-proxy" key = "%{Realm}" relaxed = no } reading pairlist file /usr/local/etc/raddb/filter/post-proxy # Instantiating module "attr_filter.pre-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter attr_filter attr_filter.pre-proxy { file = "/usr/local/etc/raddb/filter/pre-proxy" key = "%{Realm}" relaxed = no } reading pairlist file /usr/local/etc/raddb/filter/pre-proxy # Instantiating module "attr_filter.access_reject" from file /usr/local/etc/raddb/mods-enabled/attr_filter attr_filter attr_filter.access_reject { file = "/usr/local/etc/raddb/filter/access_reject" key = "%{User-Name}" relaxed = no } reading pairlist file /usr/local/etc/raddb/filter/access_reject # Instantiating module "attr_filter.access_challenge" from file /usr/local/etc/raddb/mods-enabled/attr_filter attr_filter attr_filter.access_challenge { file = "/usr/local/etc/raddb/filter/access_challenge" key = "%{User-Name}" relaxed = no } reading pairlist file /usr/local/etc/raddb/filter/access_challenge # Instantiating module "attr_filter.accounting_response" from file /usr/local/etc/raddb/mods-enabled/attr_filter attr_filter attr_filter.accounting_response { file = "/usr/local/etc/raddb/filter/accounting_response" key = "%{User-Name}" relaxed = no } reading pairlist file /usr/local/etc/raddb/filter/accounting_response # Loaded module rlm_dynamic_clients # Instantiating module "dynamic_clients" from file /usr/local/etc/raddb/mods-enabled/dynamic_clients # Loaded module rlm_unix # Instantiating module "unix" from file /usr/local/etc/raddb/mods-enabled/unix unix { radwtmp = "/usr/local/var/log/radius/radwtmp" } # Loaded module rlm_eap # Instantiating module "eap" from file /usr/local/etc/raddb/mods-enabled/eap eap { default_eap_type = "md5" timer_expire = 60 ignore_unknown_eap_types = no mod_accounting_username_bug = no max_sessions = 4096 } # Linked to sub-module rlm_eap_md5 # Linked to sub-module rlm_eap_leap # Linked to sub-module rlm_eap_gtc gtc { challenge = "Password: " auth_type = "PAP" } # Linked to sub-module rlm_eap_tls tls { tls = "tls-common" } tls-config tls-common { rsa_key_exchange = no dh_key_exchange = yes rsa_key_length = 512 dh_key_length = 512 verify_depth = 0 CA_path = "/usr/local/etc/raddb/certs" pem_file_type = yes private_key_file = "/usr/local/etc/raddb/certs/server.pem" certificate_file = "/usr/local/etc/raddb/certs/server.pem" CA_file = "/usr/local/etc/raddb/certs/ca.pem" private_key_password = "whatever" dh_file = "/usr/local/etc/raddb/certs/dh" random_file = "/usr/local/etc/raddb/certs/random" fragment_size = 1024 include_length = yes check_crl = no cipher_list = "DEFAULT" make_cert_command = "/usr/local/etc/raddb/certs/bootstrap" ecdh_curve = "prime256v1" cache { enable = yes lifetime = 24 max_entries = 255 } verify { } ocsp { enable = no override_cert_url = yes url = "http://127.0.0.1/ocsp/" use_nonce = yes timeout = 0 softfail = yes } } # Linked to sub-module rlm_eap_ttls ttls { tls = "tls-common" default_eap_type = "md5" copy_request_to_tunnel = no use_tunneled_reply = no virtual_server = "inner-tunnel" include_length = yes require_client_cert = no } debug: Using cached TLS configuration from previous invocation # Linked to sub-module rlm_eap_peap peap { tls = "tls-common" default_method = "mschapv2" copy_request_to_tunnel = no use_tunneled_reply = no proxy_tunneled_request_as_eap = yes virtual_server = "inner-tunnel" soh = no require_client_cert = no } debug: Using cached TLS configuration from previous invocation # Linked to sub-module rlm_eap_mschapv2 mschapv2 { with_ntdomain_hack = no send_error = no } # Loaded module rlm_sql # Instantiating module "sql" from file /usr/local/etc/raddb/mods-enabled/sql sql { driver = "rlm_sql_oracle" server = "localhost" port = "" login = "foobar" password = "foo" radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=bar)))" read_groups = yes readclients = no deletestalesessions = yes sql_user_name = "%{User-Name}" default_user_profile = "" nas_query = "SELECT id, nasname, shortname, type, secret, server 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 radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,radusergroup WHERE radusergroup.Username = '%{SQL-User-Name}' AND radusergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id" authorize_group_reply_query = "SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,radusergroup WHERE radusergroup.Username = '%{SQL-User-Name}' AND radusergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id" group_membership_query = "SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}'" simul_count_query = "" simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM radacct WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL" safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } accounting { reference = "%{tolower:type.%{Acct-Status-Type}.query}" } post-auth { reference = ".query" } rlm_sql (sql): Driver rlm_sql_oracle (module rlm_sql_oracle) loaded and linked rlm_sql (sql): Attempting to connect to database "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=bar)))" rlm_sql (sql): Initialising connection pool pool { start = 5 min = 4 max = 10 spare = 3 uses = 0 lifetime = 0 cleanup_delay = 5 idle_timeout = 60 } rlm_sql (sql): Opening additional connection (0) rlm_sql (sql): Opening additional connection (1) rlm_sql (sql): Opening additional connection (2) rlm_sql (sql): Opening additional connection (3) rlm_sql (sql): Opening additional connection (4) # Loaded module rlm_expiration # Instantiating module "expiration" from file /usr/local/etc/raddb/mods-enabled/expiration expiration { reply-message = "Password Has Expired " } # Loaded module rlm_attr_rewrite # Instantiating module "sanecallerid" from file /usr/local/etc/raddb/mods-enabled/attr_rewrite attr_rewrite sanecallerid { attribute = "Called-Station-Id" searchfor = "[+ ]" searchin = "packet" replacewith = "" append = no ignore_case = no new_attribute = no max_matches = 10 } # Loaded module rlm_dhcp # Instantiating module "dhcp" from file /usr/local/etc/raddb/mods-enabled/dhcp # Instantiating module "ntlm_auth" from file /usr/local/etc/raddb/mods-enabled/ntlm_auth exec ntlm_auth { wait = yes program = "/path/to/ntlm_auth --request-nt-key --domain=MYDOMAIN --username=%{mschap:User-Name} --password=%{User-Password}" input_pairs = "request" shell_escape = yes } # Loaded module rlm_checkval # Instantiating module "checkval" from file /usr/local/etc/raddb/mods-enabled/checkval checkval { item-name = "Calling-Station-Id" check-name = "Calling-Station-Id" data-type = "string" notfound-reject = no } rlm_checkval: Registered name Calling-Station-Id for attribute 31 # Loaded module rlm_sqlippool # Instantiating module "dhcp_sqlippool" from file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool sqlippool dhcp_sqlippool { sql-instance-name = "sql" lease-duration = 300 pool-name = "" allocate-begin = "START TRANSACTION" allocate-clear = "UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)" allocate-find = "SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = '%{User-Name}' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '%{Calling-Station-Id}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE" allocate-update = "UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{DHCP-Client-Hardware-Address}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '%I' AND expiry_time IS NULL" allocate-commit = "COMMIT" allocate-rollback = "ROLLBACK" pool-check = "SELECT id FROM (SELECT id FROM radippool WHERE pool_name = '%{control:Pool-Name}') WHERE ROWNUM = 1" start-begin = "START TRANSACTION" start-update = "UPDATE radippool SET expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{DHCP-Client-Hardware-Address}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" start-commit = "COMMIT" start-rollback = "ROLLBACK" alive-begin = "START TRANSACTION" alive-update = "UPDATE radippool SET expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{DHCP-Client-Hardware-Address}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" alive-commit = "COMMIT" alive-rollback = "ROLLBACK" stop-begin = "START TRANSACTION" stop-clear = "UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{DHCP-Client-Hardware-Address}' AND username = '%{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 = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) 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 = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE nasipaddress = '%{Nas-IP-Address}'" off-commit = "COMMIT" off-rollback = "ROLLBACK" sqlippool_log_exists = "DHCP: Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_success = "DHCP: 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 = "DHCP: Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})" sqlippool_log_failed = "DHCP: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_nopool = "DHCP: No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" defaultpool = "main_pool" } # Loaded module rlm_cache # Instantiating module "cache_eap" from file /usr/local/etc/raddb/mods-enabled/cache_eap cache cache_eap { key = "%{%{control:State}:-%{%{reply:State}:-%{State}}}" ttl = 15 max_entries = 16384 epoch = 0 add_stats = no } # Loaded module rlm_preprocess # Instantiating module "preprocess" from file /usr/local/etc/raddb/mods-enabled/preprocess preprocess { huntgroups = "/usr/local/etc/raddb/huntgroups" hints = "/usr/local/etc/raddb/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 } reading pairlist file /usr/local/etc/raddb/huntgroups reading pairlist file /usr/local/etc/raddb/hints # Loaded module rlm_mschap # Instantiating module "mschap" from file /usr/local/etc/raddb/mods-enabled/mschap mschap { use_mppe = yes require_encryption = no require_strong = no with_ntdomain_hack = yes passchange { } allow_retry = yes } # Loaded module rlm_radutmp # Instantiating module "radutmp" from file /usr/local/etc/raddb/mods-enabled/radutmp radutmp { filename = "/usr/local/var/log/radius/radutmp" username = "%{User-Name}" case_sensitive = yes check_with_nas = yes perm = 384 callerid = yes } # Instantiating module "auth_log" from file /usr/local/etc/raddb/mods-enabled/detail.log detail auth_log { detailfile = "/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } # Instantiating module "reply_log" from file /usr/local/etc/raddb/mods-enabled/detail.log detail reply_log { detailfile = "/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } # Instantiating module "pre_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log detail pre_proxy_log { detailfile = "/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/pre-proxy-detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } # Instantiating module "post_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log detail post_proxy_log { detailfile = "/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/post-proxy-detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } # Loaded module rlm_realm # Instantiating module "IPASS" from file /usr/local/etc/raddb/mods-enabled/realm realm IPASS { format = "prefix" delimiter = "/" ignore_default = no ignore_null = no } # Instantiating module "suffix" from file /usr/local/etc/raddb/mods-enabled/realm realm suffix { format = "suffix" delimiter = "@" ignore_default = no ignore_null = no } # Instantiating module "realmpercent" from file /usr/local/etc/raddb/mods-enabled/realm realm realmpercent { format = "suffix" delimiter = "%" ignore_default = no ignore_null = no } # Instantiating module "ntdomain" from file /usr/local/etc/raddb/mods-enabled/realm realm ntdomain { format = "prefix" delimiter = "\" ignore_default = no ignore_null = no } # Loaded module rlm_pap # Instantiating module "pap" from file /usr/local/etc/raddb/mods-enabled/pap pap { auto_header = no } # Instantiating module "exec" from file /usr/local/etc/raddb/mods-enabled/exec exec { wait = no input_pairs = "request" shell_escape = yes } # Loaded module rlm_logintime # Instantiating module "logintime" from file /usr/local/etc/raddb/mods-enabled/logintime logintime { reply-message = "You are calling outside your allowed timespan " minimum-timeout = 60 } # Loaded module rlm_replicate # Instantiating module "replicate" from file /usr/local/etc/raddb/mods-enabled/replicate # Instantiating module "sradutmp" from file /usr/local/etc/raddb/mods-enabled/sradutmp radutmp sradutmp { filename = "/usr/local/var/log/radius/sradutmp" username = "%{User-Name}" case_sensitive = yes check_with_nas = yes perm = 420 callerid = no } # Loaded module rlm_always # Instantiating module "fail" from file /usr/local/etc/raddb/mods-enabled/always always fail { rcode = "fail" simulcount = 0 mpp = no } # Instantiating module "reject" from file /usr/local/etc/raddb/mods-enabled/always always reject { rcode = "reject" simulcount = 0 mpp = no } # Instantiating module "noop" from file /usr/local/etc/raddb/mods-enabled/always always noop { rcode = "noop" simulcount = 0 mpp = no } # Instantiating module "handled" from file /usr/local/etc/raddb/mods-enabled/always always handled { rcode = "handled" simulcount = 0 mpp = no } # Instantiating module "updated" from file /usr/local/etc/raddb/mods-enabled/always always updated { rcode = "updated" simulcount = 0 mpp = no } # Instantiating module "notfound" from file /usr/local/etc/raddb/mods-enabled/always always notfound { rcode = "notfound" simulcount = 0 mpp = no } # Instantiating module "ok" from file /usr/local/etc/raddb/mods-enabled/always always ok { rcode = "ok" simulcount = 0 mpp = no } # Loaded module rlm_expr # Instantiating module "expr" from file /usr/local/etc/raddb/mods-enabled/expr expr { safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } } # modules radiusd: #### Loading Virtual Servers #### server { # from file /usr/local/etc/raddb/radiusd.conf } # server server dhcp { # from file /usr/local/etc/raddb/sites-enabled/dhcp Module: Checking dhcp DHCP-Discover {...} for more modules to load # Loading virtual module dhcp_sqlippool Module: Checking dhcp DHCP-Request {...} for more modules to load # Loading virtual module dhcp_sqlippool Module: Checking dhcp DHCP-Release {...} for more modules to load Module: Checking dhcp DHCP-Inform {...} for more modules to load Module: Checking dhcp DHCP-Decline {...} for more modules to load } # server radiusd: #### Opening IP addresses and Ports #### listen { type = "dhcp" ipaddr = 0.0.0.0 port = 67 max_pps = 0 } Listening on dhcp interface eth1 address * port 67 as server dhcp Ready to process requests. Received DHCP-Discover of id 64b2e216 from 0.0.0.0:68 to 0.0.0.0:67 DHCP-Opcode = Client-Message DHCP-Hardware-Type = Ethernet DHCP-Hardware-Address-Length = 6 DHCP-Hop-Count = 0 DHCP-Transaction-Id = 1689444886 DHCP-Number-of-Seconds = 0 DHCP-Flags = 0 DHCP-Client-IP-Address = 0.0.0.0 DHCP-Your-IP-Address = 0.0.0.0 DHCP-Server-IP-Address = 0.0.0.0 DHCP-Gateway-IP-Address = 0.0.0.0 DHCP-Client-Hardware-Address = 00:0c:29:a6:a0:e7 DHCP-Message-Type += DHCP-Discover DHCP-Parameter-Request-List += DHCP-Subnet-Mask DHCP-Parameter-Request-List += DHCP-Broadcast-Address DHCP-Parameter-Request-List += DHCP-Time-Offset DHCP-Parameter-Request-List += DHCP-Router-Address DHCP-Parameter-Request-List += DHCP-Domain-Name DHCP-Parameter-Request-List += DHCP-Domain-Name-Server DHCP-Parameter-Request-List += DHCP-Domain-Search DHCP-Parameter-Request-List += DHCP-Hostname DHCP-Parameter-Request-List += DHCP-NETBIOS-Name-Servers DHCP-Parameter-Request-List += DHCP-NETBIOS DHCP-Parameter-Request-List += DHCP-Interface-MTU-Size DHCP-Parameter-Request-List += DHCP-Classless-Static-Route DHCP-Parameter-Request-List += DHCP-NTP-Servers Trying sub-section dhcp DHCP-Discover {...} (0) group DHCP-Discover { (0) - entering group DHCP-Discover {...} (0) update control { (0) } # update control = noop (0) policy dhcp_sqlippool.post-auth { (0) - entering policy dhcp_sqlippool.post-auth {...} (0) update request { (0) expand: 'DHCP-%{DHCP-Client-Hardware-Address}' -> 'DHCP-00:0c:29:a6:a0:e7' (0) expand: '%{DHCP-Client-Hardware-Address}' -> '00:0c:29:a6:a0:e7' (0) expand: '%{DHCP-Gateway-IP-Address}' -> '0.0.0.0' (0) expand: '%{%{DHCP-Gateway-IP-Address}:-127.0.0.1}' -> '0.0.0.0' (0) } # update request = noop rlm_sql (sql): Reserved connection (4) (0) dhcp_sqlippool : expand: '%{User-Name}' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : SQL-User-Name updated (0) dhcp_sqlippool : expand: 'START TRANSACTION' -> 'START TRANSACTION' rlm_sql (sql): Executing query: 'START TRANSACTION' rlm_sql_oracle: execute query failed in sql_query: ORA-00900: invalid SQL statement rlm_sql_oracle: OCI_SERVER_NORMAL rlm_sql (sql): Database query error: 'ORA-00900: invalid SQL statement ' sqlippool_command: database query error in: 'START TRANSACTION' (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' -> 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' (0) dhcp_sqlippool : escape: 'test_ip_pool' -> 'test_ip_pool' (0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : expand: 'SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = '%{User-Name}' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '%{Calling-Station-Id}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' -> 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = 'DHCP-00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' rlm_sql (sql): Executing query: 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = 'DHCP-00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' (0) dhcp_sqlippool : escape: '0.0.0.0' -> '0.0.0.0' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{DHCP-Client-Hardware-Address}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' -> 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = '00:0c:29:a6:a0:e7', callingstationid = '00:0c:29:a6:a0:e7', username = 'DHCP-00:0c:29:a6:a0:e7', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = '00:0c:29:a6:a0:e7', callingstationid = '00:0c:29:a6:a0:e7', username = 'DHCP-00:0c:29:a6:a0:e7', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' (0) dhcp_sqlippool : Allocated IP 10.99.0.11 [0b00630a] (0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT' rlm_sql (sql): Executing query: 'COMMIT' rlm_sql (sql): Released connection (4) rlm_sql (sql): Closing connection (0): Too many free connections (5 > 3) rlm_sql_mysql: Socket destructor called, closing socket (0) dhcp_sqlippool : expand: 'DHCP: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})' -> 'DHCP: Allocated IP: 10.99.0.11 from test_ip_pool (did cli 00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7)' DHCP: Allocated IP: 10.99.0.11 from test_ip_pool (did cli 00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7) (0) [dhcp_sqlippool] = ok (0) ? if (ok) (0) ? Evaluating (ok) -> TRUE (0) ? if (ok) -> TRUE (0) if (ok) { (0) - entering if (ok) {...} (0) update reply { (0) expand: '%{reply:Framed-IP-Address}' -> '10.99.0.11' (0) } # update reply = ok (0) - if (ok) returns ok (0) - policy dhcp_sqlippool.post-auth returns ok (0) update reply { (0) } # update reply = ok (0) DHCP: Reply will be sent unicast to your-ip-address Sending DHCP-Offer of id 64b2e216 to 10.99.0.11:68 (0) Finished request 0. Waking up in 0.3 seconds. Waking up in 4.6 seconds. (0) Cleaning up request packet ID 1689444886 with timestamp +13 Ready to process requests. Signalled to terminate Exiting normally. rlm_sql (sql): Removing connection pool rlm_sql (sql): Closing connection (4) rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (3) rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (2) rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (1) rlm_sql_mysql: Socket destructor called, closing socket