1 package FS::part_export::sqlradius;
4 use vars qw(@ISA @EXPORT_OK $DEBUG %info %options $notes1 $notes2);
7 use FS::Record qw( dbh qsearch qsearchs str2time_sql str2time_sql_closing );
14 @ISA = qw(FS::part_export);
15 @EXPORT_OK = qw( sqlradius_connect );
20 tie %options, 'Tie::IxHash',
21 'datasrc' => { label=>'DBI data source ' },
22 'username' => { label=>'Database username' },
23 'password' => { label=>'Database password' },
24 'usergroup' => { label => 'Group table',
26 options => [qw( usergroup radusergroup ) ],
28 'ignore_accounting' => {
30 label => 'Ignore accounting records from this database'
32 'process_single_realm' => {
34 label => 'Only process one realm of accounting records',
36 'realm' => { label => 'The realm of of accounting records to be processed' },
37 'ignore_long_sessions' => {
39 label => 'Ignore sessions which span billing periods',
43 label => 'Hide IP address information on session reports',
47 label => 'Hide download/upload information on session reports',
49 'show_called_station' => {
51 label => 'Show the Called-Station-ID on session reports', #as a phone number
53 'overlimit_groups' => {
54 label => 'Radius groups to assign to svc_acct which has exceeded its bandwidth or time limit (if not overridden by overlimit_groups global or per-agent config)',
60 option_values => sub {
62 map { $_->groupnum, $_->long_description }
63 qsearch('radius_group', {}),
68 'groups_susp_reason' => { label =>
69 'Radius group mapping to reason (via template user) (svcnum|username|username@domain reasonnum|reason)',
74 label => 'Export RADIUS group attributes to this database',
77 label => 'To send a disconnection request to each RADIUS client when modifying, suspending or deleting an account, enter a ssh connection string (username@host) with access to the radclient program',
79 'disconnect_port' => {
80 label => 'Port to send disconnection requests to, default 1700',
83 label => 'Print disconnect output and errors to the queue log (will otherwise fail silently)',
89 Real-time export of <b>radcheck</b>, <b>radreply</b> and <b>usergroup</b>/<b>radusergroup</b>
90 tables to any SQL database for
91 <a href="http://www.freeradius.org/">FreeRADIUS</a>
92 or <a href="http://radius.innercite.com/">ICRADIUS</a>.
96 An existing RADIUS database will be updated in realtime, but you can use
97 <a href="http://www.freeside.biz/mediawiki/index.php/Freeside:1.9:Documentation:Developer/bin/freeside-sqlradius-reset">freeside-sqlradius-reset</a>
98 to delete the entire RADIUS database and repopulate the tables from the
99 Freeside database. See the
100 <a href="http://search.cpan.org/dist/DBI/DBI.pm#connect">DBI documentation</a>
102 <a href="http://search.cpan.org/search?mode=module&query=DBD%3A%3A">documentation for your DBD</a>
103 for the exact syntax of a DBI data source.
105 <li>Using FreeRADIUS 0.9.0 with the PostgreSQL backend, the db_postgresql.sql schema and postgresql.conf queries contain incompatible changes. This is fixed in 0.9.1. Only new installs with 0.9.0 and PostgreSQL are affected - upgrades and other database backends and versions are unaffected.
106 <li>Using ICRADIUS, add a dummy "op" column to your database:
108 ALTER TABLE radcheck ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
109 ALTER TABLE radreply ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
110 ALTER TABLE radgroupcheck ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
111 ALTER TABLE radgroupreply ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='
113 <li>Using Radiator, see the
114 <a href="http://www.open.com.au/radiator/faq.html#38">Radiator FAQ</a>
115 for configuration information.
121 'desc' => 'Real-time export to SQL-backed RADIUS (FreeRADIUS, ICRADIUS)',
122 'options' => \%options,
125 'nas' => 'Y', # show export_nas selection in UI
126 'default_svc_class' => 'Internet',
128 'This export does not export RADIUS realms (see also '.
129 'sqlradius_withdomain). '.
133 sub _groups_susp_reason_map { map { reverse( /^\s*(\S+)\s*(.*)$/ ) }
134 split( "\n", shift->option('groups_susp_reason'));
137 sub rebless { shift; }
139 sub export_username { # override for other svcdb
140 my($self, $svc_acct) = (shift, shift);
141 warn "export_username called on $self with arg $svc_acct" if $DEBUG > 1;
145 sub radius_reply { #override for other svcdb
146 my($self, $svc_acct) = (shift, shift);
147 my %every = $svc_acct->EVERY::radius_reply;
148 map { @$_ } values %every;
151 sub radius_check { #override for other svcdb
152 my($self, $svc_acct) = (shift, shift);
153 my %every = $svc_acct->EVERY::radius_check;
154 map { @$_ } values %every;
158 my($self, $svc_x) = (shift, shift);
160 foreach my $table (qw(reply check)) {
161 my $method = "radius_$table";
162 my %attrib = $self->$method($svc_x);
163 next unless keys %attrib;
164 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'insert',
165 $table, $self->export_username($svc_x), %attrib );
166 return $err_or_queue unless ref($err_or_queue);
168 my @groups = $svc_x->radius_groups('hashref');
170 cluck localtime(). ": queuing usergroup_insert for ". $svc_x->svcnum.
171 " (". $self->export_username($svc_x). " with ". join(", ", @groups)
173 my $usergroup = $self->option('usergroup') || 'usergroup';
174 my $err_or_queue = $self->sqlradius_queue(
175 $svc_x->svcnum, 'usergroup_insert',
176 $self->export_username($svc_x), $usergroup, @groups );
177 return $err_or_queue unless ref($err_or_queue);
182 sub _export_replace {
183 my( $self, $new, $old ) = (shift, shift, shift);
185 local $SIG{HUP} = 'IGNORE';
186 local $SIG{INT} = 'IGNORE';
187 local $SIG{QUIT} = 'IGNORE';
188 local $SIG{TERM} = 'IGNORE';
189 local $SIG{TSTP} = 'IGNORE';
190 local $SIG{PIPE} = 'IGNORE';
192 my $oldAutoCommit = $FS::UID::AutoCommit;
193 local $FS::UID::AutoCommit = 0;
198 # disconnect users before changing username
199 if ($self->option('disconnect_ssh')) {
200 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'user_disconnect',
201 'disconnect_ssh' => $self->option('disconnect_ssh'),
202 'svc_acct_username' => $old->username,
203 'disconnect_port' => $self->option('disconnect_port'),
204 'disconnect_log' => $self->option('disconnect_log'),
206 unless ( ref($err_or_queue) ) {
207 $dbh->rollback if $oldAutoCommit;
208 return $err_or_queue;
210 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
213 if ( $self->export_username($old) ne $self->export_username($new) ) {
214 my $usergroup = $self->option('usergroup') || 'usergroup';
215 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'rename',
216 $self->export_username($new), $self->export_username($old), $usergroup );
217 unless ( ref($err_or_queue) ) {
218 $dbh->rollback if $oldAutoCommit;
219 return $err_or_queue;
222 my $error = $err_or_queue->depend_insert( $jobnum );
224 $dbh->rollback if $oldAutoCommit;
228 $jobnum = $err_or_queue->jobnum;
231 foreach my $table (qw(reply check)) {
232 my $method = "radius_$table";
233 my %new = $self->$method($new);
234 my %old = $self->$method($old);
235 if ( grep { !exists $old{$_} #new attributes
236 || $new{$_} ne $old{$_} #changed
239 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert',
240 $table, $self->export_username($new), %new );
241 unless ( ref($err_or_queue) ) {
242 $dbh->rollback if $oldAutoCommit;
243 return $err_or_queue;
246 my $error = $err_or_queue->depend_insert( $jobnum );
248 $dbh->rollback if $oldAutoCommit;
252 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
255 my @del = grep { !exists $new{$_} } keys %old;
257 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'attrib_delete',
258 $table, $self->export_username($new), @del );
259 unless ( ref($err_or_queue) ) {
260 $dbh->rollback if $oldAutoCommit;
261 return $err_or_queue;
264 my $error = $err_or_queue->depend_insert( $jobnum );
266 $dbh->rollback if $oldAutoCommit;
270 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
275 my (@oldgroups) = $old->radius_groups('hashref');
276 my (@newgroups) = $new->radius_groups('hashref');
277 $error = $self->sqlreplace_usergroups( $new->svcnum,
278 $self->export_username($new),
279 $jobnum ? $jobnum : '',
284 $dbh->rollback if $oldAutoCommit;
288 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
293 #false laziness w/broadband_sqlradius.pm
294 sub _export_suspend {
295 my( $self, $svc_acct ) = (shift, shift);
297 my $new = $svc_acct->clone_suspended;
299 local $SIG{HUP} = 'IGNORE';
300 local $SIG{INT} = 'IGNORE';
301 local $SIG{QUIT} = 'IGNORE';
302 local $SIG{TERM} = 'IGNORE';
303 local $SIG{TSTP} = 'IGNORE';
304 local $SIG{PIPE} = 'IGNORE';
306 my $oldAutoCommit = $FS::UID::AutoCommit;
307 local $FS::UID::AutoCommit = 0;
312 # disconnect users before changing anything
313 if ($self->option('disconnect_ssh')) {
314 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'user_disconnect',
315 'disconnect_ssh' => $self->option('disconnect_ssh'),
316 'svc_acct_username' => $svc_acct->username,
317 'disconnect_port' => $self->option('disconnect_port'),
318 'disconnect_log' => $self->option('disconnect_log'),
320 unless ( ref($err_or_queue) ) {
321 $dbh->rollback if $oldAutoCommit;
322 return $err_or_queue;
324 $jobnum = $err_or_queue->jobnum;
327 my @newgroups = $self->suspended_usergroups($svc_acct);
329 unless (@newgroups) { #don't change password if assigning to a suspended group
331 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert',
332 'check', $self->export_username($new), $new->radius_check );
333 unless ( ref($err_or_queue) ) {
334 $dbh->rollback if $oldAutoCommit;
335 return $err_or_queue;
338 my $error = $err_or_queue->depend_insert( $jobnum );
340 $dbh->rollback if $oldAutoCommit;
347 $self->sqlreplace_usergroups(
349 $self->export_username($new),
351 [ $svc_acct->radius_groups('hashref') ],
355 $dbh->rollback if $oldAutoCommit;
358 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
363 sub _export_unsuspend {
364 my( $self, $svc_x ) = (shift, shift);
366 local $SIG{HUP} = 'IGNORE';
367 local $SIG{INT} = 'IGNORE';
368 local $SIG{QUIT} = 'IGNORE';
369 local $SIG{TERM} = 'IGNORE';
370 local $SIG{TSTP} = 'IGNORE';
371 local $SIG{PIPE} = 'IGNORE';
373 my $oldAutoCommit = $FS::UID::AutoCommit;
374 local $FS::UID::AutoCommit = 0;
377 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'insert',
378 'check', $self->export_username($svc_x), $self->radius_check($svc_x) );
379 unless ( ref($err_or_queue) ) {
380 $dbh->rollback if $oldAutoCommit;
381 return $err_or_queue;
385 my (@oldgroups) = $self->suspended_usergroups($svc_x);
386 $error = $self->sqlreplace_usergroups(
388 $self->export_username($svc_x),
391 [ $svc_x->radius_groups('hashref') ],
394 $dbh->rollback if $oldAutoCommit;
397 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
403 my( $self, $svc_x ) = (shift, shift);
407 # disconnect users before changing anything
408 if ($self->option('disconnect_ssh')) {
409 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'user_disconnect',
410 'disconnect_ssh' => $self->option('disconnect_ssh'),
411 'svc_acct_username' => $svc_x->username,
412 'disconnect_port' => $self->option('disconnect_port'),
413 'disconnect_log' => $self->option('disconnect_log'),
415 return $err_or_queue unless ref($err_or_queue);
416 $jobnum = $err_or_queue->jobnum;
419 my $usergroup = $self->option('usergroup') || 'usergroup';
420 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'delete',
421 $self->export_username($svc_x), $usergroup );
423 my $error = $err_or_queue->depend_insert( $jobnum );
424 return $error if $error;
427 ref($err_or_queue) ? '' : $err_or_queue;
430 sub sqlradius_queue {
431 my( $self, $svcnum, $method ) = (shift, shift, shift);
433 my $queue = new FS::queue {
435 'job' => "FS::part_export::sqlradius::sqlradius_$method",
438 $self->option('datasrc'),
439 $self->option('username'),
440 $self->option('password'),
445 sub suspended_usergroups {
446 my ($self, $svc_x) = (shift, shift);
448 return () unless $svc_x;
450 my $svc_table = $svc_x->table;
452 #false laziness with FS::part_export::shellcommands
453 #subclass part_export?
455 my $r = $svc_x->cust_svc->cust_pkg->last_reason('susp');
456 my %reasonmap = $self->_groups_susp_reason_map;
459 $userspec = $reasonmap{$r->reasonnum}
460 if exists($reasonmap{$r->reasonnum});
461 $userspec = $reasonmap{$r->reason}
462 if (!$userspec && exists($reasonmap{$r->reason}));
465 if ( $userspec =~ /^\d+$/ ){
466 $suspend_svc = qsearchs( $svc_table, { 'svcnum' => $userspec } );
467 } elsif ( $userspec =~ /^\S+\@\S+$/ && $svc_table eq 'svc_acct' ){
468 my ($username,$domain) = split(/\@/, $userspec);
469 for my $user (qsearch( 'svc_acct', { 'username' => $username } )){
470 $suspend_svc = $user if $userspec eq $user->email;
472 }elsif ( $userspec && $svc_table eq 'svc_acct' ){
473 $suspend_svc = qsearchs( 'svc_acct', { 'username' => $userspec } );
476 return $suspend_svc->radius_groups('hashref') if $suspend_svc;
480 sub sqlradius_insert { #subroutine, not method
481 my $dbh = sqlradius_connect(shift, shift, shift);
482 my( $table, $username, %attributes ) = @_;
484 foreach my $attribute ( keys %attributes ) {
486 my $s_sth = $dbh->prepare(
487 "SELECT COUNT(*) FROM rad$table WHERE UserName = ? AND Attribute = ?"
488 ) or die $dbh->errstr;
489 $s_sth->execute( $username, $attribute ) or die $s_sth->errstr;
491 if ( $s_sth->fetchrow_arrayref->[0] ) {
493 my $u_sth = $dbh->prepare(
494 "UPDATE rad$table SET Value = ? WHERE UserName = ? AND Attribute = ?"
495 ) or die $dbh->errstr;
496 $u_sth->execute($attributes{$attribute}, $username, $attribute)
497 or die $u_sth->errstr;
501 my $i_sth = $dbh->prepare(
502 "INSERT INTO rad$table ( UserName, Attribute, op, Value ) ".
503 "VALUES ( ?, ?, ?, ? )"
504 ) or die $dbh->errstr;
508 ( $attribute eq 'Password' ? '==' : ':=' ),
509 $attributes{$attribute},
510 ) or die $i_sth->errstr;
518 sub sqlradius_usergroup_insert { #subroutine, not method
519 my $dbh = sqlradius_connect(shift, shift, shift);
520 my $username = shift;
521 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
524 my $s_sth = $dbh->prepare(
525 "SELECT COUNT(*) FROM $usergroup WHERE UserName = ? AND GroupName = ?"
526 ) or die $dbh->errstr;
528 my $sth = $dbh->prepare(
529 "INSERT INTO $usergroup ( UserName, GroupName, Priority ) VALUES ( ?, ?, ? )"
530 ) or die $dbh->errstr;
532 foreach ( @groups ) {
533 my $group = $_->{'groupname'};
534 my $priority = $_->{'priority'};
535 $s_sth->execute( $username, $group ) or die $s_sth->errstr;
536 if ($s_sth->fetchrow_arrayref->[0]) {
537 warn localtime() . ": sqlradius_usergroup_insert attempted to reinsert " .
538 "$group for $username\n"
542 $sth->execute( $username, $group, $priority )
543 or die "can't insert into groupname table: ". $sth->errstr;
545 if ( $s_sth->{Active} ) {
546 warn "sqlradius s_sth still active; calling ->finish()";
549 if ( $sth->{Active} ) {
550 warn "sqlradius sth still active; calling ->finish()";
556 sub sqlradius_usergroup_delete { #subroutine, not method
557 my $dbh = sqlradius_connect(shift, shift, shift);
558 my $username = shift;
559 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
562 my $sth = $dbh->prepare(
563 "DELETE FROM $usergroup WHERE UserName = ? AND GroupName = ?"
564 ) or die $dbh->errstr;
565 foreach ( @groups ) {
566 my $group = $_->{'groupname'};
567 $sth->execute( $username, $group )
568 or die "can't delete from groupname table: ". $sth->errstr;
573 sub sqlradius_rename { #subroutine, not method
574 my $dbh = sqlradius_connect(shift, shift, shift);
575 my($new_username, $old_username) = (shift, shift);
576 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
577 foreach my $table (qw(radreply radcheck), $usergroup ) {
578 my $sth = $dbh->prepare("UPDATE $table SET Username = ? WHERE UserName = ?")
580 $sth->execute($new_username, $old_username)
581 or die "can't update $table: ". $sth->errstr;
586 sub sqlradius_attrib_delete { #subroutine, not method
587 my $dbh = sqlradius_connect(shift, shift, shift);
588 my( $table, $username, @attrib ) = @_;
590 foreach my $attribute ( @attrib ) {
591 my $sth = $dbh->prepare(
592 "DELETE FROM rad$table WHERE UserName = ? AND Attribute = ?" )
594 $sth->execute($username,$attribute)
595 or die "can't delete from rad$table table: ". $sth->errstr;
600 sub sqlradius_delete { #subroutine, not method
601 my $dbh = sqlradius_connect(shift, shift, shift);
602 my $username = shift;
603 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
605 foreach my $table (qw( radcheck radreply), $usergroup ) {
606 my $sth = $dbh->prepare( "DELETE FROM $table WHERE UserName = ?" );
607 $sth->execute($username)
608 or die "can't delete from $table table: ". $sth->errstr;
613 sub sqlradius_connect {
614 #my($datasrc, $username, $password) = @_;
615 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
616 DBI->connect(@_) or die $DBI::errstr;
619 sub sqlreplace_usergroups {
620 my ($self, $svcnum, $username, $jobnum, $old, $new) = @_;
622 # (sorta) false laziness with FS::svc_acct::replace
623 my @oldgroups = @$old;
624 my @newgroups = @$new;
626 foreach my $oldgroup ( @oldgroups ) {
627 if ( grep { $oldgroup eq $_ } @newgroups ) {
628 @newgroups = grep { $oldgroup ne $_ } @newgroups;
631 push @delgroups, $oldgroup;
634 my $usergroup = $self->option('usergroup') || 'usergroup';
637 my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_delete',
638 $username, $usergroup, @delgroups );
640 unless ref($err_or_queue);
642 my $error = $err_or_queue->depend_insert( $jobnum );
643 return $error if $error;
645 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
649 cluck localtime(). ": queuing usergroup_insert for $svcnum ($username) ".
650 "with ". join(", ", @newgroups)
652 my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_insert',
653 $username, $usergroup, @newgroups );
655 unless ref($err_or_queue);
657 my $error = $err_or_queue->depend_insert( $jobnum );
658 return $error if $error;
667 =item usage_sessions HASHREF
669 =item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ]
671 New-style: pass a hashref with the following keys:
675 =item stoptime_start - Lower bound for AcctStopTime, as a UNIX timestamp
677 =item stoptime_end - Upper bound for AcctStopTime, as a UNIX timestamp
679 =item session_status - 'closed' to only show records with AcctStopTime,
680 'open' to only show records I<without> AcctStopTime, empty to show both.
682 =item starttime_start - Lower bound for AcctStartTime, as a UNIX timestamp
684 =item starttime_end - Upper bound for AcctStartTime, as a UNIX timestamp
696 TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see
697 L<perlfunc/"time">. Also see L<Time::Local> and L<Date::Parse> for conversion
700 SVC_ACCT, if specified, limits the results to the specified account.
702 IP, if specified, limits the results to the specified IP address.
704 PREFIX, if specified, limits the results to records with a matching
707 #SQL_SELECT defaults to * if unspecified. It can be useful to set it to
708 #SUM(acctsessiontime) or SUM(AcctInputOctets), etc.
710 Returns an arrayref of hashrefs with the following fields:
716 =item framedipaddress
722 =item acctsessiontime
724 =item acctinputoctets
726 =item acctoutputoctets
728 =item callingstationid
730 =item calledstationid
736 #some false laziness w/cust_svc::seconds_since_sqlradacct
742 my($start, $end, $svc_acct, $ip, $prefix) = ( '', '', '', '', '');
746 $start = $opt->{stoptime_start};
747 $end = $opt->{stoptime_end};
748 $svc_acct = $opt->{svc} || $opt->{svc_acct};
750 $prefix = $opt->{prefix};
751 $summarize = $opt->{summarize};
753 ( $start, $end ) = splice(@_, 0, 2);
754 $svc_acct = @_ ? shift : '';
755 $ip = @_ ? shift : '';
756 $prefix = @_ ? shift : '';
757 #my $select = @_ ? shift : '*';
762 return [] if $self->option('ignore_accounting');
764 my $dbh = sqlradius_connect( map $self->option($_),
765 qw( datasrc username password ) );
767 #select a unix time conversion function based on database type
768 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
769 my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} );
772 qw( username realm framedipaddress
773 acctsessiontime acctinputoctets acctoutputoctets
774 callingstationid calledstationid
776 "$str2time acctstarttime $closing as acctstarttime",
777 "$str2time acctstoptime $closing as acctstoptime",
780 @fields = ( 'username', 'sum(acctsessiontime) as acctsessiontime', 'sum(acctinputoctets) as acctinputoctets',
781 'sum(acctoutputoctets) as acctoutputoctets',
788 my $username = $self->export_username($svc_acct);
789 if ( $username =~ /^([^@]+)\@([^@]+)$/ ) {
790 push @where, '( UserName = ? OR ( UserName = ? AND Realm = ? ) )';
791 push @param, $username, $1, $2;
793 push @where, 'UserName = ?';
794 push @param, $username;
798 if ($self->option('process_single_realm')) {
799 push @where, 'Realm = ?';
800 push @param, $self->option('realm');
804 push @where, ' FramedIPAddress = ?';
808 if ( length($prefix) ) {
809 #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/
810 push @where, " CalledStationID LIKE 'sip:$prefix\%'";
813 my $acctstoptime = '';
814 if ( $opt->{session_status} ne 'open' ) {
816 $acctstoptime .= "$str2time AcctStopTime $closing >= ?";
818 $acctstoptime .= ' AND ' if $end;
821 $acctstoptime .= "$str2time AcctStopTime $closing <= ?";
825 if ( $opt->{session_status} ne 'closed' ) {
826 if ( $acctstoptime ) {
827 $acctstoptime = "( ( $acctstoptime ) OR AcctStopTime IS NULL )";
829 $acctstoptime = 'AcctStopTime IS NULL';
832 push @where, $acctstoptime;
834 if ( $opt->{starttime_start} ) {
835 push @where, "$str2time AcctStartTime $closing >= ?";
836 push @param, $opt->{starttime_start};
838 if ( $opt->{starttime_end} ) {
839 push @where, "$str2time AcctStartTime $closing <= ?";
840 push @param, $opt->{starttime_end};
843 my $where = join(' AND ', @where);
844 $where = "WHERE $where" if $where;
847 $groupby = 'GROUP BY username' if $summarize;
849 my $orderby = 'ORDER BY AcctStartTime DESC';
850 $orderby = '' if $summarize;
852 my $sql = 'SELECT '. join(', ', @fields).
853 " FROM radacct $where $groupby $orderby";
856 warn join(',', @param);
858 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
859 $sth->execute(@param) or die $sth->errstr;
861 [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ];
872 my $conf = new FS::Conf;
875 my $dbh = sqlradius_connect( map $self->option($_),
876 qw( datasrc username password ) );
878 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
879 my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} );
881 my @fields = qw( radacctid username realm acctsessiontime );
886 my $sth = $dbh->prepare("
887 SELECT RadAcctId, UserName, Realm, AcctSessionTime,
888 $str2time AcctStartTime $closing, $str2time AcctStopTime $closing,
889 AcctInputOctets, AcctOutputOctets
891 WHERE FreesideStatus IS NULL
892 AND AcctStopTime IS NOT NULL
893 ") or die $dbh->errstr;
894 $sth->execute() or die $sth->errstr;
896 while ( my $row = $sth->fetchrow_arrayref ) {
897 my($RadAcctId, $UserName, $Realm, $AcctSessionTime, $AcctStartTime,
898 $AcctStopTime, $AcctInputOctets, $AcctOutputOctets) = @$row;
899 warn "processing record: ".
900 "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s"
903 my $fs_username = $UserName;
905 $fs_username = lc($fs_username) unless $conf->exists('username-uppercase');
907 #my %search = ( 'username' => $fs_username );
910 my $errinfo = "for RADIUS detail RadAcctID $RadAcctId ".
911 "(UserName $UserName, Realm $Realm)";
914 if ( ref($self) =~ /withdomain/ ) { #well, should be a callback to that
919 } elsif ( $fs_username =~ /\@/ ) {
920 ($fs_username, $domain) = split('@', $fs_username);
922 warn 'WARNING: nothing Realm column and no @realm in UserName column '.
923 "$errinfo -- skipping\n" if $DEBUG;
924 $status = 'skipped (no realm)';
927 $extra_sql = " AND '$domain' = ( SELECT domain FROM svc_domain
928 WHERE svc_domain.svcnum = svc_acct.domsvc ) ";
931 my $oldAutoCommit = $FS::UID::AutoCommit; # can't undo side effects, but at
932 local $FS::UID::AutoCommit = 0; # least we can avoid over counting
938 if ( $self->option('process_single_realm')
939 && $self->option('realm') ne $Realm )
941 warn "WARNING: wrong realm $errinfo - skipping\n" if $DEBUG;
944 grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
945 'svcpart' => $_->cust_svc->svcpart,
950 { 'username' => $fs_username },
956 warn "WARNING: no svc_acct record found $errinfo - skipping\n";
957 } elsif ( scalar(@svc_acct) > 1 ) {
958 warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
961 my $svc_acct = $svc_acct[0];
962 warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG;
964 $svc_acct->last_login($AcctStartTime);
965 $svc_acct->last_logout($AcctStopTime);
967 my $session_time = $AcctStopTime;
968 $session_time = $AcctStartTime
969 if $self->option('ignore_long_sessions');
971 my $cust_pkg = $svc_acct->cust_svc->cust_pkg;
972 if ( $cust_pkg && $session_time < ( $cust_pkg->last_bill
973 || $cust_pkg->setup ) ) {
974 $status = 'skipped (too old)';
977 push @st, _try_decrement($svc_acct,'seconds', $AcctSessionTime);
978 push @st, _try_decrement($svc_acct,'upbytes', $AcctInputOctets);
979 push @st, _try_decrement($svc_acct,'downbytes', $AcctOutputOctets);
980 push @st, _try_decrement($svc_acct,'totalbytes', $AcctInputOctets
981 + $AcctOutputOctets);
982 $status=join(' ', @st);
989 warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG;
990 my $psth = $dbh->prepare("UPDATE radacct
991 SET FreesideStatus = ?
993 ) or die $dbh->errstr;
994 $psth->execute($status, $RadAcctId) or die $psth->errstr;
996 $fdbh->commit or die $fdbh->errstr if $oldAutoCommit;
1002 sub _try_decrement {
1003 my ($svc_acct, $column, $amount) = @_;
1004 if ( $svc_acct->$column !~ /^$/ ) {
1005 warn " svc_acct.$column found (". $svc_acct->$column.
1006 ") - decrementing\n"
1008 my $method = 'decrement_' . $column;
1009 my $error = $svc_acct->$method($amount);
1010 die $error if $error;
1013 warn " no existing $column value for svc_acct - skipping\n" if $DEBUG;
1018 =item export_nas_insert NAS
1020 =item export_nas_delete NAS
1022 =item export_nas_replace NEW_NAS OLD_NAS
1024 Update the NAS table (allowed RADIUS clients) on the attached RADIUS
1025 server. Currently requires the table to be named 'nas' and to follow
1026 the stock schema (/etc/freeradius/nas.sql).
1030 sub export_nas_insert { shift->export_nas_action('insert', @_); }
1031 sub export_nas_delete { shift->export_nas_action('delete', @_); }
1032 sub export_nas_replace { shift->export_nas_action('replace', @_); }
1034 sub export_nas_action {
1036 my ($action, $new, $old) = @_;
1037 # find the NAS in the target table by its name
1038 my $nasname = ($action eq 'replace') ? $old->nasname : $new->nasname;
1039 my $nasnum = $new->nasnum;
1041 my $err_or_queue = $self->sqlradius_queue('', "nas_$action",
1042 nasname => $nasname,
1045 return $err_or_queue unless ref $err_or_queue;
1049 sub sqlradius_nas_insert {
1050 my $dbh = sqlradius_connect(shift, shift, shift);
1052 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1053 or die "nasnum ".$opt{'nasnum'}.' not found';
1054 # insert actual NULLs where FS::Record has translated to empty strings
1055 my @values = map { length($nas->$_) ? $nas->$_ : undef }
1056 qw( nasname shortname type secret server community description );
1057 my $sth = $dbh->prepare('INSERT INTO nas
1058 (nasname, shortname, type, secret, server, community, description)
1059 VALUES (?, ?, ?, ?, ?, ?, ?)');
1060 $sth->execute(@values) or die $dbh->errstr;
1063 sub sqlradius_nas_delete {
1064 my $dbh = sqlradius_connect(shift, shift, shift);
1066 my $sth = $dbh->prepare('DELETE FROM nas WHERE nasname = ?');
1067 $sth->execute($opt{'nasname'}) or die $dbh->errstr;
1070 sub sqlradius_nas_replace {
1071 my $dbh = sqlradius_connect(shift, shift, shift);
1073 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1074 or die "nasnum ".$opt{'nasnum'}.' not found';
1075 my @values = map {$nas->$_}
1076 qw( nasname shortname type secret server community description );
1077 my $sth = $dbh->prepare('UPDATE nas SET
1078 nasname = ?, shortname = ?, type = ?, secret = ?,
1079 server = ?, community = ?, description = ?
1080 WHERE nasname = ?');
1081 $sth->execute(@values, $opt{'nasname'}) or die $dbh->errstr;
1084 =item export_attr_insert RADIUS_ATTR
1086 =item export_attr_delete RADIUS_ATTR
1088 =item export_attr_replace NEW_RADIUS_ATTR OLD_RADIUS_ATTR
1090 Update the group attribute tables (radgroupcheck and radgroupreply) on
1091 the RADIUS server. In delete and replace actions, the existing records
1092 are identified by the combination of group name and attribute name.
1094 In the special case where attributes are being replaced because a group
1095 name (L<FS::radius_group>->groupname) is changing, the pseudo-field
1096 'groupname' must be set in OLD_RADIUS_ATTR.
1100 # some false laziness with NAS export stuff...
1102 sub export_attr_insert { shift->export_attr_action('insert', @_); }
1104 sub export_attr_delete { shift->export_attr_action('delete', @_); }
1106 sub export_attr_replace { shift->export_attr_action('replace', @_); }
1108 sub export_attr_action {
1110 my ($action, $new, $old) = @_;
1113 if ( $action eq 'delete' ) {
1116 if ( $action eq 'delete' or $action eq 'replace' ) {
1117 # delete based on an exact match
1119 attrname => $old->attrname,
1120 attrtype => $old->attrtype,
1121 groupname => $old->groupname || $old->radius_group->groupname,
1123 value => $old->value,
1125 $err_or_queue = $self->sqlradius_queue('', 'attr_delete', %opt);
1126 return $err_or_queue unless ref $err_or_queue;
1128 # this probably doesn't matter, but just to be safe...
1129 my $jobnum = $err_or_queue->jobnum if $action eq 'replace';
1130 if ( $action eq 'replace' or $action eq 'insert' ) {
1132 attrname => $new->attrname,
1133 attrtype => $new->attrtype,
1134 groupname => $new->radius_group->groupname,
1136 value => $new->value,
1138 $err_or_queue = $self->sqlradius_queue('', 'attr_insert', %opt);
1139 $err_or_queue->depend_insert($jobnum) if $jobnum;
1140 return $err_or_queue unless ref $err_or_queue;
1145 sub sqlradius_attr_insert {
1146 my $dbh = sqlradius_connect(shift, shift, shift);
1150 # make sure $table is completely safe
1151 if ( $opt{'attrtype'} eq 'C' ) {
1152 $table = 'radgroupcheck';
1154 elsif ( $opt{'attrtype'} eq 'R' ) {
1155 $table = 'radgroupreply';
1158 die "unknown attribute type '$opt{attrtype}'";
1161 my @values = @opt{ qw(groupname attrname op value) };
1162 my $sth = $dbh->prepare(
1163 'INSERT INTO '.$table.' (groupname, attribute, op, value) VALUES (?,?,?,?)'
1165 $sth->execute(@values) or die $dbh->errstr;
1168 sub sqlradius_attr_delete {
1169 my $dbh = sqlradius_connect(shift, shift, shift);
1173 if ( $opt{'attrtype'} eq 'C' ) {
1174 $table = 'radgroupcheck';
1176 elsif ( $opt{'attrtype'} eq 'R' ) {
1177 $table = 'radgroupreply';
1180 die "unknown attribute type '".$opt{'attrtype'}."'";
1183 my @values = @opt{ qw(groupname attrname op value) };
1184 my $sth = $dbh->prepare(
1185 'DELETE FROM '.$table.
1186 ' WHERE groupname = ? AND attribute = ? AND op = ? AND value = ?'.
1189 $sth->execute(@values) or die $dbh->errstr;
1192 #sub sqlradius_attr_replace { no longer needed
1194 =item export_group_replace NEW OLD
1196 Replace the L<FS::radius_group> object OLD with NEW. This will change
1197 the group name and priority in all radusergroup records, and the group
1198 name in radgroupcheck and radgroupreply.
1202 sub export_group_replace {
1204 my ($new, $old) = @_;
1205 return '' if $new->groupname eq $old->groupname
1206 and $new->priority == $old->priority;
1208 my $err_or_queue = $self->sqlradius_queue(
1211 ($self->option('usergroup') || 'usergroup'),
1215 return $err_or_queue unless ref $err_or_queue;
1219 sub sqlradius_group_replace {
1220 my $dbh = sqlradius_connect(shift, shift, shift);
1221 my $usergroup = shift;
1222 $usergroup =~ /^(rad)?usergroup$/
1223 or die "bad usergroup table name: $usergroup";
1224 my ($new, $old) = (shift, shift);
1225 # apply renames to check/reply attribute tables
1226 if ( $new->{'groupname'} ne $old->{'groupname'} ) {
1227 foreach my $table (qw(radgroupcheck radgroupreply)) {
1228 my $sth = $dbh->prepare(
1229 'UPDATE '.$table.' SET groupname = ? WHERE groupname = ?'
1231 $sth->execute($new->{'groupname'}, $old->{'groupname'})
1232 or die $dbh->errstr;
1235 # apply renames and priority changes to usergroup table
1236 my $sth = $dbh->prepare(
1237 'UPDATE '.$usergroup.' SET groupname = ?, priority = ? WHERE groupname = ?'
1239 $sth->execute($new->{'groupname'}, $new->{'priority'}, $old->{'groupname'})
1240 or die $dbh->errstr;
1243 =item sqlradius_user_disconnect
1245 For a specified user, sends a disconnect request to all nas in the server database.
1247 Accepts L</sqlradius_connect> connection input and the following named parameters:
1249 I<disconnect_ssh> - user@host with access to radclient program (required)
1251 I<svc_acct_username> - the user to be disconnected (required)
1253 I<disconnect_port> - the port (on the nas) to send disconnect requests to (defaults to 1700)
1255 I<disconnect_log> - if true, print disconnect command & output to the error log
1257 Note this is NOT the opposite of sqlradius_connect.
1261 sub sqlradius_user_disconnect {
1262 my $dbh = sqlradius_connect(shift, shift, shift);
1265 my $sth = $dbh->prepare('select nasname, secret from nas') or die $dbh->errstr;
1266 $sth->execute() or die $dbh->errstr;
1267 my $nas = $sth->fetchall_arrayref({});
1270 die "No nas found in radius db" unless @$nas;
1271 # set up ssh connection
1272 eval "use Net::SSH";
1273 my $ssh = Net::OpenSSH->new($opt{'disconnect_ssh'});
1274 die "Couldn't establish SSH connection: " . $ssh->error
1276 # send individual disconnect requests
1277 my $user = $opt{'svc_acct_username'}; #svc_acct username
1278 my $port = $opt{'disconnect_port'} || 1700; #or should we pull this from the db?
1279 foreach my $nas (@$nas) {
1280 my $nasname = $nas->{'nasname'};
1281 my $secret = $nas->{'secret'};
1282 my $command = qq(echo "User-Name=$user" | radclient -r 1 $nasname:$port disconnect '$secret');
1283 my ($output, $errput) = $ssh->capture2($command);
1284 warn $command . "\n" . $output . $errput . $ssh->error . "\n"
1285 if $opt{'disconnect_log'};
1291 # class method to fetch groups/attributes from the sqlradius install on upgrade
1294 sub _upgrade_exporttype {
1295 # do this only if the radius_attr table is empty
1296 local $FS::radius_attr::noexport_hack = 1;
1298 return if qsearch('radius_attr', {});
1300 foreach my $self ($class->all_sqlradius) {
1301 my $error = $self->import_attrs;
1302 die "exportnum ".$self->exportnum.":\n$error\n" if $error;
1309 my $dbh = DBI->connect( map $self->option($_),
1310 qw( datasrc username password ) );
1312 warn "Error connecting to RADIUS server: $DBI::errstr\n";
1316 my $usergroup = $self->option('usergroup') || 'usergroup';
1318 warn "Importing RADIUS groups and attributes from ".$self->option('datasrc').
1321 # map out existing groups and attrs
1324 foreach my $radius_group ( qsearch('radius_group', {}) ) {
1325 $attrs_of{$radius_group->groupname} = +{
1326 map { $_->attrname => $_ } $radius_group->radius_attr
1328 $groupnum_of{$radius_group->groupname} = $radius_group->groupnum;
1331 # get groupnames from radgroupcheck and radgroupreply
1333 SELECT groupname, attribute, op, value, \'C\' FROM radgroupcheck
1335 SELECT groupname, attribute, op, value, \'R\' FROM radgroupreply';
1336 my @fixes; # things that need to be changed on the radius db
1337 foreach my $row ( @{ $dbh->selectall_arrayref($sql) } ) {
1338 my ($groupname, $attrname, $op, $value, $attrtype) = @$row;
1339 warn "$groupname.$attrname\n";
1340 if ( !exists($groupnum_of{$groupname}) ) {
1341 my $radius_group = new FS::radius_group {
1342 'groupname' => $groupname,
1345 $error = $radius_group->insert;
1347 warn "error inserting group $groupname: $error";
1348 next;#don't continue trying to insert the attribute
1350 $attrs_of{$groupname} = {};
1351 $groupnum_of{$groupname} = $radius_group->groupnum;
1354 my $a = $attrs_of{$groupname};
1355 my $old = $a->{$attrname};
1358 if ( $attrtype eq 'R' ) {
1359 # Freeradius tolerates illegal operators in reply attributes. We don't.
1360 if ( !grep ($_ eq $op, FS::radius_attr->ops('R')) ) {
1361 warn "$groupname.$attrname: changing $op to +=\n";
1362 # Make a note to change it in the db
1364 'UPDATE radgroupreply SET op = \'+=\' WHERE groupname = ? AND attribute = ? AND op = ? AND VALUE = ?',
1365 $groupname, $attrname, $op, $value
1367 # and import it correctly.
1372 if ( defined $old ) {
1374 $new = new FS::radius_attr {
1379 $error = $new->replace($old);
1381 warn "error modifying attr $attrname: $error";
1386 $new = new FS::radius_attr {
1387 'groupnum' => $groupnum_of{$groupname},
1388 'attrname' => $attrname,
1389 'attrtype' => $attrtype,
1393 $error = $new->insert;
1395 warn "error inserting attr $attrname: $error" if $error;
1399 $attrs_of{$groupname}->{$attrname} = $new;
1403 my ($sql, @args) = @$_;
1404 my $sth = $dbh->prepare($sql);
1405 $sth->execute(@args) or warn $sth->errstr;
1418 #don't just look for ->can('usage_sessions'), we're sqlradius-specific
1419 # (radiator is supposed to be setup with a radacct table)
1420 #i suppose it would be more slick to look for things that inherit from us..
1422 my @part_export = ();
1423 push @part_export, qsearch('part_export', { 'exporttype' => $_ } )
1424 foreach qw( sqlradius sqlradius_withdomain radiator phone_sqlradius
1425 broadband_sqlradius );
1429 sub all_sqlradius_withaccounting {
1431 grep { ! $_->option('ignore_accounting') } $class->all_sqlradius;