setup radius server using mysql

setup_radsrv_mysql.sh

#!/bin/bash
#

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_ROOT_PWD=mysql123

# 数据库名字默认为radius，建议不改
MYSQL_RADIUS_USER=freeradius
MYSQL_RADIUS_PWD=freeradius123

# 初始配置的用户和nas
RADIUS_USER1=test1
RADIUS_USER2=test2
RADIUS_PASSWORD=vpn123456
RADIUS_NAS_PASSWORD=testing123

# 安装必要的软件包, freeradius-2.1.12+dfsg-1.2ubuntu8
apt-get install -y freeradius freeradius-mysql

# 创建数据库
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT -uroot -p$MYSQL_ROOT_PWD <<EOF
CREATE DATABASE radius;
grant all on radius.* to $MYSQL_RADIUS_USER IDENTIFIED BY "$MYSQL_RADIUS_PWD";
EOF

# for sql/mysql/dialup.conf
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT -uroot -p$MYSQL_ROOT_PWD radius < /etc/freeradius/sql/mysql/schema.sql
# for clients.conf
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT -uroot -p$MYSQL_ROOT_PWD radius < /etc/freeradius/sql/mysql/nas.sql

# init mysql
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT -uroot -p$MYSQL_ROOT_PWD radius <<EOF
insert into nas(nasname, shortname, secret) VALUES("127.0.0.1", "localhost", "testing123");
insert into radcheck(username, attribute, op, value) values
	("$RADIUS_USER1", "Cleartext-Password", ":=", "$RADIUS_PASSWORD"), 
	("$RADIUS_USER1", "Simultaneous-Use", ":=", "1"), 
	("$RADIUS_USER1", "Expiration", ":=", "06 Jun 2015 14:55:22");
insert into radcheck(username, attribute, op, value) values
	("$RADIUS_USER2", "Cleartext-Password", ":=", "$RADIUS_PASSWORD"), 
	("$RADIUS_USER2", "Simultaneous-Use", ":=", "1"), 
	("$RADIUS_USER2", "Max-All-Session", ":=", "1800");
EOF

# 修改radiusd.conf
cp /etc/freeradius/radiusd.conf /etc/freeradius/radiusd.conf.old
cat > /etc/freeradius/radiusd.conf <<EOF
prefix = /usr
exec_prefix = /usr
sysconfdir = /etc
localstatedir = /var
sbindir = \${exec_prefix}/sbin
logdir = /var/log/freeradius
raddbdir = /etc/freeradius
radacctdir = \${logdir}/radacct
name = freeradius
confdir = \${raddbdir}
run_dir = \${localstatedir}/run/\${name}
db_dir = \${raddbdir}

libdir = /usr/lib/freeradius
pidfile = \${run_dir}/\${name}.pid

user = freerad
group = freerad

max_request_time = 30
cleanup_delay = 5
max_requests = 65536

listen {
	type = auth
	ipaddr = *
	port = 1812
}

listen {
	ipaddr = *
	port = 1813
	type = acct
}

hostname_lookups = no
allow_core_dumps = no
regular_expressions = yes
extended_expressions = yes

log {
	destination = files
	file = \${logdir}/radius.log
	syslog_facility = daemon
	stripped_names = no
	auth = no
	auth_badpass = no
	auth_goodpass = no
}

checkrad = \${sbindir}/checkrad
security {
	max_attributes = 200
	reject_delay = 1
	status_server = yes
}

proxy_requests  = no

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/
	\$INCLUDE eap.conf
	\$INCLUDE sql.conf
	\$INCLUDE timelimit.conf
}

instantiate {
	exec
	expr
	expiration
	logintime
}

\$INCLUDE policy.conf
\$INCLUDE sites-enabled/
EOF
 
# 设置sites
rm /etc/freeradius/sites-enabled/*
cat > /etc/freeradius/sites-enabled/my.conf <<EOF
authorize {
	if(NAS-IP-Address) {
		reject
        }
	preprocess
	chap
	mschap
	digest
	suffix
	eap {
		ok = return
	}
	sql
	#expiration
	#logintime
	pap
	timelimit
}

authenticate {
	Auth-Type PAP {
		pap
	}
	Auth-Type CHAP {
		chap
	}
	Auth-Type MS-CHAP {
		mschap
	}
	digest
	eap
}


preacct {
	preprocess
	acct_unique
	suffix
	#files
}

#
#  Accounting.  Log the accounting data.
#
accounting {
	detail
	#unix
	#radutmp
	sql
	if (noop) {
		ok
	}
	exec
	attr_filter.accounting_response
}

session {
	#radutmp
	sql
}


post-auth {
	sql
	exec
	Post-Auth-Type REJECT {
		attr_filter.access_reject
	}
}
EOF
 
# 修改sql.conf
cp /etc/freeradius/sql.conf /etc/freeradius/sql.conf.old
cat >/etc/freeradius/sql.conf <<EOF
sql {
	database = "mysql"
	driver = "rlm_sql_\${database}"
	server = "$MYSQL_HOST"
	port = $MYSQL_PORT
	login = "$MYSQL_RADIUS_USER"
	password = "$MYSQL_RADIUS_PWD"
	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 = yes
	sqltrace = yes
	sqltracefile = \${logdir}/sqltrace.sql
	num_sql_socks = 5

	connect_failure_retry_delay = 60
	lifetime = 0
	max_queries = 0
	readclients = yes
	nas_table = "nas"
	\$INCLUDE sql/\${database}/dialup.conf
}
EOF

# gen timelimit.conf
cat >/etc/freeradius/timelimit.conf <<EOF
sqlcounter timelimit {
        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}'"                         
}
EOF

# 如果需要最大连接数配置生效
# 需要手工反注释掉sql/mysql/dialup.conf中
# sql 语句: simul_count_query

# 启动freeradius
service freeradius start
 
# 测试freeradius
radtest $RADIUS_USER $RADIUS_PASSWORD localhost 0 $RADIUS_NAS_PASSWORD