OpenRADIUS

Introduction

FAQ

Download

Documentation
   Installation/Usage
   Module interface
   Language
   Packet handling
   Modules
      Ascfile
      Delay
      Radlogger
      RadLDAP
      RadSQL
      Unixpasswd

Configurations
   Pre-paid

Contributed
   Using VSAs
   Installing on OSX

Mailing list...

Add-ons/Services

Other software...

OpenRADIUS RadSQL module


Better documentation will follow; for now, this will have to do. It's the source of the module as of version 0.9.7 and the example schema for MySQL. An example configuration- and behaviour file is included in the 0.9.7 tarball as well.


# Example OpenRADIUS schema for MySQL
#
# 2003/04/28 - EvB

create database openradius;

use openradius;


# Owner of the database

grant all on openradius.* to openradius@localhost identified by 'openradius';


# Data table containing clients, users, groups, realms, hints, huntgroups,
# whatnot; everything that adds attributes, whether for use in subsequent
# queries, for checking or for use in the reply.

create table data (
    id int not null auto_increment,
    space varchar(8) not null,
    name varchar(64) not null,

    attribute varchar(64) not null,
    value varchar(255),

    primary key (id),
    index space_name (space, name)
);


# Logging table

create table log (
    log_id int not null auto_increment,
  
    log_when datetime,
    log_who varchar(64),
    log_what varchar(255),

    primary key (log_id)
);


# Accounting table
#
# 1. To detect duplicates in Stop records (the only ones you need for metered
# billing) while recording them, lock the accounting table and check that the
# query
#
# select acct_id 
#   from accounting 
#  where acct_nas = ? 
#    and acct_sessionid = ? 
#    and acct_timestamp +  >= now()
#
# does not return any records before inserting. The theory behind this is that
# although Acct-Session-Id may collide among NASes and a single NAS may reuse
# the same values quickly, a single NAS' values should be unique over the
# period during which duplicates can be expected.
#
# This period is at most max_retrans_count * timeout, which is typically
# below one minute. Any simplistic NAS' values for Acct-Session-Id should be
# unique within such a short timespan. Even a simple counter that's reset at
# reboot will do as session id that way.
#
# So, we can safely conclude a record is a duplicate -- erring on the safe
# side, i.e. in favour of dropping the record -- if the same NAS generated the
# same Acct-Session-Id in the last minute or two. False negatives are only
# possible if a NAS uses different Session-Ids for the same session, which is
# forbidden by RFC2866.
#
# 2. To match Stop records to Start records (not very useful in most cases, as
# only few broken NASes send information in the Start record that is not
# repeated in the Stop record, and for 'sessions in progress' it's probably
# better to use a separate session table; see below), I'd advise to lock the
# table and use the following statement; do the (standard) insert only if the
# update affected no rows.
#
# update accounting 
#    set acct_status_type = 'Stop',
#        acct_session_time = ?,
#        acct_input_octets = ?,
#        acct_output_octets = ?,
#        acct_input_packets = ?,
#        acct_output_packets = ?,
#  where acct_nas = ?
#    and acct_sessionid = ?
#    and acct_timestamp + ? + 600 >= now()  # use acct_session_time as bind var
#    and acct_timestamp + ? <= now() + 600  # dito
#    and acct_status_type = 'Start'
#    and user_name = ?			    # optional
#    and nas_port = ?			    # optional
#
# The idea is to look back in time, 10 minutes on either side around now() -
# session time, for a start record from the same NAS with the same session id.
# A few extra safety checks prevent overeager matching for NASes that are 
# broken enough to reuse the same session id for a start record within 10
# minutes. Be sure to only use information for this that is known in both the
# Start- and Stop records; some NASes may not send eg. Framed-IP-Address in
# start records, so don't use that.

