修改 radpostauth 的表结构:
mysql> drop table radpostauth;
mysql> CREATE TABLE radpostauth (
id int(11) NOT NULL auto_increment,
username varchar(64) NOT NULL default '',
pass varchar(64) NOT NULL default '',
nasipaddress varchar(15) NOT NULL default '',
clientipaddress varchar(100) NOT NULL default '',
reply varchar(32) NOT NULL default '',
replymessage varchar(100) NOT NULL default '',
authdate timestamp NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
Freeradius 3.x 下修改raddb/mods-config/sql/main/mysql/queries.conf
将
post-auth {
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
# logfile = ${logdir}/post-auth.sql
query = "\
INSERT INTO ${..postauth_table} \
(username, pass, reply, authdate) \
VALUES ( \
'%{SQL-User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{Calling-Station-Id}', \
'%{reply:Packet-Type}', \
'%S')"
}
修改为
post-auth {
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
# logfile = ${logdir}/post-auth.sql
query = "\
INSERT INTO ${..postauth_table} \
(username, nasipaddress, clientipaddress, reply, authdate) \
VALUES ( \
'%{SQL-User-Name}', \
'%{Client-IP-Address}', \
'%{Calling-Station-Id}', \
'%{reply:Packet-Type}', \
'%S')"
}
Freeradius 2.x 下
修改/usr/local/radius/etc/raddb/sql/mysql/dialup.conf
将
postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, reply, authdate) \
VALUES ( \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type}', '%S')"
修改为
postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, nasipaddress, clientipaddress, reply, authdate) \
VALUES ( \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{Client-IP-Address}', \
'%{Calling-Station-Id}', \
'%{reply:Packet-Type}', \
'%S')"
登陆的错误信息到数据库
在/usr/local/radius/etc/raddb/modules/attr_rewrite最末添加如下
attr_rewrite sanereplymessage {
attribute = Reply-Message
searchin = reply
searchfor = "\n|\r"
replacewith = ""
ignore_case = no
new_attribute = no
max_matches = 10
append = no
}
修改/usr/local/radius/etc/raddb/sites-enabled/default,在sql前面增加sanereplymessage
Post-Auth-Type REJECT {
..
sanereplymessage
sql
..
}
那么/usr/local/radius/etc/raddb/sql/mysql/dialup.conf就要修改为如下
postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, nasipaddress, clientipaddress, reply, replymessage, authdate) \
VALUES ( \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{Client-IP-Address}', \
'%{Calling-Station-Id}', \
'%{reply:Packet-Type}', \
'%{Module-Failure-Message}-%{reply:Reply-Message}', \
'%S')"
当然需要先在psotauth表里添加相应的字段先.
因为 NAS-IP-Address 很多时候都是 "127.0.0.1" .所以替换dialup.conf中的%{NAS-IP-Address}替换为%{Client-IP-Address}
添加字段
alter table radpostauth add column replymessage varchar(100) after reply;