Kusprayitna Blogs

Hidup untuk berbagi ilmu :: IT Linux Windows Database Oracle PHP OpenSource

Setup freeradius agar support database MySQL

Posted by kusprayitna on February 18th, 2010

Setelah berhasil setup freeradius standar, maksudnya usernya disimpan difile, dimana ini sangat tidak fleksibel, maka tahapan berikut adalah setup freeradius agar support database MySQL;

1.  Menyiapkan database MySQL

Untuk instalasi database MySQL sendiri bisa dibaca di artikel http://kusprayitna.staff.uii.ac.id/2008/08/21/instalasi-mysql-server-cpanel-plus-pphpmyadmin/

Login kedatabase server dan lakukan berikut ini:

a. Buat database radius_db
b. Buat user radius, dengan hak bisa  membuat/akses radius_% database

2. Import script standar database MySQL untuk radius.

Ini dapat dicari banyak di internet

drop table if exists  radacct;
CREATE TABLE radacct (
RadAcctId bigint(21) NOT NULL auto_increment,
AcctSessionId varchar(32) NOT NULL default '',
AcctUniqueId varchar(32) NOT NULL default '',
UserName varchar(64) NOT NULL default '',
Realm varchar(64) default '',
NASIPAddress varchar(15) NOT NULL default '',
NASPortId varchar(15) default NULL,
NASPortType varchar(32) default NULL,
AcctStartTime datetime NULL default '0000-00-00 00:00:00',
AcctStopTime datetime NULL default '0000-00-00 00:00:00',
AcctSessionTime int(12) default NULL,
AcctAuthentic varchar(32) default NULL,
ConnectInfo_start varchar(50) default NULL,
ConnectInfo_stop varchar(50) default NULL,
AcctInputOctets bigint(12) default NULL,
AcctOutputOctets bigint(12) default NULL,
CalledStationId varchar(50) NOT NULL default '',
CallingStationId varchar(50) NOT NULL default '',
AcctTerminateCause varchar(32) NOT NULL default '',
ServiceType varchar(32) default NULL,
FramedProtocol varchar(32) default NULL,
FramedIPAddress varchar(15) NOT NULL default '',
AcctStartDelay int(12) default NULL,
AcctStopDelay int(12) default NULL,
PRIMARY KEY  (RadAcctId),
KEY UserName (UserName),
KEY FramedIPAddress (FramedIPAddress),
KEY AcctSessionId (AcctSessionId),
KEY AcctUniqueId (AcctUniqueId),
KEY AcctStartTime (AcctStartTime),
KEY AcctStopTime (AcctStopTime),
KEY NASIPAddress (NASIPAddress)
) ;

drop table if exists  radcheck;
CREATE TABLE radcheck (
id int(11) unsigned NOT NULL auto_increment,
UserName varchar(64) NOT NULL default '',
Attribute varchar(32)  NOT NULL default '',
op char(2) NOT NULL DEFAULT '==',
Value varchar(253) NOT NULL default '',
PRIMARY KEY  (id),
KEY UserName (UserName(32))
) ;

drop table if exists  radgroupcheck;
CREATE TABLE radgroupcheck (
id int(11) unsigned NOT NULL auto_increment,
GroupName varchar(64) NOT NULL default '',
Attribute varchar(32)  NOT NULL default '',
op char(2) NOT NULL DEFAULT '==',
Value varchar(253)  NOT NULL default '',
PRIMARY KEY  (id),
KEY GroupName (GroupName(32))
) ;

drop table if exists  radgroupreply;
CREATE TABLE radgroupreply (
id int(11) unsigned NOT NULL auto_increment,
GroupName varchar(64) NOT NULL default '',
Attribute varchar(32)  NOT NULL default '',
op char(2) NOT NULL DEFAULT '=',
Value varchar(253)  NOT NULL default '',
PRIMARY KEY  (id),
KEY GroupName (GroupName(32))
) ;