create table accounting (
    acct_id bigint(22) not null auto_increment,

    acct_nas varchar(32),		    # NAS-IP-Address or NAS-Identifier
    acct_session_id varchar(32),
    acct_timestamp datetime,

    user_name varchar(64),
    nas_ip_address varchar(16),
    nas_port varchar(16),
    service_type varchar(16),
    framed_protocol varchar(16),
    framed_ip_address varchar(16),
    framed_ip_netmask varchar(16),
    login_ip_host varchar(16),
    login_service varchar(16),
    login_tcp_port integer,
    class varchar(16),
    called_station_id varchar(64),
    calling_station_id varchar(64),
    nas_identifier varchar(64),
    nas_port_type varchar(16),
    port_limit integer,

    acct_status_type varchar(16),
    acct_input_octets integer,
    acct_output_octets integer,
    acct_session_time integer,
    acct_input_packets integer,
    acct_output_packets integer,
    acct_terminate_cause varchar(16),
    acct_multi_session_id varchar(64),
    acct_link_count integer,

    primary key (acct_id),
    unique index nassesstime (acct_nas, acct_session_id, acct_timestamp)
);


# Session table
#
# This is useful to track open sessions, for debugging purposes and to limit
# concurrent access for users, calling station, area codes, realms, whatever. 
# I think a separate table is cleaner than overloading the accounting table.
# By keeping session data elsewhere, you can use your accounting table for
# purely billing-related information.
#
# Theory: the session_key is something you choose to identify the session or
# resource for which you want to keep a current counter. It can be a hash of
# nas, user, the full username, just the username suffix, or whatever you need,
# as long as the information is repeated in start, status update and stop
# records.
#
# Each session has a 'check' statement associated with it that may be used to
# verify the actual count using an external module. Most likely, this string
# will have to contain the type of the NAS and the NAS IP address in some way,
# so that your checking module may do the right thing.
#
# Each session can have multiple records with the same session_key; therefore
# you must count the total number of open sessions using sum(opencount). This
# is useful, because this way, each NAS (or even port type!) can have its own
# check statement. Even if you're keeping a large total ports used count across
# a large number of NASes, you can still have a 'checkrad'-like module verify
# it at each individual NAS if it looks like the customer has hit the limits.
#
# When a session is closed, you must decrement the session_count if above 0,
# and you may drop the record if the session_count reaches 0.
#
# Some extra fields are provided for informational purposes only. Of course,
# you can add more if you need them; even Acct-Input-Octets may be useful if
# you have long running sessions and NASes that send status updates.

create table sessions (
    session_id integer not null auto_increment,	    # not related to RADIUS

    session_key varchar(64),
    session_count integer,
    session_check varchar(255),

    user_name varchar(64),
    nas varchar(64),
    nas_port varchar(16),
    nas_port_type varchar(16),
    framed_ip_address varchar(16),
    login_ip_host varchar(16),
    login_tcp_port integer,
    called_station_id varchar(64),
    calling_station_id varchar(64),

    primary key (session_id),
    index (session_key)
);


# Check items table  la FreeRADIUS' radcheck table. Not used by default.
#
#create table radcheck (
#    radcheck_id integer not null auto_increment,
#    space varchar(8) not null,
#    name varchar(64) not null,
#
#    attribute varchar(64) not null,
#    op varchar(2),
#    value varchar(255),
#
#    primary key (radcheck_id),
#    index space_name (space, name)
#);


# Access rights for the server itself

grant usage on openradius.* to radiusd@localhost identified by 'radiusd';

grant select on data to radiusd@localhost;
grant insert on log to radiusd@localhost;
grant select, insert, update on accounting to radiusd@localhost;
grant select, insert, update, delete on sessions to radiusd@localhost;


# Create some test data

insert into data (space, name, attribute, value) 
    values ('clients', '127.0.0.1', 'Secret', 'h1dd3n');

insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'clear-password', 'welcome1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Framed-IP-Address', '172.31.1.1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Service-Type', 'Framed');





