SQL query difficulties

Dave Aldwinckle daldwinc at uwaterloo.ca
Tue Dec 9 18:08:20 CET 2014


Hello,

When I run an SQL query from freeradius, I get no results. When I copy 
and paste the query into mysql, there is a result. Configs and debug 
below; help is appreciated.


I have a wireless captive portal that sends Access-Request messages like so:

(7) Received Access-Request Id 120 from 172.16.2.202:43036 to 
123.12.128.5:1812 length 218
(7)   NAS-IP-Address = 172.16.2.222
(7)   NAS-Port = 0
(7)   NAS-Port-Type = Wireless-802.11
(7)   User-Name = 'c8:bc:c8:ed:27:68'
(7)   User-Password = 'c8:bc:c8:ed:27:68'
(7)   Service-Type = Login-User
(7)   Calling-Station-Id = 'c8:bc:c8:ed:27:68'
(7)   Called-Station-Id = '00:1a:1e:00:2b:30'
(7)   Aruba-Essid-Name = 'uw-unsecured'
(7)   Aruba-Location-Id = 'AS-AP-MC-1095-B'
(7)   Aruba-AP-Group = 'MC-std'
(7)   Attr-26 = 0x000039e70c02
(7)   Message-Authenticator = 0xcd12451619e8e44483843b9a1330e5a7
(7) session-state: No State attribute

What I am trying to do, is check an SQL database for the existence of 
User-Name = 'c8:bc:c8:ed:27:68'. If it exists, accept, otherwise reject. 
Below is the virtual server I've created for this:

server uw_portal {

         authorize {
                 if("%{sql:SELECT COUNT(macaddress) FROM 
`radmacauthgroup` WHERE macaddress = '%{User-Name}'" > 0){
                         accept
                 }
                 else {
                         reject
                 }
         }

}

This record does actually exist.....

$ mysql -uradius -p wifimacauth
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| wifimacauth        |
+--------------------+
3 rows in set (0.00 sec)

mysql> use wifimacauth;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_wifimacauth |
+-----------------------+
| nas                   |
| radcheck              |
| radgroupcheck         |
| radgroupreply         |
| radmacauthgroup       |
| radpostauth           |
| radreply              |
| radusergroup          |
| retiredmacauthgroup   |
+-----------------------+
9 rows in set (0.00 sec)

mysql> SELECT COUNT(macaddress) FROM `radmacauthgroup` WHERE macaddress 
= 'c8:bc:c8:ed:27:68';
+-------------------+
| COUNT(macaddress) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)


