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

2013/3/24 Alan DeKok <aland@deployingradius.com>:

Бен Томпсон wrote:

...

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 :-

The debug log

...

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

The broadcast flag isn't set. So the client is asking for a unicast response.

...

(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

That needs to be fixed. I don't know much about Oracle, and I don't have an Oracle system running to test it.

...

(0) DHCP: Reply will be sent unicast to your-ip-address Sending DHCP-Offer of id 64b2e216 to 10.99.0.11:68

You should be able to fix this by doing:

update reply { DHCP-Flags = Broadcast }

Which will force the server to send a broadcast reply.

Alan DeKok.

Hi Alan Many thanks for the quick reply. The SQL statement "START TRANSACTION" looks to be hard coded into rlm_sqlippool.c but I don't know enough about Oracle etiher to say why it is flagged as an error. However from looking at the code I assume that it is supposed to signify the begining of a batch of SQL statements which after execution will be be either committed or rolled back. My guess would be that it is a redundant command as according this page: http://stackoverflow.com/questions/1366851/how-do-i-find-out-if-an-oracle-da... - commit/rollback, is a purely client side thing. So if a client says to Oracle "here are some statements which I will not commit straight away" I guess the server reply's with "why are you telling me?". If I am right then, I guess we can just remove the "START TRANSACTION" statement for Oracle, but unfoturnately I don't know enough myself to be sure. I do have access to and Oracle database though, so I am happy to do any testing, if someone else with more Oracle knowledge can advise what we should do. The broadcast flag did the trick thanks. Here is the DHCP discover section I am using :- dhcp DHCP-Discover { update control { Pool-Name := test_ip_pool } dhcp_sqlippool update reply { DHCP-Subnet-Mask = 255.255.255.0 DHCP-Domain-Name-Server = 192.168.12.1 DHCP-Router-Address = 10.99.0.1 DHCP-IP-Address-Lease-Time = 300 DHCP-DHCP-Server-Identifier = 10.99.0.100 } if (DHCP-Gateway-IP-Address == 0.0.0.0) { update reply { DHCP-Flags = Broadcast } } } However, it seems that none of the options are added to the reply, and for some reason an empty packet is sent to the client :- Received DHCP-Discover of id 3f1a9769 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 = 1058707305 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) ? if (DHCP-Gateway-IP-Address == 0.0.0.0) (0) ? Evaluating (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE (0) ? if (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE (0) if (DHCP-Gateway-IP-Address == 0.0.0.0) { (0) - entering if (DHCP-Gateway-IP-Address == 0.0.0.0) {...} (0) update reply { (0) } # update reply = ok (0) - if (DHCP-Gateway-IP-Address == 0.0.0.0) returns ok (0) DHCP: Reply will be broadcast Sending DHCP-Offer of id 3f1a9769 to 255.255.255.255:68 (0) Finished request 0. Waking up in 0.2 seconds. Waking up in 4.7 seconds. (0) Cleaning up request packet ID 1058707305 with timestamp +9 Ready to process requests. Signalled to terminate Exiting normally.

2013/3/25 Alan DeKok <aland@deployingradius.com>:

Бен Томпсон wrote:

...

The SQL statement "START TRANSACTION" looks to be hard coded into rlm_sqlippool.c but I don't know enough about Oracle etiher to say why it is flagged as an error.

It's a configuration item. You can change it by editing the queries, and adding:

allocate-begin = "..." allocate-commit = "..." allocate-rollback = "..."

Thanks again for the info. I have figured it out now, and after looking at the queries.conf for for the standard sqlippool module added this to my queries.conf : # Commit anything outstanding before beginning a new batch of transactions allocate-begin = "COMMIT" start-begin = "COMMIT" alive-begin = "COMMIT" stop-begin = "COMMIT" on-begin = "COMMIT" off-begin = "COMMIT" I also tried pulled the latest git commit this morning and now I have a new couple of new errors :- (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' rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query: ORA-24424: Invalid attempt to define at position 0 rlm_sql (sql): Database query error 'ORA-24424: Invalid attempt to define at position 0 ' sqlippool_query1: database query error (0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT' rlm_sql (sql): Executing query: 'COMMIT' (0) dhcp_sqlippool : escape: 'test_ip_pool' -> 'test_ip_pool' (0) dhcp_sqlippool : expand: 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = '%{control:Pool-Name}') WHERE ROWNUM = 1' -> 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = 'test_ip_pool') WHERE ROWNUM = 1' rlm_sql (sql): Executing query: 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = 'test_ip_pool') WHERE ROWNUM = 1' rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query: ORA-24424: Invalid attempt to define at position 0 rlm_sql (sql): Database query error 'ORA-24424: Invalid attempt to define at position 0 ' sqlippool_query1: database query error I am not sure what is happening here so I will have to do some reading.

On 25 Mar 2013, at 02:57, Arran Cudbard-Bell <a.cudbardb@freeradius.org> wrote:

...

I am not sure what is happening here so I will have to do some reading.

Fixed. Git pull.

Or would have been if git push hadn't hung... Sorry GitHub has been really flakey lately. -Arran

Many thanks. I will give it a try.

I am still working on this but, I have some further issues which I need to resolve. Firstly the ippool table for the oracle ippool schema contains various columns which are defined as NOT NULL: CREATE TABLE radippool ( id INT PRIMARY KEY, pool_name VARCHAR(30) NOT NULL, framedipaddress VARCHAR(30) NOT NULL, nasipaddress VARCHAR(30) NOT NULL, pool_key INT NOT NULL, CalledStationId VARCHAR(64), CallingStationId VARCHAR(64) NOT NULL, expiry_time timestamp(0) NOT NULL, username VARCHAR(100) ); The problem is that we also have insert and update staements which try to insert empty strings e.g. (taken from the standard ippol queries.conf): allocate-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', \ expiry_time = current_timestamp - INTERVAL '1' second(1) \ WHERE pool_key = '${pool-key}'" As I understand it, and empty string and NULL are the same thing in Oracle so this update statement will not work as is. I suppose we need to change the schema and this is what i have done for testing, but I want to understand why it is like this. I don't understand how the standard ippool queries.conf ever worked as it is trying to insert empty strings into NOT NULL columns. I can create a new schema and send a patch, but I just wanted to double check that this is the right thing to do.

Hello everyone I am still testing ippool-dhcp and after updating to the latest git HEAD I have come across a new issue. For some reason I am seeing the error "Unknown attribute" regarding the variable %{pool-key}. Here is a snippet of the debug log: (0) ERROR: dhcp_sqlippool : database query error in: 'UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'' (0) ERROR: dhcp_sqlippool : SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN pool_key = '%{pool-key}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE (0) ERROR: dhcp_sqlippool : ^ Unknown attribute In file mods-enabled/dhcp_sqlippool I have the following configuration: # Client's MAC address is mapped to Calling-Station-Id in policy.conf pool-key = "%{DHCP-Client-Hardware-Address}" I would be grateful for any help in solving this. It was working before but I made the mistake of chnging my configuration and upgrading FreeRADIUS at the same time so I am not sure where the problem lies. Here is the full debug output :- root@xxxx:~# /usr/local/sbin/radiusd -X radiusd: FreeRADIUS Version 3.0.0 (git #e6495d1), for host x86_64-unknown-linux-gnu, built on Jun 9 2013 at 13:41:15 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 dictionary file /usr/local/etc/raddb/dictionary including configuration file /usr/local/etc/raddb/radiusd.conf including configuration file /usr/local/etc/raddb/proxy.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/attr_filter 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/linelog including configuration file /usr/local/etc/raddb/mods-enabled/sradutmp including configuration file /usr/local/etc/raddb/mods-enabled/detail including configuration file /usr/local/etc/raddb/mods-enabled/mschap including configuration file /usr/local/etc/raddb/mods-enabled/expiration including configuration file /usr/local/etc/raddb/mods-enabled/always including configuration file /usr/local/etc/raddb/mods-enabled/passwd including configuration file /usr/local/etc/raddb/mods-enabled/logintime including configuration file /usr/local/etc/raddb/mods-enabled/pap including configuration file /usr/local/etc/raddb/mods-enabled/ntlm_auth including configuration file /usr/local/etc/raddb/mods-enabled/echo including configuration file /usr/local/etc/raddb/mods-enabled/utf8 including configuration file /usr/local/etc/raddb/mods-enabled/files including configuration file /usr/local/etc/raddb/mods-enabled/unix including configuration file /usr/local/etc/raddb/mods-enabled/cache_eap including configuration file /usr/local/etc/raddb/mods-enabled/chap including configuration file /usr/local/etc/raddb/mods-enabled/realm including configuration file /usr/local/etc/raddb/mods-enabled/replicate including configuration file /usr/local/etc/raddb/mods-enabled/preprocess including configuration file /usr/local/etc/raddb/mods-enabled/radutmp including configuration file /usr/local/etc/raddb/mods-enabled/eap including configuration file /usr/local/etc/raddb/mods-enabled/dynamic_clients including configuration file /usr/local/etc/raddb/mods-enabled/soh including configuration file /usr/local/etc/raddb/mods-enabled/expr including configuration file /usr/local/etc/raddb/mods-enabled/detail.log including configuration file /usr/local/etc/raddb/mods-enabled/dhcp 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/digest including configuration file /usr/local/etc/raddb/mods-enabled/exec 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/accounting including configuration file /usr/local/etc/raddb/policy.d/operator-name including configuration file /usr/local/etc/raddb/policy.d/eap including configuration file /usr/local/etc/raddb/policy.d/canonicalization including configuration file /usr/local/etc/raddb/policy.d/dhcp including configuration file /usr/local/etc/raddb/policy.d/filter 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 } } 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 = yes 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 #### proxy server { retry_delay = 5 retry_count = 3 default_fallback = no dead_time = 120 wake_all_if_all_dead = no } home_server localhost { ipaddr = 127.0.0.1 port = 1812 type = "auth" secret = "testing123" response_window = 20 max_outstanding = 65536 zombie_period = 40 status_check = "status-server" ping_interval = 30 check_interval = 30 num_answers_to_alive = 3 revive_interval = 120 status_check_timeout = 4 coa { irt = 2 mrt = 16 mrc = 5 mrd = 30 } limit { max_connections = 16 max_requests = 0 lifetime = 0 idle_timeout = 0 } } home_server_pool my_auth_failover { type = fail-over home_server = localhost } realm example.com { auth_pool = my_auth_failover } realm LOCAL { } 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_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_sqlippool # Instantiating module "dhcp_sqlippool" from file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool sqlippool dhcp_sqlippool { sql-instance-name = "sql" lease-duration = 7200 pool-name = "" defaultpool = "main_pool" allocate-begin = "" allocate-clear = "UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'" allocate-find = "SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN pool_key = '%{pool-key}' 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 '7200' SECOND(1) WHERE framedipaddress = '%I' AND pool_name = '%{control:Pool-Name}'" allocate-commit = "COMMIT" pool-check = "SELECT id FROM (SELECT id FROM radippool WHERE pool_name = '%{control:Pool-Name}') WHERE ROWNUM = 1" start-begin = "" start-update = "UPDATE radippool SET expiry_time = current_timestamp + INTERVAL '7200' 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" alive-begin = "" alive-update = "UPDATE radippool SET expiry_time = current_timestamp + INTERVAL '7200' 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}' AND pool_name = '%{control:Pool-Name}'" alive-commit = "COMMIT" stop-begin = "" 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" on-begin = "" on-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_name = '%{control:Pool-Name}'" on-commit = "COMMIT" off-begin = "" off-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_name = '%{control:Pool-Name}'" off-commit = "COMMIT" messages { } } # Loaded module rlm_sql # Instantiating module "sql" from file /usr/local/etc/raddb/mods-enabled/sql sql { driver = "rlm_sql_oracle" server = "localhost" port = "1521" login = "xxxxxx" password = "xxxxxx" radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=xxxxxx)))" 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=xxxxxx)))" rlm_sql (sql): Initialising connection pool pool { start = 2 min = 1 max = 2 spare = 1 uses = 0 lifetime = 0 cleanup_delay = 5 idle_timeout = 60 spread = no } rlm_sql (sql): Opening additional connection (0) rlm_sql (sql): Opening additional connection (1) # 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}" } # Loaded module rlm_radutmp # 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_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_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_expiration # Instantiating module "expiration" from file /usr/local/etc/raddb/mods-enabled/expiration expiration { reply-message = "Password Has Expired " } # 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_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_logintime # Instantiating module "logintime" from file /usr/local/etc/raddb/mods-enabled/logintime logintime { minimum-timeout = 60 } # Loaded module rlm_pap # Instantiating module "pap" from file /usr/local/etc/raddb/mods-enabled/pap pap { auto_header = no } # Loaded module rlm_exec # 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 } # 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_utf8 # Instantiating module "utf8" from file /usr/local/etc/raddb/mods-enabled/utf8 # 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_unix # Instantiating module "unix" from file /usr/local/etc/raddb/mods-enabled/unix unix { radwtmp = "/usr/local/var/log/radius/radwtmp" } # 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_chap # Instantiating module "chap" from file /usr/local/etc/raddb/mods-enabled/chap # 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_replicate # Instantiating module "replicate" from file /usr/local/etc/raddb/mods-enabled/replicate # 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 # 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 } # 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" 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 } 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 } 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_dynamic_clients # Instantiating module "dynamic_clients" from file /usr/local/etc/raddb/mods-enabled/dynamic_clients # Loaded module rlm_soh # Instantiating module "soh" from file /usr/local/etc/raddb/mods-enabled/soh soh { dhcp = yes } # Loaded module rlm_expr # Instantiating module "expr" from file /usr/local/etc/raddb/mods-enabled/expr expr { safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } # 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_dhcp # Instantiating module "dhcp" from file /usr/local/etc/raddb/mods-enabled/dhcp # Loaded module rlm_digest # Instantiating module "digest" from file /usr/local/etc/raddb/mods-enabled/digest # Instantiating module "exec" from file /usr/local/etc/raddb/mods-enabled/exec exec { wait = no input_pairs = "request" shell_escape = yes } } # 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 (null) {...} for more modules to load /usr/local/etc/raddb/sites-enabled/dhcp[112]: No name specified for Post-Auth-Type block } # server radiusd: #### Opening IP addresses and Ports #### listen { type = "dhcp" ipaddr = 127.0.0.1 port = 67 } Listening on dhcp interface lo address 127.0.0.1 port 67 as server dhcp Opening new proxy address * port 0 Listening on proxy address * port 52846 Ready to process requests. Received DHCP-Request of id 3d6ab451 from 127.0.0.1:68 to 127.0.0.1:67 DHCP-Opcode = Client-Message DHCP-Hardware-Type = Ethernet DHCP-Hardware-Address-Length = 6 DHCP-Hop-Count = 0 DHCP-Transaction-Id = 1030403153 DHCP-Number-of-Seconds = 0 DHCP-Flags = 0 DHCP-Client-IP-Address = 127.0.0.1 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 = c0:ff:ee:c0:ff:ee DHCP-Message-Type += DHCP-Request Trying sub-section dhcp DHCP-Request {...} (0) group DHCP-Request { (0) - entering group DHCP-Request {...} (0) update reply { (0) DHCP-Message-Type = DHCP-Ack (0) } # update reply = noop (0) update reply { (0) DHCP-Domain-Name-Server = 8.8.8.8 (0) DHCP-Domain-Name-Server = 8.8.4.4 (0) DHCP-Subnet-Mask = 255.255.255.0 (0) DHCP-Router-Address = 127.0.0.1 (0) DHCP-IP-Address-Lease-Time = 300 (0) DHCP-DHCP-Server-Identifier = 127.0.0.1 (0) } # update reply = noop (0) update control { (0) Pool-Name := "test_ip_pool" (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-c0:ff:ee:c0:ff:ee' (0) User-Name = "DHCP-c0:ff:ee:c0:ff:ee" (0) expand: '%{DHCP-Client-Hardware-Address}' -> 'c0:ff:ee:c0:ff:ee' (0) Calling-Station-Id = "c0:ff:ee:c0:ff:ee" (0) expand: '%{%{DHCP-Gateway-IP-Address}:-127.0.0.1}' -> '0.0.0.0' (0) NAS-IP-Address = 0.0.0.0 (0) Acct-Status-Type = Start (0) } # update request = noop rlm_sql (sql): Reserved connection (1) (0) dhcp_sqlippool : expand: '%{User-Name}' -> 'DHCP-c0:ff:ee:c0:ff:ee' (0) dhcp_sqlippool : SQL-User-Name updated (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'' -> 'UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'' (0) ERROR: dhcp_sqlippool : database query error in: 'UPDATE radippool SET nasipaddress = 'this sql statement' WHERE nasipaddress = 'is not in use'' (0) ERROR: dhcp_sqlippool : SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN pool_key = '%{pool-key}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE (0) ERROR: dhcp_sqlippool : ^ Unknown attribute (0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT' rlm_sql (sql): Executing query: 'COMMIT' (0) ERROR: dhcp_sqlippool : database query error in: 'COMMIT' (0) dhcp_sqlippool : expand: 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = '%{control:Pool-Name}') WHERE ROWNUM = 1' -> 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = 'test_ip_pool') WHERE ROWNUM = 1' rlm_sql (sql): Executing query: 'SELECT id FROM (SELECT id FROM radippool WHERE pool_name = 'test_ip_pool') WHERE ROWNUM = 1' rlm_sql (sql): Released connection (1) rlm_sql (sql): Closing connection (0): Too many free connections (2 > 1) (0) dhcp_sqlippool : pool appears to be full (0) [dhcp_sqlippool] = notfound (0) ? if (ok) (0) ? if (ok) -> FALSE (0) - policy dhcp_sqlippool.post-auth returns notfound (0) [ok] = ok (0) DHCP: Reply will be sent unicast to client-ip-address Sending DHCP-Ack of id 3d6ab451 to 127.0.0.1:68 (0) Finished request 0. Waking up in 0.3 seconds. Received DHCP-Release of id 3d6ab451 from 127.0.0.1:68 to 127.0.0.1:67 (0) Cleaning up request packet ID 1030403153 with timestamp +20 DHCP-Opcode = Client-Message DHCP-Hardware-Type = Ethernet DHCP-Hardware-Address-Length = 6 DHCP-Hop-Count = 0 DHCP-Transaction-Id = 1030403153 DHCP-Number-of-Seconds = 0 DHCP-Flags = 0 DHCP-Client-IP-Address = 127.0.0.1 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 = c0:ff:ee:c0:ff:ee DHCP-Message-Type += DHCP-Release Trying sub-section dhcp DHCP-Release {...} (1) WARNING: Unknown value specified for Post-Auth-Type. Cannot perform requested action. (1) Finished request 1. Waking up in 0.3 seconds. Waking up in 4.6 seconds. 2013/3/31 Alan DeKok <aland@deployingradius.com>

Бен Томпсон wrote:

...

Firstly the ippool table for the oracle ippool schema contains various columns which are defined as NOT NULL:

That is probably too strong a requirement.

...

CREATE TABLE radippool ( id INT PRIMARY KEY, pool_name VARCHAR(30) NOT NULL, framedipaddress VARCHAR(30) NOT NULL, nasipaddress VARCHAR(30) NOT NULL, pool_key INT NOT NULL, CalledStationId VARCHAR(64), CallingStationId VARCHAR(64) NOT NULL, expiry_time timestamp(0) NOT NULL, username VARCHAR(100) );

The problem is that we also have insert and update staements which try to insert empty strings e.g. (taken from the standard ippol queries.conf):

Yes. Those queries try to clear the entries... i.e. set them to NULL.

...

As I understand it, and empty string and NULL are the same thing in Oracle so this update statement will not work as is. I suppose we need to change the schema and this is what i have done for testing, but I want to understand why it is like this. I don't understand how the standard ippool queries.conf ever worked as it is trying to insert empty strings into NOT NULL columns.

I think no one ever tested it on Oracle.

...

I can create a new schema and send a patch, but I just wanted to double check that this is the right thing to do.

It's the right thing to do.

Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Many thanks. 2013/6/9 Alan DeKok <aland@deployingradius.com>

Бен Томпсон wrote:

...

I am still testing ippool-dhcp and after updating to the latest git HEAD I have come across a new issue. For some reason I am seeing the error "Unknown attribute" regarding the variable %{pool-key}.

That's because the Pool-Key attribute doesn't exist.

The example "dhcp_sqlippool" module doesn't have %{pool-key}. It uses ${pool-key}. See the difference?

Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html