#!/usr/bin/perl -w
#
# RADSQL - OpenRADIUS module that queries any DBI/DBD-supported database
#
# Usage: radsql [-d] [-n] [-c] database dbuser dbpass
#	 radsql -h
# 
# 'database' is a DBI connect string without the leading 'dbi:' part.
# -d increases verbosity on stderr and allows module to run standalone
# -n removes 'int' attribute containing number of rows affected from output
# -c treats first three returned columns (attribute, value, op) as check items;
#    instead of number of rows affected, returns 1 if all checks OK, -1 if no 
#    rows found, and 0 otherwise in the 'int' attribute.
#
# The module uses the value of the first 'str' attribute from incoming requests
# as a SQL query and the value of the second 'str' attribute as a comma-
# separated list of attributes to use as bind variables. Attributes that are
# not present in the request will not be bound, so a NULL value will be used.
# Attributes may be listed multiple times; subsequent instances will be used
# in that case.
#
# If the query is the same as the one in the previous request, the query is 
# not reparsed, only re-executed (possibly using new bind variable values).
# This allows you to choose in the behaviour file whether you want to 
# put your values in the query, or use a fixed query and bind variables;
# the latter is much more efficient for most databases, especially Oracle.
#
# Each column returned by the query is sent to OpenRADIUS as 'columnname =
# value'. This allows the mapping SQL fields to RADIUS attributes to be done
# using 'AS' clauses in the SQL query instead of a fixed table as with radldap.
#
# There is one exception. If the name of a column is 'attribute', then its
# value will be used as the fieldname for the next column, instead of the
# fieldname as given by the table or 'AS' clause. This allows you to orient
# attribute sets vertically as well as horizontally (select ... as attribute,
# value from ...).
#
# Author: Emile van Bergen, emile@evbergen.xs4all.nl
#
# Permission to redistribute an original or modified version of this program in
# source, intermediate or object code form is hereby granted exclusively under
# the terms of the GNU General Public License, version 2. Please see the file
# COPYING for details, or refer to http://www.gnu.org/copyleft/gpl.html.
#
# History: 
# 2003/04/24 - EvB - Started
# 2003/04/28 - EvB - Added vertical attribute sets (select attribute, value...)
# 2003/05/01 - EvB - Added variable bind attribute set. Previously only str and 
#                    int, in fixed order (first all strs, then all ints).
#                  - Moved check item support from radchecksql to here


########
# USES #
########

use Getopt::Long;
use DBI qw(:sql_types);
use strict qw(vars);


###########
# OPTIONS #
###########

my $usage = 0;
my $debug = 0;
my $noint = 0;
my $check = 0;


########
# MAIN #
########

# Get options

Getopt::Long::Configure("bundling");
GetOptions("h"  => \$usage,
	   "d+" => \$debug,
	   "n"  => \$noint,
	   "c"  => \$check);

if ($usage || !$ARGV[2]) {
    die("Usage: radsql [-d] [-n] [-c] database dbuser dbpass\n" .
        "       radsql -h\n");
}

# Check that we're running under OpenRADIUS, interface version 1

unless ($debug ||
	$ENV{'RADIUSINTERFACEVERSION'} &&
	$ENV{'RADIUSINTERFACEVERSION'} == 1) {
	die "radsql: ERROR: not running under OpenRADIUS, interface v1!\n";
}

# Connect to database

my $dbh = DBI->connect("dbi:" . $ARGV[0], $ARGV[1], $ARGV[2]) 
    or die "ERROR: Could not connect to @ARGV!\n";

# Set record separator to empty line and loop on input.

$/ = "\n\n";
$| = 1;			# Important - we're outputting to a pipe

my $sql;
my $lastsql;
my $sth;
my $a;
my $v;
my $t;
my $n;
my $ca;
my $cv;
my $co;
my $r;
my %pairs;
my %types;
my @bindvars;
my $colcnt;
my $colref;
my $valref;