drop table if exists  radreply;
CREATE TABLE radreply (
id int(11) unsigned NOT NULL auto_increment,
UserName varchar(64) NOT NULL default '',
Attribute varchar(32) NOT NULL default '',
op char(2) NOT NULL DEFAULT '=',
Value varchar(253) NOT NULL default '',
PRIMARY KEY  (id),
KEY UserName (UserName(32))
) ;

drop table if exists  usergroup;
CREATE TABLE usergroup (
UserName varchar(64) NOT NULL default '',
GroupName varchar(64) NOT NULL default '',
priority int(11) NOT NULL default '1',
KEY UserName (UserName(32))
) ;

drop table if exists  radpostauth;
CREATE TABLE radpostauth (
id int(11) NOT NULL auto_increment,
user varchar(64) NOT NULL default '',
pass varchar(64) NOT NULL default '',
reply varchar(32) NOT NULL default '',
date timestamp(14) NOT NULL,
PRIMARY KEY  (id)
) ;

#  The next table is commented out because it is not
#  currently used in the server.
#

#
# Table structure for table 'dictionary'
#
drop table if exists  dictionary;
CREATE TABLE dictionary (
id int(10) NOT NULL auto_increment,
Type varchar(30),
Attribute varchar(64),
Value varchar(64),
Format varchar(20),
Vendor varchar(32),
PRIMARY KEY (id)
);

drop table if exists  nas;
CREATE TABLE nas (
id int(10) NOT NULL auto_increment,
nasname varchar(128) NOT NULL,
shortname varchar(32),
type varchar(30) DEFAULT 'other',
ports int(5),
secret varchar(60) DEFAULT 'secret' NOT NULL,
community varchar(50),
description varchar(200) DEFAULT 'RADIUS Client',
PRIMARY KEY (id),
KEY nasname (nasname)
);

3. Inisialisasi database dengan data contoh

##contoh isian database
delete from usergroup;
delete from radcheck;
delete from radgroupcheck;
delete from radreply;
delete from radgroupreply;
delete from nas;

insert into nas(nasname,shortname,type) values ('192.168.1.1','NASUII','unix');
insert into usergroup(UserName,GroupName)
values ("prayitna","dynamic"),
("kuswidianta","dynamic");

insert into radcheck(UserName,Attribute,Value,Op)
values
("prayitna","Password","password1","=="),
("kuswidianta","Password","password2","==");

insert into radgroupcheck(GroupName,Attribute,Value,Op)
values
("dynamic","Auth-Type","Local",":="),
("static","Auth-Type","Local",":=");
insert into radgroupreply(GroupName,Attribute,Value,Op)
values
("dynamic","Framed-Compression","Van-Jacobsen-TCP-IP","=="),
("dynamic","Framed-Protocol","PPP","=="),
("dynamic","Service-Type","Framed-User","=="),
("dynamic","Framed-MTU","1500","=="),
("static","Framed-Protocol","PPP",":="),
("static","Service-Type","Framed-User",":="),
("static","Framed-Compression","Van-Jacobsen-TCP-IP",":=");

Dalam script ini dibuatkan user prayitna (password1) dan kuswidianta (password2).

4. Edit radius.conf agar support SQL


# vi /etc/raddb/radiusd.conf

a.    Aktifkan SQL dengan cara sesuaikan baris berikut:

#       $INCLUDE  ${confdir}/sql.conf
menjadi
$INCLUDE  ${confdir}/sql.conf

b.    Aktifkan authorize  dengan SQL

#  Look in an SQL database.  The schema of the database
#  is meant to mirror the "users" file.
#
#  See "Authorization Queries" in sql.conf
sql

c.    Aktifkan accounting  dengan SQL

#
#  Log traffic to an SQL database.
#
#  See "Accounting queries" in sql.conf
sql

d.    Tangani session dengan SQL

#  Session database, used for checking Simultaneous-Use. Either the radutmp
#  or rlm_sql module can handle this.
#  The rlm_sql module is *much* faster
session {
#radutmp

#
#  See "Simultaneous Use Checking Querie" in sql.conf
sql
}

e.    Yang berhasil otentifikasi disimpan di SQL (post-auth )