# radiusd -X
radiusd: FreeRADIUS Version 3.0.5, for host x86_64-redhat-linux-gnu, 
built on Dec  8 2014 at 10:44:02
Copyright (C) 1999-2014 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/share/freeradius/dictionary
including dictionary file /usr/share/freeradius/dictionary.dhcp
including dictionary file /usr/share/freeradius/dictionary.vqp
including dictionary file /etc/raddb/dictionary
including configuration file /etc/raddb/radiusd.conf
including configuration file /etc/raddb/proxy.conf
including configuration file /etc/raddb/clients.conf
including files in directory /etc/raddb/mods-enabled/
including configuration file /etc/raddb/mods-enabled/exec
including configuration file /etc/raddb/mods-enabled/ntlm_auth
including configuration file /etc/raddb/mods-enabled/expr
including configuration file /etc/raddb/mods-enabled/dynamic_clients
including configuration file /etc/raddb/mods-enabled/preprocess
including configuration file /etc/raddb/mods-enabled/unix
including configuration file /etc/raddb/mods-enabled/digest
including configuration file /etc/raddb/mods-enabled/chap
including configuration file /etc/raddb/mods-enabled/detail.log
including configuration file /etc/raddb/mods-enabled/linelog
including configuration file /etc/raddb/mods-enabled/pap
including configuration file /etc/raddb/mods-enabled/cache_eap
including configuration file /etc/raddb/mods-enabled/logintime
including configuration file /etc/raddb/mods-enabled/expiration
including configuration file /etc/raddb/mods-enabled/always
including configuration file /etc/raddb/mods-enabled/detail
including configuration file /etc/raddb/mods-enabled/utf8
including configuration file /etc/raddb/mods-enabled/echo
including configuration file /etc/raddb/mods-enabled/radutmp
including configuration file /etc/raddb/mods-enabled/eap
including configuration file /etc/raddb/mods-enabled/unpack
including configuration file /etc/raddb/mods-enabled/sradutmp
including configuration file /etc/raddb/mods-enabled/sql
including configuration file 
/etc/raddb/mods-config/sql/main/sqlite/queries.conf
including configuration file /etc/raddb/mods-enabled/dhcp
including configuration file /etc/raddb/mods-enabled/passwd
including configuration file /etc/raddb/mods-enabled/mschap
including configuration file /etc/raddb/mods-enabled/realm
including configuration file /etc/raddb/mods-enabled/soh
including configuration file /etc/raddb/mods-enabled/files
including configuration file /etc/raddb/mods-enabled/replicate
including configuration file /etc/raddb/mods-enabled/attr_filter
including files in directory /etc/raddb/policy.d/
including configuration file /etc/raddb/policy.d/abfab-tr
including configuration file /etc/raddb/policy.d/control
including configuration file /etc/raddb/policy.d/operator-name
including configuration file /etc/raddb/policy.d/filter
including configuration file /etc/raddb/policy.d/eap
including configuration file /etc/raddb/policy.d/dhcp
including configuration file /etc/raddb/policy.d/cui
including configuration file /etc/raddb/policy.d/accounting
including configuration file /etc/raddb/policy.d/debug
including configuration file /etc/raddb/policy.d/canonicalization
including files in directory /etc/raddb/sites-enabled/
including configuration file /etc/raddb/sites-enabled/default
including configuration file /etc/raddb/sites-enabled/uw_portal
including configuration file /etc/raddb/sites-enabled/inner-tunnel
main {
  security {
      allow_core_dumps = no
  }
}
main {
     name = "radiusd"
     prefix = "/usr"
     localstatedir = "/var"
     sbindir = "/usr/sbin"
     logdir = "/var/log/radius"
     run_dir = "/var/run/radiusd"
     libdir = "/usr/lib64/freeradius"
     radacctdir = "/var/log/radius/radacct"
     hostname_lookups = no
     max_request_time = 30
     cleanup_delay = 5
     max_requests = 1024
     pidfile = "/var/run/radiusd/radiusd.pid"
     checkrad = "/usr/sbin/checkrad"
     debug_level = 0
     proxy_requests = yes
  log {
      stripped_names = no
      auth = yes
      auth_badpass = no
      auth_goodpass = no
      colourise = yes
      msg_denied = "You are already logged in - access denied"
  }
  security {
      max_attributes = 200
      reject_delay = 1.000000
      status_server = yes
      allow_vulnerable_openssl = "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 = <<< secret >>>
      response_window = 20.000000
      response_timeouts = 1
      max_outstanding = 65536
      zombie_period = 40
      status_check = "status-server"
      ping_interval = 30
      check_interval = 30
      check_timeout = 4
      num_answers_to_alive = 3
      revive_interval = 120
   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 {
  }
  realm UW_PORTAL_REALM {
     virtual_server = uw_portal
  }
radiusd: #### Loading Clients ####
  client localhost {
      ipaddr = 127.0.0.1
      require_message_authenticator = no
      secret = <<< secret >>>
      nas_type = "other"
      proto = "*"
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client localhost_ipv6 {
      ipv6addr = ::1
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-mc-1061a {
      ipaddr = 172.16.2.196
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-phy-211 {
      ipaddr = 172.16.2.197
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-mc-1061a-a {
      ipaddr = 172.16.2.198
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-mc-1061a-b {
      ipaddr = 172.16.2.199
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-mc-1061a-c {
      ipaddr = 172.16.2.200
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-phy-211-a {
      ipaddr = 172.16.2.201
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-phy-211-b {
      ipaddr = 172.16.2.202
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
  client wn-wc-phy-211-c {
      ipaddr = 172.16.2.203
      require_message_authenticator = no
      secret = <<< secret >>>
   limit {
       max_connections = 16
       lifetime = 0
       idle_timeout = 30
   }
  }
Debugger not attached
radiusd: #### Instantiating modules ####
  instantiate {
  }
  modules {
   # Loaded module rlm_exec
   # Instantiating module "exec" from file /etc/raddb/mods-enabled/exec
   exec {
       wait = no
       input_pairs = "request"
       shell_escape = yes
       timeout = 10
   }
   # Instantiating module "ntlm_auth" from file 
/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}"
       shell_escape = yes
   }
   # Loaded module rlm_expr
   # Instantiating module "expr" from file /etc/raddb/mods-enabled/expr
   expr {
       safe_characters = 
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: 
/äéöüàâæçèéêëîïôœùûüaÿÄÉÖÜßÀÂÆÇÈÉÊËÎÏÔŒÙÛÜŸ"
   }
   # Loaded module rlm_dynamic_clients
   # Instantiating module "dynamic_clients" from file 
/etc/raddb/mods-enabled/dynamic_clients
   # Loaded module rlm_preprocess
   # Instantiating module "preprocess" from file 
/etc/raddb/mods-enabled/preprocess
   preprocess {
       huntgroups = "/etc/raddb/mods-config/preprocess/huntgroups"
       hints = "/etc/raddb/mods-config/preprocess/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 /etc/raddb/mods-config/preprocess/huntgroups
reading pairlist file /etc/raddb/mods-config/preprocess/hints
   # Loaded module rlm_unix
   # Instantiating module "unix" from file /etc/raddb/mods-enabled/unix
   unix {
       radwtmp = "/var/log/radius/radwtmp"
   }
   # Loaded module rlm_digest
   # Instantiating module "digest" from file /etc/raddb/mods-enabled/digest
   # Loaded module rlm_chap
   # Instantiating module "chap" from file /etc/raddb/mods-enabled/chap
   # Loaded module rlm_detail
   # Instantiating module "auth_log" from file 
/etc/raddb/mods-enabled/detail.log
   detail auth_log {
       filename = 
"/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d"
       header = "%t"
       permissions = 384
       locking = no
       log_packet_header = no
   }
rlm_detail (auth_log): 'User-Password' suppressed, will not appear in 
detail output
   # Instantiating module "reply_log" from file 
/etc/raddb/mods-enabled/detail.log
   detail reply_log {
       filename = 
"/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d"
       header = "%t"
       permissions = 384
       locking = no
       log_packet_header = no
   }
   # Instantiating module "pre_proxy_log" from file 
/etc/raddb/mods-enabled/detail.log
   detail pre_proxy_log {
       filename = 
"/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/pre-proxy-detail-%Y%m%d"
       header = "%t"
       permissions = 384
       locking = no
       log_packet_header = no
   }
   # Instantiating module "post_proxy_log" from file 
/etc/raddb/mods-enabled/detail.log
   detail post_proxy_log {
       filename = 
"/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/post-proxy-detail-%Y%m%d"
       header = "%t"
       permissions = 384
       locking = no
       log_packet_header = no
   }
   # Loaded module rlm_linelog
   # Instantiating module "linelog" from file 
/etc/raddb/mods-enabled/linelog
   linelog {
       filename = "/var/log/radius/linelog"
       permissions = 384
       format = "This is a log message for %{User-Name}"
       reference = "messages.%{%{Packet-Type}:-default}"
   }
   # Instantiating module "log_accounting" from file 
/etc/raddb/mods-enabled/linelog
   linelog log_accounting {
       filename = "/var/log/radius/linelog-accounting"
       permissions = 384
       format = ""
       reference = "Accounting-Request.%{%{Acct-Status-Type}:-unknown}"
   }
   # Loaded module rlm_pap
   # Instantiating module "pap" from file /etc/raddb/mods-enabled/pap
   pap {
       normalise = yes
   }
   # Loaded module rlm_cache
   # Instantiating module "cache_eap" from file 
/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_logintime
   # Instantiating module "logintime" from file 
/etc/raddb/mods-enabled/logintime
   logintime {
       minimum_timeout = 60
   }
   # Loaded module rlm_expiration
   # Instantiating module "expiration" from file 
/etc/raddb/mods-enabled/expiration
   # Loaded module rlm_always
   # Instantiating module "reject" from file /etc/raddb/mods-enabled/always
   always reject {
       rcode = "reject"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "fail" from file /etc/raddb/mods-enabled/always
   always fail {
       rcode = "fail"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "ok" from file /etc/raddb/mods-enabled/always
   always ok {
       rcode = "ok"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "handled" from file /etc/raddb/mods-enabled/always
   always handled {
       rcode = "handled"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "invalid" from file /etc/raddb/mods-enabled/always
   always invalid {
       rcode = "invalid"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "userlock" from file 
/etc/raddb/mods-enabled/always
   always userlock {
       rcode = "userlock"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "notfound" from file 
/etc/raddb/mods-enabled/always
   always notfound {
       rcode = "notfound"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "noop" from file /etc/raddb/mods-enabled/always
   always noop {
       rcode = "noop"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "updated" from file /etc/raddb/mods-enabled/always
   always updated {
       rcode = "updated"
       simulcount = 0
       mpp = no
   }
   # Instantiating module "detail" from file /etc/raddb/mods-enabled/detail
   detail {
       filename = 
"/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d"
       header = "%t"
       permissions = 384
       locking = no
       log_packet_header = no
   }
   # Loaded module rlm_utf8
   # Instantiating module "utf8" from file /etc/raddb/mods-enabled/utf8
   # Instantiating module "echo" from file /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_radutmp
   # Instantiating module "radutmp" from file 
/etc/raddb/mods-enabled/radutmp
   radutmp {
       filename = "/var/log/radius/radutmp"
       username = "%{User-Name}"
       case_sensitive = yes
       check_with_nas = yes
       permissions = 384
       caller_id = yes
   }
   # Loaded module rlm_eap
   # Instantiating module "eap" from file /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 = 1024
   }
    # 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 = "/etc/raddb/certs"
        pem_file_type = yes
        private_key_file = "/etc/raddb/certs/server.pem"
        certificate_file = "/etc/raddb/certs/server.pem"
        ca_file = "/etc/raddb/certs/ca.pem"
        private_key_password = <<< secret >>>
        dh_file = "/etc/raddb/certs/dh"
        fragment_size = 1024
        include_length = yes
        check_crl = no
        cipher_list = "DEFAULT"
        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 = no
     }
    }
    # 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_unpack
   # Instantiating module "unpack" from file /etc/raddb/mods-enabled/unpack
   # Instantiating module "sradutmp" from file 
/etc/raddb/mods-enabled/sradutmp
   radutmp sradutmp {
       filename = "/var/log/radius/sradutmp"
       username = "%{User-Name}"
       case_sensitive = yes
       check_with_nas = yes
       permissions = 420
       caller_id = no
   }
   # Loaded module rlm_sql
   # Instantiating module "sql" from file /etc/raddb/mods-enabled/sql
   sql {
       driver = "rlm_sql_null"
       server = "localhost"
       port = "3306"
       login = "radius"
       password = <<< secret >>>
       radius_db = "wifimacauth"
       read_groups = yes
       read_profiles = yes
       read_clients = no
       delete_stale_sessions = yes
       sql_user_name = "%{User-Name}"
       default_user_profile = ""
       client_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 id, groupname, attribute, 
Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id"
       authorize_group_reply_query = "SELECT id, groupname, attribute, 
value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id"
       group_membership_query = "SELECT groupname FROM radusergroup 
WHERE username = '%{SQL-User-Name}' ORDER BY priority"
       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}"
     type {
      accounting-on {
          query = "UPDATE radacct SET acctstoptime = 
%{%{integer:Event-Timestamp}:-date('now')}, acctsessiontime    = 
%{%{integer:Event-Timestamp}:-strftime('%%s', 'now')} - strftime('%%s', 
acctstarttime)), acctterminatecause = '%{Acct-Terminate-Cause}' WHERE 
acctstoptime IS NULL AND nasipaddress   = '%{NAS-IP-Address}' AND 
acctstarttime <= %{integer:Event-Timestamp}"
      }
      accounting-off {
          query = "UPDATE radacct SET acctstoptime = 
%{%{integer:Event-Timestamp}:-date('now')}, acctsessiontime    = 
%{%{integer:Event-Timestamp}:-strftime('%%s', 'now')} - strftime('%%s', 
acctstarttime)), acctterminatecause = '%{Acct-Terminate-Cause}' WHERE 
acctstoptime IS NULL AND nasipaddress   = '%{NAS-IP-Address}' AND 
acctstarttime <= %{integer:Event-Timestamp}"
      }
      start {
          query = "INSERT INTO radacct (acctsessionid, acctuniqueid,    
     username, realm,            nasipaddress,     nasportid, 
nasporttype,        acctstarttime, acctupdatetime, 
acctstoptime,acctsessiontime,     acctauthentic, connectinfo_start,    
connectinfo_stop,     acctinputoctets, acctoutputoctets,    
calledstationid,     callingstationid, acctterminatecause,    
servicetype,        framedprotocol, framedipaddress) VALUES 
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', 
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', 
%{%{integer:Event-Timestamp}:-date('now')}, 
%{%{integer:Event-Timestamp}:-date('now')}, NULL, '0', 
'%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', 
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', 
'%{Framed-Protocol}', '%{Framed-IP-Address}')"
      }
      interim-update {
          query = "UPDATE radacct SET acctupdatetime  = 
%{%{integer:Event-Timestamp}:-date('now')}, acctinterval    = 0, 
framedipaddress = '%{Framed-IP-Address}', acctsessiontime = 
'%{Acct-Session-Time}', acctinputoctets = %{%{Acct-Input-Gigawords}:-0} 
<< 32 | %{%{Acct-Input-Octets}:-0}, acctoutputoctets = 
%{%{Acct-Output-Gigawords}:-0} << 32 | %{%{Acct-Output-Octets}:-0} WHERE 
acctsessionid     = '%{Acct-Session-Id}' AND username            = 
'%{SQL-User-Name}' AND nasipaddress        = '%{NAS-IP-Address}'"
      }
      stop {
          query = "UPDATE radacct SET acctstoptime    = 
%{%{integer:Event-Timestamp}:-date('now')}, acctsessiontime    = 
'%{Acct-Session-Time}', acctinputoctets    = 
%{%{Acct-Input-Gigawords}:-0} << 32 | %{%{Acct-Input-Octets}:-0}, 
acctoutputoctets = %{%{Acct-Output-Gigawords}:-0} << 32 | 
%{%{Acct-Output-Octets}:-0}, acctterminatecause = 
'%{Acct-Terminate-Cause}', connectinfo_stop = '%{Connect-Info}' WHERE 
acctsessionid     = '%{Acct-Session-Id}' AND username        = 
'%{SQL-User-Name}' AND nasipaddress    = '%{NAS-IP-Address}'"
      }
     }
    }
    post-auth {
        reference = ".query"
        query = "INSERT INTO radpostauth (username, pass, reply, 
authdate) VALUES ( '%{SQL-User-Name}', 
'%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')"
    }
   }
rlm_sql (sql): Driver rlm_sql_null (module rlm_sql_null) loaded and linked
rlm_sql (sql): Attempting to connect to database "wifimacauth"
rlm_sql (sql): Initialising connection pool
    pool {
        start = 5
        min = 4
        max = 32
        spare = 3
        uses = 0
        lifetime = 0
        cleanup_interval = 30
        idle_timeout = 60
        retry_delay = 1
        spread = no
    }
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_dhcp
   # Instantiating module "dhcp" from file /etc/raddb/mods-enabled/dhcp
   # Loaded module rlm_passwd
   # Instantiating module "etc_passwd" from file 
/etc/raddb/mods-enabled/passwd
   passwd etc_passwd {
       filename = "/etc/passwd"
       format = "*User-Name:Crypt-Password:"
       delimiter = ":"
       ignore_nislike = no
       ignore_empty = yes
       allow_multiple_keys = no
       hash_size = 100
   }
rlm_passwd: nfields: 3 keyfield 0(User-Name) listable: no
   # Loaded module rlm_mschap
   # Instantiating module "mschap" from file /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_realm
   # Instantiating module "IPASS" from file /etc/raddb/mods-enabled/realm
   realm IPASS {
       format = "prefix"
       delimiter = "/"
       ignore_default = no
       ignore_null = no
   }
   # Instantiating module "suffix" from file /etc/raddb/mods-enabled/realm
   realm suffix {
       format = "suffix"
       delimiter = "@"
       ignore_default = no
       ignore_null = no
   }
   # Instantiating module "realmpercent" from file 
/etc/raddb/mods-enabled/realm
   realm realmpercent {
       format = "suffix"
       delimiter = "%"
       ignore_default = no
       ignore_null = no
   }
   # Instantiating module "ntdomain" from file /etc/raddb/mods-enabled/realm
   realm ntdomain {
       format = "prefix"
       delimiter = "\\"
       ignore_default = no
       ignore_null = no
   }
   # Loaded module rlm_soh
   # Instantiating module "soh" from file /etc/raddb/mods-enabled/soh
   soh {
       dhcp = yes
   }
   # Loaded module rlm_files
   # Instantiating module "files" from file /etc/raddb/mods-enabled/files
   files {
       filename = "/etc/raddb/mods-config/files/authorize"
       usersfile = "/etc/raddb/mods-config/files/authorize"
       acctusersfile = "/etc/raddb/mods-config/files/accounting"
       preproxy_usersfile = "/etc/raddb/mods-config/files/pre-proxy"
       compat = "cistron"
   }
reading pairlist file /etc/raddb/mods-config/files/authorize
[/etc/raddb/mods-config/files/authorize]:2 Cistron compatibility checks 
for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:184 Cistron compatibility 
checks for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:191 Cistron compatibility 
checks for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:198 Cistron compatibility 
checks for entry DEFAULT ...
reading pairlist file /etc/raddb/mods-config/files/authorize
[/etc/raddb/mods-config/files/authorize]:2 Cistron compatibility checks 
for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:184 Cistron compatibility 
checks for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:191 Cistron compatibility 
checks for entry DEFAULT ...
[/etc/raddb/mods-config/files/authorize]:198 Cistron compatibility 
checks for entry DEFAULT ...
reading pairlist file /etc/raddb/mods-config/files/accounting
reading pairlist file /etc/raddb/mods-config/files/pre-proxy
   # Loaded module rlm_replicate
   # Instantiating module "replicate" from file 
/etc/raddb/mods-enabled/replicate
   # Loaded module rlm_attr_filter
   # Instantiating module "attr_filter.post-proxy" from file 
/etc/raddb/mods-enabled/attr_filter
   attr_filter attr_filter.post-proxy {
       filename = "/etc/raddb/mods-config/attr_filter/post-proxy"
       key = "%{Realm}"
       relaxed = no
   }
reading pairlist file /etc/raddb/mods-config/attr_filter/post-proxy
   # Instantiating module "attr_filter.pre-proxy" from file 
/etc/raddb/mods-enabled/attr_filter
   attr_filter attr_filter.pre-proxy {
       filename = "/etc/raddb/mods-config/attr_filter/pre-proxy"
       key = "%{Realm}"
       relaxed = no
   }
reading pairlist file /etc/raddb/mods-config/attr_filter/pre-proxy
   # Instantiating module "attr_filter.access_reject" from file 
/etc/raddb/mods-enabled/attr_filter
   attr_filter attr_filter.access_reject {
       filename = "/etc/raddb/mods-config/attr_filter/access_reject"
       key = "%{User-Name}"
       relaxed = no
   }
reading pairlist file /etc/raddb/mods-config/attr_filter/access_reject
   # Instantiating module "attr_filter.access_challenge" from file 
/etc/raddb/mods-enabled/attr_filter
   attr_filter attr_filter.access_challenge {
       filename = "/etc/raddb/mods-config/attr_filter/access_challenge"
       key = "%{User-Name}"
       relaxed = no
   }
reading pairlist file /etc/raddb/mods-config/attr_filter/access_challenge
   # Instantiating module "attr_filter.accounting_response" from file 
/etc/raddb/mods-enabled/attr_filter
   attr_filter attr_filter.accounting_response {
       filename = "/etc/raddb/mods-config/attr_filter/accounting_response"
       key = "%{User-Name}"
       relaxed = no
   }
reading pairlist file /etc/raddb/mods-config/attr_filter/accounting_response
  } # modules
radiusd: #### Loading Virtual Servers ####
server { # from file /etc/raddb/radiusd.conf
} # server
server default { # from file /etc/raddb/sites-enabled/default
  # Creating Auth-Type = digest
  # Loading authenticate {...}
  # Loading authorize {...}
Ignoring "ldap" (see raddb/mods-available/README.rst)
  # Loading preacct {...}
  # Loading accounting {...}
  # Loading post-proxy {...}
  # Loading post-auth {...}
} # server default
server uw_portal { # from file /etc/raddb/sites-enabled/uw_portal
  # Loading authorize {...}
} # server uw_portal
server inner-tunnel { # from file /etc/raddb/sites-enabled/inner-tunnel
  # Loading authenticate {...}
  # Loading authorize {...}
  # Loading session {...}
  # Loading post-proxy {...}
  # Loading post-auth {...}
} # server inner-tunnel
radiusd: #### Opening IP addresses and Ports ####
listen {
       type = "auth"
       ipaddr = *
       port = 0
    limit {
        max_connections = 16
        lifetime = 0
        idle_timeout = 30
    }
}
listen {
       type = "acct"
       ipaddr = *
       port = 0
    limit {
        max_connections = 16
        lifetime = 0
        idle_timeout = 30
    }
}
listen {
       type = "auth"
       ipaddr = 127.0.0.1
       port = 18120
}
Listening on auth address * port 1812 as server default
Listening on acct address * port 1813 as server default
Listening on auth address 127.0.0.1 port 18120 as server inner-tunnel
Opening new proxy socket 'proxy address * port 0'
Listening on proxy address * port 52549
Ready to process requests

(7) Received Access-Request Id 120 from 172.16.2.202:43036 to 
10.10.10.10:1812 length 218
(7)   NAS-IP-Address = 172.16.2.222
(7)   NAS-Port = 0
(7)   NAS-Port-Type = Wireless-802.11
(7)   User-Name = 'c8:bc:c8:ed:27:68'
(7)   User-Password = 'c8:bc:c8:ed:27:68'
(7)   Service-Type = Login-User
(7)   Calling-Station-Id = 'c8:bc:c8:ed:27:68'
(7)   Called-Station-Id = '00:1a:1e:00:2b:30'
(7)   Aruba-Essid-Name = 'uw-unsecured'
(7)   Aruba-Location-Id = 'AS-AP-MC-1095-B'
(7)   Aruba-AP-Group = 'MC-std'
(7)   Attr-26 = 0x000039e70c02
(7)   Message-Authenticator = 0xcd12451619e8e44483843b9a1330e5a7
(7) session-state: No State attribute
(7) # Executing section authorize from file /etc/raddb/sites-enabled/default
(7)   authorize {
(7)     policy filter_username {
(7)       if (!&User-Name) {
(7)       if (!&User-Name)  -> FALSE
(7)       if (&User-Name =~ / /) {
(7)       if (&User-Name =~ / /)  -> FALSE
(7)       if (&User-Name =~ /@.*@/ ) {
(7)       if (&User-Name =~ /@.*@/ )  -> FALSE
(7)       if (&User-Name =~ /\.\./ ) {
(7)       if (&User-Name =~ /\.\./ )  -> FALSE
(7)       if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  {
(7)       if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))   
-> FALSE
(7)       if (&User-Name =~ /\.$/)  {
(7)       if (&User-Name =~ /\.$/)   -> FALSE
(7)       if (&User-Name =~ /@\./)  {
(7)       if (&User-Name =~ /@\./)   -> FALSE
(7)     } # policy filter_username = notfound
(7)     [preprocess] = ok
(7)     [chap] = noop
(7)     [mschap] = noop
(7)     [digest] = noop
(7) suffix: Checking for suffix after "@"
(7) suffix: No '@' in User-Name = "c8:bc:c8:ed:27:68", looking up realm NULL
(7) suffix: No such realm "NULL"
(7)     [suffix] = noop
(7) eap: No EAP-Message, not doing EAP
(7)     [eap] = noop
(7) files: users: Matched entry DEFAULT at line 2
(7)     [files] = ok
(7)     [expiration] = noop
(7)     [logintime] = noop
(7)     [pap] = noop
(7)   } # authorize = ok
Proxying to virtual server uw_portal
(7) session-state: No State attribute
(7) # Executing section authorize from file 
/etc/raddb/sites-enabled/uw_portal
(7)   authorize {
(7)     if ("%{sql:SELECT COUNT(macaddress) FROM `radmacauthgroup` WHERE 
macaddress = '%{User-Name}'" > 0){
(7)     EXPAND %{User-Name}
(7)        --> c8:bc:c8:ed:27:68
(7)     SQL-User-Name set to 'c8:bc:c8:ed:27:68'
rlm_sql (sql): Reserved connection (4)
rlm_sql (sql): Executing query: 'SELECT COUNT(macaddress) FROM 
`radmacauthgroup` WHERE macaddress = 'c8:bc:c8:ed:27:68''
(7)     SQL query returned no results
rlm_sql (sql): Released connection (4)
(7)     EXPAND %{sql:SELECT COUNT(macaddress) FROM `radmacauthgroup` 
WHERE macaddress = '%{User-Name}'
(7)        -->
(7)     if ("%{sql:SELECT COUNT(macaddress) FROM `radmacauthgroup` WHERE 
macaddress = '%{User-Name}'" > 0) -> FALSE
(7)     else {
(7)       [reject] = reject
(7)     } # else = reject
(7)   } # authorize = reject
(7) Invalid user: [c8:bc:c8:ed:27:68] (from client wn-wc-phy-211-b port 
0 cli c8:bc:c8:ed:27:68 via TLS tunnel)
(7) Using Post-Auth-Type Reject
(7) Finished internally proxied request.
(7) # Executing section post-proxy from file 
/etc/raddb/sites-enabled/default
(7)   post-proxy {
(7) eap: No pre-existing handler found
(7)     [eap] = noop
(7)   } # post-proxy = noop
(7) Login incorrect (Home Server says so): [c8:bc:c8:ed:27:68] (from 
client wn-wc-phy-211-b port 0 cli c8:bc:c8:ed:27:68)
(7) Using Post-Auth-Type Reject
(7) # Executing group from file /etc/raddb/sites-enabled/default
(7)   Post-Auth-Type REJECT {
(7) attr_filter.access_reject: EXPAND %{User-Name}
(7) attr_filter.access_reject:    --> c8:bc:c8:ed:27:68
(7) attr_filter.access_reject: Matched entry DEFAULT at line 11
(7)     [attr_filter.access_reject] = updated
(7) eap: Request didn't contain an EAP-Message, not inserting EAP-Failure
(7)     [eap] = noop
(7)     policy remove_reply_message_if_eap {
(7)       if (&reply:EAP-Message && &reply:Reply-Message) {
(7)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(7)       else {
(7)         [noop] = noop
(7)       } # else = noop
(7)     } # policy remove_reply_message_if_eap = noop
(7)   } # Post-Auth-Type REJECT = updated
(7) Delaying response for 1.000000 seconds


Thanks for your time.

Dave


-- 
Dave Aldwinckle
Network Support Specialist
Information Systems and Technology, TIS
Phone: (519)-888-4567 ext. 31145



More information about the Freeradius-Users mailing list