On 2009-Apr-17, at 03:08, Alan DeKok wrote:
Guy Fraser wrote:
I have installed : "radiusd: FreeRADIUS Version 2.1.3, for host i386-portbld-freebsd7.1, built on Feb 26 2009 at 15:47:46"
I have not been able figure out how to get it to log failed authentication attempts into the radpostauth sql table, like I had it working in Version 1.
What do you mean by that?
Q: "I tried to do stuff, but it didn't work". A: Huh?
I thought this would be enough to make it log failed authentications : log { destination = files file = ${logdir}/radius.log requests = ${logdir}/radacct/radiusd-%{%{Virtual-Server}:-DEFAULT}-%Y %m%d.log syslog_facility = daemon stripped_names = no auth = yes auth_badpass = yes auth_goodpass = no } Here is the recursive, uncommented and redacted configuration : ------- prefix = /usr/local exec_prefix = ${prefix} sysconfdir = ${prefix}/etc localstatedir = /var sbindir = ${exec_prefix}/sbin logdir = /var/log raddbdir = ${sysconfdir}/raddb radacctdir = ${logdir}/radacct name = radiusd confdir = ${raddbdir} run_dir = ${localstatedir}/run/radiusd db_dir = ${raddbdir} libdir = /usr/local/lib/freeradius-2.1.3 pidfile = ${run_dir}/${name}.pid user = freeradius group = freeradius max_request_time = 30 cleanup_delay = 5 max_requests = 1024 listen { type = auth ipaddr = * port = 1645 } listen { ipaddr = * port = 1646 type = acct } hostname_lookups = no allow_core_dumps = no regular_expressions = yes extended_expressions = yes log { destination = files file = ${logdir}/radius.log requests = ${logdir}/radacct/radiusd-%{%{Virtual-Server}:-DEFAULT}-%Y %m%d.log syslog_facility = daemon stripped_names = no auth = yes auth_badpass = yes auth_goodpass = no } checkrad = ${sbindir}/checkrad security { max_attributes = 200 reject_delay = 1 status_server = yes } proxy_requests = yes $INCLUDE proxy.conf #start : proxy.conf# proxy server { default_fallback = no } home_server localhost { type = auth ipaddr = 127.0.0.1 port = 1645 secret = XXXXXXX response_window = 20 zombie_period = 40 revive_interval = 120 status_check = status-server check_interval = 30 num_answers_to_alive = 3 } home_server_pool my_auth_failover { type = fail-over home_server = localhost } realm LOCAL { type = radius authhost = LOCAL accthost = LOCAL } realm domain.net { type = radius authhost = LOCAL accthost = LOCAL } realm customer.com { type = radius authhost = x.x.x.x:1645 accthost = x.x.x.x:1646 secret = XXXXXXX nostrip } ... #end# $INCLUDE clients.conf #start : clients.conf# client localhost { ipaddr = 127.0.0.1 secret = XXXXXXX require_message_authenticator = no nastype = other } #end# thread pool { start_servers = 5 max_servers = 32 min_spare_servers = 3 max_spare_servers = 10 max_requests_per_server = 0 } modules { $INCLUDE ${confdir}/modules/ #start : modules/*# acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port" } always fail { rcode = fail } always reject { rcode = reject } always noop { rcode = noop } always handled { rcode = handled } always updated { rcode = updated } always notfound { rcode = notfound } always ok { rcode = ok simulcount = 0 mpp = no } attr_filter attr_filter.post-proxy { attrsfile = ${confdir}/attrs } attr_filter attr_filter.pre-proxy { attrsfile = ${confdir}/attrs.pre-proxy } attr_filter attr_filter.access_reject { key = %{User-Name} attrsfile = ${confdir}/attrs.access_reject } attr_filter attr_filter.accounting_response { key = %{User-Name} attrsfile = ${confdir}/attrs.accounting_response } attr_rewrite sanecallerid { attribute = Called-Station-Id searchin = packet searchfor = "[+ ]" replacewith = "" ignore_case = no new_attribute = no max_matches = 10 append = no } chap { } checkval { item-name = Calling-Station-Id check-name = Calling-Station-Id data-type = string } counter daily { filename = ${db_dir}/db.daily key = User-Name count-attribute = Acct-Session-Time reset = daily counter-name = Daily-Session-Time check-name = Max-Daily-Session reply-name = Session-Timeout allowed-servicetype = Framed-User cache-size = 5000 } detail { detailfile = ${radacctdir}/%{Client-IP-Address}/detail-%Y%m%d detailperm = 0600 header = "%t" } detail detail.example.com { detailfile = ${radacctdir}/detail.example.com/detail-%Y%m%d:%H } detail auth_log { detailfile = ${radacctdir}/%{Client-IP-Address}/auth-detail-%Y%m%d detailperm = 0600 suppress { User-Password } } detail reply_log { detailfile = ${radacctdir}/%{Client-IP-Address}/reply-detail-%Y%m%d detailperm = 0600 } detail pre_proxy_log { detailfile = ${radacctdir}/%{Client-IP-Address}/pre-proxy-detail-%Y%m%d detailperm = 0600 } detail post_proxy_log { detailfile = ${radacctdir}/%{Client-IP-Address}/post-proxy-detail-%Y%m %d detailperm = 0600 } digest { } exec echo { wait = yes program = "/bin/echo %{User-Name}" input_pairs = request output_pairs = reply shell_escape = yes } passwd etc_group { filename = /etc/group format = "=Etc-Group-Name:::*,User-Name" hashsize = 50 ignorenislike = yes allowmultiplekeys = yes delimiter = ":" } exec { wait = no input_pairs = request shell_escape = yes output = none } expiration { reply-message = "Password Has Expired\r\n" } expr { } files { usersfile = ${confdir}/users acctusersfile = ${confdir}/acct_users preproxy_usersfile = ${confdir}/preproxy_users compat = no } eap inner-eap { default_eap_type = mschapv2 timer_expire = 60 max_sessions = 2048 md5 { } gtc { auth_type = PAP } mschapv2 { } tls { certdir = ${confdir}/certs cadir = ${confdir}/certs private_key_password = whatever private_key_file = ${certdir}/server.pem certificate_file = ${certdir}/server.pem CA_file = ${cadir}/ca.pem dh_file = ${certdir}/dh random_file = ${certdir}/random cipher_list = "DEFAULT" } } ippool main_pool { range-start = 192.168.1.1 range-stop = 192.168.3.254 netmask = 255.255.255.0 cache-size = 800 session-db = ${db_dir}/db.ippool ip-index = ${db_dir}/db.ipindex override = no maximum-timeout = 0 } krb5 { keytab = /path/to/keytab service_principal = name_of_principle } ldap { server = "ldap.your.domain" basedn = "o=My Org,c=UA" filter = "(uid=%{Stripped-User-Name:-%{User-Name}})" ldap_connections_number = 5 timeout = 4 timelimit = 3 net_timeout = 1 tls { start_tls = no } dictionary_mapping = ${confdir}/ldap.attrmap edir_account_policy_check = no } linelog { filename = ${logdir}/linelog format = "This is a log message for %{User-Name}" reference = "%{%{Packet-Type}:-format}" Access-Request = "Requested access: %{User-Name}" Access-Reject = "Rejected access: %{User-Name}" Access-Challenge = "Sent challenge: %{User-Name}" foo { bar = "Example log. Please ignore" } Accounting-Request { Start = "Connect: [%{User-Name}] (did %{Called-Station-Id} cli % {Calling-Station-Id} port %{NAS-Port} ip %{Framed-IP-Address})" Stop = "Disconnect: [%{User-Name}] (did %{Called-Station-Id} cli % {Calling-Station-Id} port %{NAS-Port} ip %{Framed-IP-Address}) %{Acct- Session-Time} seconds" Alive = "" Accounting-On = "NAS %C (%{NAS-IP-Address}) just came online" Accounting-Off = "NAS %C (%{NAS-IP-Address}) just went offline" unknown = "" } } logintime { reply-message = "You are calling outside your allowed timespan\r\n" minimum-timeout = 60 } passwd mac2ip { filename = ${confdir}/mac2ip format = "*DHCP-Client-Hardware-Address:=DHCP-Your-IP-Address" delimiter = "," } passwd mac2vlan { filename = ${confdir}/mac2vlan format = "*VMPS-Mac:=VMPS-VLAN-Name" delimiter = "," } mschap { } pam { pam_auth = radiusd } pap { auto_header = no } perl { module = ${confdir}/example.pl } policy { filename = ${confdir}/policy.txt } preprocess { huntgroups = ${confdir}/huntgroups hints = ${confdir}/hints with_ascend_hack = no ascend_channels_per_line = 23 with_ntdomain_hack = no with_specialix_jetstream_hack = no with_cisco_vsa_hack = no } radutmp { filename = ${logdir}/radutmp username = %{User-Name} case_sensitive = yes check_with_nas = yes perm = 0600 callerid = "yes" } realm IPASS { format = prefix delimiter = "/" } realm suffix { format = suffix delimiter = "@" } realm realmpercent { format = suffix delimiter = "%" } realm ntdomain { format = prefix delimiter = "\\" } passwd smbpasswd { filename = /etc/smbpasswd format = "*User-Name::LM-Password:NT-Password:SMB-Account-CTRL-TEXT::" hashsize = 100 ignorenislike = no allowmultiplekeys = no } sql_log { path = "${radacctdir}/sql-relay" acct_table = "radacct" postauth_table = "radpostauth" sql_user_name = "%{%{User-Name}:-DEFAULT}" Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \ NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \ AcctSessionTime, AcctTerminateCause) VALUES \ ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \ '%{Framed-IP-Address}', '%S', '0', '0', '');" Stop = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \ NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \ AcctSessionTime, AcctTerminateCause) VALUES \ ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \ '%{Framed-IP-Address}', '0', '%S', '%{Acct-Session-Time}', \ '%{Acct-Terminate-Cause}');" Alive = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \ NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \ AcctSessionTime, AcctTerminateCause) VALUES \ ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \ '%{Framed-IP-Address}', '0', '0', '%{Acct-Session-Time}','');" Post-Auth = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) VALUES \ ('%{User-Name}', '%{User-Password:-Chap-Password}', \ '%{reply:Packet-Type}', '%S');" } sqlcounter expire_on_login { counter-name = Expire-After-Initial-Login check-name = Expire-After sqlmod-inst = sql key = User-Name reset = never query = "SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \ FROM radacct \ WHERE UserName='%{%k}' \ ORDER BY acctstarttime \ LIMIT 1;" } radutmp sradutmp { filename = ${logdir}/sradutmp perm = 0644 callerid = "no" } unix { radwtmp = ${logdir}/radwtmp } wimax { } #end# $INCLUDE sql.conf #start : sql.conf# sql { database = "postgresql" driver = "rlm_sql_${database}" server = "X.X.X.X" port = 5432 login = "radius" password = "XXXXXXXX" radius_db = "radius" acct_table1 = "radacct" acct_table2 = "radacct" postauth_table = "radpostauth" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "radusergroup" deletestalesessions = no sqltrace = yes sqltracefile = ${logdir}/radacct/sqltrace.sql num_sql_socks = 5 connect_failure_retry_delay = 60 readclients = yes nas_table = "nas" $INCLUDE sql/${database}/dialup.conf #start : sql/postgresql/dialup.conf# sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}" nas_query = "SELECT id, nasname, shortname, type, secret FROM $ {nas_table}" authorize_check_query = "SELECT ${authcheck_table}.id, $ {authcheck_table}.UserName, ${authcheck_table}.Attribute, $ {authcheck_table}.Value, ${authcheck_table}.Op \ FROM ${authcheck_table} , ${usergroup_table}\ WHERE ${authcheck_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.UserName = '%{SQL-User-Name}'\ AND ${usergroup_table}.Priority != '-1'\ ORDER BY id" authorize_reply_query = "SELECT ${authreply_table}.id, $ {authreply_table}.UserName, ${authreply_table}.Attribute, $ {authreply_table}.Value, ${authreply_table}.Op \ FROM ${authreply_table} , ${usergroup_table}\ WHERE ${authreply_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.UserName = '%{SQL-User-Name}'\ AND ${usergroup_table}.Priority != '-1'\ ORDER BY id" authorize_group_check_query = "SELECT ${groupcheck_table}.id, $ {groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, $ {groupcheck_table}.Value, ${groupcheck_table}.op \ FROM ${groupcheck_table} , ${usergroup_table}\ WHERE ${groupcheck_table}.GroupName = '%{Sql-Group}' \ ORDER BY id" authorize_group_reply_query = "SELECT ${groupreply_table}.id, $ {groupreply_table}.GroupName, ${groupreply_table}.Attribute, $ {groupreply_table}.Value, ${groupreply_table}.op \ FROM ${groupreply_table} , ${usergroup_table}\ WHERE ${groupreply_table}.GroupName = '%{Sql-Group}' \ ORDER BY id" accounting_onoff_query = "UPDATE ${acct_table1} \ SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay- Time}:-0}'::interval), \ AcctSessionTime = (EXTRACT(EPOCH FROM ('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone \ - '%{%{Acct-Delay-Time}:-0}'::interval)))::BIGINT, \ AcctTerminateCause = '%{Acct-Terminate-Cause}', \ AcctStopDelay = 0 \ WHERE AcctStopTime IS NULL \ AND NASIPAddress= '%{NAS-IP-Address}' \ AND AcctStartTime <= '%S'::timestamp" accounting_update_query = "UPDATE ${acct_table1} \ SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint) \ WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL- User-Name}' \ AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_update_query_alt = "INSERT INTO ${acct_table1} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \ NASPortId, NASPortType, AcctStartTime, \ AcctSessionTime, AcctAuthentic, AcctInputOctets, \ AcctOutputOctets, CalledStationId, CallingStationId, \ ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \ VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', \ %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', \ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct- Session-Time}:-0}'::interval), \ '%{Acct-Session-Time}', '%{Acct-Authentic}', \ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input- Octets}:-0}'::bigint), \ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct- Output-Octets}:-0}'::bigint), \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr- Key}')" accounting_start_query = "INSERT INTO ${acct_table1} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \ NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \ ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, \ FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \ VALUES('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ NULLIF('%{Realm}', ''), \ '%{NAS-IP-Address}', \ %{%{NAS-Port}:-NULL}::integer, \ '%{NAS-Port-Type}', \ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet, \ 0, \ '%{X-Ascend-Session-Svr-Key}')" accounting_start_query_alt = "UPDATE ${acct_table1} \ SET AcctStartTime = ('%S'::timestamp - '%{%{Acct-Delay- Time}:-0}'::interval), \ AcctStartDelay = 0, \ ConnectInfo_start = '%{Connect-Info}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}' \ AND AcctStopTime IS NULL" accounting_stop_query = "UPDATE ${acct_table2} \ SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay- Time}:-0}'::interval), \ AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \ (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE \ - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct- Session-Time}' END, \ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \ AcctTerminateCause = '%{Acct-Terminate-Cause}', \ AcctStopDelay = 0, \ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ ConnectInfo_stop = '%{Connect-Info}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}' \ AND AcctStopTime IS NULL" accounting_stop_query_alt = "INSERT INTO ${acct_table2} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \ AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, \ CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \ values('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ NULLIF('%{Realm}', ''), \ '%{NAS-IP-Address}', \ %{%{NAS-Port}:-NULL}::integer, \ '%{NAS-Port-Type}', \ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct- Session-Time}:-0}'::interval), \ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \ NULLIF('%{Acct-Session-Time}', '')::bigint, '%{Acct-Authentic}', \ '%{Connect-Info}', \ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input- Octets}:-0}'::bigint), \ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct- Output-Octets}:-0}'::bigint), \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Acct-Terminate-Cause}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet, 0)" group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}' ORDER BY priority" postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply, authdate) \ VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '% {reply:Packet-Type}', NOW())" #end# } #end# $INCLUDE sql/postgresql/counter.conf #start : sql/postgresql/counter.conf# sqlcounter dailycounter { counter-name = Daily-Session-Time check-name = Max-Daily-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = daily query = "SELECT SUM(AcctSessionTime - \ GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \ FROM radacct WHERE UserName='%{%k}' AND \ AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'" } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = monthly query = "SELECT SUM(AcctSessionTime - \ GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \ FROM radacct WHERE UserName='%{%k}' AND \ AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'" } sqlcounter noresetcounter { counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'" } #end# } instantiate { exec expr expiration logintime } $INCLUDE policy.conf #start : policy.conf# policy { forbid_eap { if (EAP-Message) { reject } } permit_only_eap { if (!EAP-Message) { if (!"%{outer.request:EAP-Message}") { reject } } } deny_realms { if (User-Name =~ /@|\\/) { reject } } } #end# $INCLUDE sites-enabled/ #start : sites-enabled/default# authorize { preprocess auth_log suffix sql expiration logintime pap } authenticate { Auth-Type PAP { pap } } preacct { preprocess acct_unique suffix files } accounting { detail daily sql sql_log attr_filter.accounting_response } session { sql } post-auth { reply_log sql sql_log exec Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { attr_filter.pre-proxy pre_proxy_log } post-proxy { post_proxy_log attr_filter.post-proxy } #end# ------- The configuration has changed significantly since I last contributed to this project. -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787