#
#  After authenticating the user, do another SQL query.
#
#  See "Authentication Logging Queries" in sql.conf
sql

5. Sesuaikan parameter server MySQL di sql.conf

# vi /etc/raddb/sql.conf

sql {
# Database type
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
# rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc, rlm_sql_freetds
driver = "rlm_sql_mysql"

# Connect info
server = "localhost"
login = "radius"
password = "radius"

# Database table configuration
radius_db = "radius_db"

# If you want both stop and start records logged to the
# same SQL table, leave this as is.  If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = "radacct"
acct_table2 = "radacct"

# Allow for storing data after authentication
postauth_table = "radpostauth"

authcheck_table = "radcheck"
authreply_table = "radreply"

groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"

usergroup_table = "usergroup"

# Table to keep radius client info
nas_table = "nas"

# Remove stale session if checkrad does not see a double login
deletestalesessions = yes

# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql

# number of sql connections to make to server
num_sql_socks = 5

# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60

sql_user_name = "%{User-Name}"

6. Restart service radius

# service radius restart

Stopping RADIUS server:                                    [  OK  ]
Starting RADIUS server: Tue Jan 26 12:06:48 2010 : Info: Starting - reading configuration files ...
[FAILED]

Cek sebab errornya di log
# tail /var/log/radius/radius.log

Tue Jan 26 10:25:22 2010 : Info: Using deprecated naslist file.  Support for this will go away soon.
Tue Jan 26 10:25:22 2010 : Info: rlm_exec: Wait=yes but no output defined. Did you mean output=none?
Tue Jan 26 10:25:22 2010 : Info: Ready to process requests.
Tue Jan 26 12:06:48 2010 : Info: Using deprecated naslist file.  Support for this will go away soon.
Tue Jan 26 12:06:48 2010 : Info: rlm_exec: Wait=yes but no output defined. Did you mean output=none?
Tue Jan 26 12:06:48 2010 : Error: ERROR: Cannot find a configuration entry for module "sql".
Tue Jan 26 12:06:48 2010 : Error: radiusd.conf[1844] Unknown module "sql".
Tue Jan 26 12:06:48 2010 : Error: radiusd.conf[1773] Failed to parse authorize section.

Error diatas ternyata disebabkan point  4.a belum dilakukan, maka sesuaikan dan restart ulang

Starting RADIUS server: Tue Jan 26 12:12:21 2010 : Info: Starting - reading configuration files ...
[  OK  ]

Jika berhasil, bisa di cek di log radius.log bahwa radius melakukan akses ke MySQL

Tue Jan 26 12:12:21 2010 : Info: Using deprecated naslist file.  Support for this will go away soon.
Tue Jan 26 12:12:21 2010 : Info: rlm_exec: Wait=yes but no output defined. Did you mean output=none?
Tue Jan 26 12:12:21 2010 : Info: rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Tue Jan 26 12:12:21 2010 : Info: rlm_sql (sql): Attempting to connect to radius@localhost:/radius_db
Tue Jan 26 12:12:21 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #0
Tue Jan 26 12:12:21 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #1
Tue Jan 26 12:12:21 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #2
Tue Jan 26 12:12:21 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #3
Tue Jan 26 12:12:21 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #4
Tue Jan 26 12:12:21 2010 : Info: Ready to process requests.

7. Cek radius-mysql


# radtest prayitna password1 127.0.0.1 1812 radiusuii

Sending Access-Request of id 141 to 127.0.0.1 port 1812
User-Name = "prayitna"
User-Password = "password1"
NAS-IP-Address = 255.255.255.255
NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=141, length=44
Framed-Compression = Van-Jacobson-TCP-IP
Framed-Protocol = PPP
Service-Type = Framed-User
Framed-MTU = 1500

Dari response di atas dapat dilihat bahwa:
a.    Otentifikasi dengan MySQL sudah berjalan
b.    Response groupreplay sudah berjalan

8. Cek di database MySQL radius_db


a.    Radpostout --> sudah tercatat yang berhasil otentifikasi
b.    Radacct --> belum tercatat

selesai

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>