MESG: 
while() {

	# get pairs from message as hash of array refs

	%pairs = ();
	%types = ();
	PAIR: 
	while(s/^\s*
	        ([A-Za-z0-9:-]+)			    # attribute ($1)
		\s*=\s*
		(
		  (\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}).*|      # ip ($3)
		  (\d+).*|				    # int or date ($4)
		  "([^"]*)".*|				    # quoted str ($5)
		  ([^"].*)				    # bare str ($6)
		)
		(\n|$)//mx) {

	    $a = $1;
	    if (defined $4) {
		$v = $4; 
		$types{$a} = 1; 
	    } else {
		if (defined $3) { $v = $3; }
		if (defined $5) { $v = $5; }
		elsif (defined $6) { $v = $6; }
		$v =~ s/\\x([a-fA-F0-9]{1,2})/pack("H2", $1)/ge; 
		$types{$a} = 0; 
	    }

	    push @{$pairs{$a}}, $v;
	    print STDERR "parsing:   a=[$a] v=[$v] t=[$types{$a}]\n"
		if ($debug > 1);
	}

	# Get SQL statement and list of attribute names for bind variables

	$sql = shift @{$pairs{str}};
	$t = shift @{$pairs{str}};
	if (defined $t) { @bindvars = split(',', $t); }
	else { @bindvars = (); $t = ''; }
	print STDERR "statement: [$sql]\nbindlist:  [$t]\n" if ($debug);
	if ($debug > 1) { foreach $a (keys %pairs) { 
	    print STDERR "pair:      a=[$a] v=[@{$pairs{$a}}] isint=$types{$a}\n";
	}}
	next MESG unless $sql;

	# Prepare statement if not same as last one

	if (!$sth || $sql ne $lastsql) {
	    if ($sth) { $sth->finish; }
	    $sth = $dbh->prepare($sql) or die "ERROR: Could not parse SQL!\n";
	    $lastsql = $sql;
	}

	# Replace attribute names in bind var array with ref to value, type
	# to work around the bind_param pass-by-reference misbehaviour

	foreach $a (@bindvars) { $a = [$a, shift @{$pairs{$a}}, $types{$a}]; }

	# Bind variables

	$n = 0;
	foreach $a (@bindvars) {
	    $n++;
	    if (!defined $a->[2]) { 
		$debug and print STDERR "bindvar $n: NULL\n"; 
		next; 
	    }
	    print STDERR "bindvar $n: a=[$a->[0]] v=[$a->[1]] i=[$a->[2]]\n" 
		if $debug;
	    $sth->bind_param($n, $a->[1], $a->[2] ? SQL_INTEGER : SQL_VARCHAR)
		or die "ERROR: Could not bind variable $n!\n";
	}

	# Execute statement
	
	$r = $sth->execute or die "ERROR: Could not execute SQL!\n";
	$colcnt = $sth->{NUM_OF_FIELDS};

	# If no columns returned, we're done

	next MESG if ($colcnt == 0);

	# If we're not doing the check item thing, return rows of columns

	if ($check == 0) {
	    $colref = $sth->{NAME};
	    $r = 0;
	    while($valref = $sth->fetchrow_arrayref) {
		COL: for($n = 0; $n < $colcnt; $n++) {
		    next COL unless defined $valref->[$n];
		    $a = $colref->[$n];
		    if ($a eq 'attribute') { $a = $valref->[$n++]; }
		    $v = $valref->[$n];
		    $v =~ s/([\\'"\x00-\x1f\x7f-\xff])/"\\x" . unpack('H2', $1)/ge;
		    print "$a=$v\n";
		    print STDERR "returning: $a=[$v]\n" if $debug;
		}
		$r++;
	    }
	    next MESG;
	}

	# Otherwise, do the check item thing

	if ($colcnt != 3) {
	    die "ERROR: Radcheck query returns $colcnt columns instead of 3!\n";
	}

	$r = -1;
	ROW: while(($ca, $co, $cv) = $sth->fetchrow_array) {

	    $v = ${$pairs{$ca}}[0]; 
	    if ($types{$ca}) {
		$v = 0 unless defined $v;
		OP: {
		    $r &= $v <  $cv, last OP if $co eq '<';
		    $r &= $v <= $cv, last OP if $co eq '<=';
		    $r &= $v != $cv, last OP if $co eq '!=';
		    $r &= $v >= $cv, last OP if $co eq '>=';
		    $r &= $v >  $cv, last OP if $co eq '>';
		    $r &= $v == $cv;
		}
	    } else {
		$v = '' unless defined $v;
		OP: { 
		    $r &= $v lt $cv, last OP if $co eq '<';
		    $r &= $v le $cv, last OP if $co eq '<=';
		    $r &= $v ne $cv, last OP if $co eq '!=';
		    $r &= $v ge $cv, last OP if $co eq '>=';
		    $r &= $v gt $cv, last OP if $co eq '>';
		    $r &= $v eq $cv;
		}
	    }
	    print STDERR "a=[$ca] cv=[$cv] co=[$co] v=[$v] r=[$r]\n"
		if ($debug > 1);
	}
}
continue {
	
	print STDERR "returning: r=[$r]\n" if $debug;
	print "int=$r\n" unless $noint;
	print "\n";
}
                                                                                                                                                                                                                           

Generated on Sun Mar 25 00:43:53 2007 by decorate.pl / menuize.pl