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 );
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} );
771 qw( username realm framedipaddress
772 acctsessiontime acctinputoctets acctoutputoctets
773 callingstationid calledstationid
775 "$str2time acctstarttime ) as acctstarttime",
776 "$str2time acctstoptime ) as acctstoptime",
779 @fields = ( 'username', 'sum(acctsessiontime) as acctsessiontime', 'sum(acctinputoctets) as acctinputoctets',
780 'sum(acctoutputoctets) as acctoutputoctets',
787 my $username = $self->export_username($svc_acct);
788 if ( $username =~ /^([^@]+)\@([^@]+)$/ ) {
789 push @where, '( UserName = ? OR ( UserName = ? AND Realm = ? ) )';
790 push @param, $username, $1, $2;
792 push @where, 'UserName = ?';
793 push @param, $username;
797 if ($self->option('process_single_realm')) {
798 push @where, 'Realm = ?';
799 push @param, $self->option('realm');
803 push @where, ' FramedIPAddress = ?';
807 if ( length($prefix) ) {
808 #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/
809 push @where, " CalledStationID LIKE 'sip:$prefix\%'";
812 my $acctstoptime = '';
813 if ( $opt->{session_status} ne 'open' ) {
815 $acctstoptime .= "$str2time AcctStopTime ) >= ?";
817 $acctstoptime .= ' AND ' if $end;
820 $acctstoptime .= "$str2time AcctStopTime ) <= ?";
824 if ( $opt->{session_status} ne 'closed' ) {
825 if ( $acctstoptime ) {
826 $acctstoptime = "( ( $acctstoptime ) OR AcctStopTime IS NULL )";
828 $acctstoptime = 'AcctStopTime IS NULL';
831 push @where, $acctstoptime;
833 if ( $opt->{starttime_start} ) {
834 push @where, "$str2time AcctStartTime ) >= ?";
835 push @param, $opt->{starttime_start};
837 if ( $opt->{starttime_end} ) {
838 push @where, "$str2time AcctStartTime ) <= ?";
839 push @param, $opt->{starttime_end};
842 my $where = join(' AND ', @where);
843 $where = "WHERE $where" if $where;
846 $groupby = 'GROUP BY username' if $summarize;
848 my $orderby = 'ORDER BY AcctStartTime DESC';
849 $orderby = '' if $summarize;
851 my $sql = 'SELECT '. join(', ', @fields).
852 " FROM radacct $where $groupby $orderby";
855 warn join(',', @param);
857 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
858 $sth->execute(@param) or die $sth->errstr;
860 [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ];
871 my $conf = new FS::Conf;
874 my $dbh = sqlradius_connect( map $self->option($_),
875 qw( datasrc username password ) );
877 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
878 my @fields = qw( radacctid username realm acctsessiontime );
883 my $sth = $dbh->prepare("
884 SELECT RadAcctId, UserName, Realm, AcctSessionTime,
885 $str2time AcctStartTime), $str2time AcctStopTime),
886 AcctInputOctets, AcctOutputOctets
888 WHERE FreesideStatus IS NULL
889 AND AcctStopTime IS NOT NULL
890 ") or die $dbh->errstr;
891 $sth->execute() or die $sth->errstr;
893 while ( my $row = $sth->fetchrow_arrayref ) {
894 my($RadAcctId, $UserName, $Realm, $AcctSessionTime, $AcctStartTime,
895 $AcctStopTime, $AcctInputOctets, $AcctOutputOctets) = @$row;
896 warn "processing record: ".
897 "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s"
900 my $fs_username = $UserName;
902 $fs_username = lc($fs_username) unless $conf->exists('username-uppercase');
904 #my %search = ( 'username' => $fs_username );
907 my $errinfo = "for RADIUS detail RadAcctID $RadAcctId ".
908 "(UserName $UserName, Realm $Realm)";
911 if ( ref($self) =~ /withdomain/ ) { #well, should be a callback to that
916 } elsif ( $fs_username =~ /\@/ ) {
917 ($fs_username, $domain) = split('@', $fs_username);
919 warn 'WARNING: nothing Realm column and no @realm in UserName column '.
920 "$errinfo -- skipping\n" if $DEBUG;
921 $status = 'skipped (no realm)';
924 $extra_sql = " AND '$domain' = ( SELECT domain FROM svc_domain
925 WHERE svc_domain.svcnum = svc_acct.domsvc ) ";
928 my $oldAutoCommit = $FS::UID::AutoCommit; # can't undo side effects, but at
929 local $FS::UID::AutoCommit = 0; # least we can avoid over counting
935 if ( $self->option('process_single_realm')
936 && $self->option('realm') ne $Realm )
938 warn "WARNING: wrong realm $errinfo - skipping\n" if $DEBUG;
941 grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
942 'svcpart' => $_->cust_svc->svcpart,
947 { 'username' => $fs_username },
953 warn "WARNING: no svc_acct record found $errinfo - skipping\n";
954 } elsif ( scalar(@svc_acct) > 1 ) {
955 warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
958 my $svc_acct = $svc_acct[0];
959 warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG;
961 $svc_acct->last_login($AcctStartTime);
962 $svc_acct->last_logout($AcctStopTime);
964 my $session_time = $AcctStopTime;
965 $session_time = $AcctStartTime
966 if $self->option('ignore_long_sessions');
968 my $cust_pkg = $svc_acct->cust_svc->cust_pkg;
969 if ( $cust_pkg && $session_time < ( $cust_pkg->last_bill
970 || $cust_pkg->setup ) ) {
971 $status = 'skipped (too old)';
974 push @st, _try_decrement($svc_acct,'seconds', $AcctSessionTime);
975 push @st, _try_decrement($svc_acct,'upbytes', $AcctInputOctets);
976 push @st, _try_decrement($svc_acct,'downbytes', $AcctOutputOctets);
977 push @st, _try_decrement($svc_acct,'totalbytes', $AcctInputOctets
978 + $AcctOutputOctets);
979 $status=join(' ', @st);
986 warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG;
987 my $psth = $dbh->prepare("UPDATE radacct
988 SET FreesideStatus = ?
990 ) or die $dbh->errstr;
991 $psth->execute($status, $RadAcctId) or die $psth->errstr;
993 $fdbh->commit or die $fdbh->errstr if $oldAutoCommit;
1000 my ($svc_acct, $column, $amount) = @_;
1001 if ( $svc_acct->$column !~ /^$/ ) {
1002 warn " svc_acct.$column found (". $svc_acct->$column.
1003 ") - decrementing\n"
1005 my $method = 'decrement_' . $column;
1006 my $error = $svc_acct->$method($amount);
1007 die $error if $error;
1010 warn " no existing $column value for svc_acct - skipping\n" if $DEBUG;
1015 =item export_nas_insert NAS
1017 =item export_nas_delete NAS
1019 =item export_nas_replace NEW_NAS OLD_NAS
1021 Update the NAS table (allowed RADIUS clients) on the attached RADIUS
1022 server. Currently requires the table to be named 'nas' and to follow
1023 the stock schema (/etc/freeradius/nas.sql).
1027 sub export_nas_insert { shift->export_nas_action('insert', @_); }
1028 sub export_nas_delete { shift->export_nas_action('delete', @_); }
1029 sub export_nas_replace { shift->export_nas_action('replace', @_); }
1031 sub export_nas_action {
1033 my ($action, $new, $old) = @_;
1034 # find the NAS in the target table by its name
1035 my $nasname = ($action eq 'replace') ? $old->nasname : $new->nasname;
1036 my $nasnum = $new->nasnum;
1038 my $err_or_queue = $self->sqlradius_queue('', "nas_$action",
1039 nasname => $nasname,
1042 return $err_or_queue unless ref $err_or_queue;
1046 sub sqlradius_nas_insert {
1047 my $dbh = sqlradius_connect(shift, shift, shift);
1049 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1050 or die "nasnum ".$opt{'nasnum'}.' not found';
1051 # insert actual NULLs where FS::Record has translated to empty strings
1052 my @values = map { length($nas->$_) ? $nas->$_ : undef }
1053 qw( nasname shortname type secret server community description );
1054 my $sth = $dbh->prepare('INSERT INTO nas
1055 (nasname, shortname, type, secret, server, community, description)
1056 VALUES (?, ?, ?, ?, ?, ?, ?)');
1057 $sth->execute(@values) or die $dbh->errstr;
1060 sub sqlradius_nas_delete {
1061 my $dbh = sqlradius_connect(shift, shift, shift);
1063 my $sth = $dbh->prepare('DELETE FROM nas WHERE nasname = ?');
1064 $sth->execute($opt{'nasname'}) or die $dbh->errstr;
1067 sub sqlradius_nas_replace {
1068 my $dbh = sqlradius_connect(shift, shift, shift);
1070 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1071 or die "nasnum ".$opt{'nasnum'}.' not found';
1072 my @values = map {$nas->$_}
1073 qw( nasname shortname type secret server community description );
1074 my $sth = $dbh->prepare('UPDATE nas SET
1075 nasname = ?, shortname = ?, type = ?, secret = ?,
1076 server = ?, community = ?, description = ?
1077 WHERE nasname = ?');
1078 $sth->execute(@values, $opt{'nasname'}) or die $dbh->errstr;
1081 =item export_attr_insert RADIUS_ATTR
1083 =item export_attr_delete RADIUS_ATTR
1085 =item export_attr_replace NEW_RADIUS_ATTR OLD_RADIUS_ATTR
1087 Update the group attribute tables (radgroupcheck and radgroupreply) on
1088 the RADIUS server. In delete and replace actions, the existing records
1089 are identified by the combination of group name and attribute name.
1091 In the special case where attributes are being replaced because a group
1092 name (L<FS::radius_group>->groupname) is changing, the pseudo-field
1093 'groupname' must be set in OLD_RADIUS_ATTR.
1097 # some false laziness with NAS export stuff...
1099 sub export_attr_insert { shift->export_attr_action('insert', @_); }
1101 sub export_attr_delete { shift->export_attr_action('delete', @_); }
1103 sub export_attr_replace { shift->export_attr_action('replace', @_); }
1105 sub export_attr_action {
1107 my ($action, $new, $old) = @_;
1110 if ( $action eq 'delete' ) {
1113 if ( $action eq 'delete' or $action eq 'replace' ) {
1114 # delete based on an exact match
1116 attrname => $old->attrname,
1117 attrtype => $old->attrtype,
1118 groupname => $old->groupname || $old->radius_group->groupname,
1120 value => $old->value,
1122 $err_or_queue = $self->sqlradius_queue('', 'attr_delete', %opt);
1123 return $err_or_queue unless ref $err_or_queue;
1125 # this probably doesn't matter, but just to be safe...
1126 my $jobnum = $err_or_queue->jobnum if $action eq 'replace';
1127 if ( $action eq 'replace' or $action eq 'insert' ) {
1129 attrname => $new->attrname,
1130 attrtype => $new->attrtype,
1131 groupname => $new->radius_group->groupname,
1133 value => $new->value,
1135 $err_or_queue = $self->sqlradius_queue('', 'attr_insert', %opt);
1136 $err_or_queue->depend_insert($jobnum) if $jobnum;
1137 return $err_or_queue unless ref $err_or_queue;
1142 sub sqlradius_attr_insert {
1143 my $dbh = sqlradius_connect(shift, shift, shift);
1147 # make sure $table is completely safe
1148 if ( $opt{'attrtype'} eq 'C' ) {
1149 $table = 'radgroupcheck';
1151 elsif ( $opt{'attrtype'} eq 'R' ) {
1152 $table = 'radgroupreply';
1155 die "unknown attribute type '$opt{attrtype}'";
1158 my @values = @opt{ qw(groupname attrname op value) };
1159 my $sth = $dbh->prepare(
1160 'INSERT INTO '.$table.' (groupname, attribute, op, value) VALUES (?,?,?,?)'
1162 $sth->execute(@values) or die $dbh->errstr;
1165 sub sqlradius_attr_delete {
1166 my $dbh = sqlradius_connect(shift, shift, shift);
1170 if ( $opt{'attrtype'} eq 'C' ) {
1171 $table = 'radgroupcheck';
1173 elsif ( $opt{'attrtype'} eq 'R' ) {
1174 $table = 'radgroupreply';
1177 die "unknown attribute type '".$opt{'attrtype'}."'";
1180 my @values = @opt{ qw(groupname attrname op value) };
1181 my $sth = $dbh->prepare(
1182 'DELETE FROM '.$table.
1183 ' WHERE groupname = ? AND attribute = ? AND op = ? AND value = ?'.
1186 $sth->execute(@values) or die $dbh->errstr;
1189 #sub sqlradius_attr_replace { no longer needed
1191 =item export_group_replace NEW OLD
1193 Replace the L<FS::radius_group> object OLD with NEW. This will change
1194 the group name and priority in all radusergroup records, and the group
1195 name in radgroupcheck and radgroupreply.
1199 sub export_group_replace {
1201 my ($new, $old) = @_;
1202 return '' if $new->groupname eq $old->groupname
1203 and $new->priority == $old->priority;
1205 my $err_or_queue = $self->sqlradius_queue(
1208 ($self->option('usergroup') || 'usergroup'),
1212 return $err_or_queue unless ref $err_or_queue;
1216 sub sqlradius_group_replace {
1217 my $dbh = sqlradius_connect(shift, shift, shift);
1218 my $usergroup = shift;
1219 $usergroup =~ /^(rad)?usergroup$/
1220 or die "bad usergroup table name: $usergroup";
1221 my ($new, $old) = (shift, shift);
1222 # apply renames to check/reply attribute tables
1223 if ( $new->{'groupname'} ne $old->{'groupname'} ) {
1224 foreach my $table (qw(radgroupcheck radgroupreply)) {
1225 my $sth = $dbh->prepare(
1226 'UPDATE '.$table.' SET groupname = ? WHERE groupname = ?'
1228 $sth->execute($new->{'groupname'}, $old->{'groupname'})
1229 or die $dbh->errstr;
1232 # apply renames and priority changes to usergroup table
1233 my $sth = $dbh->prepare(
1234 'UPDATE '.$usergroup.' SET groupname = ?, priority = ? WHERE groupname = ?'
1236 $sth->execute($new->{'groupname'}, $new->{'priority'}, $old->{'groupname'})
1237 or die $dbh->errstr;
1240 =item sqlradius_user_disconnect
1242 For a specified user, sends a disconnect request to all nas in the server database.
1244 Accepts L</sqlradius_connect> connection input and the following named parameters:
1246 I<disconnect_ssh> - user@host with access to radclient program (required)
1248 I<svc_acct_username> - the user to be disconnected (required)
1250 I<disconnect_port> - the port (on the nas) to send disconnect requests to (defaults to 1700)
1252 I<disconnect_log> - if true, print disconnect command & output to the error log
1254 Note this is NOT the opposite of sqlradius_connect.
1258 sub sqlradius_user_disconnect {
1259 my $dbh = sqlradius_connect(shift, shift, shift);
1262 my $sth = $dbh->prepare('select nasname, secret from nas') or die $dbh->errstr;
1263 $sth->execute() or die $dbh->errstr;
1264 my $nas = $sth->fetchall_arrayref({});
1267 die "No nas found in radius db" unless @$nas;
1268 # set up ssh connection
1269 eval "use Net::SSH";
1270 my $ssh = Net::OpenSSH->new($opt{'disconnect_ssh'});
1271 die "Couldn't establish SSH connection: " . $ssh->error
1273 # send individual disconnect requests
1274 my $user = $opt{'svc_acct_username'}; #svc_acct username
1275 my $port = $opt{'disconnect_port'} || 1700; #or should we pull this from the db?
1276 foreach my $nas (@$nas) {
1277 my $nasname = $nas->{'nasname'};
1278 my $secret = $nas->{'secret'};
1279 my $command = qq(echo "User-Name=$user" | radclient -r 1 $nasname:$port disconnect '$secret');
1280 my ($output, $errput) = $ssh->capture2($command);
1281 warn $command . "\n" . $output . $errput . $ssh->error . "\n"
1282 if $opt{'disconnect_log'};
1288 # class method to fetch groups/attributes from the sqlradius install on upgrade
1291 sub _upgrade_exporttype {
1292 # do this only if the radius_attr table is empty
1293 local $FS::radius_attr::noexport_hack = 1;
1295 return if qsearch('radius_attr', {});
1297 foreach my $self ($class->all_sqlradius) {
1298 my $error = $self->import_attrs;
1299 die "exportnum ".$self->exportnum.":\n$error\n" if $error;
1306 my $dbh = DBI->connect( map $self->option($_),
1307 qw( datasrc username password ) );
1309 warn "Error connecting to RADIUS server: $DBI::errstr\n";
1313 my $usergroup = $self->option('usergroup') || 'usergroup';
1315 warn "Importing RADIUS groups and attributes from ".$self->option('datasrc').
1318 # map out existing groups and attrs
1321 foreach my $radius_group ( qsearch('radius_group', {}) ) {
1322 $attrs_of{$radius_group->groupname} = +{
1323 map { $_->attrname => $_ } $radius_group->radius_attr
1325 $groupnum_of{$radius_group->groupname} = $radius_group->groupnum;
1328 # get groupnames from radgroupcheck and radgroupreply
1330 SELECT groupname, attribute, op, value, \'C\' FROM radgroupcheck
1332 SELECT groupname, attribute, op, value, \'R\' FROM radgroupreply';
1333 my @fixes; # things that need to be changed on the radius db
1334 foreach my $row ( @{ $dbh->selectall_arrayref($sql) } ) {
1335 my ($groupname, $attrname, $op, $value, $attrtype) = @$row;
1336 warn "$groupname.$attrname\n";
1337 if ( !exists($groupnum_of{$groupname}) ) {
1338 my $radius_group = new FS::radius_group {
1339 'groupname' => $groupname,
1342 $error = $radius_group->insert;
1344 warn "error inserting group $groupname: $error";
1345 next;#don't continue trying to insert the attribute
1347 $attrs_of{$groupname} = {};
1348 $groupnum_of{$groupname} = $radius_group->groupnum;
1351 my $a = $attrs_of{$groupname};
1352 my $old = $a->{$attrname};
1355 if ( $attrtype eq 'R' ) {
1356 # Freeradius tolerates illegal operators in reply attributes. We don't.
1357 if ( !grep ($_ eq $op, FS::radius_attr->ops('R')) ) {
1358 warn "$groupname.$attrname: changing $op to +=\n";
1359 # Make a note to change it in the db
1361 'UPDATE radgroupreply SET op = \'+=\' WHERE groupname = ? AND attribute = ? AND op = ? AND VALUE = ?',
1362 $groupname, $attrname, $op, $value
1364 # and import it correctly.
1369 if ( defined $old ) {
1371 $new = new FS::radius_attr {
1376 $error = $new->replace($old);
1378 warn "error modifying attr $attrname: $error";
1383 $new = new FS::radius_attr {
1384 'groupnum' => $groupnum_of{$groupname},
1385 'attrname' => $attrname,
1386 'attrtype' => $attrtype,
1390 $error = $new->insert;
1392 warn "error inserting attr $attrname: $error" if $error;
1396 $attrs_of{$groupname}->{$attrname} = $new;
1400 my ($sql, @args) = @$_;
1401 my $sth = $dbh->prepare($sql);
1402 $sth->execute(@args) or warn $sth->errstr;
1415 #don't just look for ->can('usage_sessions'), we're sqlradius-specific
1416 # (radiator is supposed to be setup with a radacct table)
1417 #i suppose it would be more slick to look for things that inherit from us..
1419 my @part_export = ();
1420 push @part_export, qsearch('part_export', { 'exporttype' => $_ } )
1421 foreach qw( sqlradius sqlradius_withdomain radiator phone_sqlradius
1422 broadband_sqlradius );
1426 sub all_sqlradius_withaccounting {
1428 grep { ! $_->option('ignore_accounting') } $class->all_sqlradius;